Boyce-Codd Normal Form

• Database relation in 3rd normal form have neither partial functional dependencies or transitive dependencies.
• The reason for this is that such dependencies can lead to update anomalies.
• However the rules for 1NF , 2NF, and 3NF do not take into account whether such dependencies remain on other candidate keys of a relation.
• Boyce-Codd Normal Form(BCNF) is based on functional dependencies that take into account all candidate keys in a relation.
• Every determinant in table is a candidate key
• When table contains only one candidate key, the 3NF and the BCNF are equivalent
• BCNF can be violated only when table contains more than one candidate key.
• 
  

A table is in Boyce-Codd normal form if It is in 3NF Every determinant in the table is a candidate key.

3NF without BCNF

When happens if a nonkey attribute is the determinant of a key attribute. It does not violate 3NF, yet fails to meet the BCNF requirements.

STU_ID	STAFF_ID	CLASS_CODE	ENROLL_GRADE
125	25	21334	A
125	20	32456	C
135	20	28458	B
135	25	27563	C
144	20	32456	B

• Each Class_Code identifies a class uniquely.
• A student can take many classes. 
• Note: Student 125 has taken both 21334 and 32456 earning the grades of A and C.
• A staff member can teach many classes, but each class is tough by only one staff
• Note:Staff member 20 teaches the classes 32456 and 28458. 

• Panel A is 3NF. It has the problem of trying to describe two things. If a different member is assigned to teach class 32456 two rows must be updated. Also if student 135 drops out we lose data on who teaches the class. 
• The solution is Panel B where we decompose the table into two new.