Exercise 1

Problem Statement

A company maintains a table to track employee projects. The table is as follows:

Employee_ID	Employee_Name	Project_ID	Project_Name	Project_Manager
101	Alice	P1	Alpha	John
101	Alice	P2	Beta	Sarah
102	Bob	P1	Alpha	John
103	Charlie	P3	Gamma	Alice

Analyze whether this table is in Second Normal Form (2NF). If it is not, provide a solution by converting the table into 2NF.

Hint

• Identify the primary key of the table (think about what uniquely identifies one employee-on-project assignment).
• Check for any attributes that depend only on a part of the primary key (partial dependencies).
• Name the insert/update/delete anomalies the partial dependencies cause before you decompose.

We will discuss the solution in the next lesson.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — Exercise 1 (2NF employee–project)

Why this concept exists (judgment layer)

2NF is partial-dependency removal: non-key attributes must depend on the whole composite key. This exercise is the classic insert/update/delete anomaly drill.

Mental model (install this intuition)

Candidate key = (Employee_ID, Project_ID). Employee_Name depends only on Employee_ID; Project_Name and Project_Manager depend only on Project_ID. Those are partial dependencies → not 2NF. Decompose into Employee, Project, Assignment.

Worked example with numbers or traced steps

R: (E_ID, E_Name, P_ID, P_Name, P_Manager)  PK ≈ (E_ID, P_ID)
FDs: E_ID→E_Name; P_ID→P_Name,P_Manager; (E_ID,P_ID)→∅ extra
Anomaly: change Alpha's manager → must update every Alice/Bob row on P1
Insert: new project with no employee yet → cannot insert without dummy employee
2NF: Employee(E_ID,E_Name) | Project(P_ID,P_Name,P_Manager) | Works(E_ID,P_ID)

When NOT to use / named alternative

If this is a one-shot extract table for a report (no updates), denormalized wide form can be fine. Do not 'normalize for purity' on immutable analytics snapshots without a write path.

Failure mode & ops fingerprint

Fingerprint: Project_Manager differs across rows of the same Project_ID after a partial update; cannot insert a project before assigning someone; deleting last assignee deletes project facts.

Hostile-panel drills (defend the decision)

Q1. Is the table in 1NF? In 2NF?
Model answer: Yes 1NF (atomic cells, no repeating groups). No 2NF: non-prime attributes depend on part of the composite key.

Q2. Name the partial dependencies.
Model answer: Employee_Name depends on Employee_ID only; Project_Name and Project_Manager depend on Project_ID only.

Q3. Give a lossless 2NF decomposition.
Model answer: Employee(Employee_ID, Employee_Name), Project(Project_ID, Project_Name, Project_Manager), Assignment(Employee_ID, Project_ID) with FKs.

I just read a lesson on **Exercise 1** (Databases) and want to truly understand it. Explain Exercise 1 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 1** 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 1** 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 1** 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.