CS3402 Database Systems Tutorials
CS3402 Tutorial 5:
1. We are going to design a database for the CS3402 course to keep track the information about students who are taking this course.
In this course, each student can be assigned to one optional group discussion section with more than one participant. Each group discussion section has a unique course number (cn) and is hosted by a TA.
Students will also do some assignments with different weights. For each assignment, we want to store the number of days the student has spent and the points the student earned on the assignment. If a student does not turn in an assignment, he/she will be recorded in the database with zero points. Assume there are many assignments, and all assignments are compulsory.
The staff decides all students in this course should work in teams of two. Each team should have one student as the lead. Assume that the number of students is even.
To implement the database, the staff plans to draw an ER diagram and has already draw some parts of it. Please complete the ER diagram.
2. Suppose we have a relational database table R for schedules of courses and sections at a university, consisting of the following attributes:
CN(CourseNo), SN(SectionNo), OD(OfferingDept), CR(Credit), CL(CourseLevel), IN(InstructorName), SM(Semster), Y(Year), DH(DayHour), RN(RoomNo), NS(NumbersOfStudents) with the following functional dependencies:
{CN → {OD, CR, CL}, {CN,SN,SM,Y} →{DH,RN,NS,IN},{RN,DH,SM,Y}→{IN,CN,SN}}.
CS3402 Database Systems Tutorials
(a) Proof {RN,DH,SM,Y }→CL holds by using the inference rules.
(b) Using closure of attribute to find all candidate keys of this database? (c) Normalize this database into BCNF?