In the relation scheme given below, assume a student can take the same course mo
ID: 3684493 • Letter: I
Question
In the relation scheme given below, assume a student can take the same course more than once each in a differemt term (of the same year or different year).
Stud(S#, Sname, C#, Cname, Credit, Year, Term, Grade).
a) In the context of a "good database design", state whether this relation scheme is efficient and justify your answer.
b) Identify the main functional dependencies (FDs) satisfied in this scheme. (Make the right-side of every FD a single attribute.)
c) Identify any (minimal) key for this scheme.
d) Is this a BCNF scheme?
e) If your answer to part(d) is No, find a BCNF decomposition for this scheme.
Explanation / Answer
Stud(S#, Sname, C#, Cname, Credit, Year, Term, Grade) - for the entity Stud , S#, Sname, C#, Cname, Credit, Year, Term, Grade are the attributes. A student is recognised by student_id i.e primary key
you will have to have two tables
1. store student information - [student no (s#) - primary key, student name (Sname) , Course_id (C#) foreign key)
2. course table with fields [ course no (C#) - primary key,course name(Cname), credit, Year, Term, Grade]
a) Bad design . if the same course is repeated the table entries are overwritten or may end up with duplicate records.
b) student id(S#) is functionally dependent on course id(C#)
c)if course does not have the course no as primary key, then <course name + year/term> can be chosen as minimal key.
d) need not do BCNF, because there are no composite candidate key.
e)course table has to have the following fields [course name(Cname), credit, Year, Term, Grade] and <course name + year/term> is composite key.