LEARN WITH ABDOU / DATABASE TEAM LAB
Coffee: model the facts
Read the schema, test relationships, and sketch a design you can explain.
Team: __________________________ Date: ______________
Dataset: Northshore Coffee · Worksheet version: 2026-09-09
Open this exact lab: https://learnwithabdou.com/courses/mis3220/labs/coffee-design
Practice companion. Confirm official assignment and submission instructions in Canvas. Rotate query writer, evidence checker, and explainer. Record SQL, evidence, and reasoning.
Before class
P1. Read and predict
Read the matching module. Name one concept you will use and predict the output grain of its example.
P2. Inspect the database
Open Quick schema and preview the relevant table. Record its key and a field whose meaning needs explanation.
P3. Prepare your check
Write one small query that could expose an incorrect answer to this lab. Explain what you would look for.
Database schema
employees · 25 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| first_name | TEXT | NOT NULL |
| last_name | TEXT | NOT NULL |
| TEXT | ||
| phone | TEXT | |
| shop_id | INTEGER | |
| position | TEXT | |
| hire_date | TEXT | |
| hourly_wage | REAL |
shop_id → shops.id
menu_items · 45 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | NOT NULL |
| category | TEXT | NOT NULL |
| size | TEXT | |
| price | REAL | NOT NULL |
| calories | INTEGER | |
| is_seasonal | INTEGER | |
| description | TEXT |
orders · 204 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| shop_id | INTEGER | |
| employee_id | INTEGER | |
| order_date | TEXT | |
| total_amount | REAL | |
| payment_method | TEXT | |
| tip_amount | REAL |
employee_id → employees.id
shop_id → shops.id
shops · 8 rows
| Column | Type | Key / required |
|---|---|---|
| id | INTEGER | PK |
| name | TEXT | NOT NULL |
| city | TEXT | NOT NULL |
| address | TEXT | |
| phone | TEXT | |
| open_date | TEXT | |
| has_drive_thru | INTEGER |
Types and declared keys reflect the original teaching database. Check business meanings and logical relationships in the lecture.
Team investigation
1. Identify entities and keys
Return id, first_name, last_name, and shop_id for the first five employees ordered by id. Which field identifies the employee? Which connects that employee to a shop?
SQL / evidence / explanation:
2. Test one-to-many relationships
Return shop_id and COUNT(*) AS order_count from orders, grouped and ordered by shop_id. Why would storing every order as a new column on shops make the design harder to maintain?
SQL / evidence / explanation:
3. Write a data dictionary and name uncertainty
Return id and name for menu items with unknown calories, ordered by id. Write a dictionary entry for calories: meaning, unit, and what NULL means. What remains unverified?
SQL / evidence / explanation:
4. Sketch and challenge an ERD
Sketch shops, employees, and orders with their keys and one-to-many relationships. Could an order reference a shop different from its employee’s shop? Inspect the data and distinguish a business rule from a declared relationship.
SQL / evidence / explanation:
5. Design for change
A designer stores shop_name on every employee row instead of using shop_id. Describe an update anomaly, propose a design, and name one tradeoff.
SQL / evidence / explanation:
Reflect and submit
Choose your strongest answer and one revision. Explain the evidence behind each. Export your SQL and notes, then follow Canvas for the assigned report, grading, and submission requirements.