Boyce-Codd normal form is a normal form used in database normalization and is a slightly stricter version of 3NF. Boyce-Codd normal form removes all redundancies based on functional dependence.

Definition

If a schema is in Boyce-Codd normal form all of it's functional dependencies X Y satisfy at least one of the following conditions:

• X Y satisfies reflexivity (Y X).
• X is a super key for the schema.

If a relational schema is in BCNF, then it is automatically also in 3NF because BCNF is a stricter form of 3NF.

Example

Consider a table Address with the following attributes

• HouseNumber
• Street
• City
• Province
• PostalCode

and the following functional dependencies

• HouseNumber, Street, PostalCode → City
• HouseNumber, Street, PostalCode → Province
• PostalCode → City
• PostalCode → Province

Is Address in BCNF? Justify why or why not and if it isn't decompose it such that it is.

Address is not in BCNF.

The candidate key is {HouseNumber, Street, PostalCode} (its closure yields all attributes). The dependencies PostalCode → City and PostalCode → Province violate BCNF because PostalCode alone is not a super key for Address.

To bring it into BCNF, we decompose using the violating dependency PostalCode → City, Province:

• PostalArea(PostalCode, City, Province): Here PostalCode is a super key, so all functional dependencies satisfied.
• Address(HouseNumber, Street, PostalCode): No non-trivial functional dependencies remain that violate BCNF.

This decomposition is loseless because the common attribute PostalCode is a super key for PostalArea.