Database Design & Normalization

-- The table that causes all three anomalies
-- CREATE TABLE records (
--     student_name VARCHAR(100),
--     roll_number  VARCHAR(20),
--     branch       VARCHAR(40),
--     hod_name     VARCHAR(100),   -- repeated in every row of the branch
--     subject      VARCHAR(60),
--     marks        INT
-- );

-- One fact, one place. A department is a thing, so it gets a table.
CREATE TABLE departments (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(60) NOT NULL UNIQUE,
    hod_name VARCHAR(100)
);

CREATE TABLE students (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    roll_number   VARCHAR(20)  NOT NULL UNIQUE,
    department_id INT,
    joined_on     DATE,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Changing a head of department is now one row, and cannot go half done
UPDATE departments SET hod_name = 'Dr R Iyer' WHERE name = 'CSE';

-- A department with no students yet can still exist
INSERT INTO departments (name, hod_name) VALUES ('AI & DS', 'Dr S Menon');

-- Surrogate primary key, natural key kept UNIQUE alongside it
CREATE TABLE students (
    id          INT AUTO_INCREMENT PRIMARY KEY,   -- stable, meaningless
    roll_number VARCHAR(20)  NOT NULL UNIQUE,     -- real, and enforced
    email       VARCHAR(120) NOT NULL UNIQUE,
    name        VARCHAR(100) NOT NULL
);

-- A roll number typo is now a one-row correction, not a migration
UPDATE students SET roll_number = 'CS2026114' WHERE id = 42;

-- Generating the key, by dialect
-- MySQL:       id INT AUTO_INCREMENT PRIMARY KEY
-- PostgreSQL:  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
--              id SERIAL PRIMARY KEY            (older style)
-- SQL Server:  id INT IDENTITY(1,1) PRIMARY KEY
-- Oracle:      id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

-- A composite primary key: the pair is what must be unique
CREATE TABLE enrollments (
    student_id  INT NOT NULL,
    course_id   INT NOT NULL,
    enrolled_on DATE,
    PRIMARY KEY (student_id, course_id)
);

-- One-to-many: the foreign key lives on the 'many' side
CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    ordered_at DATETIME NOT NULL,
    status     VARCHAR(20) NOT NULL DEFAULT 'pending',
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- One student's orders
SELECT * FROM orders WHERE student_id = 42;

-- Many-to-many: a third table holds the pairings
CREATE TABLE courses (
    id    INT AUTO_INCREMENT PRIMARY KEY,
    code  VARCHAR(12) NOT NULL UNIQUE,
    title VARCHAR(120) NOT NULL
);

CREATE TABLE enrollments (
    student_id  INT  NOT NULL,
    course_id   INT  NOT NULL,
    enrolled_on DATE NOT NULL,
    grade       CHAR(2),            -- a fact about the pairing
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id)  REFERENCES courses(id)
);

-- Reading across a many-to-many needs both joins
SELECT s.name, c.title, e.grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses     c ON c.id = e.course_id
WHERE s.id = 42;

-- One-to-one: UNIQUE is what stops it becoming one-to-many
CREATE TABLE student_documents (
    student_id     INT PRIMARY KEY,     -- also unique, by definition
    aadhaar_number VARCHAR(20),
    photo_url      VARCHAR(255),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- CASCADE where the child genuinely cannot exist alone.
-- An order line has no meaning without its order.
CREATE TABLE order_items (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);
-- Note the second one: deleting a product that appears on a past
-- order is refused, because that order line is a historical record.

-- SET NULL keeps the child and forgets the link.
-- The column must allow NULL for this to be legal.
CREATE TABLE products (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    category_id INT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE SET NULL
);

-- The alternative to deleting: stop showing it
ALTER TABLE products ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;
UPDATE products SET is_active = FALSE WHERE id = 17;
SELECT * FROM products WHERE is_active = TRUE;

-- Adding a constraint to a table that already exists
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_student
    FOREIGN KEY (student_id) REFERENCES students(id)
    ON DELETE RESTRICT;

-- Is the constraint actually being enforced?
SHOW CREATE TABLE orders;        -- MySQL: check ENGINE=InnoDB
-- SQLite: PRAGMA foreign_keys = ON;   (per connection, every time)

-- Not 1NF: several values crammed into one column
-- CREATE TABLE bad_students (
--     id       INT PRIMARY KEY,
--     name     VARCHAR(100),
--     subjects VARCHAR(200)      -- 'DBMS, OS, CN'
-- );

-- 1NF: one row per value, in a table of its own
CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    grade      CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

-- The question is now answerable, and can use an index
SELECT COUNT(*) FROM enrollments WHERE course_id = 7;

-- Not 2NF: course_title depends on course_id alone, not on the pair
-- PRIMARY KEY (student_id, course_id)
-- student_id | course_id | course_title | grade

-- 2NF: the title moves to the table its key belongs to
-- courses:     id | code | title
-- enrollments: student_id | course_id | grade

-- Not 3NF: city is determined by pincode, not by the student
-- students: id | name | pincode | city | state

-- 3NF: the pincode fact gets its own home
CREATE TABLE pincodes (
    pincode CHAR(6) PRIMARY KEY,
    city    VARCHAR(60) NOT NULL,
    state   VARCHAR(60) NOT NULL
);

ALTER TABLE students ADD COLUMN pincode CHAR(6);
ALTER TABLE students
    ADD CONSTRAINT fk_students_pincode
    FOREIGN KEY (pincode) REFERENCES pincodes(pincode);

-- Reading it back is one join
SELECT s.name, p.city, p.state
FROM students s
LEFT JOIN pincodes p ON p.pincode = s.pincode;

-- Normalised, and correct: three joins to build one invoice line
SELECT o.id, p.name, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
WHERE o.id = 1001;

-- Deliberate denormalization: a counter kept beside the students row
ALTER TABLE students ADD COLUMN order_count INT NOT NULL DEFAULT 0;

-- ...and something that keeps it honest
UPDATE students s
SET order_count = (
    SELECT COUNT(*) FROM orders o WHERE o.student_id = s.id
);

-- NOT denormalization: unit_price records what was actually paid
CREATE TABLE order_items (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,   -- the price on the day
    FOREIGN KEY (order_id)   REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Types and constraints that protect the design
CREATE TABLE payments (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT NOT NULL,
    amount     DECIMAL(10,2) NOT NULL,   -- never FLOAT for money
    method     VARCHAR(20)   NOT NULL,
    paid_at    DATETIME      NOT NULL,
    CONSTRAINT chk_amount_positive CHECK (amount > 0),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);
-- MySQL enforces CHECK from 8.0.16 onward. Older versions accept
-- the clause and ignore it, so verify before relying on it.