Defense A2

Assignment Reference

Assignment 2 — Creating a Physical Database

Create a physical database using the relational model from Assignment 1.

Key deliverables:

• Separate database and schema with meaningful domain-related name
• All tables from Assignment 1, using CREATE TABLE IF NOT EXISTS
• Correct data types, DEFAULT values, and GENERATED ALWAYS AS computed columns
• Primary and foreign keys matching the Assignment 1 model
• Five CHECK constraints (date validation, non-negative value, restricted options, UNIQUE, NOT NULL)
• At least 5 ALTER TABLE operations, each with an explanatory comment
• TRUNCATE used before all INSERT statements, in correct FK order
• At least 3 realistic rows per table; FK values resolved via subqueries
• SQL comments explaining type, constraint, and ALTER choices

Submission folder: TASK 2 Creating DB inside PO_RO_1.5_Yourname_Group

Files: createdb_script.sql and NEW_SCHEMA.img

Grading Rubric

Verification Questions

The instructor may ask any of the following during the defense:

1. Run your script live from scratch on the instructor's machine — it must execute without errors.
2. Why did you choose this schema name? How does it relate to your subject area?
3. Explain CREATE TABLE IF NOT EXISTS — what happens if you run the script a second time?
4. Show me the GENERATED ALWAYS AS column. What does it calculate and why is that useful?
5. Walk me through one of your CHECK constraints. What invalid data would it reject?
6. Explain one of your ALTER TABLE operations. What mistake were you correcting?
7. What does TRUNCATE do? Why did you use it instead of DELETE?
8. Show me an INSERT that uses a subquery instead of a hardcoded ID. Why is this important?
9. What is the FK dependency order in your script? Which tables must be inserted first?
10. Run the script a second time — does it produce duplicates? How did you prevent them?
11. Show me the NEW_SCHEMA.img. Point to a relationship that changed between Assignment 1 and Assignment 2.

Submission Checklist

Students must have the following ready before the defense begins:

• TASK 2 Creating DB folder is accessible and open
• createdb_script.sql file is ready to run on a clean database
• NEW_SCHEMA.img is visible and legible
• Script runs without errors (tested in advance)
• Script can be run a second time without producing duplicates or errors
• All 5 CHECK constraints are present and explained
• All 5 ALTER TABLE operations are present with comments
• At least one INSERT uses a subquery for FK resolution
• TRUNCATE statements are present before all INSERTs
• Student can explain every design decision without relying on notes