Normalization to 3NF and denormalization
Diagnose dependencies, construct 3NF and then evaluate when deliberate redundancy is justified.
Dependency reasoning, not table splitting
- explain 1NF, 2NF and 3NF using atomicity, unique identification and dependencies;
- recognize duplication, missing-data and dependency/anomaly problems;
- construct real-world databases normalized to 3NF;
- evaluate normalization versus denormalization for read-intensive systems.
Rapid Recall Deck
Attempt each card first. If a term is new, flip and study it, then answer it again without looking. Mark only secure recall as “Know it”.
Find the dependencies
ORDERLINE(OrderID, ProductID, ProductName, Quantity) with key (OrderID, ProductID), if ProductID → ProductName, then ProductName depends on only part of the composite key. That is a partial-key dependency. Use the same “what determines this fact?” reasoning below.Dependency map
ENROLMENT(StudentID, StudentName, CourseID, CourseTitle, TeacherID, TeacherName, Grade) Key: (StudentID, CourseID)
For ENROLMENT(StudentID, StudentName, CourseID, CourseTitle, TeacherID, TeacherName, Grade) with composite key (StudentID, CourseID):
1. identify non-key attributes that depend on only StudentID or only CourseID (partial-key dependencies);
2. identify a non-key attribute that depends on another non-key attribute (a transitive dependency);
3. state which attribute genuinely depends on the whole enrolment key.
StudentID → StudentName. CourseID → CourseTitle, TeacherID. TeacherID → TeacherName. (StudentID, CourseID) → Grade. StudentName/Course facts are partial-key dependencies; TeacherName is transitively dependent through TeacherID.
What each stage removes
1NF
Atomic values, no repeating groups/multi-value cells, unique identification.
2NF
1NF + every non-key attribute depends on the whole key.
3NF
2NF + no transitive dependency between non-key attributes.
Normalize the enrolment data
Build 3NF
Construct the final 3NF tables.
Normalize ENROLMENT(StudentID, StudentName, CourseID, CourseTitle, TeacherID, TeacherName, Grade) to 3NF. Use composite key (StudentID, CourseID) and the dependencies identified in the previous task: student facts depend on StudentID, course facts on CourseID, teacher name on TeacherID, and Grade on the whole enrolment key.
STUDENT(StudentID PK, StudentName) TEACHER(TeacherID PK, TeacherName) COURSE(CourseID PK, CourseTitle, TeacherID FK) ENROLMENT(StudentID PK/FK, CourseID PK/FK, Grade)
Each descriptive fact now sits with the key that determines it.
Why the change matters
Update anomaly
TeacherName is duplicated in 400 enrolment rows. What is the risk?
Every copy must be changed consistently; missed rows create contradictory data. In 3NF the teacher name is stored once.
Missing/deletion anomaly
If the last student leaves a course and course facts exist only in enrolment rows, what can be lost?
The only course/teacher facts can disappear. Separate COURSE/TEACHER tables allow those facts to exist independently of enrolments.
Hospital 3NF
Construct the design
Build PATIENT, DOCTOR and VISIT in 3NF.
A hospital needs patient names/dates of birth, doctor names and visits. Each visit records a date and treatment and links exactly one patient to exactly one doctor. Construct PATIENT, DOCTOR and VISIT tables in 3NF, marking primary and foreign keys.
PATIENT(PatID PK, PatientName, PatientDOB) DOCTOR(DocID PK, DoctorName) VISIT(VisitID PK, PatID FK, DocID FK, VisitDate, Treatment)
A scenario-guaranteed composite key such as (PatID, DocID, VisitDate) may identify VISIT instead.
When to denormalize
Read-heavy analytics
A dashboard repeatedly joins five normalized tables; data updates once nightly. Evaluate denormalization.
Normalization protects consistency and minimizes redundancy but repeated joins can be costly/complex. Carefully denormalized reporting structures can simplify/faster repeated reads. The cost is duplicated data, refresh logic and consistency/storage risk. In a strongly read-intensive nightly-refresh workload, denormalization can be justified if the performance gain outweighs those risks.
Dependency transfer
Paper 1 practice
(1) Explain 2NF vs 3NF. (2) ProductName depends only on ProductID within composite key (OrderID, ProductID): identify the problem. (3) Evaluate denormalization for read-heavy reporting.
- 2NF removes partial-key dependencies; 3NF also removes transitive/non-key dependencies.
- ProductName has a partial-key dependency and belongs in PRODUCT.
- Balance simpler/faster reads against redundancy, consistency/update complexity and storage, then judge from read/update frequency.
Finish the learning cycle
IA — main task
Complete the scheduled IA authenticity/final-submission check. Explain one implementation choice, test and evaluation judgement in your own words.
Paper 1 — short practice
Normalize one fresh school/inventory/e-commerce scenario to 3NF and write a short denormalization evaluation.
Checkpoint
External normalization assessment stops at 3NF.