Lesson 6 — Normalization, Abstraction, and Engineering Direction
Normalization stabilizes relational logic
Normalization reduces redundancy and dependency defects in logical structure. Chapter 5 puts most exam emphasis on 1NF–3NF and treats higher forms mainly at recognition level.
First Normal Form (1NF)
Recognition ideas: - atomic values; - no repeating groups; - valid key structure; - resolve many-to-many structures through an associative entity where needed.
A Student record containing Course1, Course2, Course3 is a repeating-group smell. Student–Course M:M with Registration Date is better modeled through an associative Registration entity.
Second Normal Form (2NF)
Every non-key attribute must depend on the complete minimal key, not only one part of a composite key.
If (Order_ID, Product_ID) identifies an Order Line but Order_Date depends only on Order_ID, the design has a partial-dependency problem.
Third Normal Form (3NF)
Non-key attributes should not depend on other non-key attributes. The familiar memory rule:
The key, the whole key, and nothing but the key.
Chapter 5 notes that an unqualified “normalized model” usually means 3NF.
Higher forms — recognition level
- BCNF: strengthens functional-dependency rules beyond 3NF.
- 4NF: addresses multivalued dependencies.
- 5NF: addresses join dependencies.
Do not spend the same retrieval effort on these as 1NF–3NF unless performance data shows you are missing them.
Normalization vs denormalization
Normalization removes redundancy/dependency defects and stabilizes logical meaning.
Denormalization deliberately introduces redundancy or combines structures in the physical design for a justified requirement such as measured performance or security/access behavior.
Denormalization is not “fixing” a bad logical model. It is a controlled physical trade-off and increases synchronization/data-quality risk.
A good exam sequence is: clean logic first → measure the physical problem → consider less disruptive options such as indexing/views/partitioning → denormalize only when justified.
Abstraction: generalization and specialization
Normalization is about dependencies. Abstraction is about commonality and inheritance.
Generalization
Move common attributes/relationships up into a shared supertype.
Example: Student and Employee both have Name, Address, Birth Date → generalize shared properties into Person.
Specialization
Create more specific subtypes for distinguishing properties/relationships while inheriting the supertype’s common properties.
Memory hook: common upward; differences downward.
Physical subtype resolution
Logical inheritance and physical table design are separate decisions.
- Subtype absorption: one broad supertype table; subtype-specific attributes become nullable columns.
- Supertype partition: separate subtype tables carry inherited supertype attributes.
Do not confuse the logical modeling technique with the later physical table mapping.
Forward vs reverse engineering
Forward engineering
Requirements → CDM → LDM → PDM
Start with business/data requirements and progressively add precision until implementation design exists.
Reverse engineering
Existing database → reconstructed PDM → inferred LDM → inferred CDM
Start with the implementation and work upward to recover structure and business meaning.
Reverse-engineered output normally needs human reorganization and interpretation. Table names alone do not reveal every intended business rule.
Build-step recognition
Conceptual build
Select scheme → choose notation → create initial user-view CDM → reconcile enterprise terminology/rules → gain sign-off.
Logical build
Analyze requirements and existing artifacts → resolve associative entities → define atomic attributes → domains → keys and detailed relationships.
Physical build
Resolve abstractions → add technical details/reference data → decide physical keys/surrogates → consider denormalization → indexes → partitions → views/materialized structures as justified.
Source anchors: current Mastery Lab normalization/abstraction/activity sections, pp. 149–157.