Student starter code (30% baseline)
index.html- Main HTML pagescript.js- JavaScript logicstyles.css- Styling and layoutpackage.json- Dependenciessetup.sh- Setup scriptREADME.md- Instructions (below)💡 Download the ZIP, extract it, and follow the instructions below to get started!
Master entity-relationship modeling through hands-on database design for a food delivery system. Learn how to translate business requirements into ER diagrams, then implement them with proper normalization.
By completing this activity, you will:
IMPORTANT: This template includes WORKING CODE! You can see results immediately:
Download SQLite Browser (if you don't have it):
Open the database files:
foodrush.dbsqlite/v1_denormalized.sql -> Click "Execute" ▶️sqlite/v3_normalized.sql -> Click "Execute" ▶️Explore the ER diagram evolution:
er-diagrams/foodrush-v1-denormalized.txt (text-based diagram)er-diagrams/foodrush-v3-normalized.txt# Make sure you're in the activity folder, then:
docker-compose up -d
# Access PostgreSQL:
docker exec -it w4-er-postgres psql -U postgres foodrush
# Load and compare schemas:
\i /docker-entrypoint-initdb.d/v1_denormalized.sql
\dt # See single table
\i /docker-entrypoint-initdb.d/v3_normalized.sql
\dt # See properly normalized tables
v1_denormalized.sql and executev3_normalized.sql and execute70% of the code is implemented for you:
Location: sqlite/exercises.sql line 15
Your Task: Add menu items to the FoodRush database
Business Requirements:
Success Criteria:
menu_items table with proper foreign key to restaurantsHints:
restaurant_id as foreign keyER Diagram Notation:
RESTAURANT (1) -------- (M) MENU_ITEM
Why This Matters: One-to-many relationships are the most common database pattern. Master this, and you'll handle 80% of real-world scenarios.
Location: sqlite/exercises.sql line 45
Your Task: Model delivery drivers who can fulfill multiple orders
Business Requirements:
Success Criteria:
drivers table with driver informationdriver_id foreign key to orders tabledeliveries table (junction pattern)Hints:
driver_id to orders (one-to-many)deliveries junction table (tracks delivery status)ER Diagram Options:
# Simple:
DRIVER (1) -------- (M) ORDER
# Advanced (with delivery tracking):
DRIVER (1) -------- (M) DELIVERY (M) -------- (1) ORDER
Why This Matters: Many-to-many relationships model complex real-world scenarios. Understanding junction tables unlocks advanced database design.
Location: sqlite/exercises.sql line 75
Your Task: Break down orders into individual items
Business Requirements:
Success Criteria:
order_items junction tableorders and menu_itemsorders.total_amountHints:
order_id, menu_item_id, quantity, price_at_orderSUM(quantity * price_at_order) to calculate totalsER Diagram:
ORDER (1) -------- (M) ORDER_ITEM (M) -------- (1) MENU_ITEM
Expected Output: Query like:
Order #1 Total: $45.99
- Margherita Pizza x2 @ $12.99 = $25.98
- Caesar Salad x1 @ $8.99 = $8.99
- Tiramisu x1 @ $6.99 = $6.99
- Delivery Fee: $4.03
Why This Matters: This is e-commerce 101. Every shopping cart, invoice system, and order management platform uses this exact pattern.
| Version | Tables | Relationships | Normalization | Problems |
|---|---|---|---|---|
| v1 | 1 (order_details) | None (all data in one table) | 0NF | Data duplication, update anomalies |
| v2 | 3 (customers, restaurants, orders) | 2 foreign keys | 1NF | Still has some redundancy |
| v3 | 3 (optimized) | 2 foreign keys + constraints | 3NF | Clean, no redundancy |
v1: Denormalized (The Problems)
┌─────────────────────────────────────────────┐
│ ORDER_DETAILS (Single Table) │
│─────────────────────────────────────────────│
│ order_id, order_date, order_status │
│ customer_id, customer_name, customer_email │ ← Customer data
│ customer_phone, customer_address │ REPEATED!
│ restaurant_id, restaurant_name, cuisine │ ← Restaurant data
│ restaurant_address, restaurant_phone │ REPEATED!
│ total_amount │
└─────────────────────────────────────────────┘
Problems:
❌ Customer "Alice" appears in 10 orders → name stored 10 times
❌ Update Alice's phone → must update 10 rows
❌ Delete all orders → lose customer information
v3: Normalized (The Solution)
┌──────────────┐ ┌──────────────┐
│ CUSTOMERS │ │ RESTAURANTS │
│──────────────│ │──────────────│
│ customer_id │ │restaurant_id │
│ name │ │ name │
│ email │ │ cuisine_type │
│ phone │ │ address │
│ address │ │ phone │
└──────┬───────┘ └──────┬───────┘
│ │
│(1) (1) │
│ │
│(M) (M) │
└───────┬───────────┬──────┘
│ ORDERS │
│───────────│
│ order_id │
│customer_id│ ← Foreign Key
│restaurant_id│ ← Foreign Key
│ order_date│
│total_amount│
│ status │
└───────────┘
Benefits:
✅ Each customer stored once → easy updates
✅ Delete orders → keep customer data
✅ Add new customer → no orders needed
✅ Referential integrity enforced
Schema Validation:
Query Validation:
Normalization Check:
Entity Design:
Relationship Design:
Ready for more? Try these bonus features:
Your project is complete when:
Once you complete this activity, you'll have:
This skill is the foundation of database design-use it to plan every database project before writing a single line of SQL!
Need Help?
Ready to Submit? Complete this activity and submit through the Activity Submission Form
Happy modeling! 🗄️✨