Courses / HEC NSCT – National Skill Competency Test: Complete Preparation for Computing Graduates
4.3 Normalization – 1NF to BCNF
Area 4 – Databases (10%) · 50 min read · 9 MCQs to check yourself
Normalization organises tables to remove redundancy and anomalies (insert, update, delete anomalies).
Functional dependency (FD) A → B: each value of A gives exactly one value of B (RollNo → Name).
| Normal form | Rule | Fix |
|---|---|---|
| 1NF | Every column holds atomic (single) values; no repeating groups | Split multi-valued cells into rows/tables |
| 2NF | 1NF + no partial dependency (non-key column depending on part of a composite key) | Move partially dependent columns to a new table |
| 3NF | 2NF + no transitive dependency (non-key → non-key) | Move the dependent columns out (e.g. Zip → City) |
| BCNF | For every FD X → Y, X is a super key | Stricter 3NF; decompose further |
| 4NF / 5NF | Remove multivalued / join dependencies | Advanced |
Example: Enrolment(StudentID, CourseID, StudentName, CourseTitle, Grade) with key (StudentID, CourseID).
- StudentName depends only on StudentID → partial dependency → violates 2NF.
- Fix: Student(StudentID, StudentName), Course(CourseID, CourseTitle), Enrolment(StudentID, CourseID, Grade).
Denormalization deliberately adds redundancy to speed up reads (reports, data warehouses).
Database normalization – 1NF to 5NF (Decomplexify)
This lesson ends with a quiz of 9 MCQs. Open the lesson on PrepOasis to take it and save your progress.
Previous: 4.2 SQL – Queries, Joins and Aggregation · Next: 4.4 Transactions, Concurrency and Recovery