Chapter 5 — Databases and tables
Lesson map
Lesson 05 published. Map, example, EX013.
flowchart TD
N0["Learning outcomes"]
N1["5.1 Tables and keys"]
N2["5.2 Queries"]
N3["5.3 Design and quality"]
N4["5.4 Normalisation in brief"]
N0 --> N1 --> N2 --> N3 --> N4
N4 --> EX["Worked example"]
EX --> Q["Exercise EX013"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["5.1 Tables and keys"]
N2["5.2 Queries"]
N3["5.3 Design and quality"]
N4["5.4 Normalisation in brief"]
N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX013"]
[Learning outcomes]
|
v
[5.1 Tables and keys]
|
v
[5.2 Queries]
|
v
[5.3 Design and quality]
|
v
[5.4 Normalisation in brief]
|
v
[Worked example] --> [Exercise EX013]
Learning outcomes
You will (1) describe tables, keys and relationships; (2) write simple queries; (3) explain how database design supports data quality.
5.1 Tables and keys
Amara learns that a relational database stores data in tables of rows and columns. The member table has one row per loyalty member, identified by a member number that serves as the primary key. The purchase table records each purchase with the member number as a foreign key linking it to the member. Keys prevent duplicate members and ensure purchases refer to members who exist. She sees that design choices like keys enforce rules automatically. The database rejects a purchase with an unknown member number.
5.2 Queries
Using a small synthetic database built with Python's sqlite3 module, Amara writes queries to count members by store, total purchases by month and find members without purchases in a year. She learns that a query is a precise question, and that wording matters: counting purchases and counting distinct members who purchased give different answers. She records each query with a plain description so that others can check what was asked and reuse it later. Her descriptions are stored in a shared file with the query text beside them.
5.3 Design and quality
Amara finds that Harbourline's old loyalty database stored the member's store as free text, producing variations such as Store 12, store twelve and S12. Reports grouped by store split one store into several. A better design stores a store code linked to a store reference table. She explains that good design prevents many quality problems at entry, which is cheaper than cleaning them later, and connects this to rules covered in later chapters. The new loyalty system uses store codes, and the old text values are mapped once.
5.4 Normalisation in brief
Dr. Lindqvist introduces normalisation: organising tables so each fact is stored once. In the old database, a member's address appears in every purchase row, so changing an address leaves older rows inconsistent. Moving the address to the member table fixes this. Amara notes that normalisation reduces inconsistency but can make queries more complex, and that reporting databases sometimes deliberately repeat data for speed. The right balance depends on how data are used. Harbourline's reporting database repeats store names in sales tables for this reason, by design.
Worked example
Amara learns keys and relationships, writes sqlite3 queries with plain descriptions, finds free-text store names splitting reports and sees normalisation remove repeated addresses.
Synthetic case. The database is synthetic.
Errors and limits
Counting rows when the question asks for distinct people gives wrong answers.
Exercises
- EX013 — Foreign key role. What does the member number in purchases link to? Rubric: 3 = the member table;; 2 = partly correct;; 1 = vague;; 0 = none.
- EX014 — Free-text problem. What did free-text store names cause? Rubric: 3 = one store split into several in reports;; 2 = partly correct;; 1 = vague;; 0 = none.
- EX015 — Normalisation trade-off. What can normalisation make harder? Rubric: 3 = queries;; 2 = partly correct;; 1 = vague;; 0 = none.
Continue studying — lesson 05 (chapter 05 of 40)
← Chapter 04 · Course chapter index · Chapter 06 →
Continue to the next authored lesson.