DBMS Notes
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregation — Database Management SystemsCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship (ER) ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationDBMS Unit 1 Summary - Database FundamentalsThree-Level Architecture (ANSI/SPARC Architecture)Weak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus (DRC) - Complete TutorialFunctions and Stored ProceduresJoins in SQLNested Queries (Subqueries)Relational AlgebraRelational Calculus in Database Management SystemsRelational ModelSQL IntroductionDBMS Unit 2 Summary - SQL and Relational ModelTriggers in SQL — Database Management SystemsTuple Relational Calculus (TRC)Views in SQL — Database Management SystemsArmstrong\Boyce-Codd Normal Form (BCNF) — Database Management SystemsClosure of AttributesDependency PreservationFifth Normal Form (5NF)First Normal Form (1NF) - Database NormalizationFourth Normal Form (4NF)Functional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form (2NF) — Database Management SystemsUnit 3 Summary — NormalizationThird Normal Form (3NF) — Database Management SystemsUnit 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 (2PL)Unit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery Optimization — Database Management SystemsQuery 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 (MCQs)DBMS Lab Programs
Each higher normal form is a stricter superset of the one below it.
The Normal Forms Ladder
Example
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 |
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:
Example
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
Example
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
Example
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 Database Management Systems (DBMS) topic.
Search Terms
dbms, database management systems (dbms), unit, normal, forms, overview, normal forms — overview
Related Database Management Systems (DBMS) Topics
Continue learning this concept
Unit 3Fifth Normal Form (5NF)A relation R is in Fifth Normal Form (5NF) — also called Project-Join Normal Form (PJ/NF) — if every non-trivial join dependency in R is implied by the...Unit 3First Normal Form (1NF) - Database NormalizationComplete guide to First Normal Form (1NF) normalization. Learn what 1NF is, why itUnit 3Fourth Normal Form (4NF)A relation R is in Fourth Normal Form (4NF) if, for every non-trivial multivalued dependency X →→ Y in R, the determinant X is a superkey of R. Fourth...Unit 3Second Normal Form (2NF) — Database Management SystemsA relation is in Second Normal Form (2NF) if it satisfies two conditions: first, it must already be in First Normal Form (1NF), and second, every non-prime...Unit 3Third Normal Form (3NF) — Database Management SystemsA relation is in Third Normal Form (3NF) if it satisfies two conditions: first, it must be in Second Normal Form (2NF), and second, no non-prime attribute...Unit 3Armstrong\Armstrong\