Database Management Systems

Unit 4: Normalization

Functional Dependencies, Normal Forms (1NF → BCNF), Decomposition & Denormalization — the science of designing databases that don't break.

🏢 Oracle & PostgreSQL | 📝 15 MCQs (Bloom's) | 🔬 5 Lab Exercises | 💼 Interview Prep

Section 1

Why This Chapter Pays Your Salary

Normalization separates database coders from database architects. A poorly normalized schema causes data corruption, storage waste, and query nightmares. A well-normalized schema is elegant, consistent, and maintainable. GATE CS dedicates 5-8 marks to normalization every year. Every DBA interview at TCS, Infosys, Oracle, and product companies asks normalization questions.

🏢 Industry Snapshot

SBI Core Banking — When SBI migrated from legacy to CBS (Core Banking Solution), normalization consultants spent 6 months redesigning the schema. The original system stored customer address in 15 different tables — a classic redundancy nightmare. After normalization to 3NF, the same data lived in ONE address table referenced by FK everywhere. Result: 60% reduction in storage, zero data inconsistency.

Flipkart — Their product catalog is normalized to 3NF for the source-of-truth (OLTP) database. But their search/display layer is intentionally denormalized — product name, price, image URL, seller name, and rating all flattened into one table for sub-100ms page loads. They normalize for correctness and denormalize for speed.

🇮🇳 SBI🇮🇳 Flipkart🇮🇳 TCS🇮🇳 IRCTC🇮🇳 RazorpayGATE CS

E.F. Codd (IBM) introduced 1NF, 2NF, and 3NF in 1970-72. Boyce and Codd together defined BCNF in 1974. 4NF (by Ronald Fagin, 1977) and 5NF (also Fagin, 1979) handle rare multi-valued and join dependencies. In practice, 3NF/BCNF is sufficient for 99% of real-world databases.

Section 2

Learning Outcomes — Bloom's Taxonomy

Bloom's Level	Outcome Statement
L1 — Remember	Define 1NF, 2NF, 3NF, BCNF; list Armstrong's axioms; recall the definition of functional dependency, candidate key, and prime attribute
L2 — Understand	Explain why update anomalies occur in unnormalized tables; describe the difference between partial and transitive dependencies; explain lossy vs lossless decomposition
L3 — Apply	Compute attribute closure, find candidate keys from a set of FDs, determine the highest normal form of a given relation, and decompose a relation to 3NF/BCNF
L4 — Analyze	Analyze whether a decomposition is lossless and dependency-preserving; compare 3NF and BCNF trade-offs
L5 — Evaluate	Justify when to stop at 3NF vs pursuing BCNF; evaluate denormalization decisions for performance-critical Indian enterprise systems
L6 — Create	Take a raw unnormalized dataset and design a complete normalized schema (BCNF) with SQL implementation and sample data