Normalization

Question:  What is Normalization? Explain the condition under which a relation needs to be normalized to 3 NF from 2 NF with the help of an example?     

Normalization: Database normalization is a technique for designing relational database tables to minimize duplication of Information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies. For example, when multiple instances of a given piece of information occur in a table, the possibility exists that these instances will not be kept consistent when the data within the table is updated, leading to a loss of data integrity. A table that is sufficiently normalized is less vulnerable to problems of this kind, because its structure reflects the basic assumptions for when multiple instances of the same information should be represented by a single Instance only.

A relation R is in Second normal form (2NF) if

(a) R is 1NF, and

(b) All non-prime attributes are fully dependent on the candidate keys   

Third normal form (3NF) requires that there are no functional dependencies of non-key attributes on something other than a candidate key.

Here an example of a relation needs to be normalized to 3 NF from 2 NF

2 NF3NFModule

DeptLectureM1D1L1M2D1L1M3D1L2M4D2L3M5D2L4LectureDeptL1D1L2D1L3D2L4D2ModulelectureM1

1M2L1M3L2M4L3M5L4  

Another example

Tabel1

Emp IDEmp nameAddressAgeDate  of Birth Dept CodeDepartment name

Here every Non key attribute are depend on Key attribute, But there is a Transitive dependenc

 So, table 1 is not in 3 NF

Table 1.1

Emp IDEmp nameAddressAgeDate  of Birth Dept Code

Table 1.2

Dept CodeDepartment name  

Please Share it! :)