Lab (1) - MySQL Introduction Lab
CS 3430  

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
This lab won't take much time to complete, but you need to read the steps carefully. I have provided you two major scripts, 1) to create all the tables, and 2) to insert data into those tables. However, you to learn how each table is defined, I have asked you to create one single table first and see how all the attributes and relations are defined. NOTE: Your login ID and dataabse name is the same as your ASU e-mail (ex. mine is tashakkorir).

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 lab1 directory under 3430 and keep all your files for this lab in lab1 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
Once you are on student machine, type (make sure to replace YourID with your CS ID, this is the default database we have created for you already):
> mysql  -p
Enter password:     (Your password is your Banner ID, 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;

This should show at least two databases; your individual database and the team database. To go to a database, you will use the command use. 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 can type line-by-line at the command prompt to create the employee table.  This may not be the best idea, so we will create a file that contains all these lines, and use that file to create the table. In order to create a file that containes this script, use the second screen and go to the directory 3430/lab1, then use an editor to create a file employee (vim employee) and copy the instructions given below in that file. There are at least two attributes for which you have to correct the data type (hint: ssn related, 9 characters, and it is fixed size)

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 PRIMARY KEY (ssn),
 CONSTRAINT 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.  For this command to work correctly, you must have executed the mysql login from the same directory where your file is.

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

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

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. Are there any data type you want to change?

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

At the mysql prompt type:
mysql> show tables;

This should show both tables. 

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 the following line (Please note you will get an error if you try this as the department table is not yet created):

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

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 exists, you will get an error, thus you have to delete the existing table first. I strongly recommend that you drop the first two tables you had created, include the script that create them in this file, and then execute one file to create all the tables. I also recommend that you create one file that contains all the drops. This way if you need to drop all the tables, you can exceute the file to drop them all and then execute one file to create all the tables. You can call the file that create tables something like createCompany and the script that drops them, something like dropCompany.

After creating a 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, '1927-11-10', '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
For these two problems, you do not need to send me anything, I can check your database online.
Problem 1 - Complete the lab. I have to see all your tables with the correct number of records in each of the tables. For this problem you do not need to submit anything as I see the tables on your MySQL database. Make sure all tables are completed and populated with the data given in the book.
Problem 2 - Populate the COMPANY database by inserting all the values shown in figure 3.6 (Page 72 of the text) into each table. If you have already populated the table using the script given above, make sure you have the correct data. Once you populated all ithe tables, at the MySQL command prompt make a list of all the tables in your database and save the result in a file. Please submit that file (screenshot) containing the list of all the tables you have obtained from MySQL on ASULearn. I am sure you know by now what command to use to show tables (hint hint).