Schema Design, Data Types & Normalization

🔒 Relational Integrity Constraints

Constraints are declarative rules configured at schema definition time (CREATE TABLE / ALTER TABLE). The database storage engine enforces these rules on every INSERT, UPDATE, and DELETE operation to guarantee domain integrity and referential data validity across tables.

• Analogy: Building Blueprints & Inspection Codes.
  • PRIMARY KEY: The unique National Identification / Fingerprint assigned to each resident.
  • FOREIGN KEY: A parental guardianship paper linking a minor child to a verified parent record.
  • UNIQUE: Passport numbers—no two residents can hold the exact same active passport string.
  • CHECK: City structural safety codes (e.g., "Balcony railing height must be ≥ 42 inches").

CREATE TABLE departments (
    dept_id     INT PRIMARY KEY,
    dept_name   VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    emp_name    VARCHAR(100) NOT NULL,
    salary      NUMERIC(10, 2) CHECK (salary > 0),
    dept_id     INT,
    email       VARCHAR(150) UNIQUE,
    status      VARCHAR(20) DEFAULT 'ACTIVE',
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id) 
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Constraint Mechanics Matrix

🔄 Foreign Key Cascading Actions

When a parent row in a referenced table is deleted or updated, foreign key rules determine how child rows react:

                          Parent Row Deleted / Updated
                                       │
     ┌───────────────────┬─────────────┴─────────────┬───────────────────┐
     ▼                   ▼                           ▼                   ▼
ON DELETE RESTRICT   ON DELETE CASCADE         ON DELETE SET NULL    ON DELETE SET DEFAULT
Blocks parent delete;  Deletes linked child       Sets child foreign    Resets child foreign
throws FK error        tuples automatically       key field to NULL     key to default value

1. ON DELETE RESTRICT (Default): Throws a referential integrity violation error and blocks deletion of the parent row if any child rows reference it.
2. ON DELETE CASCADE: Automatically deletes all child rows linked to the parent row being removed.
3. ON DELETE SET NULL: Automatically updates the foreign key column in all linked child rows to NULL when the parent row is deleted.

🔢 SQL Data Types Engineering Guide

Choosing proper physical column data types impacts disk storage footprint, CPU cache alignment, and indexing speed:

📐 Database Normalization (1NF to BCNF)

Normalization is the systematic process of organizing relational schema tables to eliminate data redundancy and prevent destructive database anomalies:

• Insertion Anomaly: Inability to insert data without adding dummy attributes in unrelated fields.
• Deletion Anomaly: Deleting one piece of data unintentionally wipes out completely unrelated critical information.
• Update Anomaly: Updating a single data entity requires modifying dozens of duplicate rows across the database.

• Analogy: Organizing your clothes closet. Instead of dumping shoes, shirts, coats, and socks into one giant unorganized bin (unnormalized table), you put shirts on hangers, shoes in shoe racks, and socks in labeled drawers (normalized relational tables).

   Unnormalized Data  ──►  1NF (Atomic Values)  ──►  2NF (Remove Partial Dependencies)
                                                                  │
   BCNF (Strict Superkey)  ◄──  3NF (Remove Transitive Dependencies) ◄┘

1. First Normal Form (1NF): Atomic Values

• Rule: Every column must contain indivisible atomic values. No repeating groups, arrays, or comma-separated strings stored inside single cells. Every row must be uniquely identifiable.

-- ❌ VIOLATES 1NF: Multi-valued comma-separated string in hobbies cell
-- emp_id | emp_name | hobbies
-- 101    | Alice    | "Reading, Swimming, Chess"

-- ✅ COMPLIES WITH 1NF: Decomposed into atomic tuples
-- emp_id | emp_name | hobby
-- 101    | Alice    | Reading
-- 101    | Alice    | Swimming
-- 101    | Alice    | Chess

2. Second Normal Form (2NF): Full Functional Dependency

• Rule: Table must be in 1NF, and every non-key attribute must depend on the entire primary key (eliminates partial functional dependencies in composite key tables).

3. Third Normal Form (3NF): Non-Transitive Dependencies

• Rule: Table must be in 2NF, and no non-key attribute can depend on another non-key attribute (eliminates transitive functional dependencies).
• Mantra: "Every non-key column must depend on the key, the whole key, and nothing but the key (so help me Codd)."

Transitive Dependency Violation:
Emp_ID (Primary Key) ──► Dept_ID (Non-Key) ──► Dept_Name (Non-Key)

Solution: Decompose into two separate tables:
1. employees: (emp_id [PK], emp_name, dept_id [FK])
2. departments: (dept_id [PK], dept_name)

4. Boyce-Codd Normal Form (BCNF): Strict Superkey Rule

• Rule: A stricter refinement of 3NF. For every non-trivial functional dependency X → Y, X must be a superkey (candidate primary key).

🏗️ Strategic Denormalization

While high normalization (3NF/BCNF) is mandatory for OLTP (Online Transaction Processing) systems to ensure high write performance and eliminate update anomalies, OLAP (Online Analytical Processing) data warehouses intentionally introduce denormalization (Star Schema / Snowflake Schema).

❓ Conceptual Quizzes

💻 Practice Problems