2.1 · Data Structure and Data Models
[markdown] content available only on the platform
[markdown] content available only on the platform
Flashcards · Data Structure and Data Models
Primary key — A field whose value uniquely identifies each record in a table.
Row ID in the Orders table.
Foreign key — A field in one table that refers to the primary key of another table.
Customer ID in Orders links to Customer.
Level of detail (grain) — What a single record in a table represents; it determines how measures may be aggregated.
Orders: one row per product line of an order.
One-to-many relationship — One record in a table links to many records in another table.
One customer, many order lines.
Many-to-many relationship — Many records link to many records in both directions, often needing a bridge table.
Products and promotions.
Left join — Keeps all records of the left table and matching records from the right; non-matches get NULL.
Customer left join Orders finds customers who never ordered.
Union — Appends records of tables with compatible structures row by row.
Orders 2023–2024 stacked with Orders 2025.
Pivot — Restructures data between wide format (values in columns) and long format (values in rows).
Twelve month columns become Month and Sales Target fields.
Double counting — Inflated totals caused by duplicated rows after joining on a non-unique key or mismatched grain.
Monthly target repeated on every order line.
[pdf] Lecture slides — 2.1 Data Structures and Data Models (PDF) — /api/lesson-media/pdfs/4d3653ff-a3ca-4f63-bf8e-bb93c553e599-1794914287821-1160f13d72edede4.pdf
