Consider the relation R, which has attributes that hold schedules of course and

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.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.