04 - SQL DDL: Creating Database Schemas

🎯 Learning Objectives

• Write SQL DDL statements to create tables with appropriate data types
• Define primary keys, foreign keys, and other constraints
• Use ALTER TABLE to modify existing schemas
• Explain the difference between CREATE TYPE and CREATE DOMAIN
• Enforce referential integrity with ON DELETE/ON UPDATE actions

📋 Prerequisites

• 03 - Relational Model — Keys (primary, foreign, candidate), domains, referential integrity
• Basic SQL terminology — what a table is

📖 Core Content

4.1 Intuition: DDL is the Blueprint

• What tables exist
• What columns each table has
• What data types those columns hold
• What rules (constraints) the data must follow The actual data (rows) comes later via DML (INSERT, UPDATE, etc.). DDL is about structure — it's executed once and changes rarely.

Why This Matters: A well-designed schema is the foundation of a good database. Poor schema design leads to data anomalies, performance problems, and maintenance nightmares. Getting the DDL right is the most important step.

4.2 CREATE TABLE: The Basics

sql
CREATE TABLE instructor (
    ID        VARCHAR(5),
    name      VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    salary    NUMERIC(8,2),
    PRIMARY KEY (ID)
);

• CREATE TABLE instructor (...) — Creates a table named instructor
• ID VARCHAR(5) — Column named ID with variable-length string up to 5 chars
• name VARCHAR(20) NOT NULL — Column cannot contain NULL values
• salary NUMERIC(8,2) — Decimal number with 8 digits total, 2 after decimal point
• PRIMARY KEY (ID) — Enforces uniqueness and NOT NULL on ID

4.3 SQL Data Types

• Use VARCHAR not CHAR for strings of varying length (saves space)
• Use INT for whole numbers, NUMERIC for money (exact decimals)
• Use DATE/TIMESTAMP for temporal data (not strings!)

4.4 Integrity Constraints

sql
CREATE TABLE student (
    ID        VARCHAR(5) PRIMARY KEY,    -- inline primary key
    name      VARCHAR(20) NOT NULL,       -- must have a value
    dept_name VARCHAR(20),
    tot_cred  NUMERIC(3,0) DEFAULT 0,     -- default value if not specified
    email     VARCHAR(50) UNIQUE,         -- no duplicates allowed
    age       NUMERIC(3,0) CHECK (age >= 0 AND age < 150), -- domain constraint
    CONSTRAINT valid_dept CHECK (dept_name IN ('CS', 'Math', 'Physics'))
);

NOT NULL

sql
name VARCHAR(20) NOT NULL

UNIQUE

sql
email VARCHAR(50) UNIQUE

CHECK

sql
CHECK (salary >= 0)
CHECK (semester IN ('Fall', 'Spring', 'Summer'))
CHECK (end_date > start_date)

PRIMARY KEY

sql
PRIMARY KEY (ID)
-- or composite:
PRIMARY KEY (course_id, semester, year)

FOREIGN KEY

sql
FOREIGN KEY (dept_name) REFERENCES department(dept_name)

4.5 Referential Integrity: ON DELETE and ON UPDATE

sql
CREATE TABLE course (
    course_id    VARCHAR(8) PRIMARY KEY,
    title        VARCHAR(50),
    dept_name    VARCHAR(20),
    credits      NUMERIC(2,0),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

• CASCADE: All courses with dept_name='CS' are also deleted
• SET NULL: Courses' dept_name becomes NULL
• RESTRICT: Delete is rejected until courses are reassigned

Best Practice: Use RESTRICT for critical data (don't accidentally delete a department and all its courses), CASCADE for dependent data (delete an order and its line items).

4.6 ALTER TABLE

sql
-- Add a column
ALTER TABLE instructor ADD COLUMN phone VARCHAR(15);
-- Drop a column
ALTER TABLE instructor DROP COLUMN phone;
-- Add a constraint
ALTER TABLE instructor ADD CONSTRAINT unique_email UNIQUE(email);
-- Add a foreign key
ALTER TABLE teaches ADD FOREIGN KEY (course_id) REFERENCES course(course_id);
-- Drop a constraint
ALTER TABLE instructor DROP CONSTRAINT unique_email;

4.7 DROP TABLE

sql
DROP TABLE instructor;  -- removes table permanently (irreversible!)

sql
DELETE FROM instructor;  -- removes all rows, table remains

4.8 CREATE TYPE vs. CREATE DOMAIN

sql
-- CREATE TYPE: New type, no constraints
CREATE TYPE dollars AS NUMERIC(12,2) FINAL;
CREATE TABLE department (
    dept_name VARCHAR(20),
    building  VARCHAR(15),
    budget    dollars  -- uses the custom type
);
-- CREATE DOMAIN: New type WITH constraints
CREATE DOMAIN degree_level AS VARCHAR(10)
    CONSTRAINT degree_level_test
    CHECK (VALUE IN ('Bachelors', 'Masters', 'Doctorate'));
CREATE TABLE professor (
    name  VARCHAR(50),
    level degree_level  -- constrained to specific values
);

4.9 Schema Creation: Complete Example

sql
-- Strong entities first (no foreign keys)
CREATE TABLE department (
    dept_name VARCHAR(20) PRIMARY KEY,
    building  VARCHAR(15),
    budget    NUMERIC(12,2) CHECK (budget >= 0)
);
CREATE TABLE student (
    ID        VARCHAR(5) PRIMARY KEY,
    name      VARCHAR(20) NOT NULL,
    dept_name VARCHAR(20),
    tot_cred  NUMERIC(3,0) DEFAULT 0,
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
-- ... more tables with foreign keys
CREATE TABLE course (
    course_id  VARCHAR(8) PRIMARY KEY,
    title      VARCHAR(50),
    dept_name  VARCHAR(20),
    credits    NUMERIC(2,0) CHECK (credits > 0),
    FOREIGN KEY (dept_name) REFERENCES department(dept_name)
);
-- Tables referencing earlier tables
CREATE TABLE takes (
    ID         VARCHAR(5),
    course_id  VARCHAR(8),
    semester   VARCHAR(6),
    year       NUMERIC(4,0),
    grade      VARCHAR(2),
    PRIMARY KEY (ID, course_id, semester, year),
    FOREIGN KEY (ID) REFERENCES student(ID),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Forgetting that PRIMARY KEY Implies NOT NULL

Pitfall 2: Using VARCHAR for Dates

Pitfall 3: Confusing CASCADE Actions

📝 Practice Questions

Q1. Write a CREATE TABLE statement for a department table with: dept_name (primary key), building, budget (must be >= 0).

Answer

sql
CREATE TABLE department (
    dept_name VARCHAR(20) PRIMARY KEY,
    building  VARCHAR(15),
    budget    NUMERIC(12,2) CHECK (budget >= 0)
);

Q2. Write a CREATE TABLE for employee with: emp_id (PK), name (NOT NULL), email (UNIQUE), salary (must be positive), dept_id (FK referencing department).

Answer

sql
CREATE TABLE employee (
    emp_id   INTEGER PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    email    VARCHAR(100) UNIQUE,
    salary   NUMERIC(10,2) CHECK (salary > 0),
    dept_id  INTEGER REFERENCES department(dept_id)
);

Q3. What is the difference between DROP TABLE and DELETE FROM?

Answer

• DROP TABLE instructor — Removes the entire table (structure + data). Table no longer exists.
• DELETE FROM instructor — Removes all rows. Table structure remains, can still insert new data.

DROP is DDL (changes schema); DELETE is DML (changes data only).

Q4. Explain what ON DELETE CASCADE does. Give an example.

Answer

ON DELETE CASCADE means: when a row in the parent table is deleted, all rows in the child table that reference it are automatically deleted too.

Example: If department('CS') is deleted, and courses table has FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON DELETE CASCADE, then all CS courses are deleted as well.

Use when: child rows have no meaning without the parent (e.g., order items without the order).

Q5. Add a column phone to the instructor table.

Answer

sql
ALTER TABLE instructor ADD COLUMN phone VARCHAR(15);

Q6. What is the difference between CREATE TYPE and CREATE DOMAIN?

Answer

• CREATE TYPE creates a new data type (like typedef in C). Cannot add constraints.
• CREATE DOMAIN creates a new data type WITH constraints (CHECK conditions).

Example:

sql
CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);
CREATE TYPE ssn AS VARCHAR(9);  -- no constraints

Q7. What are the five possible actions for foreign key ON DELETE? Briefly describe each.

Answer

1. CASCADE — Delete referencing rows too
2. SET NULL — Set foreign key to NULL
3. SET DEFAULT — Set foreign key to its default value
4. RESTRICT — Don't allow delete if there are referencing rows
5. NO ACTION — Same as RESTRICT, but checked at end of transaction

Q8. Write the ALTER TABLE commands to: (a) make email NOT NULL, (b) add a CHECK that age >= 18.

Answer

sql
-- (a) Make email NOT NULL
ALTER TABLE student ALTER COLUMN email SET NOT NULL;

-- (b) Add age check constraint
ALTER TABLE student ADD CONSTRAINT age_check CHECK (age >= 18);

🔗 Cross-References

• Next Topic: 05 - SQL Queries
• Previous Topic: 03 - Relational Model
• Related: BSCS2003 (MAD 1) — Creating tables for web applications
• Related: BSMS2001 (BDM) — Database schema design for business
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 3 (SQL), Chapter 4 (Intermediate SQL) Join Discord Previous03 - Relational ModelNext05 - SQL Queries