Exercises (SQL and Normal Forms)

Exercise

Create the students table by source command

You can start with downloading students.sql and uploading it into your public_html directory. Then you can execute MySQL command: source public_html/students.sql to create the following students table as well as students records.

mysql> describe students;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| student_ID         | varchar(30)  | NO   |	 | NULL    |	   |
| first_Name         | varchar(30)  | NO   | 	 | NULL    |	   |
| middle_Name        | varchar(30)  | YES  |	 | NULL	   | 	   |
| last_Name          | varchar(30)  | NO   | 	 | NULL    |	   |
| email              | varchar(100) | NO   |	 | NULL	   | 	   |
| course_Name        | varchar(30)  | NO   | 	 | NULL    |	   |
| course_Description | varchar(255) | NO   |	 | NULL	   |	   |
| marks              | (double) real| NO   | 	 | NULL    |       |
+--------------------+--------------+------+-----+---------+-------+

Preprocessing

The table you created may contain errors or inconsistancy information due to various user inputs, you work is to practising SQL Update and Delete commands and fix these problems.

Basic syntax for update and delete commands are listed as follows:

Update command:

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value;

Delete command:

DELETE FROM table_name
WHERE some_column=some_value;

• A student, Ahmed Addad, has a problem with his email address. Find the problem and use update command to fix it.
• Qaswar also has a problem with his email address.
• Yupeng Xu doesn't have a middle name, but the system put his last name into the middle name column. Try to fix the problem for him.
• The course description of CS215 should be Web Oriented Programming, but some records use a different description for CS215, try to find it out and fix it.
• A student, Tao Ke, is taking a course "CS000" that does not exist. Use delete command to remove this record for her.
• There might be other problems in the students table. If so, try to fix them with update and delete command.

Decomposing the students table

Decompose students table into several sub tables according to the following description:

A students basic information table, which contains five attributes, student ID, first, middle and last name, email address.

A course information table, which includes "course_Name" and "course_Description" which describes a course information.

A student may take several courses. Once we know the student ID number and the course name, we will know the marks they got. Therefore, we need the third table, the students marks table, which contains "student_ID", "course_Name" and "marks".

Inserting records

Following insert...into...select...where... command may help you to extract data from old students table and inert into your new tables.

insert into new_table (column_name_1,column_name2,...column_name_m)                            
select distinct col_name_1, col_name_2,...col_name_m from old_table
where condition(s);

The keyword distinct is optional and it helps to get non-duplicate database records.

How to submit

There are two types of files that you need to submit

1. A .sql file includes all commands that you used (You get 0 marks if you didn't submit this file.)
2. Take a sceenshot of your results (As an option, you can copy the results and paste it into a .txt file):
   • use describe command to display each table you created (except the students table), and take screenshots.