Normalization

NORMALIZATION

It’s a process of eliminating/ reducing duplication of data in database because every operation on the db is dependent on the size of the data in db.

So, size should be optimized (by deleting duplicate records).

Ideally, there shouldn’t be any duplicate data in the database (practically, cent percent deletion of duplication data is not possible, so should be minimized as much as possible).

“If data can be defined as a process during which redundant relation schemas are decomposed by breaking up their attributes into smaller relation schemas that posses desirable properties.”

How to Achieve Normalization?

Apply Primary Key Constraint (Which Uniquely Records Every Data In The Table) On Every Table In The Db.

DE-NORMALIZATION

It‘s a process which prohibits further normalization of dbms, to avoid unmanageable complexity in terms of no. of tables and relationship among those tables that will result in due to next level of normalization.

FLATTENING / DE-COMPOSITION OF TABLE

Definition: The process of reducing a table into two with one table containing multiple attribute and other with single attribute.

Forms of Normalization:

1^st normal form

• Any db relation table is said to be in 1^st normal form if and only if there is only one value for every attribute that is dependent in the determinant attribute.
• It means for every instruction of a row and a column there exist only one value for an attribute i.e. all the attribute in the relation are single valued attributes.

2^nd normal form

• A relation R is in 2NF if and only if
• It’s in 1NF.
• Every non-key attribute is fully dependent on the primary key attribute.

3^rd normal form

• A relation R is in 2NF if and only if
• It’s already in 2NF
• No non-prime attribute is transitively dependent on the key.
• Every non-key attribute in the relation must not be transitively dependent on the primary key.

 eg: Name ⟶ Roll and Roll ⟶ Branch

      implies Name ↛ Branch ( because same name can be of different branches)

Boyce-Codd Normal Form

• BCNF is simply a stronger definition of 3^rd NF.
• It does not make any explicit reference to first or second NF.
• It’s neither full nor transitive dependence

A relation R is in BCNF if and only if

• Every determinant is a candidate key.
• Determinant is a simple attribute or composite attribute on which some other attributes is fully functionally dependent.

4^th Normal Form

• A relation is said to be in 4^th NF if and only if

R is already in 3^rd NF or BCNF

• It contains no multi-valued dependencies in the relation
•  eg: no ‘one to many’ relation allowed

5^th Normal Form

A relation is said to be in 5^th NF if and only if

• It is in 4^th NF
• It can’t be further decomposed without any loss of information.
• If there is a lossless join decomposition resulting in further elimination of redundancy, the resulting relations will be in 5^th NF.