DBMS Normalization – 1NF 2NF 3NF Explained for GATE CSE 2026
Normalization is the most important topic of DBMS for GATE. Every year 1-2 questions are asked. Let’s understand with easy Hinglish + English.
What is Normalization?
Normalization is used to remove redundancy (duplicate data) and improve data integrity.
1NF – First Normal Form
Rule: Each cell should have single value. No multi-values.
Wrong:
Student | Subjects
Amit | C, Python, Java
Correct 1NF:
Student | Subject
Amit | C
Amit | Python
Amit | Java
2NF – Second Normal Form
Rule: Must be in 1NF + No Partial Dependency.
Partial Dependency means non-key attribute depends on part of composite key.
For table: StudentID, SubjectID -> Marks, StudentName
Here StudentName depends only on StudentID (part of key)
So it is NOT in 2NF.
Solution: Split into 2 tables
Table1: StudentID -> StudentName
Table2: StudentID, SubjectID -> Marks
3NF – Third Normal Form
Rule: Must be in 2NF + No Transitive Dependency.
Transitive: A -> B, B -> C then A -> C (indirect)
Example:
StudentID -> DeptID, DeptID -> DeptName
So StudentID -> DeptName (Transitive)
Not in 3NF. Split:
Table1: StudentID -> DeptID
Table2: DeptID -> DeptName
GATE PYQ on Normalization
Q1. GATE CSE 2021
Q: Which normal form removes transitive dependency?
Ans: 3NF
Q2. GATE CSE 2020
Q: Table R(A,B,C,D) with FD A->B, B->C. Is it in 3NF?
Ans: No, because B->C is transitive dependency (A->B->C). Need to decompose.
Q3. GATE CSE 2019
Q: What is highest normal form of relation?
Trick: First find Candidate Keys, then check 1NF, 2NF, 3NF, BCNF step by step.
Quick Revision Table for GATE
| Normal Form | Removes |
|---|---|
| 1NF | Multi-valued attribute |
| 2NF | Partial Dependency |
| 3NF | Transitive Dependency |
| BCNF | All remaining anomalies |
Conclusion
Database Normalization GATE PYQ [1994-2005] – 20 Questions with Solution
DBMS me Normalization se har saal 2-4 marks aate hain. Yaha 1994 se 2005 tak ke 20 PYQ ka complete question, options aur solution Hindi trick ke saath diya gaya hai.
3NF > 2NF > 1NF” width=”100%”>
Q1. [GATE 1994 : 1 Mark – True/False] There is always a decomposition into Boyce-Codd Normal Form (BCNF) that is lossless and dependency preserving.
Options:
(A) TRUE
(B) FALSE
Answer: (B) FALSE
Solution: BCNF decomposition hamesha lossless hota hai par dependency preserving hona zaruri nahi hai. 3NF me hamesha lossless + dependency preserving dono milte hain. Isliye 3NF ko adequate mana jata hai.
Q2. [GATE 1995 : 2 Marks] Consider a relation scheme R=(A,B,C) with FDs AB → C, C → A. Show that this scheme is in 3NF but not in BCNF.
Options: (a) 3NF and BCNF both (b) 3NF but not BCNF (c) BCNF but not 3NF (d) Neither
Answer: (b) 3NF but not BCNF
Solution: Candidate Keys = AB and BC.
– AB → C : AB is superkey → BCNF condition satisfied.
– C → A : C is not superkey but A is prime attribute (part of AB). So 3NF allows it, BCNF does not.
Trick: RHS prime ho to 3NF pass hai.
Q3. [GATE 1997 : 2 Marks] Consider relation R(a,b,c,d) with FDs a → c, b → d. This relation is in?
(A) 1NF but not in 2NF
(B) 2NF but not in 3NF
(C) 3NF
(D) BCNF
Answer: (A) 1NF but not in 2NF
Solution: Key = ab.
a → c me ‘a’ key ka proper subset hai aur ‘c’ non-prime hai. Ye Partial Dependency hai. Isliye 2NF fail, sirf 1NF me hai.
Q4. [GATE 1997 : 1 Mark] Consider R(a,b,c) and S(d,e,f). d is foreign key of S that refers to primary key of R. Which operation may cause violation of referential integrity?
(A) Insert into R and Delete from S
(B) Insert into S and Insert into R
(C) Delete from R and Delete from S
(D) Insert into S and Delete from R
Answer: (D) Insert into S and Delete from R
Solution Hindi: Child table S me insert karoge to parent R me value hona chahiye. Parent R se delete karoge to child orphan ho jayega. Dono case me referential integrity tootegi.
Q5. [GATE 1998 : 1 Mark] Which normal form is considered adequate for normal relational database design?
(A) 2NF
(B) 5NF
(C) 4NF
(D) 3NF
Answer: (D) 3NF
Solution: 3NF me lossless + dependency preserving dono milte hain aur redundancy bhi kaafi kam ho jati hai. Isliye practical design ke liye 3NF adequate hai.
Q6. [GATE 1998 : 1 Mark] A table has Category field with values A,B,C,D. Index file has entries 1,3,2,5,4. Index is built on which field?
(A) Category
(B) Name
(C) Occupation
(D) Age
Answer: (C) Occupation
Solution: Occupation ka order CON, ENG, DOC, SER, MUS = A,A,B,D,C jo 1,3,2,5,4 se match karta hai. Ye dense index ka example hai.
Q7. [GATE 1998 : 2 Marks] Relation with attributes Book_id (PK), Subject_Category, Name_of_Author, Nationality. (a) Highest normal form? (b) If PK is {Name_of_Author, Book_title} and Author_address added, highest NF?
(A) 2NF, 1NF
(B) 3NF, 2NF
(C) 1NF, 1NF
(D) 3NF, 1NF
Answer: (A) First case 2NF, Second case 1NF
Solution: (a) Book_id → Author, Author → Nationality = Transitive dependency (Author non-prime). So 2NF me hai, 3NF me nahi. (b) Ab Author → Address partial dependency ban jayegi, isliye sirf 1NF me rahega.
Q8. [GATE 1999 : 1 Mark] Let R = (A, B, C, D, E, F) be a relation scheme with FDs C → F, E → A, EC → D, A → B. Which one is a key for R?
(A) CD
(B) EC
(C) AE
(D) AC
Answer: (B) EC
Solution: Closure of EC: EC → EC, C→F => ECF, E→A => ECFA, A→B => ECFAB, EC→D => ECFABD = All attributes. So EC is key.
Q9. [GATE 1999 : 2 Marks] Consider schema R=(S,T,U,V) and FDs S→T, T→U, U→V, V→S. Let R1(S,T) and R2(T,U,V) be decomposition. This decomposition is in?
(A) 1NF only
(B) 2NF but not 3NF
(C) 3NF but not BCNF
(D) BCNF
Answer: (B) 2NF but not in 3NF
Solution: Candidate keys are S,T,U,V all are keys. But decomposition check me T→U,V violates BCNF because T is not superkey of original but after decomposition dependency preserved hai.
Q10. [GATE 2000 : 2 Marks] Given instance: X Y Z / 1 4 2 / 1 5 3 / 1 6 3 / 3 2 2. Which FDs are satisfied?
(A) XY→Z and Z→Y
(B) YZ→X and Y→Z
(C) YZ→X and X→Z
(D) XZ→Y and Y→X
Answer: (B) YZ→X and Y→Z
Solution: Same YZ ka X same hai? Check karo Y=4,Z=2→X=1 only one value, Y=5,Z=3→X=1, Y=6,Z=3→X=1, Y=2,Z=2→X=3. So YZ→X holds. Y→Z fails? Y=4→2, 5→3, 6→3, 2→2 distinct hai but 2 values nahi hai. Actually Y→Z fails kyunki Y=?? Data me Y unique hai, but X→Z fails.
Q11. [GATE 2001 : 1 Mark] Schema R(A,B,C,D) with A→B, C→D. Decomposition into R1(A,B) and R2(C,D) is?
(A) Dependency preserving and lossless
(B) Lossless but not dependency preserving
(C) Dependency preserving but not lossless
(D) Neither
Answer: (C) Dependency preserving but not lossless
Solution: A→B is in R1, C→D is in R2 => DP yes. R1∩R2 = ∅ (empty) so condition R1∩R2 → R1/R2 fail, so NOT lossless.
Q12. [GATE 2001 : 2 Marks] Which of the following has NOT both lossless join and dependency preserving BCNF decomposition?
(A) A→B, B→C
(B) A→B, B→C, C→D
(C) AB→C, C→A
(D) AB→C, C→A, A→B
Answer: (B) A→B, B→C, C→D
Solution: Chain wali FD hai. BCNF me todoge to B→C ya C→D dependency toot jayegi. Isliye lossless + DP BCNF possible nahi hai.
Q13. [GATE 2002 : 1 Mark] A relation is decomposed into BCNF. The redundancy is?
(A) Zero
(B) At most 50%
(C) Depends on FD
(D) 25%
Answer: (A) Zero
Solution: BCNF me har determinant superkey hota hai, isliye redundancy zero ho jati hai.
Q14. [GATE 2002 : 2 Marks] R(A,B,C,D) with FDs A→B, B→C, C→D, D→A. Which decomposition is in BCNF?
(A) R1(A,B), R2(B,C), R3(C,D)
(B) R1(A,B,C), R2(C,D)
(C) Check superkey condition
(D) None
Answer: (C) Check superkey – Every LHS is superkey
Solution: BCNF rule: For every FD X→Y, X must be superkey. Is relation me A,B,C,D sab keys hain, isliye BCNF me hai.
Q15. [GATE 2002 : 2 Marks] From the following instance of a relation schema R(A,B,C), we can conclude that:
| A | B | C |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 1 | 0 |
| 2 | 3 | 2 |
| 2 | 3 | 2 |
Options:
A) A functionally determines B and B functionally determines C
B) A functionally determines B and B does not functionally determine C
C) B does not functionally determine C
D) A does not functionally determine B and B does not functionally determine C
Answer: (C) B does not functionally determine C
Solution: B=1 par C=1 bhi hai aur C=0 bhi hai. Same B ka different C hai, isliye B→C FAIL.
Q16. [GATE 2003 : 2 Marks] Relation (Roll_number, Name, Date_of_birth, Age) is in which NF? Given Roll_number→Name, Roll_number→DOB.
(A) 1NF
(B) 2NF
(C) 3NF
(D) None of these / Not even 1NF
Answer: (D) Not in 1NF (as per GATE key) – Actually 1NF only because Age derived attribute
Solution: Age = derived from DOB (Date_of_Birth → Age). Derived attribute allowed nahi hai, aur DOB → Age partial + transitive dono hai. GATE key ne ise None diya tha.
Q17. [GATE 2004 : 1 Mark] Student(rollno, name, address) 120 tuples, Enroll(rollno, courseno, coursename) 8 tuples. Max/Min tuples in Natural Join?
(A) 8,8
(B) 120,8
(C) 960,8
(D) 960,120
Answer: (A) 8,8
Solution: Enroll ka rollno FK hai jo Student ko refer karta hai. Isliye har Enroll tuple ka match Student me pakka milega. Natural join = 8 tuples hi ayenge. Max=Min=8.
Q18. [GATE 2004 : 2 Marks] Relation Performance(name, courseNo, rollNo, grade) with FDs name,courseNo→grade, rollNo,courseNo→grade, name→rollNo, rollNo→name. Highest NF?
(A) 2NF
(B) 3NF
(C) BCNF
(D) 4NF
Answer: (B) 3NF
Solution: Keys = (name,courseNo) and (rollNo,courseNo). Prime attributes = name, rollNo, courseNo.
name→rollNo me LHS key nahi hai par RHS prime hai, isliye 3NF pass, BCNF fail.
Q19. [GATE 2004 : 2 Marks] Empdtl(empcode(unique), name, street, city, state, pincode) with pincode→city,state and (street,city,state)→pincode. Highest NF?
(A) 1NF
(B) 2NF and hence also in 1NF
(C) 3NF
(D) BCNF
Answer: (B) 2NF
Solution: Key = empcode (single). No partial dependency because key single attribute hai. But pincode→city transitive hai (pincode non-superkey, city non-prime). So 3NF fail, 2NF me hai.
Q20. [GATE 2005 : 1 Mark] Table F1,F2,F3,F4,F5 with FDs F1→F3, F2→F4, (F1,F2)→F5. This table is in?
(A) 1NF
(B) 2NF
(C) 3NF
(D) BCNF
Answer: (A) 1NF
Solution: Key = (F1,F2). F1→F3 me F1 key ka part hai aur F3 non-prime hai = Partial Dependency. Isliye 2NF me hi fail. Sirf 1NF me hai.
Conclusion
BCNF > 3NF > 2NF > 1NF. 3NF is adequate for design. Lossless condition: R1∩R2 → R1 or R2.