{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

BCNF Notes - Conversion to BCNF To convert this table to...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Boyce-Codd Normal Form (BCNF) A table is in BCNF when every determinant in the table is a candidate key. Therefore, when a table contains only one candidate key (i.e. the primary key) the 3NF and the BCNF are equivalent. That is why the algorithm discussed in class works for both 3NF and BCNF in a majority of table. BCNF can be violated only when the table contains more than one candidate keys. BCNF is a special case of 3NF. So how can a table be in 3NF and not be in BCNF? This happens when a nonkey attribute is the determinant of a key attribute. e.g. Consider the table – TABLE ( A, B , C, D) (A,B) (C,D) C B (This not considered a transitive dependency) This table is in 1NF, 2NF and 3NF. There are no partial and transitive dependencies. The dependency C B causes the table to fail to meet the BCNF requirements – the determinant C is not candidate.
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Background image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Conversion to BCNF To convert this table to BCNF, first change the primary key to (A,C). So the new table is TABLE (A,C ,B,D) At this point the table is in 1NF but not in 2NF (partial dependency exists C B). We will now follow the algorithm to decompose the offending dependency. TABLE 1(A, C , D) TABLE2 (C, B) These two tables are now in BCNF! Now consider the example in Lecture 3, Slide 3-13. The table ITEM (ItemNumber , Type, AcquisitionCost) has the functional dependency – Type (ItemNumber, AcquisitionCost) In other words, we have two candidate keys in that table ItemNumber and Type. Therefore, all determinants are candidate keys. Hence, this table is in BCNF and needs no further decomposition. Had we not made the assumption that Type (ItemNumber, AcquisitionCost), then the table would be in 3NF....
View Full Document

{[ snackBarMessage ]}