Working with Normalization

1. Understanding Functional Dependencies

Type	Notation	Meaning
Functional Dependency	X → Y	X uniquely determines Y
Full FD	{A,B} → C	Both A and B needed
Partial FD	A → C (subset of PK)	Violates 2NF
Transitive FD	A → B → C	Violates 3NF
Trivial FD	X → Y where Y ⊆ X	Always holds
Multi-valued (MVD)	X →→ Y	Violates 4NF

2. Applying First Normal Form

1NF Rule	Requirement
Atomic Values	No lists, sets, or composite values in a cell
No Repeating Groups	No `phone1, phone2, phone3` columns
Unique Rows	Primary key defined
Same Type per Column	Consistent data type in each column

Example: 1NF Conversion

-- BAD: multi-valued
-- customer(id, name, phones='555-1, 555-2')

-- 1NF: split into separate table
CREATE TABLE customer_phones (
  customer_id BIGINT REFERENCES customers(id),
  phone       VARCHAR(20),
  PRIMARY KEY (customer_id, phone)
);

3. Applying Second Normal Form

2NF Rule	Requirement
Must be 1NF	Already satisfies 1NF
No Partial Dependencies	Non-key attrs depend on entire PK, not part
Applies When	PK is composite

Example: Removing Partial Dependency

-- BAD: product_name depends only on product_id, not (order_id, product_id)
-- order_items(order_id, product_id, qty, product_name)

-- 2NF: move product_name to products
CREATE TABLE products    (id BIGSERIAL PK, name TEXT);
CREATE TABLE order_items (
  order_id   BIGINT, product_id BIGINT, qty INT,
  PRIMARY KEY (order_id, product_id)
);

4. Applying Third Normal Form

3NF Rule	Requirement
Must be 2NF	Already satisfies 2NF
No Transitive Dependencies	Non-key attrs depend only on PK
Test	For each non-trivial FD X→A, X must be superkey OR A part of candidate key

Example: Removing Transitive Dependency

-- BAD: zip → city, state (transitive via zip)
-- customer(id, name, zip, city, state)

-- 3NF
CREATE TABLE zip_codes (zip CHAR(5) PRIMARY KEY, city TEXT, state CHAR(2));
CREATE TABLE customers (id BIGSERIAL PK, name TEXT, zip CHAR(5) REFERENCES zip_codes(zip));

5. Applying Boyce-Codd Normal Form (BCNF)

BCNF Rule	Requirement
Must be 3NF	Stricter form of 3NF
Determinant is Superkey	For every non-trivial FD X→Y, X must be superkey
When Matters	Overlapping candidate keys exist

6. Applying Fourth Normal Form

4NF Rule	Requirement
Must be BCNF	Plus eliminate MVDs
No Multi-valued Dependencies	Independent multi-valued facts in separate tables

Note: If student → courses and student → hobbies are independent, put them in separate tables — not a single (student, course, hobby) table.

7. Applying Fifth Normal Form

5NF Rule	Requirement
Must be 4NF	Plus no join dependencies that aren't implied by keys
Decomposable	Cannot lose info when splitting/rejoining tables
Rare in Practice	Mostly theoretical concern

8. Identifying Normalization Anomalies

Anomaly	Symptom	Caused By
Insert Anomaly	Can't add data without unrelated data	Missing entity decomposition
Update Anomaly	Same fact in multiple rows; partial updates	Redundancy
Delete Anomaly	Removing row loses unrelated info	Coupled entities
Redundancy	Same data stored in many rows	Lack of decomposition

9. Using Normalization Tools and Techniques

Tool	Purpose
ERwin / ER/Studio	ER modeling with normalization
dbdiagram.io / DrawSQL	Schema diagrams with FK detection
DBeaver / DataGrip	Reverse-engineer + FD discovery
SchemaSpy	Generates schema docs, finds anomalies
Synthesis Algorithm	3NF synthesis from FDs
Decomposition Algorithm	BCNF lossless-join decomposition

10. Balancing Normalization vs Performance

Goal	Approach
OLTP (writes)	3NF/BCNF — minimize redundancy, fast updates
OLAP (reads)	Star/snowflake — denormalize fact tables
Hybrid (HTAP)	Normalized core + materialized read views
Trade-off	More joins (normalized) vs more storage + update cost (denormalized)

Warning: Don't denormalize until profiling proves a normalized schema is too slow. Premature denormalization creates data integrity bugs.