Normalization – Database
Design Theory
Module 3 – CHAPTER 1
Presented by:
M Y LIKHITH
Introduction to
Normalization
Organizing data to minimize redundancy
• Enhances data integrity and consistency
• Helps avoid anomalies (insert, update, delete)
• Based on relational schema design
• Process of decomposing complex tables
• Maintains logical data independence
• Promotes efficient data access
• Example: Employee database with multiple phone numbers and
addresses
Functional
Dependencies
A relationship between two attributes
• Denoted by A → B (A functionally determines B)
• Used to identify redundancy
• Helps in determining the normal form
• Key to understanding normalization steps
• Example: EmployeeID → EmployeeName
• Basis for 2NF, 3NF, BCNF
• Example: StudentID → StudentName in college
Multivalued
Dependencies
Occur when one attribute determines a set of values
• Represented as A →→ B
• Independent multivalued facts in a relation
• Not handled by 3NF or BCNF
• Essential for 4NF
• Can lead to data repetition
• Example: Project →→ Skills
• Example: Movie with multiple actors and languages
Informal Design
Guidelines
Avoid redundancy
• Ensure every attribute depends on key, the whole key, and nothing
but the key
• Preserve functional dependencies
• Avoid null values where possible
• Ensure lossless decomposition
• Use meaningful attribute names
• Reduce data anomalies
• Example: Retail store's product inventory
Primary Key Constraints
Unique identifier for table rows
• Basis for normalization
• Enforces entity integrity
• Can be single or composite
• Helps eliminate duplicate rows
• Must be minimal and non-null
• Drives relationships with foreign keys
• Example: CustomerID in a bank table
Second Normal Form
(2NF)
Meets 1NF + no partial dependency
• All non-key attributes depend on the whole key
• Solves partial dependency issue
• Applies to tables with composite keys
• Reduces redundant data
• Simplifies updates and deletions
• Improves table design
• Example: Course enrollment table
Third Normal Form
(3NF)
Meets 2NF + no transitive dependency
• Non-key attributes depend only on the primary key
• Improves data integrity
• Prevents redundancy due to transitive relationships
• Simplifies schema design
• Easier to maintain
• Ensures better consistency
• Example: Employee table with department details
Boyce-Codd Normal
Form (BCNF)
A stricter version of 3NF
• Every determinant is a candidate key
• Resolves anomalies not handled by 3NF
• Useful in complex relationships
• Ensures a stronger design
• Avoids hidden dependencies
• Requires careful decomposition
• Example: Course scheduling with instructor and room
Multivalued
Dependency and 4NF
4NF = BCNF + no multivalued dependencies
• Removes independent multivalued facts
• Achieved by decomposition
• Ensures no unnecessary repetition
• Improves performance
• Maintains data clarity
• Reduces table size
• Example: Employee with dependents and skills
Join Dependencies and
5NF
5NF = No join dependencies other than candidate keys
• Deals with reconstructing data from joins
• Useful in complex business processes
• Requires lossless decomposition
• Based on project-join normal form
• Very rare in practical applications
• Increases normalization precision
• Example: Supplier supplying parts to projects
Real-World Case: Online
Retailer
1NF: Separate columns for address parts
• 2NF: Separate table for product category
• 3NF: Split supplier details to another table
• BCNF: Customer type determines discount
• 4NF: Separate product reviews and features
• 5NF: Normalize offers by product, location, season
Tools for Normalization
ER Diagrams (draw.io, Lucidchart)
• DBMS (MySQL, PostgreSQL)
• Design tools (DBDesigner, Oracle SQL Developer)
• Functional dependency tools
• Data modeling software
• Case study datasets
• Schema validation scripts
• SQL Query testing
Thank You
• Thank you for your attention!
