Relational Model & Keys

-- Without PK: duplicates are possible (SQL multiset behaviour)
CREATE TABLE bad_table (name VARCHAR(100), city VARCHAR(100));
INSERT INTO bad_table VALUES ('Rahul', 'Bengaluru');
INSERT INTO bad_table VALUES ('Rahul', 'Bengaluru');  -- allowed! duplicate silently inserted
SELECT COUNT(*) FROM bad_table;  -- Returns: 2

-- With PK: duplicates impossible on the PK column
CREATE TABLE good_table (
    customer_id  VARCHAR(10) PRIMARY KEY,  -- PK prevents duplicate customer_ids
    name         VARCHAR(100),
    city         VARCHAR(100)
);

-- WRONG ASSUMPTION: rows come back in insertion order
INSERT INTO customers VALUES ('C003', 'Arjun', 'Mumbai');
INSERT INTO customers VALUES ('C001', 'Rahul', 'Bengaluru');
INSERT INTO customers VALUES ('C002', 'Priya', 'Hyderabad');

SELECT * FROM customers;
-- MIGHT return:  C001, C002, C003 (index order)
-- MIGHT return:  C003, C001, C002 (insertion order)
-- MIGHT return:  C002, C003, C001 (random, based on DBMS internals)
-- ALL OF THESE ARE CORRECT by the relational model.

-- CORRECT: if you need sorted output, explicitly request it
SELECT * FROM customers ORDER BY customer_id;  -- always alphabetical
SELECT * FROM customers ORDER BY name;          -- always alphabetical by name

-- FRAGILE: positional column access (never do this)
SELECT col1, col2, col3 FROM customers;  -- position-dependent
-- If someone does ALTER TABLE ADD COLUMN before col2, this breaks

-- ROBUST: named column access (always do this)
SELECT customer_id, name, city FROM customers;  -- always correct
-- Column order in CREATE TABLE can change; named access remains valid

-- The relational model says these two are semantically equivalent:
CREATE TABLE customers (customer_id VARCHAR(10), name VARCHAR(100), city VARCHAR(50));
CREATE TABLE customers (city VARCHAR(50), customer_id VARCHAR(10), name VARCHAR(100));
-- Column order in CREATE TABLE is a display preference, not a semantic choice.

-- 1NF VIOLATION: storing multiple phone numbers in one cell
CREATE TABLE customers_bad (
    customer_id  VARCHAR(10) PRIMARY KEY,
    name         VARCHAR(100),
    phones       TEXT  -- "98765-43210, 87654-32109, 76543-21098"  ← WRONG
);
-- Problems:
-- Cannot query: WHERE phones = '98765-43210' (won't work reliably)
-- Cannot index: can't create a useful B-tree index on this column
-- Cannot update: changing one phone requires parsing + reconstructing the string
-- Violates the foundational definition of a relation

-- 1NF COMPLIANT: atomic values throughout
CREATE TABLE customers (
    customer_id  VARCHAR(10) PRIMARY KEY,
    name         VARCHAR(100)
);
CREATE TABLE customer_phones (
    customer_id   VARCHAR(10) NOT NULL,
    phone_number  VARCHAR(20) NOT NULL,
    phone_type    VARCHAR(20) DEFAULT 'mobile',
    PRIMARY KEY (customer_id, phone_number),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- SQL enforces unique column names within a table:
CREATE TABLE test (
    id   INT PRIMARY KEY,
    name VARCHAR(100),
    name VARCHAR(50)  -- ERROR: column "name" specified more than once
);

-- But JOIN can bring same-named columns from different tables:
SELECT c.name AS customer_name, r.name AS restaurant_name
FROM customers c JOIN restaurants r ON c.city = r.city;
-- Must qualify: c.name vs r.name to disambiguate

-- EMPLOYEES(emp_id, email, phone, name, dept_id, pan_number, salary)

-- SUPER KEYS (any set that uniquely identifies each row):

-- Single-attribute super keys (minimum size):
{emp_id}          -- emp_id is unique per employee
{email}           -- each employee has a unique work email
{phone}           -- each employee has a unique mobile
{pan_number}      -- PAN card is unique per person in India

-- Two-attribute super keys:
{emp_id, email}   -- still unique (adding email to already-unique emp_id)
{emp_id, name}    -- unique (emp_id alone is already unique)
{emp_id, salary}  -- unique
{email, phone}    -- unique (email alone is already unique)
{email, pan_number} -- unique
-- ... and many more combinations

-- Multi-attribute super keys (including redundant attributes):
{emp_id, email, phone}        -- unique
{emp_id, email, name}         -- unique
{emp_id, email, phone, name, dept_id, pan_number, salary}  -- ALL attributes = super key

-- NOT super keys (do NOT uniquely identify rows):
{name}     -- two employees could have the same name
{dept_id}  -- multiple employees per department
{salary}   -- multiple employees can have the same salary
{name, dept_id} -- two employees in same dept could have same name

-- TOTAL COUNT of super keys in this relation:
-- Starting with 4 single-attribute keys (emp_id, email, phone, pan_number),
-- every superset of any of these is also a super key.
-- The number of super keys grows exponentially with relation size.
-- This is why super keys alone are not useful practically.

-- EMPLOYEES(emp_id, email, phone, name, dept_id, pan_number, salary)

-- CANDIDATE KEY TEST: start with a super key, try removing each attribute
-- If removing any attribute breaks uniqueness → it's a candidate key (minimal)

-- Test {emp_id}:
--   Remove emp_id from {emp_id}: {} → empty set, clearly not unique
--   Therefore, {emp_id} cannot be reduced further → CANDIDATE KEY ✓

-- Test {email}:
--   Remove email from {email}: {} → not unique
--   Therefore, {email} is a candidate key ✓

-- Test {phone}:
--   Remove phone from {phone}: {} → not unique
--   Therefore, {phone} is a candidate key ✓

-- Test {pan_number}:
--   Remove pan_number: {} → not unique
--   Therefore, {pan_number} is a candidate key ✓

-- Test {emp_id, email} (a super key):
--   Remove emp_id: {email} → still unique (email alone is unique) ← CAN REDUCE!
--   Therefore, {emp_id, email} is NOT a candidate key (it's a non-minimal super key)

-- CANDIDATE KEYS of EMPLOYEES:
-- {emp_id}, {email}, {phone}, {pan_number}
-- These are ALL equally valid for becoming the primary key.

-- COMPOSITE CANDIDATE KEY EXAMPLE — when no single attribute is unique:
-- FLIGHTS(flight_number, departure_date, departure_time, origin, destination, airline)
-- 
-- Single attributes tested:
--   {flight_number}: NOT unique — same flight number runs daily (AI-101 exists every day)
--   {departure_date}: NOT unique — many flights depart the same day
--   {departure_time}: NOT unique — many flights depart at 08:00
--
-- Combinations tested:
--   {flight_number, departure_date}: unique! Flight AI-101 on 2024-03-15 is one specific flight
--                                    Try removing flight_number: {departure_date} — NOT unique
--                                    Try removing departure_date: {flight_number} — NOT unique
--                                    Cannot reduce further → CANDIDATE KEY ✓
--
-- {flight_number, departure_date} is the candidate key (and primary key) for FLIGHTS.

-- Given relation R(A, B, C, D, E) and FDs:
-- AB → C, C → D, D → E, E → A

-- Check if {A, B} is a candidate key:
-- Closure of {A,B}:
--   Start: {A, B}
--   Apply AB → C: {A, B, C}
--   Apply C → D:  {A, B, C, D}
--   Apply D → E:  {A, B, C, D, E}
-- {A,B}+ = {A,B,C,D,E} = all attributes → {A,B} is a super key
-- Can we remove A? {B}+ = {B} ≠ all attributes → NO
-- Can we remove B? {A}+ = {A, E, A} = {A,E} ≠ all attributes → NO
-- (using E→A: {A}+ = {A,E} only)
-- {A,B} is minimal → CANDIDATE KEY ✓

-- Check if {B, E} is a candidate key:
-- {B,E}+:
--   Start: {B, E}
--   Apply E → A: {A, B, E}
--   Apply AB → C: {A, B, C, E}
--   Apply C → D:  {A, B, C, D, E}
-- {B,E}+ = all attributes → super key
-- Remove B? {E}+ = {E,A} ≠ all → cannot remove
-- Remove E? {B}+ = {B} ≠ all → cannot remove
-- {B,E} is minimal → CANDIDATE KEY ✓

-- This relation has TWO candidate keys: {A,B} and {B,E}
-- The DBA must choose one as the primary key.

-- PRIMARY KEY declaration:
CREATE TABLE employees (
    emp_id      SERIAL        PRIMARY KEY,  -- system-assigned INT, never NULL
    email       VARCHAR(150)  NOT NULL UNIQUE,   -- candidate key → alternate key
    phone       VARCHAR(20)   NOT NULL UNIQUE,   -- candidate key → alternate key
    name        VARCHAR(100)  NOT NULL,
    dept_id     INT           NOT NULL,
    pan_number  CHAR(10)      NOT NULL UNIQUE,   -- candidate key → alternate key
    salary      DECIMAL(10,2) NOT NULL CHECK (salary > 0),
    hire_date   DATE          NOT NULL DEFAULT CURRENT_DATE
);

-- ENTITY INTEGRITY CONSTRAINT TEST:
INSERT INTO employees (email, name, dept_id, pan_number, salary)
VALUES (NULL, 'Test', 1, 'ABCDE1234F', 50000);
-- ERROR: null value in column "emp_id" violates not-null constraint
-- The DBMS rejects NULL in the primary key column.

INSERT INTO employees (emp_id, email, name, dept_id, pan_number, salary)
VALUES (1, 'rahul@co.in', 'Rahul', 1, 'ABCDE1234F', 50000);
INSERT INTO employees (emp_id, email, name, dept_id, pan_number, salary)
VALUES (1, 'priya@co.in', 'Priya', 2, 'FGHIJ5678K', 60000);
-- ERROR: duplicate key value violates unique constraint "employees_pkey"
-- The DBMS rejects duplicate primary key values.

-- COMPOSITE PRIMARY KEY:
CREATE TABLE order_items (
    order_id     VARCHAR(15)   NOT NULL,
    line_number  INT           NOT NULL,
    product_id   VARCHAR(20)   NOT NULL,
    quantity     INT           NOT NULL,
    unit_price   DECIMAL(10,2) NOT NULL,
    
    PRIMARY KEY (order_id, line_number)  -- composite PK
    -- Both order_id AND line_number are NOT NULL (entity integrity on composite PK)
    -- The COMBINATION must be unique (not each attribute individually)
);

-- Test composite PK uniqueness:
INSERT INTO order_items VALUES ('ORD-001', 1, 'P001', 2, 280);
INSERT INTO order_items VALUES ('ORD-001', 2, 'P003', 1, 340);  -- OK: different line_number
INSERT INTO order_items VALUES ('ORD-002', 1, 'P001', 1, 280);  -- OK: different order_id
INSERT INTO order_items VALUES ('ORD-001', 1, 'P005', 3, 200);  -- ERROR: duplicate (ORD-001, 1)

-- EMPLOYEES relation has candidate keys: {emp_id}, {email}, {phone}, {pan_number}
-- Design decision: emp_id is chosen as PRIMARY KEY
-- email, phone, pan_number become ALTERNATE KEYS

CREATE TABLE employees (
    emp_id      SERIAL        PRIMARY KEY,       -- chosen candidate key → PK
    
    email       VARCHAR(150)  NOT NULL UNIQUE,   -- alternate key #1
    phone       VARCHAR(20)   NOT NULL UNIQUE,   -- alternate key #2
    pan_number  CHAR(10)      NOT NULL UNIQUE,   -- alternate key #3
    
    name        VARCHAR(100)  NOT NULL,
    dept_id     INT           NOT NULL,
    salary      DECIMAL(10,2)
);

-- Why UNIQUE constraints on alternate keys matter:
-- 1. Data integrity: prevents accidentally assigning same email to two employees
-- 2. Performance: UNIQUE constraints automatically create an index
--    → fast lookup by email (login systems look up by email, not by emp_id)
-- 3. Natural identifiers: business users know employees by email, not by emp_id
--    → alternate keys are often more user-facing than the PK

-- Querying by alternate key:
-- Login endpoint: "authenticate user with this email"
SELECT emp_id, name, dept_id, salary
FROM employees
WHERE email = 'rahul@co.in';
-- Uses the UNIQUE index on email → O(log n) lookup, extremely fast

-- Password reset: "find account by phone number"
SELECT emp_id, name, email
FROM employees
WHERE phone = '98765-43210';
-- Uses the UNIQUE index on phone → O(log n) lookup

-- Tax system: "validate employee by PAN"
SELECT emp_id, name
FROM employees
WHERE pan_number = 'ABCDE1234F';
-- Uses the UNIQUE index on pan_number → O(log n) lookup

-- Parent (referenced) relation:
CREATE TABLE departments (
    dept_id    SERIAL        PRIMARY KEY,
    dept_name  VARCHAR(100)  UNIQUE NOT NULL,
    location   VARCHAR(100),
    budget     DECIMAL(15,2)
);

-- Child (referencing) relation:
CREATE TABLE employees (
    emp_id     SERIAL        PRIMARY KEY,
    name       VARCHAR(100)  NOT NULL,
    dept_id    INT           NOT NULL,  -- FK to departments
    manager_id INT,                     -- FK to employees (self-referential, nullable)
    salary     DECIMAL(10,2),
    
    CONSTRAINT fk_employee_dept
        FOREIGN KEY (dept_id)
        REFERENCES departments(dept_id)
        ON DELETE RESTRICT    -- cannot delete a dept that has employees
        ON UPDATE CASCADE,    -- if dept_id changes, update all employee records
    
    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employees(emp_id)
        ON DELETE SET NULL    -- if manager is deleted, set manager_id to NULL
        ON UPDATE CASCADE
);

-- REFERENTIAL INTEGRITY VIOLATION TESTS:

-- Test 1: INSERT with non-existent FK value
INSERT INTO employees (name, dept_id, salary)
VALUES ('New Employee', 999, 50000);
-- ERROR: insert or update on table "employees" violates foreign key constraint
-- Department 999 does not exist in departments table

-- Test 2: DELETE parent that has children
DELETE FROM departments WHERE dept_id = 1;
-- ERROR: update or delete on table "departments" violates foreign key constraint
-- Employees reference dept_id = 1 — ON DELETE RESTRICT blocks this

-- Test 3: Allowed INSERT (FK value exists)
INSERT INTO departments (dept_name, location) VALUES ('Engineering', 'Bengaluru');
-- dept_id = 1 is assigned by SERIAL
INSERT INTO employees (name, dept_id, salary) VALUES ('Rahul', 1, 85000);
-- OK: dept_id = 1 exists in departments

-- Test 4: Self-referential FK (manager hierarchy)
-- First insert must have NULL manager (top of hierarchy)
INSERT INTO employees (name, dept_id, salary, manager_id)
VALUES ('CEO', 1, 200000, NULL);  -- emp_id = 1, no manager

INSERT INTO employees (name, dept_id, salary, manager_id)
VALUES ('VP Engineering', 1, 150000, 1);  -- reports to emp_id = 1 (CEO)

INSERT INTO employees (name, dept_id, salary, manager_id)
VALUES ('Rahul', 1, 85000, 2);  -- reports to emp_id = 2 (VP)

-- Test 5: Circular FK reference — always causes constraint violation
-- Cannot insert A with FK pointing to B if B doesn't exist yet
-- Solution for chicken-and-egg scenarios:
BEGIN;
    INSERT INTO employees (name, dept_id, salary) VALUES ('Alice', 1, 80000);
    -- emp_id = 4, manager_id = NULL initially
    INSERT INTO employees (name, dept_id, salary, manager_id) VALUES ('Bob', 1, 75000, 4);
    UPDATE employees SET manager_id = 5 WHERE emp_id = 4;  -- now Alice reports to Bob
COMMIT;  -- constraints checked at commit, not mid-transaction (DEFERRED mode)

-- EXAMPLE 1: Junction table composite PK
-- (student_id, course_id) — each student-course pair is unique
-- but student_id alone repeats (student takes many courses)
-- and course_id alone repeats (course has many students)
CREATE TABLE enrollments (
    student_id   INT  NOT NULL,
    course_id    INT  NOT NULL,
    grade        CHAR(2),
    enrolled_at  DATE DEFAULT CURRENT_DATE,
    
    PRIMARY KEY (student_id, course_id)  -- composite PK
);

-- The composite PK allows:
-- ('S001', 'CS101') -- Rahul in DBMS
-- ('S001', 'CS102') -- Rahul in Algorithms (same student_id, different course_id → OK)
-- ('S002', 'CS101') -- Priya in DBMS (different student_id, same course_id → OK)
-- ('S001', 'CS101') -- REJECTED: (S001, CS101) already exists → duplicate composite PK

-- EXAMPLE 2: Natural composite key (no surrogate PK needed)
-- A flight is uniquely identified by its number AND departure date
CREATE TABLE flights (
    flight_number    CHAR(8)       NOT NULL,  -- 'AI-101', 'IndiGo-6E204'
    departure_date   DATE          NOT NULL,
    origin           CHAR(3)       NOT NULL,  -- IATA airport code 'BLR', 'DEL', 'BOM'
    destination      CHAR(3)       NOT NULL,
    departure_time   TIME          NOT NULL,
    arrival_time     TIME          NOT NULL,
    aircraft_type    VARCHAR(50),
    
    PRIMARY KEY (flight_number, departure_date)  -- natural composite PK
);

-- EXAMPLE 3: Composite UNIQUE constraint (not PK but still composite key)
-- A company can have only one department with a given name in each location
CREATE TABLE departments (
    dept_id    SERIAL        PRIMARY KEY,
    dept_name  VARCHAR(100)  NOT NULL,
    city       VARCHAR(100)  NOT NULL,
    budget     DECIMAL(15,2),
    
    UNIQUE (dept_name, city)  -- composite alternate key: no two depts with same name in same city
);
-- 'Engineering' in 'Bengaluru' is unique
-- 'Engineering' in 'Mumbai' is allowed (different city)
-- Two 'Engineering' in 'Bengaluru' → REJECTED

-- IMPORTANT NUANCE: Individual components of a composite key CAN repeat
INSERT INTO enrollments VALUES ('S001', 'CS101', 'A', '2024-01-15');
INSERT INTO enrollments VALUES ('S001', 'CS102', NULL, '2024-01-15');
-- student_id 'S001' appears twice — this is CORRECT and expected
-- The COMBINATION (S001, CS101) and (S001, CS102) are both unique

-- Option 1: SERIAL (PostgreSQL — auto-incrementing integer)
CREATE TABLE customers (
    customer_id  SERIAL        PRIMARY KEY,   -- generates 1, 2, 3, 4...
    email        VARCHAR(150)  NOT NULL UNIQUE,
    name         VARCHAR(100)  NOT NULL
);
-- SERIAL is shorthand for: integer + sequence + DEFAULT nextval('sequence')
-- Internally equivalent to:
-- customer_id  INTEGER NOT NULL DEFAULT nextval('customers_customer_id_seq')
-- + SEQUENCE customers_customer_id_seq START 1 INCREMENT 1

-- Option 2: IDENTITY column (SQL standard, newer PostgreSQL syntax)
CREATE TABLE customers (
    customer_id  INT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        VARCHAR(150) NOT NULL UNIQUE,
    name         VARCHAR(100) NOT NULL
);
-- GENERATED ALWAYS: cannot manually insert a customer_id value
-- GENERATED BY DEFAULT: can manually specify customer_id if needed (e.g., data migration)

-- Option 3: UUID (Universally Unique Identifier — 128-bit random value)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";  -- enable UUID generation
CREATE TABLE customers (
    customer_id  UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    -- generates values like: '550e8400-e29b-41d4-a716-446655440000'
    email        VARCHAR(150) NOT NULL UNIQUE,
    name         VARCHAR(100) NOT NULL
);
-- UUID advantages:
-- Globally unique: can merge records from multiple databases without conflicts
-- Can be generated client-side: no round-trip to DB for ID generation
-- Not sequential: cannot guess record counts from URL (security)
-- UUID disadvantages:
-- 16 bytes vs 4 bytes for INT: larger index, larger FK storage
-- Not human-readable: harder to debug/trace in logs
-- Random insertion order: can cause index fragmentation (use UUID v7 for sequential UUIDs)

-- WHEN TO USE WHICH:
-- SERIAL/IDENTITY INT:  single-database system, performance is critical, sequential IDs OK
-- UUID:                 multi-database merge scenarios, public-facing IDs, microservices
-- Natural key:          stable, simple, single-attribute natural identifiers that never change
--                       (e.g., ISO country codes, IATA airport codes, standard part numbers)