Normalization

1. Normalization

2. Normalization Introduction • Developed by E.F Codd • Normalization of data is a process of analyzing the given relation schema based on their FDs (Functional Dependencies) and primary keys to achieve the desirable properties of • Minimizing redundancy • Minimizing the insertion, deletion and update anomalies.

3. Types of Normalization There are seven types normalization such as • First Normal Form (1NF) • Second Normal Form (2NF) • Third Normal Form (3NF) • Boyce-Codd Normal Form (BCNF), • Fourth Normal Form (4NF), • Fifth Normal Form (5NF) • Domain Key Normal Form (DKNF)

4. First Normal Form • Elimination of Multivalued attributes • A domain is atomic if elements of the domain are considered to be indivisible units. • A relation schema R is in first normal form (1NF) if the domains of all attributes of R are atomic Example Unnormalized table DNAME DNO DMGRID DLOC Research 5 100 {A,B,C} Administration 4 200 {D} Head 1 400 {E} Normalized table – Staff table in 1NF DNAME DNO DMGRID DLOC Research 5 100 A Research 5 100 B Research 5 100 C Administration 4 200 D Head 1 400 E

5. Example -2

6. Transforming to 1NF Transforming to rows, rather than columns 6

7. Transforming to 1NF: Example Another example UNF 1NF 7

8. Repeat First NF (cont.)

9. Multi-value Problem Staff • Problem • Difficult to manipulate data • Redundancy • UPDATE ANOMALIES

10. P06 Speech Corpus Insert Staff We can not insert new project if the project has not assigned to any employee yet.

11. UPDATE ANOMALIES Staff Change ProjName from Voice Ordering to Speech Ordering need to change all in Database

12. DELETE Problem Staff Delete Employee E002 Somchay Project P03 Medical Image Processing was deleted also

13. Solution • Remove the repeating group • In case of multi-valued • Create new relation • Columns = Key + multi-valued • Take its determinant with it

14. PayCheck Employee Repeating group Employee

15. Multi-Valued Staff

16. Multi-Valued