A2 Given & Rerunable Review

🎯Learning Objectives

• Understand what makes a SQL script "re-runnable" (idempotent)
• Know how to use CREATE TABLE IF NOT EXISTS
• Know how to use TRUNCATE before INSERT statements
• Understand the Assignment 2 requirements
• Plan the submission structure

📖Theory

1. What Is a Re-Runnable Script?

A re-runnable (idempotent) script can be executed multiple times against the same database without producing errors, duplicates, or incorrect results. This is a professional requirement for database scripts.

A common mistake is using DROP TABLE before CREATE TABLE:

-- BAD: This destroys data and breaks dependencies
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (...);

The correct approach:

• Use CREATE TABLE IF NOT EXISTS — the table is created only if it doesn't exist
• Use TRUNCATE before INSERT — clears data without destroying the schema

-- GOOD: Table is only created on first run
CREATE TABLE IF NOT EXISTS orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE DEFAULT CURRENT_DATE
);

2. TRUNCATE — Resetting Data Safely

TRUNCATE instantly removes all rows from a table but preserves columns, constraints, and indexes. It is faster than DELETE FROM and is the correct tool for resetting data before re-inserting.

-- Basic truncate
TRUNCATE TABLE orders;

-- With foreign keys — truncate child tables first, or use CASCADE
TRUNCATE TABLE order_items CASCADE;

-- Multiple tables at once
TRUNCATE TABLE order_items, orders, customers;

-- Correct order: children first
TRUNCATE TABLE order_items CASCADE;
TRUNCATE TABLE orders CASCADE;
TRUNCATE TABLE customers CASCADE;

-- Then insert parents first
INSERT INTO customers ...
INSERT INTO orders ...
INSERT INTO order_items ...

3. Resolving Foreign Keys Without Hardcoding

Never hardcode FK values like customer_id = 5. Instead, use subqueries to look up IDs dynamically:

-- BAD: hardcoded FK
INSERT INTO orders (customer_id, order_date)
VALUES (5, '2026-02-01');

-- GOOD: resolved dynamically
INSERT INTO orders (customer_id, order_date)
VALUES (
    (SELECT customer_id FROM customers WHERE email = 'alice@example.com'),
    '2026-02-01'
);

4. GENERATED ALWAYS AS and DEFAULT Columns

Use computed columns for values derived from other columns, and DEFAULT for sensible initial values:

CREATE TABLE IF NOT EXISTS invoice_lines (
    line_id     SERIAL PRIMARY KEY,
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10,2) NOT NULL,
    total_price NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

5. ALTER TABLE — Correcting Your Schema

After writing CREATE TABLE statements, use ALTER TABLE to demonstrate corrections:

-- phone_number was too short for international numbers
ALTER TABLE customers
    ALTER COLUMN phone_number TYPE VARCHAR(20);

-- forgot to add the email uniqueness constraint
ALTER TABLE customers
    ADD CONSTRAINT uq_customers_email UNIQUE (email);

-- decided the 'notes' column is not needed
ALTER TABLE customers
    DROP COLUMN notes;

Always add a comment above each ALTER explaining the reason.

Assignment 2 — Creating a Physical Database

Task Description

Create a physical database based on your relational model from Assignment 1.

1. Create a database and schema with a meaningful domain-related name.

2. Use your Assignment 1 model — all tables must be based on your Assignment 1 relational model.

3. Ensure 3NF — your database must remain in 3rd Normal Form.

4. Data types, DEFAULT, and GENERATED columns

Use appropriate data types. Apply DEFAULT values and GENERATED ALWAYS AS computed columns where appropriate:

-- Computed column
total_price NUMERIC(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,

-- Default value
status VARCHAR(20) DEFAULT 'active'

5. Primary and foreign keys — define all relationships using PKs and FKs.

6. Five CHECK constraints including:

• A date that must be after January 1, 2026
• A measured value that cannot be negative
• A value restricted to specific options (e.g., gender: 'M', 'F', 'Other')
• A UNIQUE constraint
• A NOT NULL constraint

7. ALTER TABLE — at least 5 operations

After all CREATE TABLE statements, add at least 5 ALTER TABLE operations with explanatory comments:

-- phone_number was VARCHAR(15) but international numbers can be longer
ALTER TABLE customers ALTER COLUMN phone_number TYPE VARCHAR(20);

-- forgot to add unique constraint on email during table creation
ALTER TABLE customers ADD CONSTRAINT uq_email UNIQUE (email);

-- removing unused column added by mistake
ALTER TABLE customers DROP COLUMN middle_name;

Operations to choose from: ADD COLUMN, DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, DROP CONSTRAINT, RENAME COLUMN, SET DEFAULT, DROP DEFAULT.

8. Re-runnable script — IF NOT EXISTS + TRUNCATE

• Use CREATE TABLE IF NOT EXISTS for all tables
• Use TRUNCATE ... CASCADE before all INSERTs, in correct FK order

9. Sample data — at least 3 realistic rows per table:

• Data must be domain-relevant (no 'aaa', 'test123')
• All constraints must be satisfied
• Use subqueries instead of hardcoded FK IDs where possible

Coding rules:

• Code must execute without errors
• Code must not produce duplicates
• Code must be re-runnable
• Add comments explaining type, constraint, and ALTER choices
• Attach updated schema image

Submission:

Folder: PO_RO_1.5_Yourname_Group → TASK 2 Creating DB

Files:

• createdb_script.sql
• NEW_SCHEMA.img

Grading Criteria

⚠️Common Mistakes

• Using DROP TABLE instead of IF NOT EXISTS — destroys the schema and breaks FK dependencies; never use DROP TABLE in your submission script.
• Hardcoding FK IDs — INSERT INTO orders VALUES (5, ...) breaks on a different database; always use subqueries to look up reference values.
• Wrong TRUNCATE order — truncating a parent table while child rows still exist causes FK errors; always truncate children first or use CASCADE.
• No comments on ALTER TABLE — every ALTER must have a comment explaining why the change was needed.
• Unrealistic test data — 'aaa', 'test', '123' will cost points; use domain-appropriate names and values.

🎓Instructor Notes

Live demo suggestion: use a simple domain (e.g., a library with books, authors, book_authors junction table) to demonstrate all concepts in 15 minutes. Show the re-run: first run creates and populates, second run truncates and repopulates cleanly.