Goal: To get started working with MySQL RDBMS by creating a simple and small relational database.

Objectives: After successfully completing the lab, one should be able to

create and populate a small relational database by using some basic MySQL SQL (Structured Query Language) commands, specifically CREATE TABLE ...; INSERT INTO ...; DROP TABLE ...,

use SELECT commands to query the small database.

We are going to create and populate a database with two tables.

The first table will be called Faculty and has two columns:

f_id (faculty id number)

f_name (faculty name).

The f_id column should be an auto-increment unsigned integer field and the f_name column should be a varchar(100) field. Since both columns are required to be filled in, we want them to be declared NOT NULL. Also as discussed in lecture, the f_id field is the primary key.The second table will be called Students and has four columns:

s_id (student id number)

s_name (student name)

s_class (class code: 'Fr', 'So', 'Ju', 'Se')

s_advfid (advisor's faculty id, f_id).

s_id is the usual auto-increment unsigned integer, s_name can be varchar(100), and s_class should be char(2) as it will contain 'Fr', 'So', 'Ju', 'Se'. Since s_advfid will contain an f_id number from the Faculty table, it should be the same type. The s_id, s_name, s_class, s_advfid should all be NOT NULL meaning that the database will require them to be filled in.

Create a MySQL script called lab07.mysql with notepad. The script will contain MySQL commands to create and populate the database.

It is a good idea to put the following commands at the top of the script in case you need to run the script more than once:

 DROP TABLE IF EXISTS Students; DROP TABLE IF EXISTS Faculty; 

They will erase any previous tables with those names so the script will be able to create new ones.Next should come the CREATE TABLE commands to create the two tables. Here is the CREATE TABLE command for the Faculty table:

 CREATE TABLE Faculty (f_id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, f_name VARCHAR(100) NOT NULL); 

You must write the CREATE TABLE command to create the Students table and put it in the script.

Next we need to add some records to the database. The INSERT INTO command is needed. Here is an example to insert a row into the Faculty table:

 INSERT INTO Faculty SET f_name='Hatfield,Bo'; 

Also enter similar INSERT INTOs for 'Kaur,Komalpreet' and 'Kasprzyk,Joseph'.Finally we need to add some records to the Students table:

 INSERT INTO Students SET s_name='Adeyeri,Olalekan',s_class='Ju',s_advfid=2; INSERT INTO Students SET s_name='Cid,Jose',s_class='Ju',s_advfid=3; INSERT INTO Students SET s_name='Alhayek,Ahmad',s_class='Se',s_advfid=1; INSERT INTO Students SET s_name='Demiri,Jani',s_class='Se',s_advfid=3; INSERT INTO Students SET s_name='Castro,Dionis',s_class='Ju',s_advfid=1; INSERT INTO Students SET s_name='Bondarev,Pavel',s_class='So',s_advfid=2; 

Use Filezilla or similar to copy your script to weblab. You should put it in the top-level directory (not public_html) as it is not a webpage and you don't want the world to be able to browse it. Then use PuTTY or similar to log into weblab and execute the script like this (filling in your Salem State ID for S1234567):

 weblab$ mysql -p S1234567 mysql> source lab07.mysql 

You will have to type in your password. If the script gives you error messages, you will have to fix the script then re-run it by typing in the source command again.

Hint: You really don't need to re-type the source command each time to re-run your script. Just leave PuTTY open with the mysql> prompt showing and press the up-arrow key to reload the source command. Then press Enter to re-execute it.

Now we are going to do some SELECT commands to display some values. You can type them directly into the mysql> prompt on weblab. When you have each one working, cut and paste it (just the SELECT command, not the output) to the bottom of the lab07.mysql script file in notepad.

Note: The last 3 queries require a join. You can use either method shown in lecture.

List full contents of Faculty table.

List full contents of Students table.

List all Prof. Bo Hatfield's advisees in the database in ascending order of their name. Show only the student's name and the class code.

List all students in the database in ascending order of their name. Show only the student's name, their class code and the advisor's name.

List all senior students in the database in ascending order of their name. Show only the student's name and the advisor's name.

List all Prof. Bo Hatfield's advisees and Prof. Joe Kasprzyk's advisees in the database as a single query, in ascending order of their name. Show only the student's name and the advisor's name.

A template for the SELECT command is like this:

 SELECT columns-to-display FROM table(s) WHERE filtering ORDER BY sorting; SELECT s_name FROM Students WHERE s_name LIKE 'A%' ORDER BY s_name;