Lab (2) - MySQL Introduction Lab
CS 3430 -  Fall 2004 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 log into the sc machine.  Your login and password on sc is the same as the one 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.  Since this is your first lab, then create a lab1 directory under 3430 and keep all your files for lab1 in lab1 directory.

You can start MySQL by going through Step(1).

Step (1): Getting started from now on
> 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 that for you.  You currently have a 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).  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 |
+-------------+--------------------+
| 3.23.47         | 2002-10-29           |
+-------------+------------------- +
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 "?" ?

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:

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

Creating Tables
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 open a new screen on sc and go to directory 3430/lab1, then create a file 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;

This will generate the table and you will get a message that a table with 0 record was created.  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.  An attempt to execute this file will result to error, since the employee table already exist, you can try it.  Thus, first you have to delete the employee table, then create the new version.  Warning: deleting a table, will result into deletion of all the contents. So be careful when you delete a file that you may loose 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.

Let's create another 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)
);

Use the same procedure to create the following tables:
dept_locations, project, works_on, and dependent.

Try to do this on your own, but if you need a hint, you can look at this file.

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

mysql> describe TableName;

If you want to update (alter) a table you can use the command ALTER.  Example:

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

At the MySQL prompt, type DESCRIBE <table name> to see the schema of each table.

Populating a Table
Exercise:
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:
mysql> insert into employee values
('James', 'E', 'Borg', 888665555, '10-NOV-27', '450 Stone, Houston, TX', 'M', 55000.00, null, 1);

You can add more tuples by repeating this command.  You also can include a set of commands in a file and run it to populate the table.  Do you know how?

Exercise:
Try to add one tuple (the first line of each table) from the tables on figure 7.6.  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 a table, you can view the contents by typing a simple query:

mysql> select * from TableName;

Homework - Due Friday November 12
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.

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 (15) on Page 260 of Chapter 8.   Save the queries and the results in a file and print that file and submit it for this part.