14.28. Consider the relation R, which has attributes that hold schedules of courses 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} → {Offering_dept, Credit_hours, Course_level} {Course_no, Sec_no, Semester, Year} → {Days_hours, Room_no, No_of_students, Instructor_ssn} {Room_no, Days_hours, Semester, Year} → {Instructor_ssn, Course_no, Sec_no} Try to determine which sets of attributes form keys of R. How would you normalize this relation?

Check the explanation

Explanation:

Let us first of all make use the following shorthand notation:

C = the CourseNo,

SN = the SecNo,

OD = the OfferingDept,

CH = the CreditHours,

CL = for CourseLevel,

I = InstructorSSN,

S = Semester,

Y = Year,

D = the Days_Hours,

RM = RoomNo,

NS = NoOfStudents

Hence R = {C, SN, OD, CH, CL, I, S, Y, D, RM, NS}, and the following functional reliance holds –

Now {C} ―  {OD, CH, CL}

Then {C, SN, S, Y} ―  {D, RM, NS, I}

{RM, D, S, Y} ―  {I, C, SN}

Now let’s compute the closure for every left hand part of a functional dependency, given that these sets of attributable elements are the candidates to be keys:

{C}+ equals to {C, OD, CH, CL}

Since {C, SN, S, Y} ―  {D, RM, NS, I}, and

{C}+ = {C, OD, CH, CL} we get:

{C, SN, S, Y}+ equals to {C, SN, S, Y, D, RM, NS, I, OD, CH, CL} = R

Since {RM, D, S, Y} ―  {I, C, SN}, we all know that {RM, S, D, Y}+ contains {RM, D, S, Y, I, C, SN}.

But {C}+ contains {OD, CH, CL}, so these are also controlled in {RM, D, S, Y}+ since C is already there.

{RM, D, S, Y}+ equals to {RM, D, S, Y, I, C, SN, OD, CH, CL, NS} = R

Hence, both K1 = {C, SN, S, Y} and

K2 = {RM, D, S, Y}

When applying the universal explanation of 2NF,

We will discover that the practical dependency {C}―  {OD, CH, CL} is an incomplete dependency for K1 (since C is included in K1).

Hence, R is normalized into R1 and R2as follows:

R1 = {C, OD, CH, CL}

R2 = {RM, D, S, Y, I, C, SN, NS} with candidate keys K1 and K2.

given that neither R1 nor R2 have transitive dependencies on any of the candidate keys,

R1 and R2 are in 3NF form also.

In addition to this, they also satisfy and gratify the BCNF form.