Exercise 2

Problem Statement

A company maintains the following table to track employee information, which is already in Second Normal Form (2NF):

Emp_ID	Emp_Name	DOB	Area	City	State	Zip
101	Alice	1990-05-01	Downtown	New York	NY	10001
102	Bob	1988-08-12	Midtown	New York	NY	10002
103	Charlie	1992-11-23	Central	Los Angeles	CA	90001
104	David	1985-03-15	West End	Chicago	IL	60601

Task:

1. Analyze whether the given table is in Third Normal Form (3NF).
2. If the table is not in 3NF, convert it to 3NF by eliminating transitive dependencies.

Hint:

• Identify the primary key of the table.
• Confirm why the table is already in 2NF (hint: single-column key).
• Check for transitive dependencies:
  • Under the modeling assumption that a Zip determines location fields, attributes like Area, City, and State depend on Zip rather than directly on Emp_ID.
• Remove transitive dependencies by splitting the table into smaller tables, keep a foreign key so the join stays lossless, and state which anomalies disappear.

In the next lesson, we will provide the solution to this problem.

🎯 STRICT STANDOUT: Why / worked / when-not / failure / drills — Normalization Exercise 2 (3NF Zip)

Why this concept exists (judgment chain)

This exercise drills the classic 2NF-but-not-3NF trap: a single-column PK kills partial dependencies (hence 2NF), but Zip→{Area,City,State} is a transitive dependency Emp_ID→Zip→City. 3NF removes non-key determinants that are not superkeys. The lossless hint forces you to keep Zip as FK so the join recovers the original.

Worked example with numbers or traced steps

Table EmpLoc(Emp_ID PK, Emp_Name, DOB, Area, City, State, Zip) — 2NF (key is atomic).
Assumption: Zip → Area, City, State.
Transitive: Emp_ID → Zip → City violates 3NF.
Decomposition:
  Employee(Emp_ID PK, Emp_Name, DOB, Zip FK)
  Location(Zip PK, Area, City, State)
Lossless: common attribute Zip is key of Location.
Anomalies removed: renaming 10001's Area updates one Location row, not every employee there.
Real-world caveat: US ZIP can span multiple cities — validate the Zip FD before modeling.

When NOT to use / named alternative

Do not normalize Zip away if the product treats address as an opaque blob with no shared Location table and rare updates. Do not claim BCNF automatically if Zip→City fails in real postal data. Prefer a single Address JSON only when you never query by city/zip at scale.

Failure / ops fingerprint

Update anomaly: two employees with Zip 10001 show different City after partial updates. Insert anomaly: cannot record a Zip's city without inventing an employee. Ops: run data quality checks for Zip→City consistency before/after migration.

Hostile-panel Q&As (model answers)

Q1. Why is the table already in 2NF?
Model answer: Primary key is single-column Emp_ID, so there is no partial dependency of non-key attributes on part of a composite key.

Q2. Is Emp_ID → City a direct FD?
Model answer: Under the Zip model it is transitive via Zip; 3NF cares about that chain, not surface correlation.

Q3. Prove lossless for the split.
Model answer: R1∩R2 = {Zip} and Zip → Location attributes (Zip is key of Location), so the join is lossless.

I just read a lesson on **Exercise 2** (Databases) and want to truly understand it. Explain Exercise 2 from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Exercise 2** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Exercise 2** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Exercise 2** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.