DBMS-CH-4 Normalization

PART 1: Functional Dependency (FD)

A relationship that exists between two attributes is called Functional Dependency.
⚠️ Concept Correct, Definition Weak.
Better definition:
Y is functionally dependent on X if each value of X determines exactly one value of Y.
Written as:

[ Emp_ID → Emp_Name ]
If Emp_ID = 101 always gives "Ram", then: ✅ Emp_Name depends on Emp_ID.
🔥 X → Y means X determines Y.
🔥 Left side = Determinant.
🔥 Right side = Dependent.
🔥 Functional Dependency is the backbone of Normalization.
If asked:
Functional dependency typically exists between:
A. Primary Key and Non-Key Attributes B. Two arbitrary attributes
C. Only Primary Keys D. Only Foreign Keys
✅ A may be accepted because of the word "typically."
But if asked:
Functional dependency is defined only between primary key and non-key attributes.
Then, ❌ FALSE.
🔥 FD means "determinant determines dependent."
🔥 FD is not restricted to PK → Non-key.
🔥 PK → Non-key is the most common practical case.
🔥 Non-key → Non-key FDs are the reason normalization is needed.
PART 2: Trivial vs Non-Trivial FD
1. Trivial Functional Dependency

DBMS-CH-4 Normalization 1
 2. Non-Trivial Functional Dependency

❌ No Therefore: ✅ Non-trivial FD

3. Completely Non-Trivial

DBMS-CH-4 Normalization 2
 One-Liners
🔥 Trivial: RHS subset of LHS.
🔥 Non-trivial: RHS not subset of LHS.
🔥 Completely Non-trivial: LHS and RHS disjoint.
PART 3: Candidate Key
Candidate Key Definition Recap
A candidate key is:
1. A minimal set of attributes that can uniquely identify a tuple (row) in the table.
2. No proper subset of this set can uniquely identify a tuple.
Student table has attributes:
Student_roll , Name , Sex , Age , Address , Class , Section

Candidate Key 1: (Class, Section, Roll) Candidate Key 2: (Name, Address)

Candidate Key Definition Recap

Minimal Super Key
🔥 Candidate Key = Minimal Super Key.
🔥 Super Key may have extra attributes.
🔥 Candidate Key has no extra attributes.
Multiple candidate keys may exist.
The total number of candidate keys cannot be determined by a fixed formula.
Candidate keys depend on the given FDs.
🔥 Primary Key = Selected Candidate Key.
Every Candidate Key is:
A. Super Key B. Primary Key C. Foreign Key D. Alternate Key
✅ Answer: Super Key
Candidate Key Short Trick
Book's trick:
Attributes not appearing on RHS are essential attributes.

DBMS-CH-4 Normalization 3
 Essential attributes are always part of candidate key - coz they don’t determined by other determined by other
attribute.
✅ VERY IMPORTANT. This trick is heavily used.

Attributes NOT appearing on RHS: ➡ Definitely part of every candidate key.

🔥 RHS Trick:
Attributes absent from RHS must belong to every candidate key.

Step 3: Candidate Key Cases

Case 1
All essential attributes together can determine all remaining non-essential attributes.

DBMS-CH-4 Normalization 4
 Combination of essential attributes = Candidate Key
It is the only possible candidate key.

Example 2
Consider relation R(A, B, C, D, E, F) with FDs:
C→F
E→A
EC → D
A→B
Find the candidate key.
Solution:
Essential attributes: C, E
Closure: (CE)+
Step by step:
1. (CE)+ = {C, E}
2. Add F (C → F) → {C, E, F}
3. Add A (E → A) → {A, C, E, F}
4. Add D (EC → D) → {A, C, D, E, F}
5. Add B (A → B) → {A, B, C, D, E, F}
✅ Candidate Key = CE
Case 2
All essential attributes together cannot determine all remaining non-essential attributes.
Some non-essential attributes must be added to form candidate keys.
Multiple candidate keys are possible.
Example: UGC NET 2020 reference
Essential attributes YZ alone insufficient
Pair with X, V, U to form UYZ, VYZ, XYZ candidate keys.

Step 4: Total Super Keys

Formula: 2^(# of optional attributes) × 1 (essential attributes fixed)
Example above: 2 optional attributes each with 2 choices → total = 2 × 2 × 2 × 2 = 16 super keys.

PYQ UGC NET 2022

Consider relation R(P, Q, R, S, T, U, V, W, X, Y) and FDs:
{P, Q, S} → T
R→Y
{P, Q} → V
{R, X} → X

DBMS-CH-4 Normalization 5
 Q→U
V → {W, X}
Which of the following is a candidate key?
(a) (P, Q, R, S)
(b) {R, Q, X}
(c) {V, X}
(d) None of the above
Solution:
Attributes not on RHS: P, Q, R, S
Closure (PQRS)+ = All attributes → Candidate Key
✅ Answer: (a) (P, Q, R, S)
Canonical Cover Example (UGC NET 2020)
1. Canonical Cover / Minimal Cover
Definition:
A Canonical Cover (also called Minimal Cover) of a set of functional dependencies (FDs) is a simplified
version of the FD set that preserves the original dependencies but:
1. Has no redundant attributes in the left-hand side (LHS) of any FD.
2. Has no redundant FDs (i.e., no FD can be removed without changing the closure of the set).
3. Each FD has a single attribute on the right-hand side (RHS).
Purpose:
Makes the FD set smaller, easier to handle, and useful for:
Database normalization.
Finding candidate keys.
Checking lossless decomposition.

Steps (remember: S-R-R)

1. Split RHS
A → BC ⇒ A → B, A → C
2. Remove extra attributes from LHS
If AB → C and A alone gives C, then AB → C ⇒ A → C
3. Remove redundant FDs
If an FD can be derived from others, delete it.
Relation: R(ABCDEFGH)

FDs:

A → B
ABCD → E
EF → G
EF → H
ACDF → EG

DBMS-CH-4 Normalization 6
 Exam Shortcut Method
Step 1: Split RHS
Only one FD has multiple RHS attributes.

ACDF → EG

Split it:

ACDF → E
ACDF → G

Now:

A → B
ABCD → E
EF → G
EF → H
ACDF → E
ACDF → G

Then continue
After splitting:

A→B
ABCD→E
EF→G
EF→H
ACDF→E
ACDF→G

Replace:

ABCD→E
↓
ACD→E

Now:

A→B
ACD→E
EF→G
EF→H
ACDF→E
ACDF→G

Remove redundant FDs

ACDF → E
Since:

ACDF
↓
ACD→E

DBMS-CH-4 Normalization 7
 ↓
E

Delete it.

ACDF → G
Since:

ACDF
↓
ACD→E
↓
EF
↓
EF→G
↓
G

Delete it.

Final Minimal (Canonical) Cover

✅ Book answer:
A → B
ACD → E
EF → G
EF → H

One-Liners / Revision Triggers (In Book Order)

1. Not on RHS → Essential attributes.
2. Essential closure = all attributes → Candidate Key.
3. Essential closure ≠ all → Add minimum extras for candidate keys.
4. Super Keys = 2^(optional attributes).
5. Candidate Key Questions → RHS trick + closure.
6. Redundant FD → Derivable from other FDs.
7. Minimal Cover → Split RHS → Remove extraneous → Remove redundant.

NORMALIZATION
It is the process of minimizing redundancy from the relation.
Normalization = Reduce redundancy + remove anomalies.
Redundancy causes:
Insertion anomaly
Deletion anomaly
Update anomaly
🔥 Revision:
Repetition ⇒ Anomalies ⇒ Normalization.

DBMS-CH-4 Normalization 8
 1. First Normal Form (1NF)
All attributes must have atomic (indivisible) values.

First Name Last Name Knowledge

Thomas Mueller Java, C++, PHP
Ursula Meier PHP, Java
Igor Mueller C++, Java

Is this 1NF?
❌ No. Because: Knowledge =(Java, C++, PHP) is a multivalued attribute.

Convert into 1NF

Break into separate rows.
Thomas: Similarly for others.
First Name Last Name Knowledge
Thomas Mueller Java
Thomas Mueller C++
Thomas Mueller PHP

One-liner
1NF removes repeating groups and multivalued attributes.
Questions ask:
Which NF removes multivalued attributes?
Is the relation in 1NF?
Convert to 1NF.

2. Second Normal Form (2NF)

Prime attribute:
Part of candidate key.
Non-prime attribute:
Not part of candidate key.

Definition
A relation is in 2NF if:
It is in 1NF.
Every non-prime attribute is fully dependent on the entire key attribute (candidate key).
2NF = 1NF + No Partial Dependency

Composite Key

DBMS-CH-4 Normalization 9
 A composite key is a key made by combining two or more attributes to uniquely identify a tuple (row).
Composite Key = More than one attribute together forms the key.

Student_Project Table
Stu_ID Proj_ID Stu_Name Proj_Name
101 P1 Ram DBMS
101 P2 Ram AI
102 P1 Shyam DBMS
Can Stu_ID alone identify rows? ❌ No.
Student 101 appears twice.
Can Proj_ID alone identify rows? ❌ No.
Project P1 appears twice.
Can (Stu_ID, Proj_ID) identify rows?
✅ Yes.
(101, P1)
(101, P2)
(102, P1)
All combinations are unique. Therefore,

Composite Key = (Stu_ID, Proj_ID)

Why important in Normalization?

Partial dependency occurs only when there is a composite candidate key.
Candidate Key:

(Stu_ID, Proj_ID)

FDs:

Stu_ID → Stu_Name
Proj_ID → Proj_Name

Here,
Stu_Name depends only on Stu_ID.

Proj_Name depends only on Proj_ID.

So this is: 🔥 Partial Dependency

Hence, ❌ Not in 2NF.
Key Types Quick Revision
Primary Key: Chosen candidate key.
Candidate Key: Minimal super key.
Composite Key: Key with ≥ 2 attributes.

DBMS-CH-4 Normalization 10
 Super Key: Uniquely identifies tuples (may contain extra attributes).

Q. Is every composite key a candidate key? ❌ No.

Example: If (A,B) is enough, then (A,B,C) is also composite but not minimal.
Only minimal keys are candidate keys.

Q. Can a primary key be composite? ✅ Yes.

Primary Key = (Stu_ID, Proj_ID)

🔥 Composite Key → Think 2NF → Check Partial Dependency.

They ask:
Identify partial dependency.
Highest NF.
Decompose into 2NF.

3. Third Normal Form (3NF)

A relation is in 3NF if:
1. It is in 2NF, and
2. There is no transitive dependency of non-prime attributes on a candidate key.
Equivalently, for every FD X→A, at least one must hold:
X is a superkey, OR
A is a prime attribute.

Transitive Dependency
If:
A → B
B → C

Then:
A → C is transitive dependency.

For transitive dependency:

1. A is not a super key.
2. B is a non-prime attribute.

Polytechnic One-liner
3NF = 2NF + No Transitive Dependency.

Example (Book)
Student_Detail

Attributes:

DBMS-CH-4 Normalization 11
 Stu_ID, Stu_Name, Zip, City

Dependencies:

Stu_ID → Zip
Zip → City

Therefore:

Stu_ID → City

Transitive.

Not in 3NF
Because: City depends indirectly on key.

Convert into 3NF

Split:

Student
| Stu_ID | Stu_Name | Zip |

Zipcodes
| Zip | City |

Key → Non-prime → Non-prime ⇒ Not 3NF

4. BCNF
For every non-trivial FD X→Y,
X must be a superkey.

🔥 BCNF = Every determinant must be a superkey.

Important Facts (Book)

✅ BCNF is stricter than 3NF.
✅ 3NF is stricter than 2NF.
✅ 2NF is stricter than 1NF.
Diagram:

DBMS-CH-4 Normalization 12
 Remember
Book says:
Every BCNF relation is in 3NF.
True.
Every 3NF relation is in BCNF.
False.

Example (Book)
R(A,B,C,D,E)
FDs:

A → BC
CD → E
B → D
E → A

Possible candidate keys:

A
E
CD
BC

All attributes are prime.

Hence:
✅ Relation is in 3NF. ❌ Not in BCNF.
Polytechnic One-liner
3NF allows some exceptions involving prime attributes; BCNF does not.

Dependency Preserving Decomposition

To check dependency preserving:

DBMS-CH-4 Normalization 13
 F1 ∪ F2 ∪ ... ∪ Fn = F

Original FDs should be preserved after decomposition.

Lossless Decomposition
After decomposition,
Natural join should reconstruct original relation.

One-liner
Join decomposed relations ⇒ Original relation.

Ultra-Short Revision Sheet (Polytechnic)

1NF → Atomic values.
2NF → No partial dependency.
3NF → No transitive dependency.
BCNF → Determinant must be superkey .
BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF.
Partial: Composite key → part → non-prime.
Transitive: Key → non-key → non-key.
Lossless: Join gives original relation.
Dependency preserving: Original FDs preserved.

1. Benefits of Using Normal Forms

✅ Reduces data redundancy.
✅ Eliminates insertion, deletion, and update anomalies.
✅ Improves data consistency and integrity.
✅ Simplifies database maintenance.
🔥 Less redundancy → Less anomalies → Better consistency
🔥 Over-normalization = Too many joins.
Example MCQ Pattern
Over-normalization may lead to:
(a) Reduced redundancy (b) Increased query complexity
(c) Improved update speed (d) Better performance
✅ (b)
Feature Normalization Denormalization
Goal Reduce redundancy Improve query speed
Tables More Fewer
Joins More Less

DBMS-CH-4 Normalization 14
 Feature Normalization Denormalization
Update
Reduced May increase
anomalies
Performance Better writes Better reads
OLAP (Online Analytical Processing): is
OLTP (Online Transaction Processing): handles real-time, day-to- designed for complex data analysis,
Used in
day operations like processing orders or banking transactions. business intelligence, and forecasting
using large volumes of historical data.

Final UPPSC Revision Sheet

🔥 Normalization → Reduce redundancy and anomalies.
🔥 Over-normalization → Too many joins.
🔥 OLTP → Prefer normalization.
🔥 OLAP/Data warehouse → Prefer denormalization.
🔥 BCNF > 3NF > 2NF > 1NF. (Strongest → Weakest)
4NF (Fourth Normal Form)
A relation is in 4NF if:
It is in BCNF and has no non-trivial Multivalued Dependency (MVD).

BCNF + No MVD

What is MVD?
Notation:

A ↠ B

(read as: A multi-determines B)

Meaning:
For one value of A, there can be multiple independent values of

Student Hobby Language

Ram Cricket Hindi
Ram Music Hindi
Ram Cricket English
Ram Music English

Here:
Student ↠ Hobby
Student ↠ Language

Hobby and Language are independent.

Therefore, ❌ Not in 4NF.
Decompose into:
Student_Hobby

DBMS-CH-4 Normalization 15
 Student Hobby
Ram Cricket
Ram Music
Student_Language
Student Language
Ram Hindi
Ram English

🔥 4NF = BCNF + No Multivalued Dependency.

5NF (Fifth Normal Form)
A relation is in 5NF if:
It is in 4NF and has no non-trivial Join Dependency.

Condition
4NF + No Join Dependency

What is Join Dependency?

A relation can be reconstructed only by joining multiple decomposed relations.

Example (Classic)
Suppose:

Supplier
Part
Project

Relation:

SPJ(Supplier, Part, Project)

It decomposes into:

SP
SJ
PJ

If joining them recreates SPJ exactly,

then 5NF is satisfied.

One-liner
🔥 5NF = 4NF + No Join Dependency.

DBMS-CH-4 Normalization 16
 How to Remember?
Think:

4NF
↓
Too many independent relationships
↓
Need multiple joins
↓
5NF

or simply:
5NF deals with decomposition caused by JOINS.

NF Removes Condition One-liner

1NF Repeating groups Atomic values Atomic
2NF Partial Dependency 1NF + Full dependency No Partial
3NF Transitive Dependency 2NF + No transitive No Transitive
BCNF Non-superkey determinant Determinant = Superkey Superkey
4NF Multivalued Dependency BCNF + No MVD No MVD
5NF Join Dependency 4NF + No JD No JD

DBMS-CH-4 Normalization 17