Database Normalization : 1NF, 2NF, 3NF, BCNF, 4NF
What is normalization? Breaking down a table into functionally independent tables Follows three-step procedures {first normal form, second NF, third NF} Why normalization? Reduce data redundancy, and thus data anomalies 1NF All the key attributes are defined There are no repeating groups in the table All attributes are dependent on the primary key 2NF It is in 1NF It includes no partial dependencies Partial dependency : attributes are dependent on only a part of a composite PK Fig: Dependency Diagram 3NF It is in 2NF It includes no transitive dependencies Transitive dependency : nonkey (nonprime) attributes are dependent on another nonkey attribute BCNF A Table is in BCNF when it is in 3NF and contains no non-key attribute that determines a key attribute. – Convert to BCNF by exchanging the determinate with the key attribute and then resolving the partial dependency with a new table. 4NF A Table is in 4NF when it is in 3NF table with no multi-valued dependencies : – Convert to 4NF