Part III — SQL: Structured Query Language

Chapter 9. Database and Table Definition Using SQL

Chapter 8 introduced SQL's categories; this chapter masters the first: DDL, the language of structure. Everything designed in Chapters 5–7 and mapped in Chapter 6 becomes executable here — CREATE DATABASE, CREATE TABLE with types and constraints, ALTER TABLE for schema evolution, and the drops and truncates that remove structure. The chapter ends where practice begins: implementing a complete relational schema from an ER diagram, parent tables first, and loading it to verified counts.

The chapter's discipline is a single idea from Chapter 6: every rule the DBMS can enforce, the DBMS should enforce. A schema that leans on application code for integrity is a schema with a hole in it — some future program will forget the rule.

After studying this chapter you will be able to:

• Create, select, and drop databases on both platforms.
• Write complete CREATE TABLE statements with well-chosen types.
• Choose among SQL's numeric, character, and temporal types and their platform variants.
• Declare primary, composite, and foreign keys with referential actions.
• Apply NOT NULL, UNIQUE, CHECK, and DEFAULT constraints deliberately.
• Evolve schemas safely with ALTER TABLE and remove objects cleanly.
• Generate identifiers with IDENTITY/SERIAL/AUTO_INCREMENT where appropriate.
• Create indexes and views, and implement a full ER-derived schema end to end.

9.1 Creating and dropping databases

A database is a named, isolated object collection (Chapter 4). Creating one is the first statement of every project:

CREATE DATABASE university;

$ createdb university

The createdb utility wraps the same statement — PostgreSQL's client tools ship a command per common task. Selecting a database differs by client: psql uses \c university, the mysql client uses USE university;. PostgreSQL copies its template1 when creating databases (custom templates let teams preinstall extensions); MySQL takes character set and collation options at creation, the standard spelling of which is worth adopting everywhere:

CREATE DATABASE university
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

Dropping is final:

DROP DATABASE IF EXISTS university_dev;   -- gone, with all tables, no recycle bin

The professional pattern for lab work is a dev database beside the canonical one: create university_dev for your experiments (Chapter 10's DML practice, Chapter 18's transactions), and keep university pristine against Appendix H's expected outputs. DROP DATABASE in production is a change-control event, never a Tuesday afternoon one; Chapter 21 shows what belongs around it.

9.2 Creating tables and defining columns

The canonical schema's most instructive table covers every column-definition decision at once:

CREATE TABLE course_section (
    section_id    INTEGER     PRIMARY KEY,
    course_id     VARCHAR(8)  NOT NULL REFERENCES course(course_id),
    semester      VARCHAR(6)  CHECK (semester IN ('Fall','Spring','Summer')),
    section_year  SMALLINT,
    room          VARCHAR(20),
    capacity      SMALLINT    DEFAULT 40,
    instructor_id INTEGER     REFERENCES instructor(instructor_id)
);

Reading it clause by clause: each line is a column definition — name, type, then constraints; section_id is the primary key; course_id must exist and must match an existing course (NOT NULL + foreign key — total participation from the ERD); semester is domain-constrained to three values; room may be NULL until scheduling; capacity defaults to 40 when omitted from an insert; instructor_id may be NULL (staffing pending — partial participation). Every decision traces to a modeling judgment of Chapters 5–6; DDL is where they all become machine-checked.

Two naming conventions, applied consistently from here on: constraint names (CONSTRAINT valid_semester CHECK (...)) make error messages and later ALTERs target the constraint by name; and table comments (COMMENT ON TABLE ... IS '...') store the data dictionary inside the database itself, where Chapter 4 says metadata belongs.

9.3 SQL data types

Types are domains approximated (Chapter 2). The core families:

Three decisions deserve commentary. First, NUMERIC over REAL for anything countable: NUMERIC(12,2) stores 3500000.00 exactly; a REAL budget accumulates representation error in aggregates — the classic "the report is one cent off" bug. Scale matters: NUMERIC(3,2) fits 0.00–9.99, exactly right for GPA. Second, VARCHAR(n) encodes a domain rule (grade CHAR(2), course_id VARCHAR(8)) — choose lengths as rules, not decoration; PostgreSQL's TEXT (unlimited) is standard practice where no rule exists. Third, avoid overloaded strings: MySQL's ENUM('Fall','Spring','Summer') type bakes a domain into the column type — convenient, but changing the allowed set is a schema change; the standard spelling is VARCHAR + CHECK, which the canonical schema uses. Platform notes: MySQL's NUMERIC is spelled DECIMAL (synonyms in both), and MySQL adds TINYINT/MEDIUMINT and the DATETIME vs TIMESTAMP distinction (timestamp is UTC-converted, range-limited) — Appendix D tabulates everything.

9.4 Primary-key and foreign-key definitions

Keys have two syntaxes — inline (single-column, as in section_id INTEGER PRIMARY KEY) and table-level (needed for composites and explicit naming):

CREATE TABLE enrollment (
    student_id  INTEGER NOT NULL,
    section_id  INTEGER NOT NULL,
    grade       CHAR(2),
    CONSTRAINT enrollment_pk PRIMARY KEY (student_id, section_id),
    CONSTRAINT enrollment_student_fk
        FOREIGN KEY (student_id) REFERENCES student(student_id)
        ON DELETE CASCADE,
    CONSTRAINT enrollment_section_fk
        FOREIGN KEY (section_id) REFERENCES course_section(section_id)
        ON DELETE RESTRICT
);

The composite primary key (student_id, section_id) is the bridge-table rule of Chapter 6 made syntax — it is the business rule "one enrollment per student per section." The named foreign keys carry the referential actions chosen in Section 6.7: a deleted student's enrollments cascade away; a section with enrollments cannot be deleted. Both platforms support all actions; the difference is the default — PostgreSQL's NO ACTION defers the check to statement end (allowing same-statement fixes), MySQL's RESTRICT-equivalent checks immediately. Also note ON UPDATE actions: changing a primary key value is rare by design (Chapter 6's stability rule), but ON UPDATE CASCADE exists for the times keys legitimately migrate.

9.5 NOT NULL, UNIQUE, CHECK, and DEFAULT

The four column-level constraints, each answering one modeling question:

• NOT NULL — is the value always known? full_name, total_credits yes; gpa, room, grade no, each with a stated absence meaning (Chapter 6's rule).
• UNIQUE — the natural identity, enforced: dept_name VARCHAR(50) NOT NULL UNIQUE. A UNIQUE constraint implicitly creates an index (Chapter 19) — free lookup speed with the correctness.
• CHECK — the domain rules: CHECK (gpa BETWEEN 0.00 AND 4.00), CHECK (credits BETWEEN 1 AND 6), CHECK (budget >= 0), CHECK (salary > 0), and the grade scale with its NULL escape:

CONSTRAINT valid_grade CHECK (
    grade IN ('A+','A','A-','B+','B','B-','C+','C','C-','D','F')
    OR grade IS NULL
)

• DEFAULT — the value inserted when the column is omitted: capacity SMALLINT DEFAULT 40, total_credits INTEGER NOT NULL DEFAULT 0. Defaults are not constraints — they do not reject anything — but they reduce NULL drift by giving "usually this" a home.

Two platform notes. PostgreSQL evaluates CHECK constraints on every insert and update (and on COPY); MySQL enforced CHECKs only from 8.0.16 — on older MySQL, CHECK parsed but was ignored, a fact that still burns migrations from legacy schemas. And NULLs pass CHECKs: three-valued logic (Chapter 2) means CHECK (gpa > 3.0) accepts a NULL gpa — the IS NULL OR ... idiom above is the deliberate, defensive spelling.

9.6 Modifying table structures using ALTER TABLE

Schemas evolve (Chapter 6's migration principle); ALTER TABLE is the evolve verb. The four everyday operations on our running schema:

ALTER TABLE student ADD COLUMN personal_email VARCHAR(120);

ALTER TABLE student ADD CONSTRAINT student_email_uniq UNIQUE (personal_email);

ALTER TABLE student ALTER COLUMN full_name TYPE VARCHAR(80);

ALTER TABLE student DROP COLUMN personal_email;

The pattern to internalize: one operation per statement — ALTER ... ADD COLUMN, ADD CONSTRAINT, DROP CONSTRAINT, RENAME COLUMN, each individually reversible, individually scripted. Type changes are the risky class: widening VARCHAR is safe (PostgreSQL rewrites the table anyway; MySQL 8.0.13+ does instant widening), narrowing or changing families requires a USING-cast or a two-step add-copy-drop, and both platforms lock or rewrite large tables — production changes belong in migration tools with downtime planning (Chapter 24), never in a hand-typed session.

Platform spellings differ on the type-change verb: PostgreSQL ALTER COLUMN ... TYPE ..., MySQL MODIFY COLUMN ... (Chapter 17 tabulates). One more verb exists specifically because constraints may outlive their welcome: ALTER TABLE student DROP CONSTRAINT student_email_uniq; — dropping by the name you gave it in Section 9.2. Unnamed constraints get system names, which is the practical reason for the naming convention of Section 9.2 in the first place.

9.7 Removing tables and constraints

Removal has a grammar of its own because dependencies refuse to vanish quietly:

• DROP TABLE enrollment; removes the table and its rows — refused while another table references it (none does), and in PostgreSQL it takes dependent views with a CASCADE variant that is almost never what you want.
• DROP TABLE department CASCADE; would drop the table and everything depending on it — students' majors, courses, sections — the nuclear option; the safe route is dropping children first, or better, DROP TABLE ... RESTRICT explicitly.
• DROP CONSTRAINT (inside ALTER TABLE, by name) removes one rule while the table lives on.
• TRUNCATE TABLE enrollment; empties a table fast — deallocating pages instead of deleting rows — but takes no WHERE, cannot trigger row-level triggers (both platforms fire TRUNCATE triggers separately), resets identity counters, and is still blocked by foreign keys referencing the table. DELETE FROM enrollment; is the row-by-row, trigger-firing, WHERE-able, transactional alternative (Chapter 10).

The dependency order is the whole game: the canonical schema must be dropped in reverse-FK order (enrollment, course_section, course, instructor, student, department) — the exact mirror of the creation order of Section 9.10.

9.8 Identity columns and auto-generated identifiers

Surrogate keys (Chapter 6) need a generator. The three spellings you will meet:

-- Standard SQL (and PostgreSQL 10+): identity columns
CREATE TABLE applicant (
    applicant_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ...
);

-- PostgreSQL legacy (still everywhere in older schemas): SERIAL
applicant_id SERIAL PRIMARY KEY,

-- MySQL: AUTO_INCREMENT
applicant_id INT AUTO_INCREMENT PRIMARY KEY,

GENERATED ALWAYS AS IDENTITY is the standard and the modern choice: ALWAYS forbids manual inserts of the key (use OVERRIDING SYSTEM VALUE when migrating); BY DEFAULT allows them. MySQL's AUTO_INCREMENT is the same idea with different plumbing — one counter per table, gaps allowed, and LAST_INSERT_ID() (PostgreSQL's equivalent is INSERT ... RETURNING applicant_id) for retrieving the generated value after an insert.

A deliberate note on the canonical schema: it uses explicit integer keys everywhere, no generators, so every expected output in this book is deterministic — inserting student 21100007 by hand produces the same rows on every run. Real registration systems would generate IDs; teaching systems benefit from stable ones, and the identity machinery is a five-line idea once you have Chapter 6's surrogate-key reasoning.

9.9 Creating indexes and views

Two derived structures complete DDL's toolkit — one for speed, one for shape.

Indexes (the deep treatment is Chapter 19) are created with one line and named like constraints:

CREATE INDEX enrollment_section_idx ON enrollment (section_id);
CREATE UNIQUE INDEX dept_name_uq    ON department (dept_name);

The first answers "who is enrolled in section 11?" without scanning all enrollments; the second shows that UNIQUE constraints are implemented as unique indexes — declared for correctness, rewarded with speed. Foreign-key columns are the classic index candidates: every FK check and every join probes them (Chapter 12's joins run at index speed because of indexes exactly like this one).

Views are stored queries — named, virtual tables (Chapter 4's external schema, implemented):

CREATE VIEW cse_student AS
SELECT student_id, full_name, gpa
FROM   student
WHERE  major_dept_id = 1;

SELECT full_name FROM cse_student WHERE gpa > 3.5;

  full_name
--------------
 Nusrat Jahan
 Tanvir Alam
 Arif Mahmud

The view is not a copy — it runs its stored query at reference time, so it is always current, and it provides logical data independence (Chapter 4): programs select from cse_student while the underlying schema evolves. Views also serve security (expose a view, hide the table — Chapter 20). Materialized views — stored result tables with refresh semantics — exist for analytics (Chapter 13/25); PostgreSQL has them natively, MySQL emulates them with tables plus triggers or events.

9.10 Implementing a relational schema from an ER diagram

The end-to-end workflow, exactly as you should execute it for every project from Chapter 29 onward:

1. Translate the ERD to DDL by dependency order — parents first. The university order: department → instructor, student, course → course_section → enrollment. A foreign key can only reference an existing table, so creation order is the reverse of drop order.
2. Declare every constraint the model carries — the full six-table DDL of Appendix H is exactly Sections 9.2–9.5 applied six times; load it with \i university_schema.sql or source university_schema.sql.
3. Load data in the same order — Appendix H's INSERT scripts follow the same dependency sequence, because enrollment cannot precede the students and sections it references.
4. Verify against expected counts — the counts that anchor every expected output in this book:

SELECT (SELECT COUNT(*) FROM department)     AS departments,
       (SELECT COUNT(*) FROM instructor)     AS instructors,
       (SELECT COUNT(*) FROM student)       AS students,
       (SELECT COUNT(*) FROM course)        AS courses,
       (SELECT COUNT(*) FROM course_section) AS sections,
       (SELECT COUNT(*) FROM enrollment)    AS enrollments;

 departments | instructors | students | courses | sections | enrollments
-------------+-------------+----------+---------+----------+------------
           5 |           6 |       12 |      10 |       13 |         28

1. Break it deliberately — attempt the three illegal inserts (duplicate PK, NULL in a NOT NULL, dangling FK) and read each error message by constraint name; a schema you cannot violate is a schema you understand.

The workflow's silent lesson is the book's whole design story compressed: the ERD of Chapter 5, the keys and actions of Chapter 6, the normalization of Chapter 7 — all of it arrives in six CREATE TABLE statements, and everything from Chapter 10 on simply queries the result.

Chapter Summary

• Databases are created per project (with charset/collation where the platform supports it), selected by client, and dropped with finality.
• CREATE TABLE column-by-column encodes the model: types as domains, NOT NULL for always-known, UNIQUE for natural identity, CHECK for rules, DEFAULT for usual values, and named constraints for evolvability.
• Choose exact NUMERIC for countables, VARCHAR/CHAR by rule, and platform extras (ENUM, TINYINT, TEXT) knowingly.
• Composite keys need table-level syntax; FKs carry named referential actions — CASCADE for ownership, RESTRICT for audit.
• ALTER TABLE evolves one operation per statement, with names making drops clean; type changes are the risky class.
• Removal respects dependency order: drop children first, CASCADE reserved for deliberate demolition; TRUNCATE is fast, WHERE-less, and FK-guarded.
• Identity/SERIAL/AUTO_INCREMENT generate surrogate keys; the canonical schema uses explicit keys for deterministic outputs.
• Indexes buy speed (UNIQUE doubles as one); views are stored queries delivering logical independence and security; materialized views come in Chapter 13.
• The ER-to-DDL workflow is: parents first, all constraints, load in order, verify counts, then try to break it.

Key Terms

Laboratory Exercises

1. Create university_dev, load Appendix H's schema and data into it, and run the six-count verification of Section 9.10. Expected result: 5, 6, 12, 10, 13, 28 — identical to the canonical database.
2. Write and run the four ALTERs of Section 9.6 against university_dev, photograph (or paste) each error-free completion, then revert them in reverse order. Confirm the student table matches the canonical columns again with \d student. Expected result: four successful ALTERs, then a student table with the original six columns.
3. Attempt the three illegal inserts against the canonical university database: a duplicate student_id, a NULL full_name, and an enrollment for student 99999. Record each error message and the constraint named in it. Expected results: duplicate key / not-null / foreign-key violations, each naming the violated constraint.
4. Drop the schema in university_dev in the wrong order (department first) and capture the error; then drop in correct order and confirm with \dt. Expected result: the wrong-order drop fails with a dependency error; the correct order leaves zero tables.
5. Create an identity-keyed applicant table (Section 9.8 spelling per platform), insert three rows without keys, and retrieve the generated keys with RETURNING (PostgreSQL) or LAST_INSERT_ID() (MySQL). Expected results: three sequential ids (1, 2, 3 or any gapless run) returned per platform mechanism.
6. Create the cse_student view of Section 9.9 and a parallel economics-style view for department 2 (EEE majors); query each, then create a UNION over both and compare the row counts with a direct query on student. Expected results: 4 rows (CSE), 3 rows (EEE), 7 rows unioned — matching a WHERE major_dept_id IN (1, 2) query.

Review Questions and Exercises

1. Why does the creation order of the canonical schema put department first, and what is the drop order? Foreign keys require referenced tables to exist — parents first; drop order is the reverse: enrollment first, department last.
2. Distinguish CHAR(2) from VARCHAR(2) with the grade column as the example. CHAR pads to fixed length and can be marginally cheaper for truly fixed-length values like 'A-'/'B+'; VARCHAR stores actual length — both hold our grades; the canonical choice is CHAR(2).
3. Why must budget be NUMERIC(12,2) rather than REAL, and what does the (12,2) mean? Money and aggregates need exact decimal arithmetic; REAL accumulates representation error; 12 total digits, 2 after the decimal point — up to 9999999999.99.
4. Give the two syntaxes for declaring enrollment's key, and state which one is required here. Inline (single-column) vs. table-level; the composite (student_id, section_id) requires table-level.
5. What do the enrollment foreign keys' ON DELETE actions encode, and why the difference between them? CASCADE on student (enrollments are owned by the student's record), RESTRICT on section (grades/audit — resolve enrollments before deleting a section).
6. Why does a CHECK constraint pass a NULL, and how does the grade constraint handle that? CHECK fails only on FALSE — NULL yields UNKNOWN, which passes; the constraint spells the domain as IN (...) OR grade IS NULL.
7. Name the risk class of ALTERs and the two-step pattern for a dangerous one. Type changes (narrowing, family changes) — add a new column, copy with cast, drop the old, rename.
8. Why does TRUNCATE refuse to run on a table referenced by foreign keys, while DELETE does not? TRUNCATE bypasses row-by-row machinery, so the server cannot verify per-row references cheaply — it refuses wholesale; DELETE walks rows and fires constraints/triggers normally.
9. Contrast GENERATED ALWAYS AS IDENTITY, SERIAL, and AUTO_INCREMENT in one sentence each. Standard, named, controllable identity columns; PostgreSQL's older integer-sequence shorthand; MySQL's per-table counter with LAST_INSERT_ID retrieval.
10. What does the canonical schema gain by avoiding generated keys, and what do real systems gain by using them? Deterministic, reproducible expected outputs for teaching; stable opaque identifiers that never encode meaning and are generated safely under concurrency.
11. Write the DDL for a dept_phone table (multivalued attribute repair) with a composite key and a foreign key with a sensible ON DELETE action.

    CREATE TABLE dept_phone (
        dept_id INTEGER NOT NULL REFERENCES department(dept_id)
                            ON DELETE CASCADE,
        phone   VARCHAR(20) NOT NULL,
        PRIMARY KEY (dept_id, phone)
    );

12. A teammate says views are "just saved queries, so they add nothing." Refute with two uses from this chapter. Logical data independence (programs target the view while the base schema evolves) and security (grant the view, hide the table and its columns).

Mini-Project

Implement the complete movie-collection schema you designed in Chapters 5–6 (or the waitlist/meetings/prerequisites extension of the Chapter 6 mini-project) as a deliverable schema.sql. Requirements: a comment header; tables in dependency order; every constraint named; every domain rule (year ranges, positive quantities, allowed ratings) as a CHECK with NULL escapes where absence is legal; surrogate keys via your platform's identity spelling where the design chose surrogates, with UNIQUE natural keys preserved; at least one index beyond the keys with a one-line justification; one view that a user-facing program would query. Then write load_and_break.sql: sample data in dependency order, the six-count verification adapted to your tables, and five deliberately illegal statements, each with a comment predicting the constraint that will reject it. Run both scripts on PostgreSQL and on MySQL, and file the run logs — this pair of scripts is the template for every case study in Chapter 29.