A2 Work Session & Review

Goals

• Complete and finalize the createdb_script.sql
• Insert realistic sample data (at least 3 rows per table) using subqueries for FK values
• Verify the script is fully re-runnable (runs cleanly twice in a row)
• Peer review: test a classmate's script for correctness and re-runability
• Submit the completed assignment

Session Structure

Final Checklist Before Submitting

Go through this checklist before submitting:

• All tables use CREATE TABLE IF NOT EXISTS
• At least 5 CHECK constraints are present and correct
• At least 5 ALTER TABLE statements, each with an explanatory comment
• TRUNCATE ... CASCADE statements present before all INSERTs, in correct order
• At least 3 realistic rows per table (no 'aaa', 'test', '123')
• At least one INSERT uses a subquery to resolve a FK value
• Script runs without errors from scratch
• Script runs a second time without duplicates or errors
• NEW_SCHEMA.img is updated to match your final schema
• All files are in the TASK 2 Creating DB folder

Peer Review Protocol

When reviewing a classmate's script:

1. Run it fresh — execute against a clean database (or after dropping and recreating)
2. Run it again — verify no errors or duplicates on the second run
3. Check constraints — verify 5 CHECK constraints are present and sensible
4. Check ALTER TABLE — verify 5+ operations with comments
5. Check data quality — is the sample data realistic and domain-appropriate?
6. Report findings — tell your partner what works and what needs fixing

Common Final Fixes

• Missing CASCADE on TRUNCATE — if truncating a parent table fails, add CASCADE
• Duplicate data on re-run — your TRUNCATE must be in the correct order to clear all dependent tables first
• Hardcoded FK IDs — replace with subqueries before submitting
• Generic data — 'Name1', 'Value1' will lose points; replace with domain-specific realistic values