DBMS Topics
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregationCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship ER ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationUnit 1 Summary — Introduction to DBMS & ER ModelThree-Level Architecture ANSI/SPARC ArchitectureWeak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus DRCFunctions and Stored ProceduresJoins in SQLNested Queries SubqueriesRelational AlgebraRelational CalculusRelational ModelSQL IntroductionUnit 2 Summary — Relational Model & SQLTriggers in SQLTuple Relational Calculus TRCViews in SQLArmstrong's Axioms & Closure of AttributesBoyce-Codd Normal Form BCNFClosure of AttributesDependency PreservationFifth Normal Form 5NFFirst Normal Form 1NFFourth Normal Form 4NFFunctional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form 2NFUnit 3 Summary — NormalizationThird Normal Form 3NFUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking 2PLUnit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery OptimizationQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSBig Data and DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions MCQsDBMS Lab Programs
Normal Forms — Overview
Last Updated : 21 May, 2026
Unit 3352 words23 headingsTables includedExamples included
The Normal Forms Ladder
Each higher normal form is a stricter superset of the one below it.
5NF ⊂ 4NF ⊂ BCNF ⊂ 3NF ⊂ 2NF ⊂ 1NF
If a relation is in BCNF, it is automatically in 3NF, 2NF, and 1NF.
| Normal Form | Condition Eliminated |
|---|---|
| 1NF | Non-atomic (multi-valued, composite) attributes |
| 2NF | Partial functional dependencies |
| 3NF | Transitive functional dependencies |
| BCNF | Every determinant is a candidate key |
| 4NF | Non-trivial multivalued dependencies |
| 5NF | Join dependencies |
1NF — First Normal Form
Definition
A relation is in 1NF if:
- All attribute values are atomic (indivisible)
- No repeating groups or arrays
- Each row is unique (has a primary key)
Violation Examples
VIOLATION — Multi-valued attribute:
+------+-------+------------------------------+
| SID | Name | Courses |
+------+-------+------------------------------+
| 1001 | Alice | CS101, CS102, MATH201 | ← NOT atomic
+------+-------+------------------------------+
VIOLATION — Composite attribute stored as one:
+------+------------------+
| EmpID| Address |
+------+------------------+
| 101 | 12 MG Road, Delhi| ← Should be split into street, city
+------+------------------+
VIOLATION — Repeating groups:
+------+-------+-------+-------+
| SID | Phone1| Phone2| Phone3| ← Columns represent repeating data
+------+-------+-------+-------+
Converting to 1NF
Before (Violation)
Student(SID, Name, Courses) Courses = "CS101, CS102"
After (1NF)
Student(SID, Name)
StudentCourse(SID, CourseID) ← Separate table; composite PK
1NF Example
NOT in 1NF
Enrollment(SID, Name, {CID, CName, Grade})
← curly braces = repeating group
In 1NF
Enrollment(SID, Name, CID, CName, Grade)
Primary Key: (SID, CID)
All values are atomic ✓
2NF — Second Normal Form
Definition
A relation is in 2NF if:
- It is in 1NF, AND
- Every non-key attribute is fully functionally dependent on the entire primary key (no partial dependencies)
Partial dependency only occurs when the primary key is composite. If PK is a single attribute, 2NF is automatically satisfied if 1NF holds.
Identifying Partial Dependencies
| Primary Key | (SID, CID) |
| (SID, CID) | Grade ← Full dependency ✓ |
| SID | SName ← PARTIAL (depends on part of PK) ✗ |
| CID | CName ← PARTIAL (depends on part of PK) ✗ |
Converting to 2NF
Remove partial dependencies into separate tables:
Before (1NF, not 2NF):
Enrollment(SID, CID, Grade, SName, CName)
After (2NF):
Student(SID, SName) ← SID → SName moved here
Course(CID, CName) ← CID → CName moved here
Enrollment(SID, CID, Grade) ← Only full dependencies remain
Visual Diagram
BEFORE (Partial Dependencies present):
┌──────────────────────────────────────────────────┐
│ Enrollment(SID, CID, Grade, SName, CName) │
│ ↑↑ │
│ Composite PK │
│ │
│ SID ─────────────────► SName (PARTIAL!) │
│ CID ─────────────────► CName (PARTIAL!) │
│ (SID,CID) ───────────► Grade (FULL ✓) │
└──────────────────────────────────────────────────┘
AFTER (2NF — Partials removed):
Student(SID, SName)
Course(CID, CName)
Enrollment(SID, CID, Grade)
3NF — Third Normal Form
Definition
A relation is in 3NF if:
- It is in 2NF, AND
- No non-key attribute is transitively dependent on the primary key
A transitive dependency: X → Y → Z (where X is the PK, Y is a non-key attribute, Z depends on Y).
Identifying Transitive Dependencies
| Primary Key | EmpID |
| EmpID | DeptID ← direct (OK) |
| DeptID | DeptName ← DeptName depends on non-key DeptID |
| DeptID | DeptLocation |
| EmpID | DeptName ← TRANSITIVE (via DeptID) ✗ |
| EmpID | DeptLocation← TRANSITIVE (via DeptID) ✗ |
Converting to 3NF
Before (2NF, not 3NF):
Employee(EmpID, Name, DeptID, DeptName, DeptLocation)
After (3NF):
Employee(EmpID, Name, DeptID) ← DeptID FK to Department
Department(DeptID, DeptName, DeptLocation)
3NF Test (Formal Definition)
A relation R with FD set F is in 3NF if, for every non-trivial FD X → A in F⁺:
- X is a superkey of R, OR
- A is a prime attribute (part of some candidate key)
BCNF — Boyce-Codd Normal Form
Definition
A relation is in BCNF if, for every non-trivial functional dependency X → Y, X is a superkey (candidate key or superset).
BCNF is stricter than 3NF — the 3NF exception for prime attributes is removed.
3NF vs BCNF
3NF allows: X → A where A is a prime attribute (even if X is not a superkey)
BCNF requires: X → A ONLY if X is a superkey (no exceptions)
Example — 3NF but NOT BCNF
| (Student, Course) | Instructor (a student-course pair has one instructor) |
| Instructor | Course (each instructor teaches one course) |
| Candidate Keys | (Student, Course) and (Student, Instructor) |
| Is it in 3NF? YES (the FD Instructor | Course has Course as prime attribute) |
| Is it in BCNF? NO! Because Instructor | Course, |
Converting to BCNF
Before
CourseInstructor(Student, Course, Instructor)
The FD causing violation: Instructor → Course
Decompose by pulling out the violating FD
Instructor_Course(Instructor, Course) ← contains Instructor → Course
Student_Instructor(Student, Instructor) ← remaining attributes
Both are in BCNF ✓
BCNF Decomposition Algorithm
WHILE relation R is not in BCNF
Find FD X → Y in R that violates BCNF
(X is not a superkey)
Decompose R into
R1 = X ∪ Y (XY)
R2 = X ∪ (R − Y) (R minus Y, keep X)
Replace R with R1 and R2
Comparison Table
| 1NF | 2NF | 3NF | BCNF | |
|---|---|---|---|---|
| -- | ----- | ----- | ----- | ------ |
| Atomic values | ✓ | ✓ | ✓ | ✓ |
| No partial deps | ✓ | ✓ | ✓ | |
| No transitive deps | ✓ | ✓ | ||
| Every determinant is candidate key | ✓ | |||
| Dependency preservation guaranteed | ✓ | ✓ | ✓ | Not always |
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Normal Forms — Overview.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, normal, forms, overview
Related DBMS Topics
Continue learning this concept
Unit 3Fifth Normal Form 5NFA relation R is in Fifth Normal Form 5NF — also called Project-Join Normal Form PJ/NF — if, for every non-trivial join dependency R1, R2, ..., Rn in R:Unit 3First Normal Form 1NFA relation is in First Normal Form 1NF if:Unit 3Fourth Normal Form 4NFA relation R is in Fourth Normal Form 4NF if, for every non-trivial multivalued dependency X →→ Y in R:Unit 3Second Normal Form 2NFA relation is in Second Normal Form 2NF if:Unit 3Third Normal Form 3NFA relation is in Third Normal Form 3NF if:Unit 3Boyce-Codd Normal Form BCNFA relation R is in BCNF Boyce-Codd Normal Form if, for every non-trivial functional dependency X → Y in R: