You are shown EMPLOYEE(emp_id, emp_name, dept_id, dept_name, dept_location) with primary key emp_id. Is it in Third Normal Form, and how would you change it?
answer
- State the FDs before judging
- 2NF holds vacuously, single-column key
- dept_id is not a superkey
- Empty department cannot be stored
- Split, foreign key, lossless join
basics
~10 sNo. dept_id determines dept_name and dept_location, so both depend on the key only through a non-key attribute. Split out DEPARTMENT(dept_id, dept_name, dept_location) and leave EMPLOYEE(emp_id, emp_name, dept_id) with a foreign key.
solid answer
~50 sIt is in 2NF, because the key `emp_id` is a single attribute and no partial dependency is possible, but it is not in 3NF. The dependencies are `emp_id` to everything, plus `dept_id` to `dept_name` and `dept_id` to `dept_location`. `dept_id` is not a superkey, since many employees share a department, and `dept_name` and `dept_location` are non-prime. So the key determines them only *through* `dept_id`, a transitive dependency. Symptoms: the department name and location are stored once per employee instead of once per department; relocating a department is an update across every employee row and can leave contradictory locations; a department with no employees yet cannot be recorded; and firing the last employee in a department erases the department entirely. Decompose into `DEPARTMENT(dept_id, dept_name, dept_location)` and `EMPLOYEE(emp_id, emp_name, dept_id)` with a foreign key. The join back is lossless because `dept_id` is the key of `DEPARTMENT`.
code
sql · 7 linesCREATE TABLE employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100) NOT NULL,
dept_id INT NOT NULL,
dept_name VARCHAR(100) NOT NULL,
dept_location VARCHAR(100) NOT NULL
);go deeper
Point at dept_name and dept_location, say they belong to the department not the employee, and draw the two tables with a foreign key.
Show the chain emp_id to dept_id to dept_name, note that 2NF already holds, and enumerate the four anomalies.
Surface the dept_id to dept_location assumption, verify it against production data, and argue losslessness plus dependency preservation of the decomposition.
Discuss when a historical snapshot column is legitimately kept on the employee row and how read-path costs are handled without reintroducing update anomalies.
## Step 1: write the dependencies down Do not judge the table by looking at column names. State the functional dependencies the business actually implies: - `emp_id` to `emp_name`, `dept_id`, `dept_name`, `dept_location`. The key determines every attribute; that is what makes it a key. - `dept_id` to `dept_name`. One department id has exactly one name. - `dept_id` to `dept_location`. Assuming a department sits in one place, which is worth stating aloud as an assumption. ## Step 2: check the forms in order **1NF**: every column holds a single atomic value, no repeating groups. Passes. **2NF**: no non-prime attribute depends on a proper subset of a candidate key. The only candidate key is `{emp_id}`, a single attribute with no proper non-empty subset, so 2NF holds vacuously. This is worth saying out loud, because it shows you know that 3NF problems are not fixed by having a simple key. **3NF**: for every non-trivial dependency X to A, X must be a superkey or A must be prime. Take `dept_id` to `dept_name`. `dept_id` is not a superkey; knowing the department does not identify the employee. `dept_name` is not prime; it belongs to no candidate key. Both clauses fail, so 3NF is violated. Same for `dept_location`. Equivalently, in chain form: `emp_id` to `dept_id` to `dept_name` is a transitive dependency of a non-key attribute on the key. ## Step 3: name the concrete damage - **Redundancy.** With 10,000 employees in 50 departments, every department name and location is duplicated on average 200 times. - **Update anomaly.** Moving Engineering from Floor 3 to Floor 5 is an UPDATE over all its employee rows. If it fails partway, or two sessions disagree, the table asserts two locations for one `dept_id`, and no constraint can reject that state. There is no single row the database can point at as authoritative. - **Insertion anomaly.** A department created before it has any staff cannot be stored, because `emp_id` is the primary key and cannot be null. Teams work around this with a dummy employee row, which is a smell that the model is wrong. - **Deletion anomaly.** Deleting the last employee of a department destroys the department's name and location. The database silently forgets a real-world entity because a different entity was removed. All four are the same defect described four ways: a fact about departments is stored in a table whose key identifies employees. ## Step 4: decompose Project the dependency out into a relation keyed by its determinant, and keep the determinant behind as a foreign key: - `DEPARTMENT(dept_id, dept_name, dept_location)`, key `dept_id` - `EMPLOYEE(emp_id, emp_name, dept_id)`, `dept_id` referencing `DEPARTMENT` Now the department name lives in exactly one row. A relocation is a one-row update that cannot go half-done. An empty department is representable. Deleting an employee cannot delete a department, and the foreign key prevents an employee from naming a department that does not exist. **Losslessness:** the shared attribute `dept_id` is the key of `DEPARTMENT`, so the natural join of the two relations reproduces the original rows exactly, with no spurious tuples. **Dependency preservation:** `dept_id` to `dept_name` and `dept_id` to `dept_location` are enforced by the primary key of `DEPARTMENT`; `emp_id` to the rest is enforced by the primary key of `EMPLOYEE`. Nothing was lost. ## Step 5: keep iterating Re-check the results. If `dept_location` in turn determines a `building_manager`, then `DEPARTMENT` has its own transitive dependency and you extract `LOCATION(dept_location, building_manager)`. Normalization is applied until no relation has a dependency whose determinant is a non-superkey. ## Assumptions worth surfacing The analysis rests on `dept_id` to `dept_location` actually holding. If a department spans several sites and an employee's location is really the employee's own site, then the column is misnamed: it depends on `emp_id`, belongs in `EMPLOYEE`, and there is no violation for that attribute. Saying this in an interview shows you check the semantics rather than pattern-matching on prefixes. Similarly, if the business wants to remember which department a person was in *at hire time*, that is a historical fact keyed by the employee and it legitimately lives on the employee row. Name it accordingly, for example `dept_id_at_hire`, so the intent is visible.
- Is the original table in Second Normal Form?Yes. The only candidate key is emp_id, a single attribute with no proper non-empty subset, so no partial dependency can exist and 2NF holds vacuously. The defect appears only at 3NF, which is exactly why a simple primary key is no guarantee of a well-normalized table.
- What if a department can have several locations?Then dept_id to dept_location does not hold and the column means something else, most likely the individual employee's site, in which case it depends on emp_id and correctly stays in EMPLOYEE. If a department genuinely has a set of locations, that is a multi-valued fact needing its own DEPARTMENT_LOCATION table rather than a repeated column.
- Reporting now needs an extra join for every employee list. Is that a reason not to split?Usually not. The join is on an indexed primary key of a small table and is cheap, and the department table is small enough to stay cached. If measurements show a real cost on a hot path, address it with a covering index, a materialized view, or a deliberate denormalized copy with a stated refresh mechanism, rather than by leaving the base table anomaly-prone.
saying these in an interview costs you the question
- Saying the table is fine because emp_id uniquely identifies each row.
- Labelling this a partial dependency or a 2NF violation.
- Splitting employee name out instead of the department attributes.
- Assuming dept_id determines dept_location without stating the assumption.
- Rejecting the split purely because it adds a join, with no measurement.