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.
Answer:
(a) Complete the ER Diagram for the Student, Assignment, and Discussion
entities by underlining the primary keys, connecting the given entity and relationship sets, and indicating the cardinality and participation constraints.
(b) Add proper notation for the weak entity which is missing in the given ER diagram.
(c) Make the recursive partner relationship.
CS3402
Database Systems Tutorials
1
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}}.
(a) Proof {RN,DH,SM,Y }→CL holds by using the inference rules.
Answer: {RN,DH,SM,Y }→{IN,CN,SN} (Given) (1)
From (1), we have {RN,DH,SM,Y }→CN (decomposition rule) (2)
CN → {OD, CR, CL} (Given) (3)
From (2)(3), we have {RN,DH,SM,Y }→{OD, CR, CL} (transitive rule) (4) Form (4), we have {RN,DH,SM,Y }→ CL (decomposition rule)
(b) Using closure of attribute to find all candidate keys of this database?
CS3402 Database Systems Tutorials
Answer: Both K1 = { CN,SN,SM,Y } and K2 = { RN,DH,SM,Y } are (candidate) keys of R, because { CN,SN,SM,Y }+={ CN,SN,SM,Y, RN,DH,SM,Y, OD, CR, CL } and { RN,DH,SM,Y }+={ CN,SN,SM,Y, RN,DH,SM,Y, OD, CR, CL }, and removing any attribute from K1 or K2, its closure cannot cover all attributes.
(c) Normalize this database into BCNF?
Answer: Applying the general definition of 2NF, we find that the functional dependency {CN} →{OD, CR, CL} is a partial dependency for K1 (since C is included in K1). Hence, R is
normalized into R1 and R2 as follows:
R1 = {CN, OD, CR, CL}
R2 = {RN, DH, SM, Y, IN, CN, SN, NS} (or R2 = {RN, DH, SM, Y, IN, CN, SN, NS}) with candidate keys K1 and K2
Since neither R1 nor R2 have transitive dependencies on either of the candidate keys, R1 and R2 are in 3NF also. They also both satisfy the definition of BCNF.