Question

How do we know the following relation with the following dependencies is BCNF?

course ( course_id , title , dept_name , credits )

Functional Dependencies

course_id → title , dept_name , credits

building , room_number → capacity

course_id , sec_id , semester , year → building , room_number , time_slot_id

Choose what makes the statement BCNF and why:

		dept_name is a superkey
		course_id, dept_name is a superkey
		course_id is a candidate key
		course_id is a superkey

Answer 1

The correct answer is option no 4,course_id is a superkey.

Let's explain the answer-

The database Course(courseid,title,dept_name,credits) has its own functional dependencies.

Now What is BCNF(Boyce-Codd Normal Form)?

From the very clear definition 1)A relation has to be in 3NF.2)The superkey will be in the derivative element in the functional dependency.

Now ,for 2NF partial dependency is not allowed.MEANS PRIME ATTRIBUTE TO NON PRIME ATTRIBUTE FUNCTIONAL DEPENDENCY ARE NOT WELCOMED.

for 3NF transitive dependency is not allowed .MEANS NONPRIME ATTRIBUTE TO NONPRIME ATTRIBUTE FUNCTIONAL DEPENDENCY GETS REJECTED.

NOW FOR BCNF,if any superkey stays on the left side of the derivation it's allowed.So,take the easy solution,FROM FUNCTIONAL DEPENDENCY WE JUST NEED TO CHECK IF ALL THE FUNCTIONAL DEPENDENCY HAS SUPERKEY IN THE LEFT SIDE.

now coming to this problem

fd1:Course_id->title,dept_name,credits BCNF IF LEFT IS SUPERKEY,Course_id is Superkey

fd2:

building , room_number → capacity ,NOT IDENTIFIED IN THE DATABASE SO THIS DEPENDENCY GETS REJECTED.

fd3:course_id , sec_id , semester , year → building , room_number , time_slot_id,Here in the left we have Course_id which has to be a superkey and then concatenating more attributes with superkey makes the total left side superkey.

so fd3 also superkey is in the left.

so ,to stay in BCNF left side has to be a superkey and left side we have course_id which has to be a superkey.