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 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 |
Continue the lesson path
Study next in this module
01Unit 3Multivalued DependencyA multivalued dependency (MVD) exists in a relation when one attribute uniquely determines a set of values for another attribute, independently of all other...02Unit 3Normalization — IntroductionNormalization is a systematic technique in database design used to organize relations (tables) in a way that reduces data redundancy and eliminates...03Unit 3Lossless DecompositionDecomposition is the process of breaking a relation R into two or more smaller relations R1, R2, ..., Rn such that the union of attributes in all Ri equals...04Unit 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...