Normal Forms & Database Normalization – Complete Exam Guide 2025 | GATE, IBPS, SSC

🗄️ Database Management · Exam 2025

Normal Forms &
Database Normalization

1NF · 2NF · 3NF · BCNF · 4NF · 5NF · Partial Dependency · Transitive Dependency · Functional Dependency · Lossless Decomposition · 45 MCQs with Explanations — GATE, IBPS, SSC, UPSC & Bank Exams

GATE CSIBPS POSSC CGLUPSC GS-III NDA/CDSRBI Grade BCampus Placements

📋 Table of Contents

1. What is Normalization? 2. Database Anomalies 3. Types of Dependencies 4. Keys in RDBMS 5. Normal Forms Overview 6. 1NF — First Normal Form 7. 2NF — Second Normal Form 8. 3NF — Third Normal Form 9. BCNF — Boyce-Codd NF 10. 4NF and 5NF 11. Master Comparison Table 12. Lossless Join & Dependency Preservation 13. MCQ Practice (45 Qs) 14. Quick Revision Cards

🗄️

FoundationWhat is Database Normalization?

Normalization is the process of organising a relational database to reduce data redundancy and eliminate data anomalies. It involves decomposing large, poorly designed tables into smaller, well-structured tables using a set of rules called Normal Forms.

⚡ Core Exam Facts

Normalization is based on the concept of Functional Dependency (FD)
Goal: reduce redundancy, improve data integrity, eliminate anomalies
A relation is considered adequately normalised at 3NF (most real databases)
Order: 1NF → 2NF → 3NF → BCNF → 4NF → 5NF (each is stricter than the previous)
If a table is in BCNF, it is also in 3NF, 2NF, and 1NF
Normalization improves data integrity but may reduce query performance (more joins needed)

⚠️

Why We Need NormalizationDatabase Anomalies

Anomaly Type	What Happens	Example
Insertion Anomaly	Cannot insert data without providing unnecessary other data	Cannot add a new department unless a student is already enrolled in it
Update Anomaly	Changing one piece of data requires multiple row updates → inconsistency risk	Changing a department name requires updating every student row in that department
Deletion Anomaly	Deleting one entity accidentally removes other unrelated information	Deleting the last student in a department also removes the department record

📌 Root Cause of Anomalies

All three anomalies are caused primarily by data redundancy — storing the same information in multiple places
Update anomaly is the most common trap question — answer is always redundant data
Normalization eliminates redundancy → eliminates all three anomalies

🔗

Core Concepts — Must Know All 4Types of Dependencies

🔴 Partial Dependency

Removed by 2NF

A non-key attribute depends on only PART of a composite primary key, not the whole key. Only occurs when the primary key is composite (2+ attributes).

PK = (StudentID, Subject)
StudentName → StudentID only
❌ Not (StudentID + Subject)

How to FixMove StudentName to a separate table with StudentID as the only key.

🟠 Transitive Dependency

Removed by 3NF

A non-key attribute depends on another non-key attribute instead of depending directly on the primary key. Chain: Key → A → B (B is transitively dependent on Key via A).

PK = StudentID
StudentID → DeptID → DeptName
DeptName is transitively dependent

How to FixMove DeptID and DeptName to a separate Departments table.

🟡 Multi-Valued Dependency

Removed by 4NF

Attribute A multi-determines attribute B if for each value of A, there are multiple independent values of B. Notation: A →→ B.

Student →→ Course
Student →→ Hobby
(Two independent MVDs)

How to FixSplit into two tables: (Student, Course) and (Student, Hobby).

🟣 Join Dependency

Removed by 5NF

A relation R has a join dependency if it can be reconstructed by joining multiple projections without information loss. Can’t always be detected from FDs alone.

R = R1 ⋈ R2 ⋈ R3
(Lossless join of 3+ projections)

How to FixDecompose into multiple tables such that the lossless join holds.

Functional Dependency Basics

Concept	Notation	Meaning	Example
Functional Dependency	`X → Y`	X functionally determines Y; knowing X uniquely identifies Y	StudentID → StudentName
Determinant	`X` (left side)	The attribute(s) that determine other attributes	StudentID is the determinant
Dependent	`Y` (right side)	The attribute(s) that are determined	StudentName is the dependent
Trivial FD	`X → Y` where Y ⊆ X	Y is a subset of X — always holds, not useful	{A,B} → A
Multi-valued Dep.	`X →→ Y`	X multi-determines Y — multiple Y values per X independently	Student →→ Hobby

🔑

Essential TerminologyKeys in RDBMS

Key Type	Definition	Example
Super Key	Any set of attributes that uniquely identifies a row (may have extra attributes)	{StudentID}, {StudentID, Name}
Candidate Key	Minimal super key — no attribute can be removed and still be unique	{StudentID} or {Email} if both are unique
Primary Key	One candidate key chosen as the main identifier for the table	StudentID is the primary key
Prime Attribute	An attribute that is part of ANY candidate key	StudentID (in a CK) is a prime attribute
Non-prime Attribute	An attribute NOT part of any candidate key	StudentName, DepartmentName
Foreign Key	An attribute that references the primary key of another table	DeptID in Students table → Departments table

📌 Key Rules for BCNF

In BCNF: every determinant must be a candidate key (not just any attribute)
Super key ⊃ Candidate key ⊃ Primary key (superset to subset)
A table with only ONE candidate key → BCNF = 3NF (they’re the same)
Prime attribute = part of any candidate key | Non-prime = not part of any CK

📐

Progressive Rules — Each Builds on PreviousNormal Forms Overview

1NF

First Normal Form

✂️ Removes: Repeating groups & non-atomic values

All values must be atomic (single, indivisible). No multi-valued attributes (no lists in a cell). No repeating groups or nested tables. Every row must be unique.

Rule: Atomic values in every cell

2NF

Second Normal Form

✂️ Removes: Partial dependency

Must be in 1NF. Every non-prime attribute must be fully functionally dependent on the entire primary key. No attribute can depend on only part of a composite key. (Only relevant when PK is composite.)

Rule: 1NF + No partial dependency

3NF

Third Normal Form

✂️ Removes: Transitive dependency

Must be in 2NF. For every FD X → Y: either X is a super key, OR Y is a prime attribute (part of a candidate key). No non-prime attribute should depend on another non-prime attribute.

Rule: 2NF + No transitive dependency

BCNF

Boyce-Codd Normal Form

✂️ Removes: All FD-based anomalies

Must be in 3NF. For every non-trivial FD X → Y, X must be a super key (not just any attribute). Stricter than 3NF — does not allow the 3NF exception where Y is a prime attribute.

Rule: Every determinant must be a candidate key

4NF

Fourth Normal Form

✂️ Removes: Multi-valued dependency (MVD)

Must be in BCNF. For every non-trivial multi-valued dependency X →→ Y, X must be a super key. Handles independent multi-valued facts stored together.

Rule: BCNF + No multi-valued dependency

5NF

Fifth Normal Form (PJNF)

✂️ Removes: Join dependency

Must be in 4NF. Every join dependency must be implied by the candidate keys. The relation cannot be losslessly decomposed into smaller relations. Hardest to detect and rarely used in practice.

Rule: 4NF + No join dependency

1️⃣

Most Basic — Always Required1NF — First Normal Form

Worked Example — Converting to 1NF

❌

Violates 1NF — Non-Atomic Values

A table where one cell contains multiple values (like a comma-separated list) violates 1NF.

StudentID	Name	Subjects
1	John	Math, Science
2	Alice	Physics, Chem

✅

Fixed — 1NF Compliant

Each cell has exactly ONE atomic value. Split multi-valued rows into separate rows.

StudentID	Name	Subject
1	John	Math
1	John	Science
2	Alice	Physics
2	Alice	Chem

✅ 1NF Rules — Exam Summary

All values atomic — one value per cell, no lists or sets
No repeating groups — no arrays or multi-value columns
All rows unique — each row should be identifiable
1NF removes repeating group anomalies
Example of 1NF violation: Phone column containing “9999, 8888” in one cell

2️⃣

Composite Key Problem2NF — Second Normal Form

Worked Example — Partial Dependency → 2NF

❌

Violates 2NF — Partial Dependency

Composite PK = (StudentID, Subject). StudentName depends only on StudentID, not the full key.

StudentID	Subject	StudentName ❌	Marks
1	Math	John	90
1	Science	John	85
2	Math	Alice	88

✅

Fixed — 2NF Compliant (Split into 2 tables)

Students
StudentID (PK)	StudentName
1	John
2	Alice

Marks
StudentID	Subject	Marks
1	Math	90
1	Science	85
2	Math	88

✅ Result

Partial dependency eliminated. StudentName now depends fully on its own PK (StudentID). Marks depends on the full composite key (StudentID + Subject).

🎯 2NF — Exam Key Points

2NF only matters when the primary key is COMPOSITE (2+ attributes)
If the primary key is a single attribute, a table in 1NF is automatically in 2NF
Partial dependency: non-prime attribute depends on part of the composite PK
Fix: split the table so each non-prime attribute depends on the entire key of its table

3️⃣

Most Practical Normal Form3NF — Third Normal Form

Worked Example — Transitive Dependency → 3NF

❌

Violates 3NF — Transitive Dependency

PK = StudentID. StudentID → DeptID → DeptName. DeptName is transitively dependent on StudentID via DeptID.

StudentID (PK)	StudentName	DeptID	DeptName ❌
1	John	D1	Computer Sci
2	Alice	D2	Physics
3	Bob	D1	Computer Sci

✅

Fixed — 3NF Compliant (Split into 2 tables)

Students
StudentID	StudentName	DeptID
1	John	D1
2	Alice	D2
3	Bob	D1

Departments
DeptID (PK)	DeptName
D1	Computer Sci
D2	Physics

✅ Result

Transitive dependency eliminated. DeptName now directly depends on its own PK (DeptID). No redundancy — if dept name changes, update one row only.

📌 3NF Formal Condition

For every FD X → Y in the table: either X is a super key, OR Y is a prime attribute
3NF allows the second exception (Y is prime) — this is what BCNF tightens
Most real-world databases are designed to 3NF as it balances integrity and performance
Transitive chain: A → B → C means C is transitively dependent on A via B

💎

Stricter than 3NFBCNF — Boyce-Codd Normal Form

Worked Example — BCNF Violation with Multiple Candidate Keys

❌

Violates BCNF — Non-key Determinant

CKs: (StudentID, Course) and (Course, Instructor). FD: Course → Instructor. Here Course is not a candidate key by itself, yet it determines Instructor.

StudentID	Course	Instructor ❌
1	DBMS	Prof. A
2	Networks	Prof. B
3	DBMS	Prof. A

✅

Fixed — BCNF Compliant (Split into 2 tables)

Course_Instructor
Course (PK)	Instructor
DBMS	Prof. A
Networks	Prof. B

Student_Course
StudentID	Course
1	DBMS
2	Networks
3	DBMS

✅ Result

Now every determinant is a candidate key. Course determines Instructor — and Course is the PK of Course_Instructor table.

📌 3NF vs BCNF — Key Difference

▶3NF: X → Y is allowed if either X is a super key OR Y is a prime attribute
▶BCNF: X → Y is allowed ONLY if X is a super key — no prime attribute exception
▶BCNF may not always preserve all dependencies while 3NF always can
▶If table has only ONE candidate key, BCNF ≡ 3NF (they are the same)
▶BCNF is the highest normal form based on functional dependencies only

⬆️

Beyond BCNF4NF and 5NF

Normal Form	Dependency Addressed	Condition	Used In Practice?
4NF	Multi-Valued Dependency (MVD) `X →→ Y`	For every non-trivial MVD X →→ Y, X must be a super key	Rarely — theoretical mostly
5NF (PJNF)	Join Dependency	Every join dependency is implied by candidate keys; no lossless decomposition possible	Extremely rare — academic

4NF Example — Multi-Valued Dependency

❌

Violates 4NF

A student can have multiple hobbies AND multiple courses — two independent multi-valued facts stored in one table creates redundant rows.

Student	Course	Hobby
Alice	DBMS	Painting
Alice	DBMS	Singing
Alice	Networks	Painting
Alice	Networks	Singing

✅

Fixed — Split into 2 tables (4NF)

Student	Course
Alice	DBMS
Alice	Networks

Student	Hobby
Alice	Painting
Alice	Singing

⚖️

Most Asked Table in ExamsMaster Comparison Table

Normal Form	Built On	Eliminates	Condition	Exam One-liner
1NF	—	Repeating groups, non-atomic values	All values atomic; no multi-valued cells	“No lists in cells”
2NF	1NF	Partial dependency	Every non-prime attr. fully depends on whole PK	“Whole key, nothing but the key”
3NF	2NF	Transitive dependency	X→Y: X is super key OR Y is prime attr.	“Non-key doesn’t depend on non-key”
BCNF	3NF	All FD anomalies	Every determinant must be a candidate key	“Every determinant is a key”
4NF	BCNF	Multi-valued dependency	For every MVD X→→Y, X is super key	“Remove independent multi-facts”
5NF	4NF	Join dependency	Every join dependency implied by CKs	“No lossless split possible”

🔄

BCNF Trade-offLossless Join & Dependency Preservation

Property	Meaning	Guaranteed By
Lossless Join	Decomposed tables can be JOINed back without gaining or losing any tuples	The common attribute between decomposed tables must be a super key in at least one of them
Dependency Preservation	All original functional dependencies can be derived from the decomposed tables without joining	3NF decomposition always preserves; BCNF may not

⚠️ BCNF vs 3NF Trade-off — Most Tested Exam Topic

3NF decomposition → always achieves lossless join + dependency preservation
BCNF decomposition → always achieves lossless join BUT may NOT preserve all dependencies
This is the key trade-off: BCNF is stronger but may sacrifice dependency preservation
Lossless condition: shared attribute must be a super key in at least one decomposed relation
When BCNF and dependency preservation cannot both be achieved → settle for 3NF

📝

Tap Any Option to Reveal AnswerMCQ Practice — 45 Questions (4 Chapters)

Score: 0 / 0

CH.11NF, 2NF & BasicsQ.01–Q.12

Q.011NF🔥 Most Asked

A relation is in 1NF if:

✔ Correct: C

1NF condition = all values are atomic (single, indivisible) — no lists, no multi-valued cells, no repeating groups. Each cell holds exactly one value. This is the most basic requirement for a relation.

Q.021NF🔥 Most Asked

A table violates 1NF when it contains:

✔ Correct: C — Repeating groups or non-atomic attributes

1NF is violated when a column stores multiple values (e.g., “Math, Science” in Subjects column) or has repeating groups (multiple columns for the same attribute like Phone1, Phone2). NULL values alone don’t violate 1NF.

Q.032NF🔥 Most Asked

Partial dependency occurs when:

✔ Correct: A

Partial dependency = non-prime attribute depends on part of the composite PK, not the whole. Example: PK = (StudentID, Subject), but StudentName depends only on StudentID → partial dependency.

Q.042NF🔥 Most Asked

2NF is only relevant when:

✔ Correct: B — Composite primary key

Partial dependency (what 2NF removes) can only exist when the PK is composite. If PK = single attribute, there’s no “part” to depend on, so a 1NF table with a single-attribute PK is automatically in 2NF.

Q.052NF🔥 Most Asked

A relation is in 2NF if it is in 1NF and:

✔ Correct: B — No partial dependency

2NF = 1NF + No partial dependency. Every non-prime (non-key) attribute must be fully functionally dependent on the entire primary key — not just a part of it.

Q.06Anomalies🔥 Most Asked

Update anomalies in a database are primarily caused by:

✔ Correct: B — Data redundancy

All three anomalies (insertion, update, deletion) are caused by data redundancy. When the same fact is stored in multiple rows, updating it requires changing every copy — if one is missed, the data becomes inconsistent.

Q.07Normalization🔥 Most Asked

The primary goal of database normalization is to:

✔ Correct: A

Normalization’s primary goal = reduce redundancy + eliminate anomalies (insert, update, delete). It also improves data integrity and consistency. The trade-off: more tables = more joins needed for queries.

Q.08Keys🔥 Most Asked

A prime attribute is:

✔ Correct: A — Part of any candidate key

A prime attribute belongs to at least one candidate key. A non-prime attribute is not part of any candidate key. This distinction is critical for understanding 3NF and BCNF conditions.

Q.09Keys🔥 Most Asked

What is a candidate key?

✔ Correct: B — Minimal super key

A Candidate Key = minimal super key. Remove any attribute → no longer uniquely identifies. Example: {StudentID} is a CK; {StudentID, Name} is a super key (not minimal). Every table must have at least one CK.

Q.10FD🔥 Most Asked

In the functional dependency X → Y, X is called:

✔ Correct: A — Determinant

In X → Y: X is the determinant (left side — what determines). Y is the dependent (right side — what is determined). BCNF requires every determinant to be a candidate key.

Q.111NF

Which anomaly does 1NF eliminate?

✔ Correct: C — Repeating group anomaly

1NF specifically eliminates repeating groups and non-atomic values. Partial dependency → 2NF. Transitive dependency → 3NF. Multi-valued dependency → 4NF.

Q.12Normalization🔥 Most Asked

A table is considered adequately normalised for most practical purposes when it reaches:

✔ Correct: C — 3NF

3NF is the standard target for most real-world databases. It eliminates partial and transitive dependencies — covering most practical anomalies. BCNF is stronger but may sacrifice dependency preservation, so 3NF is often the practical choice.

CH.23NF, BCNF & DifferencesQ.13–Q.27

Q.133NF🔥 Most Asked

A transitive dependency means:

✔ Correct: A — A→B and B→C implies A→C transitively

Transitive dependency: if A→B and B→C, then C is transitively dependent on A via B. Example: StudentID → DeptID → DeptName. DeptName is transitively dependent on StudentID.

Q.143NF🔥 Most Asked

A relation is in 3NF if it is in 2NF and:

✔ Correct: A — X is super key OR Y is prime attribute

3NF formal condition: for every non-trivial FD X→Y, either X is a super key (strong case) OR Y is a prime attribute (allows exception). This exception is what BCNF removes — making BCNF stricter than 3NF.

Q.153NF🔥 Most Asked

Which normal form eliminates transitive dependency?

✔ Correct: C — 3NF

Dependency → Normal Form: Repeating groups → 1NF. Partial dependency → 2NF. Transitive dependency → 3NF. All FD anomalies → BCNF. Multi-valued dependency → 4NF. Join dependency → 5NF.

Q.16BCNF🔥 Most Asked

A table is in BCNF if:

✔ Correct: B — Every determinant must be a candidate key

BCNF condition: for every non-trivial FD X→Y, X must be a super key. No exceptions (unlike 3NF which allows Y to be prime). This makes BCNF stricter than 3NF.

Q.17BCNF🔥 Most Asked

BCNF is stricter than 3NF because:

✔ Correct: A

3NF allows FD X→Y when Y is a prime attribute even if X is not a super key. BCNF removes this exception — every determinant must be a candidate key, period. This makes BCNF handle more anomalies.

Q.18BCNF🔥 Most Asked

BCNF is violated when:

✔ Correct: A

BCNF is violated when a non-key attribute acts as a determinant (determines another attribute) — because it’s not a candidate key. Example: Course → Instructor where Course is not a candidate key by itself.

Q.19BCNF🔥 Most Asked

The highest normal form achievable using FUNCTIONAL DEPENDENCIES only is:

✔ Correct: B — BCNF

BCNF is the highest normal form that is purely based on functional dependencies. 4NF goes beyond to handle multi-valued dependencies (not FDs). 5NF handles join dependencies.

Q.203NF vs BCNF🔥 Most Asked

What does 3NF allow that BCNF does NOT?

✔ Correct: C

3NF allows X→Y when Y is a prime attribute, even if X is not a super key. BCNF removes this exception — in BCNF, X must be a super key always. This is the exact difference between 3NF and BCNF.

Q.21BCNF🔥 Most Asked

If a table has only ONE candidate key, then 3NF and BCNF are:

✔ Correct: A — Identical for single candidate key tables

When there’s only ONE candidate key, every prime attribute belongs to that key. The 3NF exception (Y is prime) coincides exactly with X being a super key — making 3NF and BCNF equivalent for single-CK tables.

Q.223NF

Which NF does NOT allow transitive dependency?

✔ Correct: C — 3NF and above

Transitive dependency is allowed in 1NF and 2NF but is eliminated starting from 3NF. Any table in 3NF, BCNF, 4NF, or 5NF has no transitive dependency (since each is built on the previous).

Q.23Applied🔥 Most Asked

Table: Employee(EmpID, Dept, Manager). FD: Dept → Manager. Key: EmpID. Which NF is violated?

✔ Correct: C — BCNF violated

Dept → Manager exists, but Dept is NOT a candidate key (EmpID is the only CK). This violates BCNF (every determinant must be a CK). It also violates 3NF (Manager is a non-prime attribute determined by a non-super-key Dept).

Q.24Applied🔥 Most Asked

R(A,B,C), FD: A→B, Key = A. R is in which normal form?

✔ Correct: C — 3NF and BCNF

Key = A (single attribute). FD: A→B where A is the candidate key. The determinant (A) IS the candidate key → BCNF satisfied. Since only one CK exists, 3NF = BCNF here. No partial/transitive dependency.

Q.25Applied🔥 Most Asked

Composite key (A, B). Dependency A → C exists. This indicates:

✔ Correct: A — Partial dependency

Composite PK = (A, B). If C depends on A alone (not on B), then it’s a partial dependency — C depends on PART of the composite key. This violates 2NF.

Q.26Applied🔥 Most Asked

Table: Order(OrderID, Product, Supplier, SupplierPhone). FD: Supplier → SupplierPhone. Key: (OrderID, Product). Which NF is violated?

✔ Correct: D — BCNF violated

Supplier → SupplierPhone, but Supplier is NOT a candidate key (CK = OrderID + Product). BCNF requires every determinant to be a CK. Fix: separate table (Supplier, SupplierPhone).

Q.27Applied

A table has attributes A, B, C. If A→B and B→C exist, then A→C is called:

✔ Correct: B — Transitive dependency

If A→B and B→C, then C is transitively dependent on A via B. This is exactly the transitive dependency that 3NF eliminates. Fix: move B and C to a separate table with B as the key.

CH.34NF, 5NF & Advanced ConceptsQ.28–Q.37

Q.284NF🔥 Most Asked

Which normal form deals with multi-valued dependency?

✔ Correct: D — 4NF

Dependency → Normal Form mapping: Partial → 2NF. Transitive → 3NF. All FD-based → BCNF. Multi-valued dependency → 4NF. Join dependency → 5NF.

Q.295NF🔥 Most Asked

5NF (Fifth Normal Form) deals with:

✔ Correct: B — Join dependency

5NF (Project-Join Normal Form / PJNF) eliminates join dependencies. A relation is in 5NF if every join dependency is implied by the candidate keys. The most complex and least commonly applied in practice.

Q.304NF🔥 Most Asked

A table is in BCNF but NOT in 4NF when:

✔ Correct: A — Multi-valued dependency with non-super-key determinant

4NF extends BCNF by handling multi-valued dependencies (MVDs). A table in BCNF can still violate 4NF if it has independent multi-valued facts stored together (like Student →→ Course and Student →→ Hobby).

Q.31Lossless🔥 Most Asked

A decomposition is lossless (lossless join) when:

✔ Correct: B — Common attribute is a super key in one decomposed relation

Lossless Join condition: when joining the decomposed tables produces exactly the original relation (no extra or missing tuples). This holds when the intersection (common attributes) is a super key in at least one decomposed relation.

Q.32Lossless🔥 Most Asked

Dependency preservation in decomposition means:

✔ Correct: A — All FDs verifiable without joining

Dependency Preservation: all FDs of the original relation can be checked against the individual decomposed tables without needing to join them. This is important for maintaining integrity constraints efficiently.

Q.33BCNF Trade-off🔥 Most Asked

BCNF decomposition always guarantees:

✔ Correct: C — Lossless join but not always DP

Key trade-off: BCNF = always lossless join, but may NOT preserve all dependencies. 3NF = always both lossless join AND dependency preservation. When both cannot be achieved together, 3NF is preferred.

Q.34MVD

Multi-valued dependency A →→ B means:

✔ Correct: A — Multiple independent values of B for each A

Multi-valued dependency (MVD): A →→ B means for each value of A, there are multiple values of B, independently of other attributes. Example: Student →→ Course (a student can take multiple courses).

Q.35Applied🔥 Most Asked

Table Student(ID, Course, Instructor). CKs: (ID, Course) and (Course, Instructor). FD: Course → Instructor. Which NF is violated?

✔ Correct: C — BCNF violated

Course → Instructor is an FD. Is Course a super key? Course alone is NOT a super key (it’s part of CK (Course, Instructor) but not by itself). BCNF violated because determinant Course is not a super key.

Q.36Keys🔥 Most Asked

The correct relationship between super key, candidate key, and primary key is:

✔ Correct: B — Super key ⊃ Candidate key ⊃ Primary key

Hierarchy: Super key = any attribute set that uniquely identifies. Candidate key = minimal super key. Primary key = one selected candidate key. All candidate keys are super keys, but not all super keys are CKs.

Q.37Normalization🔥 Most Asked

Functional dependencies are used to determine:

✔ Correct: D — All of the above

Functional dependencies are the foundation of database normalisation. They are used to: determine candidate keys, identify normal form violations, detect anomalies, and design lossless decompositions.

CH.4Applied & Comprehensive QuestionsQ.38–Q.45

Q.38NF Sequence🔥 Most Asked

The correct sequence of normal forms from weakest to strongest is:

✔ Correct: A — 1NF → 2NF → 3NF → BCNF → 4NF → 5NF

Each normal form is stricter than the previous: 1NF → 2NF → 3NF → BCNF → 4NF → 5NF. If a table is in 5NF, it is automatically in 4NF, BCNF, 3NF, 2NF, and 1NF.

Q.39Applied🔥 Most Asked

A table in 3NF is also in:

✔ Correct: A — Also in 2NF and 1NF

Higher normal forms subsume lower ones. A table in 3NF is automatically in 2NF and 1NF. It is NOT necessarily in BCNF (3NF allows an exception that BCNF doesn’t).

Q.40Applied🔥 Most Asked

Normalization is said to improve:

✔ Correct: C — Data integrity and reduced redundancy

Normalization improves data integrity and reduces redundancy. It may actually reduce query performance (needs more JOINs) — this is a common trade-off. Higher normalisation = less redundancy + more joins needed.

Q.41Applied🔥 Most Asked

Which is NOT true about normalization?

✔ Correct: C — This is NOT true

Normalization may actually reduce query performance because more tables are created → more JOINs needed in queries. This is the key trade-off: higher normalisation = better integrity but potentially slower queries.

Q.42Applied🔥 Most Asked

When it is impossible to simultaneously achieve BCNF, lossless join, AND dependency preservation, we typically settle for:

✔ Correct: B — 3NF

The practical standard: when BCNF + dependency preservation cannot both be achieved, we settle for 3NF, which always guarantees both lossless join AND dependency preservation. This is the most commonly cited design choice in practice.

Q.43Applied🔥 Most Asked

Which of the following pairs is CORRECTLY matched?

✔ Correct: C

Complete mapping: Repeating groups → 1NF. Partial → 2NF. Transitive → 3NF. All FD anomalies → BCNF. MVD → 4NF. Join dependency → 5NF. This is the most comprehensive and important mapping to memorise for exams.

Q.44Applied🔥 Most Asked

A relation R has only ONE candidate key and no multi-valued dependencies. R is in BCNF. R must also be in:

✔ Correct: D

BCNF ⊇ 3NF ⊇ 2NF ⊇ 1NF. If in BCNF, automatically in 3NF, 2NF, 1NF. With no MVD, 4NF conditions are also satisfied (4NF only concerns non-trivial MVDs). One CK means BCNF = 3NF anyway.

Q.45Comprehensive🔥 Most Asked

Which statement BEST summarises the progression of normal forms?

✔ Correct: C

Each NF targets a specific issue: 1NF (atomic), 2NF (partial), 3NF (transitive), BCNF (FD determinant = key), 4NF (MVD), 5NF (join). Each is strictly stronger than the previous, progressively reducing anomalies. 3NF does NOT handle MVDs (that’s 4NF).

⚡

Last-Minute PrepQuick Revision Flash Cards

📋 Normal Forms Summary

1NF = Atomic values, no repeating groups
2NF = 1NF + No partial dependency
3NF = 2NF + No transitive dependency
BCNF = Every determinant is a candidate key
4NF = BCNF + No multi-valued dependency
5NF = 4NF + No join dependency

🔴 Partial Dependency

Non-prime attr depends on PART of composite PK
Removed by: 2NF
Only occurs with composite primary keys
Single-attribute PK = no partial dependency possible
Fix: Split into separate tables

🟠 Transitive Dependency

A → B → C (C is transitively dependent on A)
Removed by: 3NF
Non-key attribute depends on another non-key attribute
Example: StudentID → DeptID → DeptName
Fix: Move B→C to a separate table

💎 BCNF Key Points

Every determinant must be a candidate key
Stricter than 3NF — removes 3NF’s prime-attr exception
BCNF = highest NF for functional dependencies only
Single CK table: BCNF = 3NF
May NOT preserve all dependencies (trade-off)

🔄 Lossless & Dependency

Lossless join: common attribute = super key in one table
3NF = always lossless + dependency preserving
BCNF = always lossless, but may NOT preserve deps
If both impossible → settle for 3NF

🔑 Key Types

Super key = any unique identifier (may have extras)
Candidate key = minimal super key
Primary key = chosen candidate key
Prime attribute = part of any candidate key
Non-prime = not part of any candidate key

⚠️ Three Anomalies

Insertion anomaly = can’t insert without unrelated data
Update anomaly = change one = change many
Deletion anomaly = delete one = lose unrelated data
Root cause: data redundancy
Solution: normalization

🎯 Exam Mnemonics

1NF: “No lists in cells”
2NF: “Whole key, nothing but key”
3NF: “Non-key doesn’t depend on non-key”
BCNF: “Every determinant is a key”
4NF: “No independent multi-facts”
5NF: “No lossless split possible”

📌 Must-Know Keywords

1NF = Atomic values 2NF = No partial dep. 3NF = No transitive dep. BCNF = Determinant is CK 4NF = No MVD 5NF = No join dep. Partial dep. → Composite PK A→B→C = Transitive 3NF preserves deps BCNF may lose deps

📚 Exam Focus: 1NF=atomic values · 2NF=1NF+no partial dep (needs composite PK) · 3NF=2NF+no transitive dep · BCNF=every determinant is candidate key · 4NF=BCNF+no MVD · 5NF=4NF+no join dep · Partial: non-prime depends on PART of composite PK · Transitive: A→B→C · BCNF stricter than 3NF (no prime attr exception) · Single CK: 3NF=BCNF · 3NF: always lossless+DP · BCNF: always lossless, may lose DP · When both impossible → 3NF · Anomalies caused by redundancy · Prime attr = part of any CK · BCNF is highest NF for FDs only · 4NF handles MVD (A→→B) · 5NF handles join dependency

© GyanDesk — Normal Forms & Database Normalization | GATE · IBPS · SSC · UPSC · NDA · Bank Exams 2025

Related notes

More free study notes in Information Technology — all part of the GyanDesk study library.

Emerging Technologies Unveiled: AI, ML, Blockchain & More

Cybersecurity in Indian Banking and RBI Guidelines

RBI Cybersecurity Framework for Banks

🔒 Credential Stuffing: What It Is & How to Protect Yourself

Virtual RM & Gen AI Chatbot