Module 54: Normalisation

Why each option is right or wrong

• “An UPDATE that runs far more slowly than expected on a big table” (Incorrect): Speed isn't the issue; inconsistent copies are.
• “An UPDATE statement that has a syntax error in it” (Incorrect): Syntax errors stop the update; anomalies come from duplicated data.
• “A database upgrade to a newer version that fails partway” (Incorrect): Upgrades are a different concern from data design.
• “A fact stored in several places is changed in only some of them” is correct.

  If a customer's phone number is copied onto every order, changing it means updating every copy. Miss one, and the database now holds two different answers for the same fact, with no way to tell which is right.

  • Cure: store each fact once and link to it.
  • Related: insert and delete anomalies.

📖 Revise: Lesson 1: Why duplicated data causes problems

Why each option is right or wrong

• “Yes, as long as the numbers are separated by commas” (Incorrect): Separators don't make it one value; it's still a list in one cell.
• “Yes, because it's stored as a single text value” (Incorrect): One text value holding several facts still breaks 1NF.
• “Only if there are fewer than three numbers in it” (Incorrect): Even two values in one cell break 1NF.
• “No, each column must hold a single value” is correct.

  First normal form needs each cell to hold one value. A comma-separated list makes searching for one number, checking the format or adding another number awkward and error-prone. Use a separate phone_numbers table with one row per number.

  • Table: customer_phones(customer_id, phone).
  • Benefit: search, validate and count each number.

📖 Revise: Lesson 2: First normal form

Why each option is right or wrong

• “Column names with digits in them confuse most database tools” (Incorrect): Digits in names are fine; the design is the problem.
• “Databases allow at most two columns of the same type in one table” (Incorrect): Tables can have many columns of the same type.
• “It's perfectly normalised and the best design for phones” (Incorrect): Repeating columns are a classic normalisation problem.
• “It's a repeating group that limits numbers and complicates searching” is correct.

  Three columns cap everyone at three numbers, waste space for people with one, and finding a number means checking all three columns. A separate table with one row per number has no limit and one place to search.

  • Search becomes: WHERE phone = ? on one column.
  • Adding a fourth: needs no table change.

📖 Revise: Lesson 2: First normal form

Why each option is right or wrong

• “quantity” (Incorrect): Quantity depends on the whole key: this product in this order.
• “order_id” (Incorrect): It's part of the key, not a column that depends on it.
• “product_id” (Incorrect): It's part of the key too.
• “product_name” is correct.

  The key is the pair (order_id, product_id). Quantity depends on both: how many of this product in this order. Product name depends only on product_id, part of the key, so it belongs in products.

  • 2NF: no column may depend on only part of a composite key.
  • Fix: join to products for the name.

📖 Revise: Lesson 3: Second normal form

Why each option is right or wrong

• “Phone numbers can't be stored in an orders table” (Incorrect): They can be; the issue is that the phone depends on the customer, not the order.
• “It breaks 1NF because phone numbers can contain plus signs and spaces” (Incorrect): A single phone in one cell is fine for 1NF.
• “Nothing; repeating it makes queries simpler” (Incorrect): Simpler queries come at the cost of stale, inconsistent data.
• “A transitive dependency (3NF); join to customers instead” is correct.

  The phone depends on the customer, not on the order. Stored on orders, it repeats on every order and goes stale when the customer changes their number. The order's customer_id already leads to the phone.

  • 3NF: non-key columns depend only on the key, not on other non-key columns.
  • Unless: you deliberately want the phone as it was at order time.

📖 Revise: Lesson 4: Third normal form

Why each option is right or wrong

• “Every table needs a primary key, a foreign key and at least three columns” (Incorrect): Column counts aren't the point; normal forms are about what each column depends on.
• “Each table should be split until it has only one column” (Incorrect): Over-splitting isn't the goal; correct dependencies are.
• “Every column must be a number, a date or a short text” (Incorrect): Types are separate; normal forms are about dependencies.
• “Every non-key column depends on the key, the whole key, and nothing but the key” is correct.

  "The key" covers 1NF: each row has a key and single values. "The whole key" is 2NF: no partial dependencies. "Nothing but the key" is 3NF: no dependencies through other columns.

  • Memorable: it's a well-known saying among database designers.
  • In practice: each fact is stored once, in the table it describes.

📖 Revise: Lesson 4: Third normal form

Why each option is right or wrong

• “Yes, it duplicates the customer's address and must be removed” (Incorrect): It's a different fact: where this order went.
• “Yes, addresses should only be stored in a separate file” (Incorrect): Addresses belong in the database; on the order, they record history.
• “It depends on how long the address is” (Incorrect): Length doesn't matter; meaning does.
• “No, it's a historical fact about that order” is correct.

  The address the parcel was sent to doesn't change when the customer moves. Storing it on the order records where that order actually went. Pointing to the customer's current address would rewrite history.

  • Ask: is this the same fact, or a snapshot at a point in time?
  • Similar: the price charged on an order line.

📖 Revise: Lesson 5: When to denormalise

Why each option is right or wrong

• “Only how many decimal places the total should be rounded to” (Incorrect): Rounding matters, but the bigger risk is the copy going out of date.
• “Nothing; calculated values can't get out of date” (Incorrect): They can, whenever the lines change without updating the total.
• “Whether the total should be stored as text or as a number” (Incorrect): Type matters, but keeping it consistent is the key decision.
• “Which value is the source of truth and how the copy stays updated” is correct.

  Storing orders.total can speed up reports, but it's a copy of the sum of the order lines. Decide which is authoritative and how the total is kept right, in the same transaction or with a trigger, so they don't disagree silently.

  • Deliberate denormalisation: a known trade-off, written down.
  • Test: totals match their lines.

📖 Revise: Lesson 5: When to denormalise

Why each option is right or wrong

• “Courses need an admin password before they can be added” (Incorrect): Permissions are separate; the design has no place for the course.
• “The database can store only a fixed number of courses” (Incorrect): Databases don't limit that; the design does.
• “Course names are too long to fit in an enrolment row” (Incorrect): Length isn't the issue; there's no row to hold it.
• “There's no row to put it in until someone enrols: an insert anomaly” is correct.

  If course details live only in enrolment rows, a course with no students has nowhere to exist. A separate courses table lets you add a course before anyone joins it.

  • Insert anomaly: can't record one fact without another.
  • Fix: each entity in its own table.

📖 Revise: Lesson 1: Why duplicated data causes problems

Why each option is right or wrong

• “Yes, price depends only on the product” (Incorrect): The current price does; the price charged depends on the sale.
• “Yes, because prices are numbers” (Incorrect): Data type has nothing to do with normal forms.
• “Only if the price has decimals in it” (Incorrect): Decimals don't affect normalisation.
• “No, it's a fact about that sale” is correct.

  The price charged depends on the whole key: this product in this order, at that time. It isn't the product's current price, so it belongs on the order line.

  • 2NF violation would be: the product's name or current price on the order line.
  • Keep both: product's current price, and the line's price charged.

📖 Revise: Lesson 3: Second normal form