Given a table ORDER_LINE(order_id, product_id, quantity, product_name, order_date) whose primary key is (order_id, product_id), does it satisfy Second Normal Form? Walk through your reasoning and the fix.
answer
- List the FDs before judging
- quantity needs both key parts
- product_name from product_id, order_date from order_id
- Three entities crammed into one relation
- Charged price stays, list price moves
basics
~10 sNo. product_name depends only on product_id and order_date only on order_id, both parts of the composite key, so both are partial dependencies. Split into ORDER(order_id, order_date), PRODUCT(product_id, product_name), and ORDER_LINE(order_id, product_id, quantity).
solid answer
~50 sIt is not in 2NF. Take each attribute and ask what it really depends on: - `quantity` needs both `order_id` and `product_id`, so it depends on the whole key. Fine. - `product_name` is determined by `product_id` alone. - `order_date` is determined by `order_id` alone. The last two are non-prime attributes determined by a proper subset of the composite key, which is the definition of a partial dependency, so 2NF fails. The symptoms are concrete: the product name repeats on every line that sells it, the order date repeats on every line of the order, renaming a product is a multi-row update that can leave the table self-contradictory, and you cannot record an order with no lines yet. Decompose by projecting each dependency onto its determinant: `ORDER(order_id, order_date)`, `PRODUCT(product_id, product_name)`, and `ORDER_LINE(order_id, product_id, quantity)` with foreign keys to both. The join back is lossless because each shared column is the key of its parent table.
code
sql · 8 linesCREATE TABLE order_line (
order_id INT,
product_id INT,
quantity INT NOT NULL,
product_name VARCHAR(120),
order_date DATE,
PRIMARY KEY (order_id, product_id)
);go deeper
Name the two offending columns, say why each is partial, and draw the three-table result with foreign keys.
Derive the functional dependencies first, then tie the violation to specific insert, update and delete anomalies rather than to vague duplication.
Discuss the semantics of price columns, argue losslessness, and describe how you would verify the dependency against existing production data before migrating.
Position the split against the write path and reporting needs, and say when you would deliberately keep a denormalized copy with an enforced refresh mechanism instead.
## Reading the table The relation is `ORDER_LINE(order_id, product_id, quantity, product_name, order_date)` with primary key `(order_id, product_id)`. Each row says: on this order, this many units of this product. The first move is always to state the functional dependencies, not to eyeball the columns. A functional dependency X to Y means rows agreeing on X must agree on Y. - `(order_id, product_id)` to `quantity`. You need both parts: the same product can appear on many orders with different quantities, and the same order can contain several products. - `product_id` to `product_name`. A product's name is a fact about the product, independent of any order. - `order_id` to `order_date`. The date is a fact about the order, the same on every line of it. ## Applying the definition 2NF requires that no non-prime attribute depend on a proper subset of a candidate key. The candidate key is composite, `(order_id, product_id)`, and its proper subsets are `{order_id}` and `{product_id}`. `product_name`, `order_date` and `quantity` are all non-prime. `product_name` depends on `{product_id}` and `order_date` on `{order_id}`. Both are partial dependencies, so the relation is in 1NF but not 2NF. `quantity` is the only attribute that genuinely earns its place beside the full key. ## What actually goes wrong A table in this shape stores a fact about a product, and a fact about an order, once per *line*. - **Redundancy.** If a popular product appears on 50,000 lines, its name is stored 50,000 times. Same for the date on every multi-line order. - **Update anomaly.** Rebranding a product means an UPDATE across every historical line. If it is applied partially, the table now holds two different names for one `product_id`, and no key or foreign key constraint can detect that. There is no single place the database can enforce "a product has one name". - **Insertion anomaly.** You cannot record a product that has never been ordered, or an order created before any line is added, because half of the primary key would be missing and primary key columns cannot be null. - **Deletion anomaly.** Deleting the last line that references a product erases the product's name from the database entirely. Deleting the last line of an order erases the order date. Each anomaly is the same underlying defect seen from a different verb: three different entities (order, product, order line) are crammed into one relation whose key identifies only the finest of them. ## The decomposition For each partial dependency, create a relation keyed by the determinant: - `ORDER(order_id, order_date)` - `PRODUCT(product_id, product_name)` - `ORDER_LINE(order_id, product_id, quantity)`, with `order_id` referencing `ORDER` and `product_id` referencing `PRODUCT` Now each fact lives once. The product name has exactly one home row, so a rename is a single-row update that cannot go half-done. An order can exist with no lines, and a product can exist with no sales. Referential integrity does real work: a line cannot name a product that does not exist. The decomposition is **lossless**. Each new relation shares its join column with `ORDER_LINE`, and that column is the key of the new relation, which is the standard sufficient condition for a lossless join. Natural-joining the three relations reproduces exactly the original rows, no more and no fewer. ## The trap column Interviewers often extend the table with `unit_price`, and the answer is no longer mechanical. If `unit_price` is the price charged on this line at the time of sale, it depends on the full key and must stay in `ORDER_LINE`. Keeping it there is also correct behaviour: an invoice must not change retroactively when the catalogue price changes. If instead the column means "the product's current price", it is determined by `product_id` alone, is a partial dependency, and belongs in `PRODUCT`. State the assumption out loud rather than guessing. A related trap is `line_total = quantity * unit_price`. That is a derived value; it is redundant for a different reason (it is computable), and normalization theory about partial dependencies is not the argument against it. ## Where this leaves you After the split, the three relations happen to also be in 3NF, because no remaining non-key attribute determines another. In general you would continue: check `ORDER` and `PRODUCT` for transitive dependencies such as a customer's city sitting next to a customer id.
- After the split, where does unit_price belong?It depends on what the column means. The price actually charged on that line depends on the full key (order_id, product_id) and stays in order_line, which also preserves invoice history when the catalogue changes. A current list price depends on product_id alone and belongs in the product table.
- Does the primary key of ORDER_LINE change after the decomposition?No. The line is still identified by (order_id, product_id); only the attributes that did not depend on that whole key were removed. If the business allows the same product on two separate lines of one order, then the natural key is inadequate and you would introduce a line number or surrogate key, but that is a modelling change, not a consequence of normalizing.
- How would you detect this kind of violation on an existing production table?Query for determinant candidates: group by the suspected determinant and count distinct values of the dependent column, for example count distinct product_name per product_id. A count of one everywhere is evidence the dependency holds; a count above one means the redundancy has already gone inconsistent, which is itself the argument for splitting.
saying these in an interview costs you the question
- Answering "it is fine because it has a primary key" without decomposing the key into its parts.
- Moving quantity out of the line table, or leaving product_name in it because it "describes the line".
- Calling this a transitive dependency; the determinants here are parts of the key, not non-key attributes.
- Blindly moving unit_price into the product table and destroying historical invoice prices.
- Claiming the fix is just adding an index or a UNIQUE constraint on product_name.