BOYCE - CODD NORMAL FORM (BCNF):
The Third Normal Form (3NF) provides no problems for relations that have single candidate key (primary key). When a relation has more than one candidate key, and candidate keys are composite then overlapping anomalies may occur. Therefore, an improved and best form of Third Normal Form (3NF) is named as Boyce-Codd Normal Form (BCNF). It was developed by Boyce and Codd. The Boyce-Codd Normal Form is defined as: a relation is in Boyce-Codd Normal Form if and only if every determinant is a candidate key.
If a relation has only one candidate key then the 3NF and BCNF are equivalent. Therefore, to check for BCNF, we simply identify all the determinants and make sure that they are candidate keys. However, all relations that are BCNF are also 3NF. For example, 'Class' relation is given below with attributes Student-ID, Subject and Instructor.
In the 'Class' relation, no single attribute is a candidate key. In other words, no single attribute is a determinant for the remaining two attributes. The following may be the candidate keys.
(Student-ID, Subject) is one candidate key. (Student-ID, Instructor) is another candidate key.Suppose we select (Student-ID, Subject) as a primary key for the 'Class' relation. It is represented as:Class (Student-ID, Subject, Instructor)The above relation is in 3NF. It has no partial functional dependencies and transitive dependencies. However, there are still anomalies in this relation.
The Third Normal Form (3NF) provides no problems for relations that have single candidate key (primary key). When a relation has more than one candidate key, and candidate keys are composite then overlapping anomalies may occur. Therefore, an improved and best form of Third Normal Form (3NF) is named as Boyce-Codd Normal Form (BCNF). It was developed by Boyce and Codd. The Boyce-Codd Normal Form is defined as: a relation is in Boyce-Codd Normal Form if and only if every determinant is a candidate key.
If a relation has only one candidate key then the 3NF and BCNF are equivalent. Therefore, to check for BCNF, we simply identify all the determinants and make sure that they are candidate keys. However, all relations that are BCNF are also 3NF. For example, 'Class' relation is given below with attributes Student-ID, Subject and Instructor.
In the 'Class' relation, no single attribute is a candidate key. In other words, no single attribute is a determinant for the remaining two attributes. The following may be the candidate keys.
(Student-ID, Subject) is one candidate key. (Student-ID, Instructor) is another candidate key.Suppose we select (Student-ID, Subject) as a primary key for the 'Class' relation. It is represented as:Class (Student-ID, Subject, Instructor)The above relation is in 3NF. It has no partial functional dependencies and transitive dependencies. However, there are still anomalies in this relation.
