Normalization — 1NF to 5NF

A relation is in First Normal Form if and only if every attribute in every tuple contains exactly one atomic value — a single, indivisible value from the attribute's domain. No attribute may contain a set of values, a list, an array, a nested relation, or any repeating group. Additionally, all rows must be unique (the relation must have a primary key).

A relation is in Second Normal Form if and only if: (1) it is in 1NF, AND (2) every non-prime attribute is fully functionally dependent on the entire primary key. A non-prime attribute is any attribute that is not part of any candidate key. Full functional dependency means no non-prime attribute is dependent on any proper subset (partial subset) of the primary key.

2NF only applies to relations with composite primary keys.A relation with a single-attribute primary key is automatically in 2NF (partial dependency is impossible when the key is one attribute — there is no proper subset of a single-element set other than the empty set).

// RELATION: STUDENT_COURSE_TEACHER
// PRIMARY KEY: (student_id, course_id)
// NON-PRIME ATTRIBUTES: student_name, student_email, course_name, teacher_id,
//                       teacher_name, teacher_phone, teacher_dept, grade

// ANALYSIS: For each non-prime attribute, what determines it?

// student_name:
//   Does student_name depend on student_id alone? YES
//   (Knowing student_id = 'S001' tells us name = 'Rahul Sharma' — we don't need course_id)
//   → PARTIAL DEPENDENCY: student_name → student_id (only part of PK)

// student_email:
//   Does student_email depend on student_id alone? YES
//   (Knowing student_id tells us the email — course doesn't matter)
//   → PARTIAL DEPENDENCY: student_email → student_id

// course_name:
//   Does course_name depend on course_id alone? YES
//   (Knowing course_id = 'CS301' tells us name = 'Database Systems' — student irrelevant)
//   → PARTIAL DEPENDENCY: course_name → course_id

// teacher_id:
//   Does teacher_id depend on course_id alone? YES
//   (CS301 is always taught by T01 regardless of which student)
//   → PARTIAL DEPENDENCY: teacher_id → course_id

// teacher_name:
//   Does teacher_name depend on course_id alone? YES (via teacher_id)
//   → PARTIAL DEPENDENCY: teacher_name → course_id

// teacher_phone:
//   Does teacher_phone depend on course_id alone? YES (via teacher_id)
//   → PARTIAL DEPENDENCY

// teacher_dept:
//   Does teacher_dept depend on course_id alone? YES (via teacher_id)
//   → PARTIAL DEPENDENCY

// grade:
//   Does grade depend on student_id alone? NO — a student has different grades per course
//   Does grade depend on course_id alone? NO — a course has different grades per student
//   Does grade depend on (student_id, course_id) TOGETHER? YES
//   → FULL DEPENDENCY: grade → (student_id, course_id)
//   grade is the ONLY 2NF-compliant attribute in this relation!

// CONCLUSION: Almost every attribute has a partial dependency.
// This table is severely 2NF-violating.

CREATE TABLE students (
    student_id    VARCHAR(10)   PRIMARY KEY,
    student_name  VARCHAR(100)  NOT NULL,
    student_email VARCHAR(150)  UNIQUE NOT NULL
);

CREATE TABLE courses (
    course_id    VARCHAR(10)   PRIMARY KEY,
    course_name  VARCHAR(200)  NOT NULL,
    teacher_id   VARCHAR(10)   NOT NULL,
    teacher_name VARCHAR(100)  NOT NULL,
    teacher_phone VARCHAR(20),
    teacher_dept VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id  VARCHAR(10)  NOT NULL,
    course_id   VARCHAR(10)  NOT NULL,
    grade       CHAR(2),
    
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)   ON DELETE RESTRICT
);

A relation is in Third Normal Form if and only if: (1) it is in 2NF, AND (2) for every non-trivial functional dependency X → A, either X is a superkey OR A is a prime attribute (part of some candidate key).

In simpler terms: no non-prime attribute should be transitively dependent on the primary key. Every non-prime attribute must depend directly and only on the primary key — not on another non-prime attribute.

// COURSES table after 2NF: (course_id, course_name, teacher_id, teacher_name, teacher_phone, teacher_dept)
// PRIMARY KEY: course_id
// NON-PRIME ATTRIBUTES: course_name, teacher_id, teacher_name, teacher_phone, teacher_dept

// DEPENDENCY ANALYSIS:

// course_id → course_name: DIRECT dependency on PK ✓
// course_id → teacher_id: DIRECT dependency on PK ✓
//   (each course is taught by one teacher — teacher_id depends directly on course_id)

// But now look at teacher_name, teacher_phone, teacher_dept:
// teacher_id → teacher_name    (knowing the teacher ID tells us their name)
// teacher_id → teacher_phone   (knowing the teacher ID tells us their phone)
// teacher_id → teacher_dept    (knowing the teacher ID tells us their department)

// So the full chain is:
// course_id → teacher_id → teacher_name    ← TRANSITIVE DEPENDENCY
// course_id → teacher_id → teacher_phone   ← TRANSITIVE DEPENDENCY
// course_id → teacher_id → teacher_dept    ← TRANSITIVE DEPENDENCY

// teacher_name, teacher_phone, teacher_dept depend on course_id TRANSITIVELY through teacher_id.
// They are facts about the TEACHER, not facts about the COURSE.
// They have no business being in the COURSES table.

// EVIDENCE OF THE PROBLEM:
// Prof. Kumar teaches CS301. Prof. Kumar changes departments from CS to AI.
// We must update the COURSES table: UPDATE courses SET teacher_dept = 'AI' WHERE teacher_id = 'T01'
// If Prof. Kumar also teaches CS303 (another course), we must update that row too.
// If we miss any row → update anomaly. Same problem as before 2NF.

-- All four tables. Every non-prime attribute depends directly on its table's primary key.

CREATE TABLE students (
    student_id    VARCHAR(10)   PRIMARY KEY,
    student_name  VARCHAR(100)  NOT NULL,
    student_email VARCHAR(150)  UNIQUE NOT NULL
    -- student_name and student_email depend DIRECTLY on student_id ✓
);

CREATE TABLE teachers (
    teacher_id    VARCHAR(10)   PRIMARY KEY,
    teacher_name  VARCHAR(100)  NOT NULL,
    teacher_phone VARCHAR(20)   UNIQUE,
    teacher_dept  VARCHAR(100)  NOT NULL
    -- All attributes depend DIRECTLY on teacher_id ✓
);

CREATE TABLE courses (
    course_id    VARCHAR(10)   PRIMARY KEY,
    course_name  VARCHAR(200)  NOT NULL,
    teacher_id   VARCHAR(10)   NOT NULL,
    -- course_name depends directly on course_id ✓
    -- teacher_id depends directly on course_id ✓ (course directly determines teacher)
    
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

CREATE TABLE enrollments (
    student_id  VARCHAR(10)  NOT NULL,
    course_id   VARCHAR(10)  NOT NULL,
    grade       CHAR(2),
    -- grade depends on the FULL composite key (student_id, course_id) ✓
    
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)   ON DELETE RESTRICT
);

-- VERIFICATION: All anomalies eliminated?
-- Insert anomaly: YES — Prof. Mehta can be added to TEACHERS without any course
-- Update anomaly: YES — Prof. Kumar's phone is in ONE row in TEACHERS
-- Delete anomaly: YES — Deleting Kavya's enrollment doesn't touch TEACHERS or COURSES

-- ORDERS table with a single PK order_id — but 3NF is still violated!
CREATE TABLE orders_bad (
    order_id       INT           PRIMARY KEY,
    customer_id    INT           NOT NULL,
    customer_name  VARCHAR(100),  -- depends on customer_id, NOT order_id directly!
    customer_email VARCHAR(150),  -- depends on customer_id, NOT order_id directly!
    customer_city  VARCHAR(100),  -- depends on customer_id, NOT order_id directly!
    order_date     DATE,
    total          DECIMAL(10,2)
);

-- TRANSITIVE DEPENDENCIES:
-- order_id → customer_id (direct)
-- customer_id → customer_name, customer_email, customer_city (transitive through customer_id)
-- So: order_id → customer_id → customer_name (transitive → 3NF VIOLATION)

-- EVIDENCE OF PROBLEM:
-- Customer Rahul changes email: must update EVERY ORDER row for Rahul
-- Rahul has placed 100 orders → 100 rows must be updated → update anomaly

-- 3NF SOLUTION: extract customer data to separate table
CREATE TABLE customers (
    customer_id    INT          PRIMARY KEY,
    customer_name  VARCHAR(100) NOT NULL,
    customer_email VARCHAR(150) UNIQUE NOT NULL,
    customer_city  VARCHAR(100)
);

CREATE TABLE orders_good (
    order_id     INT           PRIMARY KEY,
    customer_id  INT           NOT NULL,  -- FK (direct dependency on PK ✓)
    order_date   DATE          NOT NULL,
    total        DECIMAL(10,2) NOT NULL CHECK (total >= 0),
    
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
-- Now: customer_name appears ONCE in customers. Update once, consistent everywhere.

A relation is in Boyce-Codd Normal Form if and only if for every non-trivial functional dependency X → Y in the relation, X is a superkey. No exceptions.

3NF allows one exception: if Y is a prime attribute (part of some candidate key), X does not need to be a superkey. BCNF removes this exception entirely. In BCNF, the only non-trivial dependencies allowed are those where the left side is a superkey.

Business rules:

Let us use a cleaner version where the rules are consistently satisfied:

Finding Candidate Keys and FDs

// FUNCTIONAL DEPENDENCIES:
// FD1: (student_id, subject) → teacher_id
//      "A student studies each subject with exactly one teacher"
//      Knowing student + subject → we know which teacher

// FD2: teacher_id → subject
//      "Each teacher teaches only one subject"
//      Knowing teacher_id → we know which subject

// CANDIDATE KEYS:
// (student_id, teacher_id):
//   Closure: {student_id, teacher_id}
//   Apply teacher_id → subject: {student_id, teacher_id, subject}
//   = all attributes → superkey
//   Is it minimal? Remove student_id: {teacher_id} → {teacher_id, subject} ≠ all ✓
//   Remove teacher_id: {student_id} → {student_id} ≠ all ✓
//   → CANDIDATE KEY #1: {student_id, teacher_id}

// (student_id, subject):
//   Closure: {student_id, subject}
//   Apply (student_id, subject) → teacher_id: {student_id, subject, teacher_id}
//   = all attributes → superkey
//   Is it minimal? Remove student_id: {subject} → cannot derive teacher_id for a student ✓
//   Remove subject: {student_id} → cannot derive teacher_id ✓
//   → CANDIDATE KEY #2: {student_id, subject}

// TWO CANDIDATE KEYS: {student_id, teacher_id} and {student_id, subject}
// Prime attributes: student_id, teacher_id, subject — ALL attributes are prime!

// 3NF CHECK:
// For FD1: (student_id, subject) → teacher_id
//   Is {student_id, subject} a superkey? YES (it's a candidate key)
//   → Satisfies 3NF ✓
// For FD2: teacher_id → subject
//   Is {teacher_id} a superkey? NO (teacher_id alone cannot determine student_id)
//   Is subject a prime attribute? YES (subject is part of candidate key {student_id, subject})
//   → 3NF exception applies: Y is prime → Satisfies 3NF ✓
// CONCLUSION: Relation IS in 3NF

// BCNF CHECK:
// For FD2: teacher_id → subject
//   Is {teacher_id} a superkey? NO!
//   BCNF requires every determinant to be a superkey — NO EXCEPTIONS
//   → BCNF VIOLATED!

The relation is in 3NF but NOT in BCNF. The FD teacher_id → subjectviolates BCNF because teacher_id is not a superkey. However, 3NF allows it because subject is a prime attribute.

The Anomaly That Remains in 3NF — But Not After BCNF

Despite being in 3NF, this relation still has an update anomaly: if teacher T01 changes from teaching Physics to teaching Mathematics, we must update every row where teacher_id = 'T01'. If T01 teaches 500 students, we update 500 rows. Miss one → inconsistency.

BCNF Decomposition

We decompose by removing the violating FD (teacher_id → subject) into its own relation:

-- After BCNF decomposition, we have:
-- TEACHER_SUBJECT(teacher_id PK, subject)
-- STUDENT_TEACHER(student_id PK, teacher_id FK)

-- The original FD: (student_id, subject) → teacher_id
-- This FD CANNOT be directly enforced in either BCNF table alone.
-- To verify it, we must JOIN:

SELECT st.student_id, ts.subject, st.teacher_id
FROM student_teacher st
JOIN teacher_subject ts ON st.teacher_id = ts.teacher_id;

-- To enforce "each student has at most one teacher per subject":
-- We need a UNIQUE constraint on (student_id, subject) — but these columns
-- are now in DIFFERENT tables! We cannot add a UNIQUE constraint that spans tables.

-- This means the BCNF schema can allow invalid data:
INSERT INTO student_teacher VALUES ('S001', 'T01');  -- T01 teaches Physics
INSERT INTO student_teacher VALUES ('S001', 'T03');  -- T03 ALSO teaches Physics!
-- Now student S001 has TWO Physics teachers — violates the original business rule
-- But the BCNF schema cannot prevent this without application-level enforcement

-- THE CHOICE:
-- 3NF: All dependencies preserved. Some redundancy possible. Anomaly-free enough for most uses.
-- BCNF: All dependencies enforced by superkeys. May lose some dependency enforcement in decomposition.

-- IN PRACTICE:
-- Most schemas in production aim for 3NF.
-- BCNF is preferred when the dependency that causes the violation is truly independent
-- and the lost FD can be enforced at the application layer or with triggers.

For production database design, the standard recommendation is: aim for 3NF, and move to BCNF only when the anomalies remaining in 3NF are causing real production problems. BCNF is the theoretically stronger form, but its inability to always preserve functional dependencies means you may need triggers or application code to enforce constraints that were automatic in 3NF. The cost of that complexity must be weighed against the benefit of eliminating the remaining anomaly.

For exams and interviews: know the BCNF definition precisely, be able to identify when 3NF and BCNF differ, and be able to explain the trade-off between them.

A relation is in Fourth Normal Form if and only if it is in BCNF AND contains no non-trivial multi-valued dependencies unless the determinant is a superkey.

// MULTI-VALUED DEPENDENCIES in COURSE_TEACHER_TEXTBOOK:
// course_id ↠ teacher_id (the set of teachers for a course is independent of textbooks)
// course_id ↠ textbook   (the set of textbooks for a course is independent of teachers)

// 4NF VIOLATION:
// course_id ↠ teacher_id: Is course_id a superkey? NO (course_id doesn't uniquely identify rows)
// course_id ↠ textbook:   Is course_id a superkey? NO
// → 4NF VIOLATED

// THE ANOMALIES:
// Insert: Cannot add a new textbook for CS301 with ONE row — must add one row per teacher
// Delete: If T01 is removed from CS301, we must delete rows carefully to not lose textbook info
// Update: These same spurious combinations must be maintained consistently

// 4NF DECOMPOSITION: Split into two separate tables, one per independent multi-valued fact
// COURSE_TEACHERS: course_id ↠ teacher_id
// COURSE_TEXTBOOKS: course_id ↠ textbook

-- AFTER 4NF DECOMPOSITION:
CREATE TABLE course_teachers (
    course_id   VARCHAR(10)  NOT NULL,
    teacher_id  VARCHAR(10)  NOT NULL,
    PRIMARY KEY (course_id, teacher_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

CREATE TABLE course_textbooks (
    course_id  VARCHAR(10)  NOT NULL,
    textbook   VARCHAR(200) NOT NULL,
    PRIMARY KEY (course_id, textbook),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- COURSE_TEACHERS:       COURSE_TEXTBOOKS:
-- CS301 | T01            CS301 | Database System Concepts
-- CS301 | T02            CS301 | Fundamentals of Database Systems

-- NOW: adding teacher T03 to CS301 = 1 row in COURSE_TEACHERS
--      adding a new textbook = 1 row in COURSE_TEXTBOOKS
-- No spurious multiplication. Independent facts stored independently.

A relation is in Fifth Normal Form (also called Project-Join Normal Form, PJNF) if and only if it is in 4NF and every join dependency in the relation is implied by the candidate keys. A join dependency exists when a relation can be losslessly decomposed into three or more projections that can be rejoined to reconstruct the original relation.

// Scenario: A SUPPLIER can supply certain PARTs, a SUPPLIER works on certain PROJECTs,
// and certain PARTs are used in certain PROJECTs.
// BUSINESS RULE: A supplier supplies a part to a project IF AND ONLY IF:
//   (a) The supplier can supply that part, AND
//   (b) The supplier works on that project, AND
//   (c) That part is used in that project
// This is a cyclic join dependency — not expressible as an FD or MVD.

-- SUPPLIER_PART_PROJECT table (contains all valid three-way combinations):
-- supplier | part  | project
-- S1       | P1    | J1
-- S1       | P1    | J2
-- S1       | P2    | J1
-- S2       | P1    | J1

-- If we decompose into three binary relations:
-- SUPPLIER_PART:    {(S1,P1), (S1,P2), (S2,P1)}
-- SUPPLIER_PROJECT: {(S1,J1), (S1,J2), (S2,J1)}
-- PART_PROJECT:     {(P1,J1), (P1,J2), (P2,J1)}

-- To reconstruct, we JOIN all three:
SELECT sp.supplier, sp.part, sj.project
FROM supplier_part sp
JOIN supplier_project sj ON sp.supplier = sj.supplier
JOIN part_project pp     ON sp.part = pp.part AND sj.project = pp.project;

-- This JOIN produces EXACTLY the original tuples — no spurious tuples.
-- If any binary pair relationship is invalid (spurious), the JOIN removes it
-- because the third pair acts as a filter.

-- IN SQL (5NF-compliant schema):
CREATE TABLE supplier_parts (
    supplier_id  VARCHAR(10) NOT NULL,
    part_id      VARCHAR(10) NOT NULL,
    PRIMARY KEY (supplier_id, part_id)
);

CREATE TABLE supplier_projects (
    supplier_id  VARCHAR(10) NOT NULL,
    project_id   VARCHAR(10) NOT NULL,
    PRIMARY KEY (supplier_id, project_id)
);

CREATE TABLE part_projects (
    part_id     VARCHAR(10) NOT NULL,
    project_id  VARCHAR(10) NOT NULL,
    PRIMARY KEY (part_id, project_id)
);

-- ADVANTAGE: Adding that S1 can now supply P3 to J1 requires
-- checking all three binary relations — the three-way constraint is
-- enforced by the intersection of independent binary facts.

// ORIGINAL RELATION R(A, B, C) with tuples:
// A | B | C
// 1 | x | p
// 1 | y | q
// 2 | x | r

// DECOMPOSITION 1: R1(A, B) and R2(B, C)
// R1:          R2:
// A | B        B | C
// 1 | x        x | p
// 1 | y        y | q
// 2 | x        x | r

// RECONSTRUCT via natural join R1 ⋈ R2 (join on B):
// A | B | C
// 1 | x | p    ← original ✓
// 1 | x | r    ← SPURIOUS! This tuple was NOT in the original relation!
// 1 | y | q    ← original ✓
// 2 | x | p    ← SPURIOUS! This tuple was NOT in the original relation!
// 2 | x | r    ← original ✓
// → LOSSY DECOMPOSITION — spurious tuples introduced. This decomposition DESTROYS INFORMATION.

// DECOMPOSITION 2: R1(A, B) and R2(A, C)
// R1:          R2:
// A | B        A | C
// 1 | x        1 | p
// 1 | y        1 | q
// 2 | x        2 | r

// RECONSTRUCT via natural join R1 ⋈ R2 (join on A):
// A | B | C
// 1 | x | p    ← original ✓
// 1 | x | q    ← SPURIOUS!
// 1 | y | p    ← SPURIOUS!
// 1 | y | q    ← original ✓
// 2 | x | r    ← original ✓
// → ALSO LOSSY. Joining on A introduces spurious combinations.

// THE LOSSLESS DECOMPOSITION RULE (Heath's Theorem):
// A decomposition of R(X, Y, Z) into R1(X, Y) and R2(X, Z) is lossless if and only if:
// X → Y  (X functionally determines Y), OR
// X → Z  (X functionally determines Z)
// In other words: the attributes in the JOIN column set must be a superkey of at least one
// of the decomposed relations.

// APPLYING THE RULE:
// Decompose COURSES(course_id, course_name, teacher_id, teacher_name, teacher_phone, teacher_dept)
// into COURSES(course_id, course_name, teacher_id) and TEACHERS(teacher_id, teacher_name, teacher_phone, teacher_dept)
// JOIN attribute: teacher_id
// Is teacher_id a superkey of TEACHERS? YES (teacher_id is the PK of TEACHERS)
// → LOSSLESS DECOMPOSITION ✓

-- VERIFICATION:
SELECT c.course_id, c.course_name, c.teacher_id, t.teacher_name, t.teacher_phone, t.teacher_dept
FROM courses c JOIN teachers t ON c.teacher_id = t.teacher_id;
-- This JOIN exactly reconstructs the original COURSES table — no spurious tuples.

// ORIGINAL RELATION: R(student_id, teacher_id, subject)
// FUNCTIONAL DEPENDENCIES:
//   FD1: (student_id, subject) → teacher_id
//   FD2: teacher_id → subject

// BCNF DECOMPOSITION produces:
//   R1(teacher_id, subject) — teacher_id is PK, enforces FD2
//   R2(student_id, teacher_id) — (student_id, teacher_id) is PK

// CHECK DEPENDENCY PRESERVATION:
// FD1: (student_id, subject) → teacher_id
//   student_id is in R2. subject is in R1. teacher_id is in both.
//   FD1 CANNOT be checked within R1 alone (no student_id there)
//   FD1 CANNOT be checked within R2 alone (no subject there)
//   Checking FD1 requires JOIN of R1 and R2 → NOT PRESERVED in decomposition
//   → FD1 is a lost dependency

// CONSEQUENCE:
-- Can we insert this into R2 without violating FD1?
INSERT INTO r2_student_teacher VALUES ('S001', 'T01');  -- T01 teaches Physics
INSERT INTO r2_student_teacher VALUES ('S001', 'T03');  -- T03 also teaches Physics!
-- Without FD1 enforced in any single table, S001 now has TWO Physics teachers
-- The BCNF schema cannot prevent this — it requires application-level enforcement

// CONTRAST WITH 3NF DECOMPOSITION (from our main example):
// Original: COURSES(course_id, course_name, teacher_id, teacher_name, teacher_phone, teacher_dept)
// 3NF decomposition:
//   COURSES(course_id, course_name, teacher_id)   — course_id → teacher_id preserved here ✓
//   TEACHERS(teacher_id, teacher_name, teacher_phone, teacher_dept) — teacher_id → * preserved ✓
// ALL FDs are preserved in the 3NF decomposition.

-- TECHNIQUE 1: Storing a derived/redundant column with a trigger to maintain it

-- Denormalised: orders.customer_name (redundant — also in customers.name)
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(100);

-- Populate existing rows:
UPDATE orders o SET customer_name = (SELECT name FROM customers WHERE customer_id = o.customer_id);

-- Trigger to keep in sync:
CREATE OR REPLACE FUNCTION sync_order_customer_name()
RETURNS TRIGGER AS $$
BEGIN
    -- When a customer's name changes, update all their orders
    UPDATE orders
    SET customer_name = NEW.name
    WHERE customer_id = NEW.customer_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_customer_name_update
AFTER UPDATE OF name ON customers
FOR EACH ROW
WHEN (OLD.name IS DISTINCT FROM NEW.name)
EXECUTE FUNCTION sync_order_customer_name();

-- TRADE-OFF: UPDATE to customers.name now triggers UPDATE on orders table.
-- If a customer has 10,000 orders, updating their name touches 10,001 rows.
-- Acceptable if customer name changes are rare; unacceptable if frequent.


-- TECHNIQUE 2: Historical snapshot — intentional denormalisation (no sync needed)
-- order_items.unit_price is denormalised from products.price
-- This is NOT an error — it's intentional historical accuracy

CREATE TABLE order_items (
    order_id    INT           NOT NULL,
    product_id  INT           NOT NULL,
    quantity    INT           NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL,  -- snapshot of price at time of order
    -- unit_price is intentionally redundant with products.current_price
    -- It must NOT be updated when products.price changes — that would corrupt history
    PRIMARY KEY (order_id, product_id)
);
-- Documentation comment: unit_price is a historical snapshot. Do NOT sync with products.price.


-- TECHNIQUE 3: Materialised view — database-managed denormalisation
-- PostgreSQL maintains a materialised view — a pre-computed, cached result set
CREATE MATERIALIZED VIEW order_summary AS
SELECT
    o.order_id,
    o.order_date,
    c.name           AS customer_name,  -- denormalised from customers
    c.city           AS customer_city,
    COUNT(oi.product_id) AS item_count,
    SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c   ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_date, c.name, c.city;

-- Create index on the materialised view for fast lookups:
CREATE INDEX idx_order_summary_customer ON order_summary(customer_name);

-- Refresh strategy: choose based on data freshness requirements
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
-- CONCURRENTLY: refresh without locking reads (requires unique index on the view)
-- Schedule via pg_cron or application-level job:
-- SELECT cron.schedule('refresh-order-summary', '*/15 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary');

-- Query the materialised view (no JOIN at query time):
SELECT * FROM order_summary WHERE customer_city = 'Bengaluru' ORDER BY total DESC;
-- Fast: reads pre-computed data, uses index, no runtime JOIN

// STEP 1: Is every cell atomic? No multi-valued cells, no repeating groups?
// NO → Not even 1NF. Fix: split into separate tables or separate columns.

// STEP 2: Is the primary key composite?
// NO → Automatically in 2NF (partial dependency impossible with single-attribute PK)
//       Proceed to Step 3.
// YES → For each non-prime attribute A, ask:
//        "Does A depend on the FULL primary key, or just PART of it?"
//        If PART → 2NF violation. Fix: move A and its partial key to a new table.

// STEP 3: For each non-prime attribute A, ask:
//   "What directly determines A?"
//   If the answer is "the primary key directly" → OK
//   If the answer is "another non-prime attribute B (which is determined by PK)"
//     → Transitive dependency → 3NF violation.
//     Fix: move B and its dependents to a new table with B as PK.

// STEP 4 (BCNF check): For every non-trivial FD X → Y in the relation:
//   Is X a superkey?
//   NO → BCNF violation (even if it passed 3NF because Y is a prime attribute).
//   Fix: decompose, accepting potential dependency-preservation loss.

// STEP 5 (4NF check): Are there independent multi-valued attributes of the same key?
//   e.g., course_id ↠ teachers AND course_id ↠ textbooks (independent sets)?
//   YES → 4NF violation. Fix: separate tables for each multi-valued set.

// STEP 6 (5NF check — rarely needed):
//   Can the relation be decomposed into 3+ projections that perfectly rejoin?
//   And is this decomposition NOT implied by FDs or MVDs?
//   YES → 5NF violation. Very rare in practice.

// PRACTICAL STOPPING POINT:
// For OLTP databases: aim for 3NF as minimum, BCNF where practical.
// For data warehouses: intentional denormalisation (star/snowflake schema) is standard.
// For exam/interview: understand all 6 normal forms precisely.

-- Migration: create training assignments table
CREATE TABLE training_assignments (
    assignment_id       SERIAL        PRIMARY KEY,
    employee_id         INT           NOT NULL,
    employee_name       VARCHAR(100),              -- VIOLATION: 3NF transitive
    employee_department VARCHAR(100),              -- VIOLATION: 3NF transitive
    employee_location   VARCHAR(100),              -- VIOLATION: 3NF transitive
    course_id           INT           NOT NULL,
    course_name         VARCHAR(200),              -- VIOLATION: 3NF transitive
    course_duration_hrs INT,                       -- VIOLATION: 3NF transitive
    vendor_id           INT,
    vendor_name         VARCHAR(100),              -- VIOLATION: 3NF transitive
    vendor_contact      VARCHAR(200),              -- VIOLATION: 3NF transitive (multi-valued?)
    assigned_date       DATE          NOT NULL DEFAULT CURRENT_DATE,
    completion_date     DATE,
    status              VARCHAR(20)   NOT NULL DEFAULT 'pending',
    score               INT,
    skills_covered      TEXT          -- VIOLATION: 1NF (comma-separated values)
                                     -- "Python,SQL,Data Engineering"
);

Senior engineer's review comments:

-- Assuming employees, courses, and vendors tables already exist or are created separately

-- Correct training_assignments table: only assignment-specific facts
CREATE TABLE training_assignments (
    assignment_id    SERIAL  PRIMARY KEY,
    employee_id      INT     NOT NULL,
    course_id        INT     NOT NULL,
    vendor_id        INT,    -- nullable: some courses are internal (no vendor)
    assigned_date    DATE    NOT NULL DEFAULT CURRENT_DATE,
    completion_date  DATE,
    status           VARCHAR(20) NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending', 'in_progress', 'completed', 'failed', 'cancelled')),
    score            INT     CHECK (score BETWEEN 0 AND 100),
    
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE RESTRICT,
    FOREIGN KEY (course_id)   REFERENCES courses(course_id)   ON DELETE RESTRICT,
    FOREIGN KEY (vendor_id)   REFERENCES vendors(vendor_id)   ON DELETE SET NULL
);

-- Separate table for skills covered (1NF compliant)
CREATE TABLE assignment_skills (
    assignment_id  INT         NOT NULL,
    skill          VARCHAR(100) NOT NULL,
    PRIMARY KEY (assignment_id, skill),
    FOREIGN KEY (assignment_id) REFERENCES training_assignments(assignment_id) ON DELETE CASCADE
);

-- Now queries are clean:
-- "Find employees who completed Python training in the last 6 months"
SELECT DISTINCT e.name, e.department
FROM employees e
JOIN training_assignments ta ON e.employee_id = ta.employee_id
JOIN assignment_skills ask    ON ta.assignment_id = ask.assignment_id
WHERE ask.skill = 'Python'
  AND ta.status = 'completed'
  AND ta.completion_date >= CURRENT_DATE - INTERVAL '6 months';

-- "If training vendor changes contact info: update ONE row in vendors table.
--  Zero training_assignment rows need updating." ← the update anomaly is gone