Lesson 10

ItemNo → Title, Price, CategoryID
CategoryID → CategoryName, CategoryManager

4. Decompose Relations and Verify the Result

In eliminating transitive dependencies, the repeated category facts moved into Category. Item retained its own attributes and a reference to the category:

Item(ItemNo PK, Title, Price, CategoryID FK)
Category(CategoryID PK, CategoryName, CategoryManager)

This is schema notation, not executable SQL. CategoryID identifies one Category row but can appear in many Item rows. Its repetition as a foreign key represents the relationship; normalization does not require every value to appear only once throughout a database.

Two items in category C10 now obtain Jazz and Morgan from the same category record. A manager change affects one authoritative row. A category can exist before its first item, and deleting its last item does not erase the category.

Lossless Reconstruction

A decomposition is lossless when joining the projections reconstructs the original relation without introducing spurious combinations. Here, the shared CategoryID determines all attributes of Category. That dependency explains why each original item recovers exactly its category facts.

Checking a few rows is useful evidence, but it is not a general proof. Compare the dependency argument with the actual constraints, including a foreign key and, for this mandatory category rule, a non-null Item.CategoryID. A newly created category with no items need not appear in an item report; its independent existence is an intended benefit.

Dependency Preservation

A dependency-preserving decomposition allows the original functional dependencies to be enforced through the dependencies in the individual relations. ItemNo determines the item facts within Item; CategoryID determines the category facts within Category. No join is needed to check those stated dependencies.

Losslessness and dependency preservation are separate requirements. Do not infer one from the other. Also check that the original update, insertion, and deletion anomalies have been removed, and that invalid foreign-key references are rejected by the implementation.

5. Apply the Complete 3NF Procedure

Achieving third normal form combined partial and transitive dependencies in a registration system. CourseID identified one offering, each offering had one instructor, and each instructor had one current office. These assumptions mattered: a teaching team or assignment history would need a different model.

Removing partial dependencies separated Student, Registration, and Course. A second inspection found instructor facts repeated in Course, so those facts moved into Instructor. The resulting relations were:

Student(StudentID PK, StudentName)
Registration(StudentID PK/FK, CourseID PK/FK)
Course(CourseID PK, CourseTitle, InstructorID FK)
Instructor(InstructorID PK, InstructorName, InstructorOffice)

Registration has one composite primary key, not two primary keys. Together its columns identify a student-offering pair; each also references its corresponding parent. Under the stated dependencies, the four relations satisfy 3NF.

The practical procedure is iterative: state the rules, identify candidate keys, list dependencies, decompose violations, and examine every resulting relation again. Then verify reconstruction and enforcement. A first split can expose another dependency rather than finish the analysis.

Before migrating existing data, look for contradictory facts. If C10 has two manager names, selecting distinct rows does not decide which is correct. Resolve the business conflict before enforcing one category record. Preserve the original information and test the application operations that will use the new structure.

6. Keep Codd's Rules Separate from Normal Forms

Codd's 12 rules, accompanied by foundational Rule 0, concern relational DBMS capabilities. They are not twelve normalization stages. A DBMS can support relational operations while storing a poorly designed table; a relation can satisfy 3NF without proving that the product meets every criterion.

The rules cover logical representation and access, systematic treatment of missing information, a queryable catalog, a comprehensive data language, view updating, and set-level modification. They also address physical and logical data independence, integrity independence, distribution independence, and preventing lower-level interfaces from bypassing integrity constraints.

For the employee example, decomposition decides where DepartmentName belongs. A foreign key enforces valid references. A view may preserve a reporting interface, and an index may improve access. Each mechanism has its own responsibility; none substitutes for all the others.

Likewise, preserving a read interface does not automatically preserve updates through that interface. A joined or aggregated view needs a separate assessment of its update meaning and the DBMS features available. The familiar normalization mnemonic about the key is a memory aid, not a replacement for these distinctions or the formal definitions.

7. Know When Stronger Normal Forms Matter

Normalization beyond 3NF examined functional, multivalued, and join dependencies. A stronger form addresses a particular kind of remaining redundancy; its purpose is not simply to increase the number of tables.

BCNF: Every Nontrivial Functional Determinant Is a Superkey

The BCNF lesson used Teaching(Student, Course, Instructor). Each student had one instructor per course, and each instructor taught one course. The candidate keys were (Student, Course) and (Student, Instructor).

Instructor determined Course but did not identify a Teaching row, because an instructor could teach many students. The relation satisfied 3NF under those dependencies because every attribute was prime, but it failed BCNF. Splitting it into InstructorCourse and StudentInstructor removed the repeated instructor-course fact and allowed lossless reconstruction.

However, local keys on the two tables no longer enforced the original one-instructor-per-student-course rule. A student could reference two instructors who teach the same course. Preserving that rule requires additional enforcement, including consideration of concurrent writes, or a different design choice. BCNF does not guarantee dependency preservation.

4NF: Separate Independent Sets of Facts

In the audiobook example, a CD had two credited authors and three credited narrators. When those sets were independent, storing every author-narrator combination required six rows. The combined relation could satisfy BCNF while violating 4NF through a nontrivial multivalued dependency with a non-superkey determinant.

CD_Author stored two relationships and CD_Narrator stored three. Adding an author then required one new relationship rather than one row per narrator. A join reconstructed the six combinations because independence was an explicit business rule.

Do not apply that split if the pairings record which narrator read a particular author's contribution. Separate independent lists would invent pairings on reconstruction. Fourth normal form concerns nontrivial multivalued dependencies, not every ordinary one-to-many relationship.

5NF and DKNF

Fifth normal form requires every nontrivial join dependency to be implied by candidate keys. A supplier-part-project example needs an explicit rule before three pairwise lists can replace the original triples. Pairwise compatibility alone does not prove an actual delivery or authorized assignment.

Domain-key normal form asks whether all constraints follow from domain and key constraints. These concepts extend the analysis, but they do not justify arbitrary splitting. Determine which dependencies exist in your domain and whether decomposition preserves their meaning.

8. Denormalize Only with a Defined Maintenance Plan

The lesson on denormalization retained normalized Employee, Genre, and Manager tables while considering a derived genre-team directory. That read structure repeated names to reduce work for a particular access pattern. The source relations could remain normalized.

First measure the query and examine indexes, statistics, filtering, and execution plans. Fewer joins do not guarantee lower total cost. Evaluate read latency alongside write overhead, storage, refresh load, and operational effort.

A stored result needs a consistency policy. Synchronous maintenance adds work to source transactions; asynchronous processing introduces lag and retry responsibilities; scheduled refreshes must publish complete results and recover from failures. A five-minute schedule alone does not guarantee five-minute freshness.

Define the authoritative source, the meaning and key of each derived row, the acceptable age of the data, and the recovery procedure. Compare values as well as counts at the same source snapshot. Test insertions, deletions, reassignment, duplicate delivery, and interrupted refreshes before depending on the copy.

Finally, distinguish redundant current values from historical facts. An agreed price on an order line describes that sale. Replacing it whenever the current catalog price changes would destroy information, not improve normalization.

Essential Terms to Retain

Determinant

The attribute or attribute set on the left of a dependency.

Candidate key

A minimal superkey. Consider every candidate key when assessing a relation.

Prime attribute

An attribute that belongs to at least one candidate key.

Transitive dependency

An indirect dependency inferred from a chain such as A → B and B → C.

Lossless decomposition

A split whose projections can be joined to recover the original relation exactly.

Dependency preservation

The ability to enforce the original functional dependencies through dependencies in the decomposed relations.

Denormalization

Deliberately storing redundant or precomputed information for an access requirement, with explicit maintenance rules.

A Short Design Review Exercise

Suppose a team proposes Book(BookID, BookTitle, PublisherID, PublisherName). BookID is the only candidate key, each book has one publisher, and many books can share a publisher. PublisherID determines one current publisher name. Apply the same procedure without relying on the employee or category labels.

The relation can satisfy 2NF, but PublisherID is not a superkey and PublisherName is non-prime. The dependency therefore fails the 3NF test. Create Publisher(PublisherID, PublisherName), retain PublisherID in Book, and declare the reference. The shared publisher identifier determines the moved facts, supporting lossless reconstruction. The stated dependencies remain enforceable within their respective tables.

Next, test the business operations. Can the team add a publisher before any books are entered? Can it rename that publisher once? Does removing the final book preserve the publisher? Does inserting a book with an unknown publisher fail? These questions connect the formal analysis to observable behavior without treating a handful of test rows as proof of every possible state.

Now change the requirement: a book can have several publishers. A single PublisherID in Book no longer represents the domain. Model the book-publisher relationship separately and reconsider its key. Normalization depends on the stated rules, so a correct answer for the first scenario does not remain correct after the meaning changes. Record such assumptions alongside the schema so future maintainers know when to revisit the design.

Check Your Reasoning Before Continuing

For any proposed design, explain what one row means, identify its candidate keys, and name the dependency that justifies each split. Demonstrate why reconstruction is lossless and identify any constraint that is no longer enforceable inside one relation. These explanations are more useful than labeling a diagram “normalized” without evidence.

Ask yourself whether a surrogate ID would remove repeated department names, whether a 3NF relation must also be in BCNF, and whether a successful sample join proves losslessness for all valid data. The answer to each is no. Return to the relevant lesson if you cannot explain the dependency or assumption behind that answer.

The next module introduces table joins and user views. You will use those tools to retrieve information from separate relations and present useful interfaces. Understanding the keys and relationship rules first helps you predict which rows a join should return and recognize unintended duplication.

Review partial and transitive dependencies, the 3NF and BCNF tests, and the tradeoffs of deliberate redundancy before taking the quiz. Use the linked lessons to revisit any unfamiliar reasoning.

Third Normal Form - Quiz