Entity-Relationship (ER) Model

CUSTOMER entity — simple attributes:
  customer_id   → column customer_id     (single value, uniquely identifies)
  age           → column age             (single integer, no sub-components)
  email         → column email           (single string value)
  is_active     → column is_active       (single boolean)
  created_at    → column created_at      (single timestamp)

-- In the relational schema:
CREATE TABLE customers (
    customer_id   VARCHAR(10)  PRIMARY KEY,
    age           INT          CHECK (age >= 0 AND age <= 150),
    email         VARCHAR(150) UNIQUE NOT NULL,
    is_active     BOOLEAN      NOT NULL DEFAULT true,
    created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);
-- Each simple attribute = one column. One-to-one translation.

-- COMPOSITE ATTRIBUTE: address (street, city, state, pincode, country)
-- COMPOSITE ATTRIBUTE: full_name (first_name, middle_name, last_name)
-- COMPOSITE ATTRIBUTE: phone (country_code, area_code, number)

-- Strategy 1: STORE AS SEPARATE COLUMNS (when components are queried independently)
CREATE TABLE customers (
    customer_id   VARCHAR(10)  PRIMARY KEY,
    first_name    VARCHAR(50)  NOT NULL,
    last_name     VARCHAR(50)  NOT NULL,
    -- full_name is derived: first_name || ' ' || last_name
    street        VARCHAR(200),
    city          VARCHAR(100),  -- searched: WHERE city = 'Bengaluru'
    state         VARCHAR(50),
    pincode       CHAR(6),        -- searched: WHERE pincode = '560001'
    country       VARCHAR(50)    DEFAULT 'India'
);
-- Queries on individual components are efficient (indexable).
-- More columns, but full query flexibility.

-- Strategy 2: STORE AS SINGLE COLUMN (when always used as a whole)
CREATE TABLE customers (
    customer_id   VARCHAR(10)   PRIMARY KEY,
    full_name     VARCHAR(150)  NOT NULL,  -- never search by first/last separately
    address       TEXT                     -- always displayed as one unit, never filtered
);
-- Simpler schema, but cannot efficiently search by city or pincode.

-- Strategy 3: JSONB for semi-structured addresses (PostgreSQL)
CREATE TABLE customers (
    customer_id   VARCHAR(10)   PRIMARY KEY,
    full_name     VARCHAR(150)  NOT NULL,
    address       JSONB         -- flexible: some customers have different address fields
    -- with GIN index: CREATE INDEX ON customers USING GIN (address)
    -- query: WHERE address->>'city' = 'Bengaluru' (indexed)
);

-- WRONG: Storing multi-valued attribute in one column (1NF violation)
CREATE TABLE customers (
    customer_id  VARCHAR(10) PRIMARY KEY,
    name         VARCHAR(100),
    phones       TEXT  -- "98765-43210, 87654-32109, 76543-21098" -- WRONG!
    -- Cannot index, cannot query by specific phone, update anomaly guaranteed
);

-- CORRECT: Separate table for each value
CREATE TABLE customer_phones (
    customer_id   VARCHAR(10)  NOT NULL,
    phone_number  VARCHAR(20)  NOT NULL,
    phone_type    VARCHAR(20)  DEFAULT 'mobile', -- mobile, home, work, whatsapp
    is_primary    BOOLEAN      DEFAULT false,
    
    PRIMARY KEY (customer_id, phone_number),  -- composite PK
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);

-- Similarly for skills:
CREATE TABLE employee_skills (
    employee_id   INT         NOT NULL,
    skill         VARCHAR(100) NOT NULL,
    proficiency   VARCHAR(20), -- beginner, intermediate, expert
    years_exp     INT,
    
    PRIMARY KEY (employee_id, skill),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
);

-- Querying multi-valued attributes:
-- "Find all customers who have a WhatsApp number"
SELECT DISTINCT c.name, cp.phone_number
FROM customers c
JOIN customer_phones cp ON c.customer_id = cp.customer_id
WHERE cp.phone_type = 'whatsapp';

-- "Find all employees with Python skill at expert level"
SELECT e.name, es.years_exp
FROM employees e
JOIN employee_skills es ON e.employee_id = es.employee_id
WHERE es.skill = 'Python' AND es.proficiency = 'expert';

-- DERIVED: age (derived from date_of_birth)
-- STORE date_of_birth, COMPUTE age at query time:
SELECT
    name,
    date_of_birth,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth)) AS age
FROM customers;
-- Always accurate. Never stale. Slight computation cost per query.

-- DERIVED: order_total (derived from sum of order_items.quantity * price)
-- Option 1: Compute dynamically
SELECT
    o.order_id,
    SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

-- Option 2: Store as a column (denormalisation for performance)
ALTER TABLE orders ADD COLUMN order_total DECIMAL(10,2);
-- Must keep in sync: update orders.order_total whenever order_items change
-- Use trigger to maintain consistency:
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET order_total = (
        SELECT SUM(quantity * unit_price)
        FROM order_items
        WHERE order_id = NEW.order_id
    )
    WHERE order_id = NEW.order_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER recalculate_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_total();

-- DERIVED: years_of_service (derived from hire_date)
SELECT
    name,
    hire_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_of_service
FROM employees;

-- RULE: For frequently queried, slowly changing derived values → store with trigger
-- For rarely needed or fast to compute → compute dynamically
-- NEVER store derived values without a mechanism to keep them in sync

-- NULL situation 1: VALUE UNKNOWN
-- An employee's salary is NULL — we simply don't have this information yet
SELECT name FROM employees WHERE salary IS NULL;  -- find unknown salaries

-- NULL situation 2: VALUE NOT APPLICABLE
-- A person's middle_name is NULL — they simply don't have one
-- This is "not applicable", not "unknown"

-- NULL situation 3: VALUE NOT YET ASSIGNED
-- An order's delivery_date is NULL — it hasn't been delivered yet
SELECT order_id FROM orders WHERE delivery_date IS NULL;  -- undelivered orders

-- The problem: SQL cannot distinguish between these three meanings
-- All three are represented identically: NULL

-- NULL in arithmetic: any arithmetic with NULL produces NULL
SELECT 100 + NULL;    -- Result: NULL (not 100!)
SELECT NULL * 5;      -- Result: NULL
SELECT NULL = NULL;   -- Result: NULL (not TRUE! Use IS NULL)

-- COALESCE: replace NULL with a default value
SELECT name, COALESCE(middle_name, '') AS middle_name FROM employees;
SELECT name, COALESCE(salary, 0) AS salary FROM employees;

-- NULLIF: return NULL if two values are equal
SELECT NULLIF(phone_number, '') AS phone  -- treat empty string same as NULL
FROM customers;

-- COUNT behaviour with NULL:
SELECT
    COUNT(*) AS total_rows,          -- counts ALL rows including NULLs
    COUNT(salary) AS with_salary,    -- counts only non-NULL salary rows
    COUNT(DISTINCT salary) AS unique_salaries  -- counts distinct non-NULL salaries
FROM employees;

CUSTOMER entity:
  key attributes: customer_id, email (both uniquely identify a customer)
  → customer_id becomes PRIMARY KEY
  → email gets UNIQUE constraint (it's a candidate key, not chosen as PK)

STUDENT entity:
  key attributes: student_id, (college_name + roll_number combined)
  → student_id is the simpler key → becomes PRIMARY KEY
  → (college_name, roll_number) is a composite candidate key → UNIQUE constraint

FLIGHT entity:
  key attribute: (flight_number, departure_date) combined
  → Neither alone is unique (same flight number runs every day)
  → Together they uniquely identify a flight instance
  → Composite primary key: PRIMARY KEY (flight_number, departure_date)

-- Strategy 1: Foreign key in either table (recommended for optional relationships)
CREATE TABLE customers (
    customer_id      INT  PRIMARY KEY,
    name             VARCHAR(100) NOT NULL
);

CREATE TABLE loyalty_profiles (
    profile_id       INT          PRIMARY KEY,
    customer_id      INT          UNIQUE NOT NULL, -- UNIQUE enforces 1:1
    points_balance   INT          DEFAULT 0,
    tier             VARCHAR(20)  DEFAULT 'silver',
    
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
);
-- UNIQUE on customer_id: ensures each customer has at most ONE loyalty profile.
-- Not all customers have a loyalty profile (optional side).

-- Strategy 2: Shared primary key (strongest 1:1 — recommended when always paired)
CREATE TABLE employees (
    employee_id  INT  PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    hire_date    DATE NOT NULL
);

CREATE TABLE employee_access_cards (
    employee_id  INT  PRIMARY KEY,  -- same PK as employees!
    card_number  VARCHAR(20) UNIQUE NOT NULL,
    issued_date  DATE NOT NULL,
    access_level VARCHAR(10),
    
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
    -- employee_id is BOTH the PK and the FK — guarantees perfect 1:1
);

-- Strategy 3: Merge into one table (when always paired, no access pattern reason to split)
CREATE TABLE countries (
    country_id    INT  PRIMARY KEY,
    country_name  VARCHAR(100) UNIQUE NOT NULL,
    capital_city  VARCHAR(100) UNIQUE NOT NULL,  -- 1:1 stored inline
    population    BIGINT,
    iso_code      CHAR(2) UNIQUE
);

-- CUSTOMER (1) → ORDER (N)
CREATE TABLE customers (
    customer_id  VARCHAR(10)  PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE orders (
    order_id     VARCHAR(15)  PRIMARY KEY,
    customer_id  VARCHAR(10)  NOT NULL,  -- FK here (many side)
    order_date   TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    status       VARCHAR(20)  DEFAULT 'pending',
    total        DECIMAL(10,2),
    
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    -- ON DELETE options:
    -- ON DELETE CASCADE:    delete customer → delete all their orders (careful!)
    -- ON DELETE RESTRICT:   cannot delete customer if they have orders (safe default)
    -- ON DELETE SET NULL:   set customer_id to NULL (only if order can be anonymous)
);

-- WRONG: trying to store the 1:N relationship on the "one" side
-- customers (customer_id, name, order_ids TEXT[])  ← WRONG!
-- This violates 1NF, makes joins impossible, and duplicates data.
-- The FK ALWAYS goes on the N side.

-- Querying 1:N:
-- "All orders for customer C001 with their total value"
SELECT o.order_id, o.order_date, o.total, o.status
FROM orders o
WHERE o.customer_id = 'C001'
ORDER BY o.order_date DESC;

-- "Customers with more than 5 orders (high-value customers)"
SELECT c.name, COUNT(o.order_id) AS order_count, SUM(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
HAVING COUNT(o.order_id) > 5
ORDER BY lifetime_value DESC;

-- STUDENT (M) ↔ COURSE (N) — a student can take many courses,
--                             a course can have many students

CREATE TABLE students (
    student_id   INT          PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    email        VARCHAR(150) UNIQUE NOT NULL
);

CREATE TABLE courses (
    course_id    VARCHAR(10)  PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    credits      INT          NOT NULL CHECK (credits BETWEEN 1 AND 6),
    department   VARCHAR(50)
);

-- Junction table — represents the M:N relationship
CREATE TABLE enrollments (
    student_id       INT   NOT NULL,
    course_id        VARCHAR(10) NOT NULL,
    -- Relationship attributes:
    enrolled_date    DATE  NOT NULL DEFAULT CURRENT_DATE,
    grade            CHAR(2),    -- NULL until graded
    attendance_pct   DECIMAL(5,2),
    
    PRIMARY KEY (student_id, course_id),  -- composite PK prevents duplicate enrollments
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)   ON DELETE RESTRICT,
    
    -- Additional constraint: grade must be a valid value or NULL
    CHECK (grade IN ('A+', 'A', 'B+', 'B', 'C+', 'C', 'D', 'F') OR grade IS NULL)
);

-- PRODUCT (M) ↔ ORDER (N) — an order contains many products,
--                           a product can be in many orders
CREATE TABLE order_items (
    order_id       VARCHAR(15)  NOT NULL,
    product_id     VARCHAR(20)  NOT NULL,
    -- Relationship attributes:
    quantity       INT          NOT NULL CHECK (quantity > 0),
    unit_price     DECIMAL(10,2) NOT NULL,  -- price at time of purchase (may change later)
    discount_pct   DECIMAL(5,2) DEFAULT 0,
    
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id)   REFERENCES orders(order_id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

-- TEACHER (M) ↔ COURSE (N) — a teacher can teach many courses,
--                            a course can be taught by many teachers (co-teaching)
CREATE TABLE course_teachers (
    teacher_id   INT         NOT NULL,
    course_id    VARCHAR(10) NOT NULL,
    semester     VARCHAR(10) NOT NULL,  -- 'Spring2024', 'Fall2024'
    role         VARCHAR(20) DEFAULT 'instructor',  -- instructor, assistant, guest
    
    PRIMARY KEY (teacher_id, course_id, semester),  -- teacher can teach same course in diff semesters
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id),
    FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

-- Querying M:N relationships:
-- "All courses taken by student 1001 with their grades"
SELECT c.title, c.credits, e.grade, e.enrolled_date
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1001
ORDER BY e.enrolled_date DESC;

-- "Students who passed both DBMS and Algorithms"
SELECT s.name
FROM students s
WHERE EXISTS (
    SELECT 1 FROM enrollments
    WHERE student_id = s.student_id AND course_id = 'CS301' AND grade NOT IN ('D','F')
)
AND EXISTS (
    SELECT 1 FROM enrollments
    WHERE student_id = s.student_id AND course_id = 'CS302' AND grade NOT IN ('D','F')
);

-- CUSTOMER entity: customer_id (key), name, email, city, date_of_birth, is_active
CREATE TABLE customers (
    customer_id  VARCHAR(10)   PRIMARY KEY,  -- key attribute → PK
    name         VARCHAR(100)  NOT NULL,     -- simple attribute
    email        VARCHAR(150)  UNIQUE,       -- simple attribute, alternate key
    city         VARCHAR(100),              -- simple attribute
    date_of_birth DATE,                     -- simple attribute
    is_active    BOOLEAN       DEFAULT true -- simple attribute
    -- Note: age is DERIVED from date_of_birth → NOT stored as column
    -- Note: if name was composite (first, last) → first_name, last_name columns (not full_name)
);

-- ORDER_ITEM (weak) depends on ORDER (strong)
-- ORDER_ITEM's partial key: line_number
-- ORDER_ITEM's full PK: (order_id, line_number)

CREATE TABLE order_items (
    order_id    VARCHAR(15)   NOT NULL,      -- FK to identifying entity (ORDER)
    line_number INT           NOT NULL,      -- partial key (discriminator)
    product_id  VARCHAR(20)   NOT NULL,
    quantity    INT           NOT NULL CHECK (quantity > 0),
    unit_price  DECIMAL(10,2) NOT NULL,
    
    PRIMARY KEY (order_id, line_number),    -- composite PK = owner PK + partial key
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
    -- CASCADE: deleting an order must delete all its items (dependency)
);

-- EMPLOYEE "has" PARKING_SPOT (optional on both sides)
-- Put FK on PARKING_SPOT (parking spots are optional assignments)

CREATE TABLE parking_spots (
    spot_id     VARCHAR(10)  PRIMARY KEY,
    floor       INT,
    spot_number INT,
    employee_id INT          UNIQUE,  -- UNIQUE enforces 1:1 max
    -- NULL allowed: a spot may be unassigned
    
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
    ON DELETE SET NULL  -- if employee leaves, spot becomes unassigned (not deleted)
);

-- COUNTRY "has" CAPITAL (mandatory on both sides — merge makes sense)
CREATE TABLE countries (
    country_id    INT          PRIMARY KEY,
    country_name  VARCHAR(100) UNIQUE NOT NULL,
    capital_city  VARCHAR(100) UNIQUE NOT NULL,  -- merged 1:1 into same table
    continent     VARCHAR(50),
    population    BIGINT
);

-- DEPARTMENT (1) "employs" EMPLOYEE (N)
-- FK goes in EMPLOYEE table (N side), referencing DEPARTMENT table

ALTER TABLE employees
    ADD COLUMN dept_id INT NOT NULL,
    ADD CONSTRAINT fk_dept
        FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- If the relationship has attributes (e.g., date_joined_dept):
ALTER TABLE employees
    ADD COLUMN dept_id       INT  NOT NULL,
    ADD COLUMN dept_join_date DATE,  -- relationship attribute stored on N side
    ADD CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- STUDENT (M) "enrolled in" COURSE (N)
-- Relationship attributes: enrolled_date, grade

CREATE TABLE enrollments (
    student_id    INT         NOT NULL,  -- FK to STUDENT
    course_id     VARCHAR(10) NOT NULL,  -- FK to COURSE
    -- Relationship attributes:
    enrolled_date DATE        NOT NULL DEFAULT CURRENT_DATE,
    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
);

-- EMPLOYEE has multi-valued attribute: previous_degrees

CREATE TABLE employee_degrees (
    employee_id  INT          NOT NULL,
    degree_name  VARCHAR(200) NOT NULL,  -- multi-valued attribute
    institution  VARCHAR(200),
    year_awarded INT,
    
    PRIMARY KEY (employee_id, degree_name),  -- PK = FK + multi-valued attr
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id) ON DELETE CASCADE
);

-- PRODUCT has multi-valued attribute: tags/categories
CREATE TABLE product_tags (
    product_id   VARCHAR(20)  NOT NULL,
    tag          VARCHAR(100) NOT NULL,
    
    PRIMARY KEY (product_id, tag),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

-- SUPPLIER "supplies" PART "to" PROJECT
-- A ternary relationship: which supplier supplies which part to which project

CREATE TABLE supply_contracts (
    supplier_id  INT  NOT NULL,
    part_id      INT  NOT NULL,
    project_id   INT  NOT NULL,
    -- Relationship attributes:
    quantity     INT  NOT NULL,
    unit_cost    DECIMAL(10,2),
    delivery_date DATE,
    
    PRIMARY KEY (supplier_id, part_id, project_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),
    FOREIGN KEY (part_id)     REFERENCES parts(part_id),
    FOREIGN KEY (project_id)  REFERENCES projects(project_id)
);