Database Normalization Explained – 1NF, 2NF, and 3NF with Examples
Normalization: In a database divide tables in the basis of dependency for avoiding redundancy.
First normal form:
l All the attributes in a relation must have atomic domain.
l The values in an atomic domain are indivisible units
Course | Content |
Programming | Java, C++ |
Web |
After re-arrange the relation as below, to convert it to first normal form,
Course | Content |
Programming | Java |
Programming | C++ |
Web | HTML |
Web | PHP |
Web | JS |
Each attribute must contain only a single value from its pre-defined domain.
Second normal form:
A relation schema R is in second normal form (2NF) if each attribute A in R meets one of the following criteria:
l Table should be in 1NF
l It appears in a candidate key.
l It is not partially dependent on a candidate key.
student_project
Here in student_project relation the primary key attributes are Stu_ID and Proj_ID. According to the rule, non-key attributes Stu_Name, Proj_Name must be depent on both not on any of the prime key attribute individually. But here Stu_name can be identified by Stu_ID and Proj_Name can be identified by Proj_ID independently. This is called partial dependency, which is not allowed in Second normal form
After breaking the relation in two as depicted in the above picture. So there exist no partial dependency
For a relation to be in Third Normal Form, it must satisfy
l It must be in Second Normal form
l No non-prime attribute is transitively dependent on prime key attribute.[Transitive dependency: when one column depends on a column which is not primary key]
l For any non-trivial functional dependency, X → A, then either −
X is a superkey or,
l A is prime attribute.
in the above Student_detail relation, Stu_ID is the key and only prime key attribute. We find that City can be identified by Stu_ID as well as Zip itself. Neither Zip is a super-key nor is City a prime attribute. Additionally, Stu_ID → Zip → City, so there exists transitive dependency.
To bring this relation into third normal form, break the relation into two relations as follows −
No comments