Normalization – Database Design
Theory
Module 3 | DBMS | With Examples
Introduction to Normalization
• Reduces data redundancy and improves integrity.
• Organizes data efficiently in relational databases.
• Divides large tables into smaller, related tables.
• Each table should represent one subject or concept.
• Helps avoid anomalies during insert/update/delete.
• Makes databases easier to maintain and query.
• Improves scalability and normalization efficiency.
• Forms the foundation of good database design.
Anomalies in Databases
• Insertion Anomaly: Can’t add data due to missing related data.
• Deletion Anomaly: Deleting one item may remove useful info.
• Update Anomaly: Redundant data leads to inconsistencies.
• Anomalies arise from poor schema design.
• All anomalies affect data integrity.
• Normalization removes these issues.
• Example: EMP_PROJ with mixed project/employee data.
• Designing properly eliminates such problems.
Functional Dependency (FD)
• Describes relation between two attributes.
• A → B means B depends on A.
• Used to identify keys and design schema.
• Helps split tables for normalization.
• Supports consistency in design.
• Represents real-world business rules.
• Example: StudentID → StudentName.
• Ensures data correctness and efficiency.
Trivial and Non-Trivial
Dependencies
• Trivial: A → A or A → subset of A.
• Non-Trivial: A → B where B is not in A.
• Non-trivial FDs are useful for normalization.
• Trivial dependencies don’t require decomposition.
• Example: {EmpID, Name} → EmpID is trivial.
• EmpID → Name is non-trivial.
• Focus on non-trivial FDs to remove redundancy.
• Essential for identifying proper table structures.
Normalization Process
• Breaks down tables into smaller ones.
• Based on Functional Dependencies.
• Ensures data is logically stored.
• Removes anomalies and redundancy.
• Applies different normal forms sequentially.
• Follows lossless-join and dependency-preservation.
• Ensures efficient query performance.
• Example: Customer table → Customer & Orders.
First Normal Form (1NF)
• Eliminates repeating groups and arrays.
• Ensures atomic values in each field.
• No multivalued or composite attributes.
• Each cell holds a single value.
• Each record must be unique.
• RDBMS typically enforce 1NF by default.
• Example: Student(Phones) → Split into Student & Phone.
• Improves data clarity and query ability.
Second Normal Form (2NF)
• Must be in 1NF first.
• Eliminates partial dependency.
• Every non-key attribute depends on full key.
• Applies mainly to composite keys.
• No attribute depends on a part of the key.
• Improves relational integrity.
• Example: (StudentID, CourseID) → Grade is valid.
• Move unrelated data to separate tables.
Third Normal Form (3NF)
• Must be in 2NF first.
• Eliminates transitive dependencies.
• Non-prime attributes should not depend on other non-prime attributes.
• Each attribute should depend only on the primary key.
• Avoids indirect relationships.
• Example: EmpID → DeptID → DeptName → split.
• Simplifies schema and improves consistency.
• Most commonly used in practice.
Boyce-Codd Normal Form (BCNF)
• Stronger version of 3NF.
• Every determinant must be a candidate key.
• Solves certain anomalies not fixed by 3NF.
• Useful when multiple candidate keys exist.
• Ensures no functional dependency violates key rule.
• Example: Instructor → Course in TEACH violates BCNF.
• Used for better schema normalization.
• Fewer but well-structured tables.
Fourth Normal Form (4NF)
• Must be in BCNF.
• Handles multivalued dependencies (MVDs).
• No table should have two independent MVDs.
• Splits tables with unrelated multi-values.
• Ensures each table stores only one multivalued fact.
• Example: EMP(Ename, Dname, Pname) → split.
• Prevents Cartesian explosion of data.
• More refined data organization.
Fifth Normal Form (5NF)
• Also called Project-Join Normal Form (PJNF).
• Deals with complex join dependencies.
• Ensures lossless decomposition for every join.
• Very rare in practice, but theoretically important.
• Prevents spurious tuples when joining tables.
• Useful in multi-dimensional relationships.
• Example: Supplier-Part-Project → multiple splits.
• Applied when all previous forms are satisfied.