Joins and Normalisation: Putting Facts in the Right Place

13.1.1 PLAIN — in simple words#

1. Seeing the same value twice does not automatically reveal a mistake. Two people may buy the same product. Two order lines may legitimately have the same price. A product identifier must appear in several places if those records refer to that product.
2. The more useful question is: are these separate facts, or several copies of one fact that should change together? Today’s product description and the description printed on an old agreement may look identical while carrying different responsibilities.
3. Suppose Mira corrects a spelling mistake in the current product catalogue. Updating one catalogue record is straightforward. Updating a thousand copied descriptions is harder: some copies may be missed, and some may be historical descriptions that should not change at all.
4. Decide which meaning you need before trying to remove repetition. Less repetition is not an improvement when it destroys the evidence of what was agreed.

13.1.2 PLAIN — a picture in your head#

1. Imagine a product card on a shelf and a folder for each completed order. The card says what the shop offers now. An order folder says what a particular customer agreed to buy at that time.
2. Several folders may point to the same product card. That repeated reference is useful. The price inside a signed folder is a different fact from the current price written on the card.
3. Where the comparison breaks: a database does not know that a folder is signed or that a card is current. Those distinctions require explicit fields, keys, rules and controlled write paths. A table name such as agreed_orders is documentation, not immutability enforcement.

13.1.3 PLAIN — a worked example#

1. These are the established four lines. Their amount is 2 × 7,550 + 2,000 + 7,550 + 2 × 2,000 = 28,650 paise.
2. The later catalogue illustration changes the notebook’s current price to 8,250. Replacing agreed notebook prices with this current price would add 3 × 700 = 2,100, producing 30,750. That is a hypothetical current-price calculation, not a corrected historical total.
3. It is appropriate to keep one current catalogue price per product in this simple model. It is also appropriate to retain the agreed price on each line. The two fields answer different questions.

13.1.4 PLAIN — what is really happening inside#

1. Repeated copies of one mutable fact create several opportunities for disagreement. An update can change only some copies. An insertion can require irrelevant information merely to create the first copy. A deletion can remove the last copy of a fact we still need.
2. Separating a product’s current details from its order lines removes those particular dependencies. The product can exist before its first sale, and deleting an isolated teaching order need not delete the catalogue entry.
3. This separation introduces a new responsibility: references must point to an existing product when the chosen model requires that. The foreign key from Chapter 11 addresses this structural condition; it does not establish that the human selected the right product.
4. Historical snapshots need an explicit policy. A preserved description may be deliberately redundant relative to the current catalogue, yet necessary for an old document. Record why it is copied and when, if ever, it may be corrected.

13.1.5 TECHNICAL — the engineer’s version#

1. Update, insertion and deletion anomalies motivate decomposition: represent facts in relations whose keys match the dependencies of those facts. Normalisation reasons about allowed states and functional dependencies, not just the strings visible in today’s sample. [S88]
2. A product reference on a line is not a redundant copy of every product attribute. It records a relationship. Conversely, an agreed line price is not functionally determined by the product identifier alone when different agreements can use different prices.
3. Separate authoritative write models from derived read models. A report or materialised projection may intentionally repeat descriptions for a workload. It then needs a refresh, correction and provenance policy rather than the unsupported label “single source of truth.”
4. This chapter’s schema reasoning does not add an immutable-agreement mechanism to the earlier lab. The intentionally demonstrated missing protection remains missing until a separately specified implementation enforces it.

13.1.6 WORDS — remember these#

1. Redundancy: several representations of a fact — duplication whose consistency depends on maintaining a specified relationship between representations. Update anomaly: one correction leaves conflicting copies — a state inconsistency caused by storing a dependent fact in multiple independently editable rows. Historical snapshot: a saved description of an earlier state — data retained under an explicit capture time, purpose and correction policy.

13.2.1 PLAIN — in simple words#

1. A join combines records that satisfy a condition. The condition must represent the relationship you actually mean. “These two cells happen to contain 17” is not enough.
2. If receipt numbers restart at each branch, receipt 17 at branch BR-A and receipt 17 at branch BR-B are different receipts. Matching only the number can attach one branch’s details to the other branch’s receipt.
3. A join can return several rows for one input row. That is often correct: one order has several lines. Trouble begins when a later calculation assumes those output rows still represent whole orders exactly once.
4. Write the intended meaning of one output row beside a query. This short sentence often catches an error earlier than a large, plausible-looking total.

13.2.2 PLAIN — a picture in your head#

1. Think of matching luggage tags at two airport desks. A sequence number may only be unique within one flight. You need the flight identifier and bag number together.
2. Matching only the bag number creates several possible pairings. The machinery has not confused the bags; the instruction supplied to it was incomplete.
3. Where the comparison breaks: SQL does not exercise human judgement when several matches appear. It produces the rows required by the written condition. Choosing one arbitrary match with a limit can hide the defect rather than repair the relationship.

13.2.3 PLAIN — a worked example#

1. Take two invented receipt headers: (BR-A, 17) and (BR-B, 17). Take two detail records with exactly the same respective keys. This is a separate key-scope exercise, not another sale in the canonical four-line dataset.
2. Joining on receipt_no alone allows four pairs: A-to-A, A-to-B, B-to-A and B-to-B. Joining on both branch_id and receipt_no allows only the two intended pairs.

SELECT h.branch_id, h.receipt_no, d.description
FROM receipt_headers AS h
JOIN receipt_details AS d
  ON d.branch_id = h.branch_id
 AND d.receipt_no = h.receipt_no;

3. These table names specify the isolated exercise, not a migration to the existing lab. Before running it, create the two small practice tables with a composite header key and a matching foreign key.
4. More generally, if one key has two left records and three right records, an equality join can produce six matching pairs for that key. Two times three is the consequence of the relationship expressed, not a database arithmetic bug.

13.2.4 PLAIN — what is really happening inside#

1. Logically, a join considers pairs and keeps those for which the condition is true. An engine can use indexes or other strategies to avoid physically checking every pair; those strategies must preserve the specified result.
2. Key constraints supply useful facts about possible matches. A join to a genuinely unique parent key has at most one matching parent per child. Joining to a description that is not unique supplies no such assurance.
3. A missing value introduces another boundary. Ordinary equality involving SQL NULL is not true, so it does not make a successful match. Treating every missing customer as one shared fake identifier would invent a relationship the input did not establish.
4. A text comparison also uses a comparison rule. Case handling and collation belong to the contract. Two spellings that appear similar to a human are not automatically the same identifier, and a permissive matching rule may merge distinct records.

13.2.5 TECHNICAL — the engineer’s version#

1. An equijoin uses equality predicates. Composite relationships need the complete tuple of joining attributes. A declared foreign key and a unique referenced key help constrain cardinality, but nullable child keys and extra filters still affect the result. [S58] [S59]
2. SQL normally preserves duplicate result rows unless an operation explicitly removes them. The same projected values can come from different underlying matches. DISTINCT removes identical projected rows, not an incorrectly specified relationship. [S57]
3. Prefer explicit ON conditions, or an intentional USING list, over an unreviewed NATURAL JOIN. A natural join derives its matching columns from shared names; a later same-named column can alter behaviour without an obvious change to the query. [S58]
4. A correct join is not an authorisation check. Matching tenant identifiers in a query is useful only within a wider policy that establishes which tenant the caller may access and prevents alternative unscoped paths. Chapter 47 develops that separate responsibility.

13.2.6 WORDS — remember these#

1. Join predicate: the rule for pairing records — a Boolean condition controlling which row combinations contribute to a joined relation. Composite key: several fields identify one thing together — an identifying attribute set whose combined values are unique in its declared scope. Cardinality: how many records or matches there are — a count or relationship multiplicity whose context must be stated.

13.3.1 PLAIN — in simple words#

1. An inner join returns successful matches. A left outer join also keeps each left record that has no successful match, filling the missing right-side fields with NULL.
2. These operations answer different questions. “Orders with a recorded customer” excludes anonymous orders. “Every order, with customer details where available” must keep them.
3. Keeping an unmatched order does not create a customer. The empty right-hand fields say that this join found no matching record. They do not explain whether the order was intentionally anonymous, its identifier was missing, or a relationship was filtered out.
4. A condition applied after a left join can remove the unmatched rows again. The word LEFT alone does not guarantee that the final report includes every left record.

13.3.2 PLAIN — a picture in your head#

1. Imagine an attendance sheet and a box of submitted assignments. An inner join lists pupils for whom a matching assignment was found. A left join starts with every pupil and attaches an assignment where one exists.
2. If someone subsequently discards every row whose assignment mark is not above 50, pupils without assignments disappear as well. The original complete attendance sheet does not prevent the later filter.
3. Where the comparison breaks: a NULL mark might mean either an unmatched assignment or an existing assignment whose mark is not yet recorded. Inspect a non-null assignment key to distinguish these cases; the mark alone is insufficient.

13.3.3 PLAIN — a worked example#

1. In our established annotation exercise, O-1042 has no customer identifier and O-1043 points to C-001. C-001’s display name is “Study customer.” These annotations do not change the two order totals.

SELECT o.order_id, c.customer_id, c.display_name
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id;

2. The left join keeps both orders. An inner join on the same relationship keeps only O-1043. These are counts of order rows, not counts of lines or physical units.
3. Adding WHERE c.display_name = 'Study customer' after the left join also retains only O-1043. The NULL comparison for the unmatched order is not true.
4. Putting that condition inside ON instead asks for every order and, where available, a customer matching that name. O-1042 survives with empty customer fields. O-1043 still matches. The difference becomes even clearer with a separately labelled customer whose name does not pass the filter. [S58]

13.3.4 PLAIN — what is really happening inside#

1. Conceptually, the join first forms matches according to ON. For a left join, a left row with no match contributes one null-extended output row. A later WHERE condition decides which of those resulting rows remain.
2. An existing right row can itself contain nullable attributes. Therefore, do not count “customers found” by checking an optional nickname. Use the customer’s non-null key when the model guarantees one.
3. COUNT(*) counts the rows produced. COUNT(c.customer_id) counts rows with a non-null customer identifier. In this two-order example the results are two and one respectively. Neither expression counts distinct real people unless the intended grouping and identity policy establish that meaning. [S19]
4. To find missing relationships, a left join followed by WHERE c.customer_id IS NULL can be useful when this is the right-side non-null key. A NOT EXISTS query can express the same absence question more directly, without projecting right-hand fields.

13.3.5 TECHNICAL — the engineer’s version#

1. Predicate placement matters around outer joins because null extension and filtering do not generally commute. Moving a predicate between ON and WHERE requires a semantic argument, not a formatting preference. [S58]
2. For a left row with m matching right rows, the left join contributes m rows when m > 0 and one null-extended row when m = 0. This still permits multiplication when the right side is one-to-many.
3. Do not use outer joining as silent data repair. A nullable annotation and a required reference with a missing parent are different conditions. Enforced constraints, import diagnostics and query output serve different purposes.
4. Query results are observations under a database’s visibility rules. Combining independently fetched customer and order lists in application memory may not reproduce the same snapshot as one query. Chapters 21–25 explain the conditions behind that distinction.

13.3.6 WORDS — remember these#

1. Inner join: keep successful pairings — a join that contributes only row combinations satisfying the join condition. Left outer join: keep every left record, even without a match — a join adding a null-extended row for each unmatched left input row. Null extension: empty right-hand fields for an unmatched record — synthetic NULL-valued attributes introduced by an outer join, not a stored right-hand record.

13.4.1 PLAIN — in simple words#

1. Some fields determine other fields under a rule. In this model, a product identifier selects one current product record. A complete order-line key selects one agreed line, including its quantity and agreed price.
2. “Determines” means that two legal records with the same determining fields cannot disagree on the dependent fields. It does not mean the first field physically causes the second one.
3. A pattern in four sample rows is not enough to establish such a rule. Our two notebook lines happen to have the same price. A future promotion could legitimately create a different agreed price for the same product.
4. Write dependencies from the meaning of the work, then use examples to challenge them. Do not infer permanent business rules solely from a small export.

13.4.2 PLAIN — a picture in your head#

1. Think of a numbered locker assigned to one student during one school term. Within that term, the locker number determines the assigned student under the school’s one-assignment rule.
2. Across several terms, the locker number alone no longer determines the student. The term becomes part of the identifying information.
3. Where the comparison breaks: the arrow in a dependency is not an arrow of time or cause. A student may have several lockers, or the school may allow shared lockers. The dependency follows the stated policy, not the object called a locker.

13.4.3 PLAIN — a worked example#

1. Use a deliberately simplified combined relation with attributes order_id, line_no, product_id, current_description, quantity, agreed_price.
2. Its agreed line rule gives (order_id, line_no) → product_id, quantity, agreed_price. The current catalogue rule gives product_id → current_description.
3. Starting with an order-line key, we can obtain its product identifier, then that product’s current description. This chain explains why copying the current description into every line creates repeated dependent information.
4. Split the relation into OrderLine(order_id, line_no, product_id, quantity, agreed_price) and Product(product_id, current_description). Provided each product identifier has exactly one permitted current description and references are represented consistently, joining on product_id reconstructs the intended combined rows.
5. Do not move agreed_price into the current product relation. The proposed dependency product_id → agreed_price is not a rule of our agreements. Equal numbers in the current fixture cannot make it one.

13.4.4 PLAIN — what is really happening inside#

1. A dependency lets us calculate an attribute closure: start with known attributes, repeatedly apply dependencies whose left sides are known, and add the attributes they determine.
2. When the closure contains every attribute of the relation, the starting set is a superkey. A candidate key is a superkey with no unnecessary attribute under the stated dependencies.
3. Decomposition must preserve information. Splitting a table and joining the pieces can produce combinations that were never in the original unless the split has the right conditions.
4. Consider three invented assignments: (Mira, blue, morning), (Mira, red, evening) and (Dev, blue, evening). Splitting these into person-colour and colour-shift pairs, then joining only on colour, invents (Mira, blue, evening) and (Dev, blue, morning). The pieces lost which person-colour pair belonged to which shift. This is a separate relational exercise, not shop staffing history.

13.4.5 TECHNICAL — the engineer’s version#

1. A functional dependency X → Y holds on a relation schema under its intended constraints when every legal relation instance has equal Y values for any two tuples equal on X. Sample agreement can falsify neither all counterexamples nor future policy changes. [S88]
2. For a binary decomposition of R into R1 and R2, a standard sufficient-and-necessary lossless condition under functional dependencies is that the shared attributes functionally determine R1 or R2 under the implied dependencies. This is a schema theorem, distinct from an accidental successful join on one sample.
3. Losslessness concerns reconstructing relation instances without spurious tuples. Dependency preservation concerns checking the original dependencies through the decomposed relations without needing a join. These are different properties; preserving one does not automatically preserve the other. [S88]
4. Classical relational dependency reasoning assumes well-defined attribute domains and relation semantics. SQL NULL, duplicate rows, collation, incomplete constraints and temporal rules require additional care when translating a theoretical decomposition into a concrete database.

13.4.6 WORDS — remember these#

1. Functional dependency: one set of fields fixes another under a rule — X → Y means equality on X implies equality on Y in every legal relation instance. Attribute closure: everything the starting fields let you determine — the set of attributes implied by repeatedly applying the specified functional dependencies. Lossless decomposition: splitting without inventing or losing combinations on reconstruction — decomposition whose natural join recovers every legal original relation instance under the stated dependencies.

13.5.1 PLAIN — in simple words#

1. A normal form is a named set of conditions on how a relation stores dependencies. The names help reviewers ask precise questions instead of saying that a table “looks tidy.”
2. Start by deciding what one field means and what identifies a row. Then ask whether some other fact depends on only part of that identifier, or on another non-identifying fact copied into the row.
3. Splitting such facts can make corrections safer. It also means that some questions need joins. Whether to maintain additional read-friendly copies is a separate workload decision.
4. More tables do not prove a better design. A decomposition that loses information, makes an important rule unenforceable without extra coordination, or confuses historical and current facts can be worse despite impressive terminology.

13.5.2 PLAIN — a picture in your head#

1. Think of organising a workshop. One drawer holds the current specification for each component; a separate folder holds each job’s agreed requirements. You stop copying the supplier’s current telephone number onto every screw’s usage record.
2. You may still pin a printed job summary beside a machine because it is convenient to read. The pinned summary is a controlled copy, not a new authority for the underlying job.
3. Where the comparison breaks: normal forms are mathematical conditions on relations and dependencies, not a general rule that physical objects must be stored in separate drawers. A neat filing system can still contain the wrong dependencies.

13.5.3 PLAIN — a worked example#

1. Consider an isolated order-header relation (order_id, customer_id, current_customer_name). Assume each order has one required customer for this exercise, and each customer identifier has one current name. These assumptions differ from the canonical model’s optional customer annotation and are stated only to illustrate the dependency.
2. order_id determines the customer identifier; customer_id determines the current name. Copying the current name into every order repeats a fact whose change is about the customer, not each order.
3. A decomposition into OrderHeader(order_id, customer_id) and Customer(customer_id, current_name) isolates that current fact. A foreign key can enforce that each header references an existing customer under this exercise’s required relationship.
4. Now change the meaning: name_printed_on_agreement is what was printed on that particular order. This field can belong to the agreement record because later customer renaming must not silently rewrite the printed document. A superficially similar layout now has a different dependency contract.
5. Normalisation follows the contract. It cannot decide which of these two fields the business intended.

13.5.4 PLAIN — what is really happening inside#

1. Review every proposed split with three questions: can the original facts be reconstructed; can required dependencies still be enforced; and what operations must now read or write several relations together?
2. A report can join normalised tables as needed. A stored report, cache or materialised view can reduce repeated work, but then readers need to know the copy’s freshness and how corrections reach it.
3. Do not combine two independent many-valued facts into every possible pair without a reason. If a product has two tags and three suppliers, a six-row tag-supplier cross-product repeats both relationships. Separate product-tag and product-supplier relations represent independence more directly.
4. But do not split a genuinely three-way fact. “Supplier S can supply product P under condition C” may be a specific permitted combination, not three independent pairwise facts. The spurious-tuple exercise in section 13.4 explains the danger.

13.5.5 TECHNICAL — the engineer’s version#

1. In the usual relational treatment, first normal form uses relation attributes with values from their declared domains, rather than repeating groups masquerading as columns. “Atomic” is relative to the domain and operations: it does not mean that text may never contain several characters or that SQL must forbid every structured type.
2. Second normal form removes partial dependencies of non-prime attributes on proper subsets of candidate keys. Third normal form permits a nontrivial dependency X → A only when X is a superkey or A is prime, meaning it belongs to some candidate key. Boyce–Codd normal form requires the determinant of every nontrivial functional dependency to be a superkey. [S88]
3. The common phrase “depends on the key, the whole key and nothing but the key” is a mnemonic, not a substitute for checking all candidate keys and dependencies. In particular, third normal form and BCNF are not identical.
4. Some BCNF decompositions do not preserve all original dependencies locally. A dependency-preserving third-normal-form design can be a deliberate choice, with trade-offs documented. Independent multivalued relationships motivate fourth normal form; more general join dependencies lead to fifth normal form. This book uses those ideas to ask about independent facts rather than claiming a full database-theory proof course.
5. Denormalisation is the deliberate maintenance of redundant representations for specified operations. It needs ownership, refresh rules and reconciliation. It does not mean “remove constraints until the benchmark improves.”

13.5.6 WORDS — remember these#

1. Prime attribute: a field belonging to at least one minimal key — an attribute contained in some candidate key, not necessarily the selected primary key. Normal form: a named condition for placing relational facts — a dependency-based schema property such as 3NF or BCNF. Denormalisation: deliberately keeping an extra representation — controlled redundancy introduced for stated requirements with explicit maintenance obligations.

13.6.1 PLAIN — in simple words#

1. A correct relationship can still be the wrong input to a sum. Joining each line to its product tags produces line-tag pairs. A notebook line with two tags now appears twice.
2. Summing the line’s amount over those pairs counts the notebook twice. The join is answering “which tags belong to each line’s product?” while the sum pretends it is seeing each line only once.
3. Fix the question, not the symptom. To total order lines, use one contribution per line. To ask whether a qualifying tag exists, ask an existence question. To combine several different totals, calculate each at its intended level before joining them.
4. Removing duplicate amounts is not the same as removing duplicate line contributions. Two separate lines can legitimately have equal amounts.

13.6.2 PLAIN — a picture in your head#

1. Imagine putting a photocopy of each receipt in every relevant product-category folder. A receipt mentioning two categories appears in two folders.
2. Adding every photocopy’s amount overstates sales. Throwing away every receipt with a repeated amount also fails: two different customers may have spent the same amount.
3. Where the comparison breaks: a SQL join need not make physical photocopies. The duplication is in the logical result rows. The error persists even when the engine streams those rows without storing a copied table.

13.6.3 PLAIN — a worked example#

1. The established tag example gives P-NOTE two tags, paper and school, and P-PEN one, writing. The notebook contributions total 22,650 paise; pen contributions total 6,000.
2. The line-tag join therefore yields 2 × 22,650 + 6,000 = 51,300 paise when line amounts are wrongly summed. The correct line total remains 28,650.

SELECT order_id, SUM(quantity * unit_price_minor) AS total_minor
FROM order_lines
GROUP BY order_id
ORDER BY order_id;

3. This query stays at line grain until it deliberately groups to order grain. Its results are O-1042: 17,100 and O-1043: 11,550.
4. To select lines whose product has a paper tag, use a correlated existence condition rather than adding a tag row to the output for every match. To combine order totals and payment totals in a later exercise, aggregate the lines and payments separately by order, then join those one-row-per-order results.
5. The separate O-REPEAT counterexample contains two genuine notebook lines of 7,550 each. Their sum is 15,100. SUM(DISTINCT line_amount) returns 7,550 and is therefore not a general repair for fan-out.

13.6.4 PLAIN — what is really happening inside#

1. Track grain through the query: order line, then line-tag pair, then group. Once a line can contribute more than once, every aggregate that uses its values needs a reason why that multiplicity is intended.
2. A useful diagnostic query lists original keys and their contribution counts. A line key appearing twice is evidence to investigate. It is not automatically wrong: perhaps the metric intentionally counts category memberships rather than sales.
3. Test empty parents, several children, equal amounts from different children, repeated descriptions and optional relationships. One parent with one child in every table hides most multiplication errors.
4. Reconcile both totals and row identities. Two wrong calculations can have the same grand total if one omitted contribution happens to cancel one duplicated contribution. Agreement on one number is evidence, not a complete proof of matching populations.

13.6.5 TECHNICAL — the engineer’s version#

1. Join cardinality and aggregation grain are separate design choices. Joining two independent one-to-many child relations through their parent generally forms combinations of children before aggregation. Pre-aggregation can restore one row per parent on each side when those aggregates answer the intended questions. [S58] [S19]
2. EXISTS expresses a semi-join-like membership question: whether at least one qualifying row exists. It avoids introducing one output contribution per qualifying child. An optimiser may implement that question with different physical strategies while preserving its semantics.
3. A distinct aggregate operates on its argument values within a group. It does not deduplicate by an unmentioned business key. Preserve the contribution identity explicitly when deciding what is counted once. [S19] [S80]
4. The companion exercises verify result rows and arithmetic in disposable databases. They are not evidence that every report, migration or production workload has been reconciled. Chapter 19 extends these ideas to averages and windows; Chapter 46 examines the population definitions behind a metric.

13.6.6 WORDS — remember these#

1. Fan-out: one record becomes several joined contributions — multiplication of rows through a one-to-many or many-to-many relationship. Pre-aggregation: calculate a total before joining it to other detail — reducing a relation to the required grouping grain before a later join. Reconciliation: compare defined representations of the same work — an evidence-based check of populations, keys, amounts and explained differences.