A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key. 1.
Anomalies can occur in relations in 3NF if there is a composite key in which part of that key has a determinant which is not itself a candidate key. 2. This can be expressed as R(A,B,C), C--->A where: o The relation R contains attributes A, B and C. o A and B form a candidate key. o C is the determinant for A (A is functionally dependent on C). o C is not part of any key. 3. Anomalies can also occur where a relation contains several candidate keys where: o The keys contain more than one attribute (they are composite keys). o An attribute is common to more than one key.
Example to understand BCNF:Take the following table: room/bldg
campus
course
class
time
East
English 101
8:00-9:00
212 AYE
East West
English 101 English 101
2 3
10:00-11:00 8:00-9:00
305 RFK
102 PPR
Note that no two buildings on any of the university campuses have the same name, thus ROOM/BLDG----->CAMPUS. As the determinant is not a candidate key this table is NOT in Boyce-Codd normal form. This table should be decomposed into the following relations:
R1(course, class, room/bldg, time) DTABASE TUTORIAL HOME A table is in Boyce-Codd normal form (BCNF) if and only if it is in 3NF and every determinant is a candidate key. 1. Anomalies can occur in relations in 3NF if there is a composite key in which part of that key has a determinant which is not itself a candidate key. 2. This can be expressed as R(A,B,C), C--->A where: o The relation R contains attributes A, B and C. o A and B form a candidate key. o C is the determinant for A (A is functionally dependent on C). o C is not part of any key. 3. Anomalies can also occur where a relation contains several candidate keys where: o The keys contain more than one attribute (they are composite keys). o An attribute is common to more than one key. Example to understand BCNF:Take the following table: campus East course English 101 class 1 time 8:00-9:00 room/bldg 212 AYE
English 101 East
10:00-11:00
305 RFK
West
English 101
8:00-9:00
102 PPR
Note that no two buildings on any of the university campuses have the same name, thus ROOM/BLDG----->CAMPUS. As the determinant is not a candidate key this table is NOT in Boyce-Codd normal form. This table should be decomposed into the following relations: R1(course, class, room/bldg, time)
room/bldg course class time
English 101
1 2 3
8:00-9:00
212 AYE
English 101 English 101
10:00-11:00 8:00-9:00
305 RFK 102 PPR
R2(room/bldg, campus) room/bldg campus East East West 212 AYE 305 RFK 102 PPR
