Reduce harmful repetition
Store one independent fact in one logical place whenever the dependencies permit it.
PART 2 • CORRECT DATABASE DESIGN
Remove Redundancy Without Losing Meaning
Diagnose unsafe table designs and transform them through 1NF, 2NF and 3NF using keys and functional dependencies—not memorized slogans.
BY THE END, YOU CAN
- Identify insertion, update and deletion anomalies.
- Verify atomic values for 1NF.
- Remove partial dependencies for 2NF.
- Remove transitive dependencies for 3NF.
- Explain each decomposition using FDs.
- State the highest normal form reached.
Normalization Controls Redundancy and Its Side Effects
Repeated facts are dangerous when one real-world fact must be changed in many rows.
Protect consistency
A department name should not disagree across student-enrolment rows.
Make changes safer
Insert, update and delete one fact without unintentionally affecting another fact.
Clarify responsibility
Each relation should describe one focused subject: student, course, department or enrolment.
Normalization is not “split every table.”
A decomposition must follow dependencies and later must be checked for lossless join and dependency preservation. Those safety tests are covered in Level 8.
UNFRepeating groups→ atomic values1NFPartial dependencies may remain→ remove partial2NFTransitive dependencies may remain→ remove transitive3NFNon-key facts depend on keys
Anomaly Laboratory
Operate on one denormalized ENROLMENT_RECORD table and observe which independent fact is placed at risk.
| student_id | student_name | dept_id | dept_name | course_id | course_title | grade |
|---|
1NF Gives Every Cell One Atomic Value
A relation is in 1NF when each attribute contains values from an atomic domain and there are no repeating groups.
1NF TEST
One row × one attribute = one value
Atomic means indivisible for the database task. A full address may be atomic in one application but should be separated when city or postcode must be searched independently.
No arrays, lists or repeating course_1, course_2, course_3 columns| student_id | phones |
|---|---|
| 101 | 9876, 8765 |
One cell contains multiple phone values.
| student_id | phone |
|---|---|
| 101 | 9876 |
| 101 | 8765 |
Each row contains one phone value.
2NF Removes Partial Dependency on a Candidate Key
A relation must be in 1NF, and every non-prime attribute must depend on the whole of every candidate key.
CANDIDATE KEY{student_id, course_id}
student_id → student_name, dept_id, dept_namePARTIAL
course_id → course_title, instructorPARTIAL
{student_id, course_id} → gradeFULL
Why the first two dependencies violate 2NF
student_id and course_id are proper subsets of the composite key. They determine non-prime attributes without requiring the entire key.
student_id, student_name, dept_id, dept_namecourse_id, course_title, instructorstudent_id, course_id, grade3NF Removes Transitive Non-Key Determination
A relation must be in 2NF, and non-prime attributes should not depend transitively on a candidate key through another non-key attribute.
student_idkey→dept_idnon-key determinant→dept_namenon-key fact
student_id → dept_id and dept_id → dept_name imply student_id → dept_name. Department name belongs with its real determinant, dept_id.
student_id, student_name, dept_id (FK)dept_id, dept_namecourse_id, course_title, instructorstudent_id (FK), course_id (FK), gradeFORMAL 3NF CONDITION
For every non-trivial FD X → A, either X is a super key or A is prime (belongs to some candidate key).
Guided Decomposition Visualizer
Move through the normal forms. Every stage identifies the dependency removed and the design concern that remains.
Highest Normal Form Diagnostic
Describe a relation’s properties and receive the highest guaranteed normal form with the first required correction.
Describe the dependency conditions
The result will explain each normal-form gate in order.
Eight Formative Concept Checks
1. Which situation is an update anomaly?
2. The central 1NF requirement is:
3. For key {student_id, course_id}, student_id → student_name is:
4. Which normal form removes partial dependencies?
5. student_id → dept_id and dept_id → dept_name create:
6. A 2NF relation with no transitive non-key dependency reaches:
7. A relation with a single-attribute candidate key is in 1NF. Which is impossible?
8. Which statement is most accurate?
Answered correctly: 0 of 8
University and Interview Questions
- Define normalization.
- What is an update anomaly?
- State the condition for 1NF.
- Define partial dependency.
- Define transitive dependency.
- Explain insertion, update and deletion anomalies with one table.
- Normalize a relation from UNF to 1NF.
- Convert a relation with a composite key into 2NF.
- Normalize a 2NF relation into 3NF using its FDs.
- Can a 1NF table still contain redundancy?
- Why does 2NF matter mainly for composite keys?
- What is the formal 3NF condition?
- Normalization versus denormalization?
- How would you validate a decomposition?
Show the step-by-step normalization answer format
- Write the relation and all candidate keys.
- List the valid functional dependencies.
- State the current normal form and prove why.
- Identify the exact violating dependency.
- Decompose relations with primary and foreign keys marked.
- Repeat the dependency test on every new relation.
- State the final normal form.
- Mention lossless join and dependency preservation as required safety checks.
LEVEL 7 SUMMARY
You Can Now Move a Schema from Repetition to 3NF
- Anomalies expose harmful redundancy.
- 1NF requires atomic values and removes repeating groups.
- 2NF removes partial dependency of non-prime attributes on candidate keys.
- 3NF removes transitive non-key determination.
- Every decomposition must be justified by keys and dependencies.
- Formal 3NF permits X → A when X is a super key or A is prime.
COURSE CHECKPOINT
Mark this level when you can diagnose a relation and show every decomposition from 1NF through 3NF with written reasoning.
Saved in this browser only.