Lab (2) - MySQL Introduction Lab
CS 3430 -  Fall 2005 DBMS

Objectives
Objectives of this lab are to learn:
    Some basics about MySQL
    to create a database (this is actually done by the system administrator)
    to create tables in the database
    to load data into the table
    to retrieve data from the table in various ways

Introduction
To run MySQL, you need to use putty to log into the student machine.  Your login and password on student is the same as that on cs. Once you are logged into that machine, change your working directory to directory 3430.  If you don't have that directory, create it and work in that directory.  Create a lab2 directory under 3430 and keep all your files for this lab in lab2 directory.   It is best to have two putty screens, so in one you could type your scripts and in another you could run MySQL.

Getting started from now on
One you are on student machine, type (make sure to replace YourID with yours):
> mysql  -user  YourID  -p
Enter password:     (Your password is your SSN, the first time)

If you log into MySQL successfully, you will get a display that looks like the following:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1773 to server version: 3.23.47

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

In order to have a database in place, one needs to go through some steps.  The first thing is to create a database.  We have done this for you.  You currently have an  empty database with your user ID that you will use throughout this semester. You can create tables in this database.  Now, that your database is in place, you need to create the relations (tables) in it.  These tables are empty to begin with (stage 0 of your relation), thus you will populate (insert data) into them.  In addition to populating a table, sometimes, you may want to delete or update a table.

Now you should have the MySQL prompt, mysql >.  Let's try a very simple query:

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------------+--------------------+
| VERSION( )        CURRENT_DATE |
+-----------------+--------------------+
| 4.1.12-standard  | 2005-11-03            |
+-----------------+--------------------+
1 row in set (0.00 sec)

This query illustrates several things about mysql.  Here are some quick notes:
    1) A command normally consists of a SQL statement followed by a ";".
    2) After issuing a command, mysql sends it to the server for execution and displays the result, then gets you the mysql> prompt.
    3) mysql displays query output as a table (rows or columns).
    4) mysql shows how many rows were returned and how long the query took to execute.

Here is a good one.  If you want to find out who the user of current database is type:

mysql> SELECT USER( );
+-------------------+
| user()                     |
+-------------------+
| ?
+-------------------+
1 row in set (0.00 sec)

What did you see in place of "?" ?  We hope that is your ID :-). 

To see the list of databases on that machine, you can type:
mysql> SHOW databases;

Is your database (a database with your ID) among them?

To use your database in this lab, you will type (make sure to replace YourID with yours):

mysql> USE YourID;
Database changed

To leave MySQL at any point of time, you can type:
mysql> quit  (note no ";" is needed)

let's quit and go back to a particular database using a different method.

% mysql -u YourID -p  DatabaseName

This will take you (YourID) to a database that you have indicated as DatabaseName. In our case today, this command should look like:
% mysql -u YourID -p YourID

Activity (1) - Creating a Table, Altering a Table, and Re-creating a Table
In this lab, we will create all the tables for the COMPANY database described on Figure (7.5).  The following is the list of commands you need to type to create the employee table.  Also, you can type them in a file and execute the file from mysql prompt.  So use the second screen and go to directory 3430/lab2, then create a file employee (vim employee) and copy the instructions given below in that file.

CREATE TABLE employee (
  fname varchar(20),
  minit char,
  lname varchar(20),
  ssn varchar(9) NOT NULL,
  bdate DATE,
  address varchar(50),
  sex char(1),
  salary double(7,2),
  superssn varchar(9),
  dno varchar(2),
 CONSTRAINT employee_ssn_pk PRIMARY KEY (ssn),
 CONSTRAINT employee_superssn_fk FOREIGN KEY (superssn) REFERENCES employee(ssn)
);

Go through the above code line-by-line and make sure you understand what each line is doing.

To execute this file, switch back to the mysql> screen and type:

mysql> source employee;

To see the description of this table, type;
mysql> describe employee;

This will generate the table and you will get a message that a table with 0 record was created.  If you want to update (alter) a table you can use the command ALTER.  For example if you wish to alter the employee table to include the DNO as a foreign key, at the mysql command prompt you could type:

ALTER TABLE employee ADD (
   CONSTRAINT employee_dno_fk FOREIGN KEY (dno) REFERENCES department(dnumber)
);

Try it, and then see the description of the file once again to see whether there was any change.

Re-creating a Table
As you may have noticed except for the SSN, we don't have NOT Null for any other attribute. We wish to modify the employee file to include NOT NULL for attributes that they require a value.  Add the NOT NULL for attributes that require one.  Now you have a new version of employee file, and you wish to create the employee that is more accurate.  Since the employee table already exist, an attempt to recreate it will result in an error.  Thus, first you have to delete the employee table, then create the new version.  Warning: deleting a table, will result in deletion of the contents of that table. So be careful when you delete a table; you may lose all your data.  To delete a table you will use:

mysql> drop table employee;

Once this is done, then you can execute the file employee to create the new employee table.

Creating a another Table
Now try to create the department table.

CREATE TABLE department (
  dname varchar(20),
  dnumber int(2) NOT NULL,
  mgrssn int(9),
  mgrstartdate date,
  CONSTRAINT department_dnumber_pk PRIMARY KEY (dnumber),
  CONSTRAINT department_dname_uk UNIQUE (dname),
  CONSTRAINT department_mgrssn_fk FOREIGN KEY (mgrssn) REFERENCES employee(ssn)
);

At the mysql prompt type:
mysql> show tables;

This should show both tables. 

Activity (2) - Creating all tables at once
Create the rest of tables for the COMPANY database, which are:
dept_locations, project, works_on, and dependent.

You can create each table one-by-one or you can write a script to create all the tables at once.  Try to do this on your own, but if you need a hint, you can look at this fileImportant note: If you attempt to create a table that already exist, you will get an error.

After creating each table, try to see the description of that table (catalog) by typing:

mysql> describe TableName;

Activity (3) - Populating a Table
Write the command to insert James E. Borg with SSN = 888665555 into the EMPLOYEE table.  James was born on Nov. 10, 1927.  His address is Houston, TX, he makes $55000/year and is the president of the department 1.

The following command will create one tuple in the EMPLOYEE table:
insert into employee values
   ('James', 'E', 'Borg', 888665555, '10-NOV-27', '450 Stone, Houston, TX', 'M', 55000.00, null, 1);

To see whether this record in your table, you can run this query:

select  *
from employee;

You can add more tuples in the employee table by repeating this command.  You also can include a set of commands in a file and run it to populate the table all at once.  Try to do this on your own, but if you need a hint, you can see this file. Note that if a tuple already exist in a table, an attempt to insert it again, will cause error.

After you populate this table, you can view the contents by typing a simple query:

mysql> select * from employee;

to view the entire contents of the table.  Make sure all the records are correct.

Post-lab - Due Wednesday November 9
Create a file and copy the results of this postlab in it. Then e-mail me ONE file that contains the answer to both parts below.

1) Populate the COMPANY database by inserting all the values shown in figure 7.6 (Page 205 of the text) into each table. Once you populate all tables, make a list of the contents of each table and save the result in a file.  You need to email me that file

2) Write and run Query (2) on Page 254, Query (8) on Page 255, and Query (4) on Page 258 of Chapter 8.   Save the queries and the results in your file and e-mail me the file as an attachment.