Normalization is the process of organizing data into a related table; it also eliminates redundancy, undesirable characteristics like Insertion, Update and Deletion Anomalies and increases the integrity.
Benefits of Normalization • Less storage space
• Quicker updates
• Less data inconsistency
• Clearer data relationships
• Easier to add data
• Flexible Structure
Update Anomaly - Cannot update information without changing information in many places. To update customer information, it must be updated for each sales order the customer has placed.
DE- NORMALIZATION:
Denormalization is the process of adding redundant data to speed up complex queries involving multiple table JOINS. One might just go to a lower form of Normalization to achieve Denormalization and better performance. Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.
First Normal Form (1NF)
If all attribute values are atomic: no repeating group, no composite attributes.
DPT_NO EMP_NO
DPT_NO EMP_NO
Second Normal Form (2NF) - No partial dependency
(a) It is in 1st Normal Form.
(b) If no non-key attribute is dependent on a part of composite key, that is all the attributes must be dependent on the whole composite key not just a part of it.
There must not be any partial dependency of any column on primary key.
2nd Normal Form can be applied only to the table which has any composite key.
{Student, Subject}, Age
Third Normal Form (3NF): No transitive dependency
(a) It is in 2NF.
(b) It doesn't contain any transitive dependencies.
Remove transitive dependencies: remove columns that are not dependent upon the primary key.
Everything is dependent on the key or is in the key.
Each column must depend on *directly* on the primary key.
Everything is dependent on the key or is in the key.
Each column must depend on *directly* on the primary key.
Boyce Codd Normal Form (BCNF):
Every determinant is a candidate key
No overlapping candidate keys
Every determinant is a candidate key
No overlapping candidate keys
A relation is said to be in BCNF if and only if the determinants are candidate keys. BCNF relation is a strong 3NF, but not every 3NF relation is BCNF.
A relation is in BCNF, if and only if, every determinant is a candidate key.
The difference between 3NF and BCNF is that for a functional dependency A -> B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key,
whereas BCNF insists that for this dependency to remain in a relation, A must be a candidate key.
Student, Course, Teacher: SC is the primary key, but teacher -> course.
When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF.
3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys
i.e. composite candidate keys with at least one attribute in common.
BCNF is based on the concept of a determinant.
A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent.
A relation is in BCNF is, and only if, every determinant is a candidate key.
A 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys.
http://psoug.org/reference/normalization.html
A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. One of these candidate keys is selected as the table primary key.
A relation is in BCNF, if and only if, every determinant is a candidate key.
The difference between 3NF and BCNF is that for a functional dependency A -> B, 3NF allows this dependency in a relation if B is a primary-key attribute and A is not a candidate key,
whereas BCNF insists that for this dependency to remain in a relation, A must be a candidate key.
Student, Course, Teacher: SC is the primary key, but teacher -> course.
When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF.
3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys
i.e. composite candidate keys with at least one attribute in common.
BCNF is based on the concept of a determinant.
A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent.
A relation is in BCNF is, and only if, every determinant is a candidate key.
A 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys.
http://psoug.org/reference/normalization.html
| Fourth Normal Form | An entity is in Fourth Normal Form (4NF) when it meets the requirement of being in Third Normal Form (3NF) and additionally:
|
| Fifth Normal Form | An entity is in Fifth Normal Form (5NF) if, and only if, it is in 4NF and every join dependency for the entity is a consequence of its candidate keys. |
References
