Conversion of a relation (table ) from 1NF to 2NF to 3NF to BCNF

Normalization:

Definition

    Normalization can be viewed as a series of steps (i.e., levels) designed, one after another, to deal with ways in which tables can be "too complicated for their own good". The purpose of normalization is to reduce the chances for anomalies to occur in a database. The definitions of the various levels of normalization illustrate complications to be eliminated in order to reduce the chances of anomalies. At all levels and in every case of a table with a complication, the resolution of the problem turns out to be the establishment of two or more simpler tables which, as a group, contain the same information as the original table but which, because of their simpler individual structures, lack the complication. 

    Through normalization we want to design for our relational database a set of files that (1) contain all the data necessary for the purposes that the database is to serve, (2) have as little redundancy as possible, (3) accommodate multiple values for types of data that require them, (4) permit efficient updates of the data in the database, and (5) avoid the danger of losing data unknowingly. 

Advantage/Disadvantage

Adv.         -   Remove redundancy, Anomaly, Storage Efficiency 

Dis. Adv.  -  Time Consuming, Difficult, maintenance overhead (higher NF-> more tables)

Terminology

Keys – Super, Candidate, Primary.

Attributes – Prime/key , Non prime/non key.

Functional Dependency 1- Full, Partial, Transitive, overlapping, trivial, non trivial, complete non trivial.

Decomposition - Lossless Join (Algo for 2R and >2R). & Dependency preserving.

Armstrong Axioms – Reflexivity, Augmentation, Transitivity.

Closure –Attribute, F.D (If F is a set of functional dependencies for a relation R, then the set of all functional dependencies that can be derived from F, F+, is called the closure of F. ).

Redundant F.D.

Steps/Process/flowchart

1st Normal Form (1NF): 

            A table (relation) is in 1NF  

            1. If There are no duplicated rows in the table.

            2. Each cell is single-valued (i.e., there are no repeating groups or arrays).3. Entries in a column (attribute,         field) are of the same kind.

            Note: The order of the rows is immaterial; the order of the columns is immaterial. 

            Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns). 

2nd Normal Form (2NF) : 

            A table is in 2NF 

            1. If it is in 1NF and 

            2. if all non-key attributes are dependent on all of the key.

            Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in 1NF and if it has no partial dependencies."

3rd Normal Form (3NF): 

            A table is in 3NF  

            1. If it is in 2NF and 

            2. If it has no transitive dependencies.

Boyce-Codd Normal Form (BCNF) : 

        A table is in BCNF 

        1. If it is in 3NF 

        2. If every determinant is a candidate key.

4th Normal Form (4NF): 

        A table is in 4NF 

        1. If it is in BCNF 

        2. If it has no multi-valued dependencies.

5th Normal Form (5NF): 

        A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), 

        if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

Domain-Key Normal Form (DKNF): 

        A table is in DKNF if every constraint on the table is a logical consequence of the definition of keys and domains.