Download DBMS Normalization Notes (1NF–BCNF) | Solved Examples + Practice Questions (PDF) and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
DBMS Normalization Notes
(1NF → 2NF → 3NF → BCNF)
Long-Form Notes (Exam + Viva + Interview Ready)
What’s inside:
- Full theory of normalization with real exam-focused explanations
- Functional Dependencies, Keys, Attribute Closure, Minimal Cover
- Lossless Join & Dependency Preservation (with conditions)
- Many solved examples + section-wise exercises + final practice set
- Quick revision sheet + common mistakes & tips
Creator: Samiksha
Updated: 22 Jan 2026
Tip: Use this PDF for last-minute revision and for writing crisp answers in exams.
Table of Contents
(^1) 1. Normalization Overview & Goals
(^2) 2. Redundancy and Anomalies (Insert/Update/Delete)
(^3) 3. Functional Dependencies (FDs) — Types + Examples
(^4) 4. Keys and Prime Attributes (Super/Candidate/Primary)
(^5) 5. Attribute Closure (Xn) — Step-by-step method
(^6) 6. Minimal Cover / Canonical Cover (FD simplification)
(^7) 7. 1NF — Atomic values & removing multivalued attributes
(^8) 8. 2NF — Partial dependency (with composite keys)
(^9) 9. 3NF — Transitive dependency + 3NF test rule
(^10) 10. BCNF — Determinant must be a superkey
(^11) 11. Decomposition: Lossless Join Property
(^12) 12. Dependency Preservation
(^13) 13. Normalization Workflow (Algorithm)
(^14) 14. Solved Examples (Easy → Advanced)
(^15) 15. Practice Exercises (Topic-wise)
(^16) 16. University/GATE-style Questions + Quick Revision Sheet
2. Redundancy and Anomalies
2.1 Data Redundancy
Redundancy occurs when the same information is stored multiple times in one table. This increases memory usage and may create inconsistent values if updates are not applied everywhere.
2.2 Anomalies
- (^) Update anomaly: Updating one value requires updating multiple rows. Missing any row leads to inconsistency.
- (^) Insert anomaly: You cannot insert a new fact without adding another unrelated fact.
- (^) Delete anomaly: Removing a record may delete important information accidentally.
2.3 Example Table (Not Normalized)
STUDENT_COURSE (Roll, Name, Dept, Course, Teacher)
Roll Name Dept Course Teacher 1 Rahul CSE DBMS Amit 1 Rahul CSE OS Neha 2 Priya IT DBMS Amit 3 Aman CSE CN Riya
Exercise (Anomalies):
- If teacher Amit changes to Amit Sharma, what anomaly occurs?
- If the last DBMS record is deleted, which information is lost?
- Can we insert a new Course='AI' without a student entry in this design?
3. Functional Dependencies (FDs)
A Functional Dependency is written as X → Y. It means that for any two tuples, if they have the same value of X, then they must have the same value of Y. In simple words: X determines Y.
Example: Roll → Name If two rows have same Roll, they must have the same Name.
3.1 Types of Functional Dependencies
- Trivial FD: Y ⊆ X (e.g., AB → A). Always true.
- Non-trivial FD: Y is not a subset of X (e.g., Roll → Dept).
- Completely Non-trivial FD: X ∩ Y = ∅ (e.g., Course → Teacher).
3.2 Determinant
The left-hand side of an FD (X) is called the determinant. It determines the right-hand side attributes.
Exercise (FD validation): Assume Roll is unique but Name can repeat.
- Roll → Name
- Name → Roll
- Course → Teacher
5. Attribute Closure (X n )
Attribute closure helps us determine all attributes functionally determined by a set X under a given set of FDs.
5.1 Steps to Compute X n
- Start with Xn = X
- For each FD (A → B), if A ⊆ Xn then add B to Xn
- Repeat until no new attribute can be added
5.2 Solved Example
R(A,B,C,D) , FDs: A→B, B→C, C→D Find An.
Start An={A} A→B ⇒ {A,B} B→C ⇒ {A,B,C} C→D ⇒ {A,B,C,D} So A is a candidate key.
Exercise: R(A,B,C,D,E), FDs: A→B, B→C, AC→D, D→E. Find An and (AC)n.
6. Minimal Cover / Canonical Cover
A minimal cover is an equivalent set of functional dependencies that is simplified. It has single attributes on RHS, no extraneous attributes on LHS, and no redundant FDs.
6.1 Steps to Find Minimal Cover
- Split RHS so each FD has one attribute on RHS (A→BC becomes A→B and A→C).
- Remove extraneous attributes from LHS (if AB→C and A alone can determine C, remove B).
- Remove redundant FDs (if an FD can be derived from others, remove it).
6.2 Worked Example
FDs: A→BC, B→C, A→B Step 1: A→B, A→C, B→C, A→B (remove duplicate A→B) Now A→C is redundant because A→B and B→C imply A→C. Minimal cover = {A → B, B → C}
Exercise: Find minimal cover for FDs: AB→C, A→C, C→D.
8. Second Normal Form (2NF)
A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on the whole candidate key.
8.1 Partial Dependency
Partial dependency occurs when a non-prime attribute depends on only part of a composite candidate key.
Example: STUDENT_COURSE(Roll,Course,Name,Dept), Key=(Roll,Course) FD: Roll→Name,Dept (depends only on part of key) ⇒ partial dependency ⇒ Not in 2NF.
8.2 Decomposition to 2NF
Decompose into: STUDENT(Roll, Name, Dept) ENROLLMENT(Roll, Course)
Exercise: R(A,B,C,D), Key=(A,B), FDs: A→C, AB→D. Normalize to 2NF.
9. Third Normal Form (3NF)
A relation is in 3NF if it is in 2NF and does not have transitive dependency for non-prime attributes.
9.1 Transitive Dependency
If A→B and B→C, then A→C. If B is not a key and C is non-prime, it violates 3NF.
9.2 Example (Not in 3NF)
EMP(Eid, Ename, DeptId, DeptName) FDs: Eid→DeptId, DeptId→DeptName So Eid→DeptName (transitive) ⇒ Not in 3NF.
Decompose: EMP(Eid,Ename,DeptId) and DEPT(DeptId,DeptName)
9.3 3NF Test Rule (Very Important)
A relation R is in 3NF if for every FD X→A, either: n X is a superkey, OR n A is a prime attribute
Exercise: R(A,B,C,D), FDs: A→B, B→C, A→D. Key=A. Is it in 3NF?
11. Decomposition: Lossless Join Property
Decomposition splits one relation into smaller relations. A correct decomposition should be lossless (no spurious tuples on join).
11.1 Lossless Join Condition
Decomposition of R into R1 and R2 is lossless if: (R1 ∩ R2) → R1 OR (R1 ∩ R2) → R
11.2 Example
R(A,B,C) decomposed into R1(A,B) and R2(B,C). Intersection = {B}. If B→A or B→C, then lossless.
Exercise: R(A,B,C,D) → R1(A,B), R2(B,C), R3(C,D). Discuss lossless join.
12. Dependency Preservation
A decomposition is dependency preserving if we can enforce all original FDs by checking each decomposed relation separately, without performing a join.
Important: BCNF decomposition may sometimes lose dependency preservation. 3NF decomposition often preserves dependencies.
14. Solved Examples (Easy → Advanced)
Solved Example 1: 1NF Conversion
R(StudID, Name, Phones) where Phones has multiple numbers. Convert to 1NF: R1(StudID,Name,Phone) with one phone per row.
Solved Example 2: 2NF Decomposition (Partial Dependency)
R(Roll, Course, Name, Dept), Key=(Roll,Course) FD: Roll→Name,Dept (partial dependency). Decompose: STUDENT(Roll,Name,Dept) and ENROLL(Roll,Course).
Solved Example 3: 3NF Decomposition (Transitive Dependency)
R(Eid, Ename, DeptId, DeptName, DeptLoc) FDs: Eid→Ename,DeptId ; DeptId→DeptName,DeptLoc Transitive: Eid→DeptName,DeptLoc ⇒ Not 3NF Decompose: EMP(Eid,Ename,DeptId) + DEPT(DeptId,DeptName,DeptLoc).
Solved Example 4: BCNF Decomposition
R(A,B,C) with FDs: A→B, B→C Key = A. BCNF violation: B→C (B not superkey). Decompose: R1(B,C) and R2(A,B).
Solved Example 5: 3NF but Not BCNF Case
R(Student,Course,Teacher), FDs: SC→T, T→C 3NF satisfied but BCNF violated because T not superkey. Decompose: (Teacher,Course) and (Student,Teacher).
15. Practice Exercises (Topic-wise)
Set A: Keys + Closure
- R(A,B,C,D), FDs: A→B, B→C, A→D. Find candidate key(s).
- R(A,B,C,D,E), FDs: AB→C, C→D, D→E. Find (AB)n.
- If candidate keys are {A,C} and {B,D}, identify prime attributes.
Set B: Normal Forms
- R(A,B,C,D), Key=AB, FDs: A→C, AB→D. Normalize to 2NF.
- R(A,B,C,D), FDs: A→B, B→C, A→D. Normalize to 3NF.
- R(S,T,C), FDs: ST→C, C→T. Check BCNF and decompose.
Set C: Lossless Join & Dependency Preservation
- State the lossless join condition for R→R1,R2.
- Explain dependency preservation with an example.
