Validation of Normalization

Chapter 7 · after decomposing, you must prove the new design is correct. There are three important validation checks: lossless join, dependency preservation and normal form checking.

a. Lossless join

A decomposition should be lossless. This means that after splitting a table into smaller tables, joining them again should give back the original table without losing or adding extra rows.

Original table

Employee_ID	Employee_Name	Project_ID	Project_Name	Project_Location
E01	Rahim	P01	Hospital Management System	Dhaka
E02	Karim	P02	Banking System	Chattogram
E03	Mina	P01	Hospital Management System	Dhaka
E04	Sumi	P03	Library System	Rajshahi

Employee table

Employee_ID	Employee_Name	Project_ID
E01	Rahim	P01
E02	Karim	P02
E03	Mina	P01
E04	Sumi	P03

Project table

Project_ID	Project_Name	Project_Location
P01	Hospital Management System	Dhaka
P02	Banking System	Chattogram
P03	Library System	Rajshahi

Joining the two tables on Project_ID reproduces the original table with all the data intact.

The rule to quote: a decomposition of R into R₁ and R₂ is lossless if the common attributes R₁ ∩ R₂ form a key of R₁ or of R₂. Here the common attribute is Project_ID, which is the key of the Project table — so the join is lossless.

A lossy decomposition invents rows that were never there (spurious tuples). It happens when you split on a column that is not a key of either part.

b. Dependency preservation

Functional dependencies should be preserved after decomposition. If a dependency becomes difficult to check without joining many tables, then dependency preservation may be lost.

Employee_ID → Employee_Name, Project_ID
can be checked entirely inside the Employee table. ✔

Project_ID → Project_Name, Project_Location
can be checked entirely inside the Project table. ✔

Both FDs live inside a single table, so this decomposition is dependency preserving. Contrast with the BCNF example on the previous page, where (Student_ID, Course_ID) → Instructor ended up spanning two tables.

c. Normal form checking

Each table should be checked against the rules of the target normal form. For example:

For 3NF, check:

Is the table in 2NF?
Are there any transitive dependencies?
Does every non-key attribute depend only on the key?

For BCNF, check:

For every FD X → Y, is X a super key?

Benefits of normalization

Benefit	Explanation
Reduces data redundancy	The same data is not stored repeatedly — e.g. a department name is stored once in the department table.
Improves data consistency	Since each fact is stored in one place, updating data becomes easier and safer.
Avoids update anomaly	A change needs to be made in only one place.
Avoids insertion anomaly	New data can be inserted independently — a new course can be inserted even if no student has enrolled yet.
Avoids deletion anomaly	Deleting one record does not accidentally remove other important information.
Improves database design	Tables become more meaningful and logically organized.
Helps maintain data integrity	Primary keys and foreign keys can be used properly.

Shortcomings of normalization

Shortcoming	Explanation
More tables are created	Normalization divides large tables into smaller tables, so the number of tables increases.
More joins are required	To retrieve complete information, multiple tables may need to be joined — e.g. student name + course title + instructor name + grade may need several joins.
Query performance may decrease	Too many joins can make some queries slower, especially in large databases.
Design becomes more complex	Highly normalized databases can be difficult for beginners to understand.
Not always suitable for reporting	Reporting and analytics systems often need faster read performance; in such cases some denormalization may be used.

The trade-off sentence to end any normalization answer with: normalize for OLTP systems where integrity and frequent updates dominate; deliberately denormalize for reporting/analytics where reads dominate and the data is loaded in bulk.

Applied end-to-end on a real table: Project Task 4 — UNF → 3NF →