Turn the ERD into CREATE TABLE

Part 1 of 2

Translate a requirement into a constraint

A table definition carries requirements: PRIMARY KEY identifies a record, NOT NULL requires a value, UNIQUE prevents repetition, CHECK constrains a value and DEFAULT supplies an omitted value.

Create parent tables before child tables. Enable PRAGMA foreign_keys = ON in a disposable SQLite database when testing FK rejection. A schema that loads still needs a data dictionary and tests.

Worked example

CREATE TABLE customers (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL, phone TEXT);

Pause and explain. Why can phone be NULL while email cannot? What does UNIQUE add beyond NOT NULL?

Try this task

Part 2 of 2

Create the child table

Assume customers(id) exists. Each booking has a date, exactly one customer, and a total that cannot be negative. Booking status defaults to pending.

Test one valid insert and one invalid input in a fresh copy after comparing your schema. The result check compares the table and constraints; it does not assess your diagram.

Worked example

CREATE TABLE bookings (id INTEGER PRIMARY KEY, booking_date TEXT NOT NULL, customer_id INTEGER NOT NULL REFERENCES customers(id), total REAL NOT NULL CHECK (total >= 0), status TEXT DEFAULT 'pending');

Pause and explain. Explain the FK’s side, requiredness and the difference between CHECK and DEFAULT.

Try this task