Practice & Assessment
Test your understanding of Schema Design and Constraints
Multiple Choice Questions
5You delete a parent row in a table with a FK relationship using ON DELETE CASCADE. What happens to child rows?
Which normal form violation is described: a table has columns (order_id, product_id, qty, product_name) with PK (order_id, product_id), and product_name depends only on product_id?
You need to enforce email uniqueness only for users who have NOT been soft-deleted (deleted_at IS NULL). What is the correct approach?
After importing 10,000 rows with explicit IDs into a table using GENERATED BY DEFAULT AS IDENTITY, the next INSERT fails with a duplicate key error. What is the fix?
What is the default schema in a new PostgreSQL database, and where are objects placed if no schema is specified?
Coding Challenges
1Normalize a Flat Orders Table
You are given a denormalized flat table: flat_orders (order_id, customer_name, customer_email, product_name, product_category, product_price, qty, order_date, order_status). Redesign it into at least 3 normalized tables (customers, products, orders/order_items). Write the full CREATE TABLE statements with appropriate data types, PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK constraints. Then write INSERT statements to migrate sample data. Include FK indexes. Time estimate: 30 minutes.
Mini Project
Multi-Schema SaaS Database
Design a schema for a multi-tenant SaaS application. Create a shared schema called platform with tables: tenants (id, name, subdomain UNIQUE, created_at), plans (id, name, price_monthly NUMERIC CHECK > 0, max_users INT). Create a per-tenant schema called tenant_template containing: users (id, email UNIQUE, role TEXT CHECK IN ('admin','member','viewer'), created_at), projects (id, name, owner_id FK to users, archived BOOLEAN DEFAULT false), tasks (id, project_id FK, title, status CHECK IN ('todo','in_progress','done'), due_date DATE). Add all foreign keys with appropriate ON DELETE behavior. Add a partial unique index to enforce no duplicate project names per tenant that are not archived. Write a script that creates the tenant_template schema and all its tables, then clones it for a new tenant by running CREATE SCHEMA tenant_1 LIKE tenant_template (simulate with explicit CREATE TABLE statements). Document each constraint choice in comments.