RelationalDBDesign RelationalDBDesign



Third Normal Form   «Prev 

Rules for BCNF

Boyce-Codd Normal Form or BCNF is an extension to the third normal form, and is also known as 3.5 Normal Form.
For a table to satisfy the Boyce-Codd Normal Form, it should satisfy the following two conditions:
  1. It should be in the Third Normal Form.
  2. And, for any dependency A → B, A should be a super key.
The second point means, that for a dependency A → B, A cannot be a non-prime attribute, if B is a prime attribute.
This will be explained using BCNF in terms of relations.

Relationship Example

Consider the following relationship: R(A,B,C,D) and following dependencies:
A -> BCD 
BC -> AD
D -> B

Above the relationship is already in 3rd normal form where the keys are A and BC.
Hence, in the functional dependency, A -> BCD, is the super key. In the second relation BC->AD, BC is also a key.
However, in the relation, D -> B, D is not a key. Therefore, we can only break our relationship R into two relationships R1 and R2.
Relationship R broke into two relationships.

Boyce-Codd Normal Form (BCNF)

A superkey is a set of fields that contain unique values. You can use a superkey to uniquely identify the records in a table and recall that a candidate key is a minimal superkey.
In other words, if you remove any of the fields from the candidate key, it will not be a superkey anymore. A determinant is a field that at least partly determines the value in another field.
Note that the definition of 3NF deals with fields that are dependent on another field that is not part of the primary key. Now we are talking about fields that might be dependent on fields that are part of the primary key (or any candidate key). A table is in BCNF if:
  1. It is in 3NF.
  2. Every determinant is a candidate key.


Four important rules for Codd's Twelve Criteria

  1. All information in a relational database is represented explicitly at the logical level in exatly one way, by the values in tables.
    Creating tables to hold data allows users to search the tables, link tables which have fields in common, and use those links to combine those records in one or more tables efficiently.
  2. Each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value, and column name.
    Being able to find a datum is at the heart of 2NF.
    Relations in 1NF must have a primary key, though the relation can contain columns that are not functionally determined by the relation's primary key field(s). While it will be theoretically possible to find any value given the datum's table name, primary key value, and column name, it may not be practical to find the information you want if the datum is in a record which is completely unrelated to the datum's source.
  3. Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpairment are made to the base tables.
    Theoretically, deleting a record about an item should not also delete information about the company that makes it. If a table is not in 3NF, however, it is possible to wipe out information about a manufacturer by deleting the last record referring to a product made by that company.
  4. Integrity constraints specific to a particular relational database must be definable in the relational data sub-language and storable in the catalog, not in the application programs. A minimum of the following two integrity constraints must be supported:
    1. Entity integrity: No component of a primary key is allowed to be null.
    2. Referential integrity: For each distinct non-null foreign key value in a relational database, there must exist a matching primary key value from the same domain

    Maintaining referential integrity is an absolute requirement for a relational database. Putting all of a database's tables in 3NF, complete with links between tables based on common fields, allows the RDBMS to check each table's contents against related tables to ensure no important information is lost or changed.