CST 221, Instructor: Zhen Jiang Final Project

CST 221, Instructor: Zhen Jiang Final Project

Part 1 (79 pts), submit SQL program in word file via D2L (in time).

Consider the following database for students who take general education courses from different department.

Q1, 5 pts) List each department and its students (with student names)

Q2, 7 pts) List each student number, with the number of courses that this student selects.

Q3, 12 pts) List each student name, with the number of courses that he/she selects.

Q4, 11 pts) List each department name with the number of award student candidates (who has GPA > 3).

Q5, 14 pts) List the names of students who haven’t take any of the above courses.

Q6, 8 pts) List each faculty’s number and his/her courses (with the course name).

Q7, 22 pts) List the departments (with the names) that have the most students.

Part 2 (21 pts) Submit your answers of the following questions in the word file via D2L

Question 1 (9 pts)

Given the follow table T( A, B, C, D, E)

Suppose we have the following dependencies: A+B---> C; A--> D; A--->E; D--> E

a) What is the primary key?

b) Convert to set of tables in 2nd N.F.

c) Convert to set of tables in 3rd N.F.

Question 2 (12 pts)

Given the follow table T( A, B, C, D, E)

Suppose we have the following dependencies: A+B---> C; A--> D; C--->D;

a) What is the primary key?

b) Convert to set of tables in 2nd N.F.

c) Convert to set of tables in 3rd N.F.

Question 3 (Bonus)

Consider the following table and dependencies T ( A, B, C); and A--> B, C; B--->A, C;

a) What is the primary key?

b) Convert to set of tables in 2nd N.F.

c) Convert to set of tables in 3rd N.F.

Page 2 of 2