What is BCNF in DBMS

The Boyce-Codd normal form (BCNF) is a further development of the third normal form (3NF). In the third normal form it can happen that part of a key candidate is functionally dependent on part of another Key candidates. The Boyce-Codd normal form prevented these functional dependency.

As Key candidate becomes an attribute or a Attribute combination denotes the one Identify the data record clearly (i.e. form a primary key).

The BCNF only needs to be used if several key candidates are present and look at this partially overlap. If there is only one candidate key in the relation or if there is no overlap in the case of several candidate keys, the relation is automatically in the BCNF.

Boyce Codd normal form definition

A relation type is then in Boyce-Codd normal form (BCNF)if each relational-type determinant is a candidate key. The Boyce-Codd normal form is the highest normal form based on functional dependencies. A relation (table) that is in Boyce-Codd normal form (BCNF) is also at the same time in the third normal form (3NF).

Boyce-Codd Normal Form (BCNF) example

The starting point for the following consideration is the table, which picks up on the invoice example and has been expanded to include the warehouse location column. The warehouse location indicates where the item was stored in the warehouse.

ReNoItem NoStorage locationnumber
1001001010221
1001001020152
100100103095
10010310401310
1001041040136

The composite key candidates are ReNoItem No and ReNoStorage location.

Between Item No and Storage location there is a functional dependency that has nothing to do with the invoice number, so the relation is in 3rd NF, but not in Boyce-Codd normal form.

The attribute Item No here is the determinant for the storage location, but the attribute Storage location is only part of a key candidate and is therefore functionally dependent on the attribute Item No. The same applies to the opposite direction, as the item number can also be determined via the storage location.

The dependency is made possible by a Distribution of the data solved in two tables. This creates two new tables from the original table, which are linked by a primary key.
ddd

New table: "Invoice Article"

ReNoItem Nonumber
10010010101
10010010202
10010010305
100103104010
10010410406

New table: "Article storage location"

Item NoStorage location
101022
102015
10309
104013
10505

Further articles