LECTURES / MODULE 4
Understand database structure
Read schemas, identify keys, and model relationships before combining or changing facts. Lab 13 adds a normalization workshop covering dependencies, anomalies, seven-table migration and reconciliation.
1 / 3
Identify entities and keys
A table groups facts about a type of entity or event. In this database one shops row represents a shop, one employees row an employee, and one orders row an order.
A primary key identifies a row. A name is often a poor key because names can repeat or change. Keep the identifier and the human-readable label separate.
An ERD should label the entities, keys, and relationship cardinalities. The database explorer shows declared keys; business rules require explanation beyond the diagram.
SELECT id, name, city FROM shops ORDER BY id;
The shop id is the stable row identifier, while name and city make the result understandable. Several shops can share a city without representing the same shop.
The employee exercise deliberately includes shop_id beside employee id. Identifiers with the same numeric value are not interchangeable when they refer to different tables.
Explore this concept →Explain it in your own words
Which field identifies the employee? Which connects that employee to a shop?
2 / 3
Test one-to-many relationships
A shop can have many employees, while each employee row stores one shop_id. The foreign-key column belongs on the many side of this relationship.
The data can show that multiple employees share a shop today. It cannot by itself prove every business rule for future data. Distinguish an observed pattern from an enforced constraint.
Use a small count to challenge your proposed cardinality before writing a larger JOIN.
SELECT shop_id, COUNT(*) AS employees FROM employees GROUP BY shop_id ORDER BY shop_id;
Grouping employees by shop_id counts the employee rows assigned to each shop represented in employees. It does not guarantee that shops with zero employees appear.
To include every shop, begin with shops and use a LEFT JOIN when you reach the relationships module. The table chosen as the starting point changes which entities can disappear.
Explore this concept →Explain it in your own words
Why would storing every order as a new column on shops make the design harder to maintain?
3 / 3
Write a data dictionary and name uncertainty
A useful data dictionary explains a field’s meaning, units, allowed values, and missing-value policy—not just its SQL type. total_amount needs a currency and a definition of what is included.
Type declarations alone do not prove data quality. SQLite uses type affinity unless stricter features or constraints are in play. Requiredness and allowed ranges need explicit rules.
Document an assumption rather than silently converting it into a fact. A NULL calorie value and a zero-calorie item should remain distinguishable.
SELECT id, name, calories FROM menu_items WHERE calories IS NULL ORDER BY id;
IS NULL identifies genuinely missing calorie values. It does not match rows whose calorie value is zero.
The result tells you which records need attention, not why the value is missing. Keep the reason for missingness as an open question unless the source documents it.
Explore this concept →Explain it in your own words
Write a dictionary entry for calories: meaning, unit, and what NULL means. What remains unverified?
SQL reference & course materials
Use these reference chapters alongside the detailed lessons above. Each example identifies its database.
Put it to work in class
Lab 6: Meet Your Client (ERD) →
Lab 13: The Inherited Schema →
Readiness practice and team activities complement the assigned Canvas lab. Official instructions and submission requirements stay in Canvas.