Practice & Assessment
Test your understanding of Schema Design and Constraints
Multiple Choice Questions
5A products table has a tags column storing comma-separated values like 'sql,backend,database'. Which normal form does this violate?
You try to delete a customer who has existing orders. The foreign key on orders.customer_id uses ON DELETE RESTRICT. What happens?
A query on orders filters by status = 'completed' WHERE clause. The status column contains 'completed' for 95% of rows. Should you add an index on status?
In a users table, you want to ensure no two users can have the same email AND no two users can have the same username, but email-username combinations can repeat. Which constraints achieve this?
A student-courses many-to-many junction table uses a composite PRIMARY KEY (student_id, course_id). What does this prevent?
Coding Challenges
1Schema Design and Constraint Implementation
Design a normalized schema for a blog platform with these requirements: authors can write many posts, posts can have many tags, a post can only have each tag once, each post has exactly one author, comments belong to posts and optionally to users (guests can comment). Create all tables with appropriate data types, primary keys, foreign keys with correct ON DELETE behavior (deleting a post deletes its comments and tag associations; deleting an author is blocked if they have posts), UNIQUE constraint on author email, CHECK constraint ensuring post published_at is not before created_at, and an index on posts.author_id. Expected output: valid DDL that executes without errors. Time estimate: 25 minutes.
Mini Project
Inventory Management Schema
Build a complete schema for an inventory management system. Tables required: suppliers (id, name, contact_email, country), products (id, supplier_id, sku, name, description, unit_price, reorder_threshold), warehouses (id, location, capacity), inventory (product_id, warehouse_id, quantity, last_updated), purchase_orders (id, supplier_id, created_at, status), purchase_order_items (id, po_id, product_id, quantity, unit_cost). Requirements: all foreign keys with appropriate ON DELETE behavior; UNIQUE on products.sku; CHECK that inventory.quantity >= 0; CHECK that purchase_order_items.unit_cost > 0; composite UNIQUE on (product_id, warehouse_id) in inventory; indexes on all foreign key columns. After creating the schema, write a query that shows each product, its total stock across all warehouses, and a flag 'reorder_needed' when total stock is below reorder_threshold.