Rules of 2nd Normal Form
- The table should be in 1NF.
- Primary Key does not functionally dependent on any subset of Candidate Key.
Table 1
ID | Full Name | Institute | Result |
---|---|---|---|
1 | Naveen Kumar | IIT Delhi | Pass |
2 | Utkarsh Tiwari | IIT Bombay | Fail |
3 | Utkarsh Tiwari | IIT Kanpur | Fail |
Table 2
ID | Subject |
---|---|
1 | DBMS |
1 | OS |
2 | CN |
2 | COA |
3 | OS |
Here, the Id in Table 2 is Foreign Key to the Id in Table 1. Now, we have to remove Transitive Functional Dependency from our Table to Normalize our Database. A Transitive Functional Dependency basically tells us that there is an indirect relationship between functional dependency.
Now, let us look at the 3rd Normal Form.
Normalization Process in DBMS
Pre-Requisite: Introduction to Database Normalization
Database Normalization is a stepwise formal process that allows us to decompose database tables in such a way that both data dependency and update anomalies are minimized. It makes use of functional dependency that exists in the table and the primary key or candidate key in analyzing the tables. Normal forms were initially proposed called
Subsequently, R, Boyce, and E. F. Codd introduced a stronger definition of 3NF called Boyce-Codd Normal Form. With the exception of 1NF, all these normal forms are based on functional dependency among the attributes of a table. Higher normal forms that go beyond BCNF were introduced later such as Fourth Normal Form (4NF) and Fifth Normal Form (5NF). However, these later normal forms deal with situations that are very rare.
Now, we are going to describe the process of Normalization by considering an Example. Let us look into that.