CSC455 Ch. 5 Lab 1 Getting Started in MySQL (55 points)

1. Using a terminal client such as PuTTY or SSH, log onto: webdev.cislabs.uncw.edu with your normal uncw email username and password. Note that nothing will display as you type your password – keep typing and press Enter.
2. If this is the first time you have logged in, a mysql database and user will be generated, and a password will be mailed to you. Check your UNCW e-mail to find that password.
3. Start the mySql client as: mysql -u your_username –p
4. Enter your database password which defaults to your uncw username. See step 6 below to change it. Each student has a separate database account, so your database will not conflict with other student databases.

5. Strings can be specified using either single or double quotes. Each sql statement is terminated by a semi-colon.

6. You can change your mySql password with the following command:

set password = password(“new_password”);

Note that your babbage/webdev password is a separate password from your mySql password. You may set them to match if you choose.

7. The database you will use has the same name as your userid. Connect to your database by typing use userid;
8. In this lab, you will create the University database that is discussed in the text.
9. Use the create table command to create the Student table as follows:

CREATE TABLE IF NOT EXISTS Student (

stuId varchar(6),

lastName varchar(20) NOT NULL,

firstName varchar(20) NOT NULL,

major varchar(10),

credits integer(3) DEFAULT 0,

CONSTRAINT Student_stuId_pk PRIMARY KEY (stuId),

CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 150))) ENGINE = INNODB;

The command will execute and you should see the message : Query OK 0 rows affected

10. To see a list of the tables in the database, key in the following command at the mysql prompt> show tables;
11. To see the structure of the Student table, key in the command> describe Student;
12. Create the three remaining tables:

CREATE TABLE IF NOT EXISTS Faculty (

facId varchar(6),

name varchar(20) NOT NULL,

department varchar(20),

rank varchar(10),

CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId)) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS Class (

classNumber varchar(8),

facId varchar(6) NOT NULL,

schedule varchar(8),

room varchar(6),

CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber),

CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId),

CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room));

CREATE TABLE IF NOT EXISTS Enroll (

stuId varchar(6),

classNumber varchar(8),

grade varchar(2),

CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId),

CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class (classNumber) ON DELETE CASCADE,

CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student (stuId) ON DELETE CASCADE);

13. Use show tables; and describe xxx; to check your work.
14. Change the Class and Enroll tables to use INNODB:

mysql>alter table Class engine=INNODB;

and repeat for the Enroll table.

15. To make sure your work is saved, key in> commit;
16. Print that page to hand in or take a screen shot (PrintScreen) copy to Word, paste, and print.
17. Quit mySql by typing \q or exit and log out from the server.