Database Normalization
Simple & Easy Study Guide
Chapter 10 — Functional Dependencies & Normal Forms
Functional Dependencies — Problems & Scenarios
1
What is a Functional Dependency (FD)?
An FD means: knowing one piece of data tells you another piece for certain.
We write it as X → Y which reads: "X determines Y".
Example: knowing your Student ID tells you your name → ID → Name.
Real-life examples:
SSN → Employee Name One social security number belongs to exactly one person.
Project# → Project Name, One project number always maps to the same project name
Location and place.
{SSN, Project#} → Hours You need BOTH the employee AND the project to know
hours worked.
What goes wrong when FDs are mixed badly? — The 3 Anomalies:
Update Anomaly The same fact is stored in many rows. Changing it means fixing ALL rows.
Example: Renaming project 'Billing' to 'Customer Accounting' requires
updating 100 rows — one per employee.
Insert Anomaly You can't add new data unless some unrelated data already exists.
Example: You can't add a new project to the database unless at least one
employee is assigned to it.
Delete Anomaly Deleting one thing accidentally wipes out another.
Example: Removing the last employee on a project also deletes the project
itself from the database.
Inference Rules — how to discover more FDs:
Reflexive If Y is part of X, then X → Y automatically. A set always determines its own
subsets.
Augmentation If X → Y, then adding extra columns to both Adding the same thing to both
sides still works: XZ → YZ. sides keeps the rule valid.
Transitivity If X → Y and Y → Z, then X → Z. Like a chain: if A knows B, and B
knows C, then A knows C.
Decomposition If X → YZ, then X → Y and X → Z You can split a combined FD into
separately. individual ones.
Union If X → Y and X → Z, then X → YZ. Opposite of decomposition —
combine two FDs with the same
left side.
Introduction to Normalization & Normal Forms
2
What is Normalization?
It's the process of cleaning up a messy database table by breaking it into smaller, well-structured
tables. Each 'Normal Form' is a level of cleanliness with its own rule.
The 4 Normal Forms — from basic to strictest:
Form Simple Rule What It Fixes Example Problem Solved
1NF Every cell must hold ONE Removes repeating Phone numbers stored as '555-1234,
value only — no lists, no groups and nested data 555-5678' in one cell → split into
groups separate rows
2NF Every non-key column must Removes partial In (StudentID, CourseID) →
depend on the WHOLE dependencies CourseName: CourseName only
primary key, not just part of needs CourseID, so move it out
it
3NF Non-key columns must Removes transitive SSN → DeptNo → DeptName:
depend ONLY on the key dependencies DeptName depends on DeptNo, not
— not on other non-key SSN → split it out
columns
BCNF Every FD's left-hand side Stricter than 3NF, Any column that determines another
must be a superkey — no handles edge cases must itself be able to uniquely identify
exceptions at all with multiple candidate a row
keys
Easy memory trick: Each normal form builds on the last.
1NF: depend on the key
2NF: depend on the whole key
3NF: depend on nothing but the key
BCNF: the same, but with zero exceptions.
Key terms to know:
Prime attribute A column that is part of a candidate (possible primary) key.
Non-prime attribute A column that is NOT part of any key. These are what normalization rules focus
on.
Superkey Any set of columns that can uniquely identify every row. A key is a minimal
superkey.
Candidate key A possible primary key — a minimal superkey. A table can have more than one.
Partial dependency When a non-key column depends on only PART of a composite key (violates
2NF).
Transitive dependency When non-key column A determines another non-key column B (violates 3NF).
Dependency Preservation & BCNF Decomposition
3
What is Decomposition?
When a table is not in a good normal form, we SPLIT it into two or more smaller tables. This is
called decomposition. But we must be careful: splitting must not lose information.
Two rules when splitting a table:
Lossless Join When you join the split tables back together, you get exactly the
REQUIRED — Cannot be original data — no extra fake rows (called spurious tuples). This rule
sacrificed must ALWAYS hold.
Dependency All original FD rules should still be checkable in the smaller tables
Preservation without needing to join them back. This is nice to have but can be
PREFERRED — Can given up for BCNF.
sometimes be sacrificed
How to do a BCNF Decomposition — Step by Step:
Check each FD in your table.
1 Ask: Is the left-hand side a superkey? (Can it uniquely identify every row?)
Find a violating FD.
2 If X → A but X is NOT a superkey, that's a BCNF violation.
Split the table.
3 Make Table 1 = {X + A}. Make Table 2 = {everything else + X}. X appears in both as the link.
Repeat.
4 Check if the new tables are in BCNF. If not, decompose again.
Verify lossless join.
5 Make sure joining the tables back gives the original table exactly. This is mandatory.
Classic BCNF Example — TEACH table:
Table: TEACH(Student, Course, Instructor)
FD1: {Student, Course} → Instructor — a student + course pair has one instructor
FD2: Instructor → Course — each instructor teaches only one course
Problem: 'Instructor' is not a superkey, so FD2 violates BCNF.
The table is in 3NF but NOT BCNF.
Best decomposition (lossless):
→ Table A: {Instructor, Course}
→ Table B: {Instructor, Student}
This is the only split that guarantees no spurious rows when you rejoin. It does lose FD1, but that is
acceptable — lossless join is the priority.
Quick Summary — All 3 Topics
Topic Core idea in one line
X → Y means knowing X always tells you Y. Bad FDs cause
Functional Dependencies
update/insert/delete anomalies.
Normalization & Normal Clean up tables level by level: 1NF → 2NF → 3NF → BCNF, each
Forms removing a specific problem.
Split tables that violate BCNF. Lossless join is a must; dependency
BCNF Decomposition
preservation is a bonus.
Study guide generated from Chapter 10 — Functional Dependencies and Normalization