Consider the relation R, which has attributes that hold schedules of course and
ID: 3868178 • Letter: C
Question
Consider the relation R, which has attributes that hold schedules of course and sections at a university: R = {Course_no, Sec_no, Offering_dept, Credit_hours, Course_level, Instructor_ssn, Semester, Year, Days_hours, Room_no, No_of_students}. Suppose that the following functional dependencies hold on R. {Course_no} rightarrow {Offering_dept, Credit_hours, Course_level} {Course_no, Sec_no, Semester, Year} rightarrow {Days_house, Room_no, No_of_students, Instructor_ssn} {Room_no, Days_hours, Semester, Year} rightarrow {Instructor_ssn, Course_no, Sec_no} Try to determine which sets of attributes from keys of R, How would you normalized this relation?Explanation / Answer
There are two keys in relation R.
key : {Room_no, Days_hours, Semester, Year} and {Course_no, Sec_no, Semester, Year}
Considering R is in 1st NF.
--> Converting to 2nd NF:
- Find out partial dependencies : There is no partial dependency.
- Given relation R is already in 2NF.
--> Converting to 3NF :
- Find out transitive dependencies :
{Course_no} -> {Offering_dept, Credit_hours, Course_level} is the only transitive dependency.
- Remove transitive dependency :
To remove transitive dependency, decompose R into two relations R1 and R2 :
R1 : {Course_No, Offering_dept, Credit_hours, Course_level}
FD in R1 : {Course_no} -> {Offering_dept, Credit_hours, Course_level} With key = {Course_No}
R2 : {Course_No, Sec_no, Instructor_ssn, Semester, Year, Days_hours, Room_no, No_of_students}
FD in R2 : {Room_no, Days_hours, Semester, Year} ->{Instructor_ssn, Course_No, Sec_no}
and {Course_no, Sec_no, Semester, Year} -> {Days_hours, Room_no, No_of_students, Instructor_ssn}
Given relation is in 3NF with no Transitive dependencies.
--> Converting to BCNF :
All attributes are functionally dependent on Superkey of respective relation.
Hence R1 and R2 both are in BCNF.
If you have any doubts then you can ask in comment section.