- Which normalization change best addresses both anomalies described?
Answer: Move supplier details to Supplier(SupplierID, SupplierPhone) and retain SupplierID with each product.
Whenever you see a question about data anomalies — specifically insertion anomalies (can't add a supplier without a product) and update anomalies (changing a phone number requires editing many rows) — you should immediately think about Second Normal Form (2NF) and Third Normal Form (3NF). The core fix is always to move repeating, dependent data into its own table.
Here, SupplierPhone depends on SupplierID, not on ProductID. That violates normalization because supplier data is scattered across every product row the supplier is associated with. The solution is to extract supplier information into a dedicated Supplier(SupplierID, SupplierPhone) table and keep only SupplierID as a foreign key in the product table. This eliminates the update anomaly (one phone change, one row to update) and the insertion anomaly (you can add a supplier to the Supplier table without needing a product). That's exactly what D does, making it the correct answer.
A makes things worse by merging two distinct pieces of data into one field, violating atomicity (1NF) and making queries and updates even harder. B moves product names to a separate table — which is already fine where they are — while leaving the real problem, the supplier phone redundancy, untouched. C adds a surrogate key, which helps with row identification but does absolutely nothing to remove the repeated SupplierPhone values or fix either anomaly.
A useful pattern to remember: when an anomaly involves data that repeats because it belongs to something else, the fix is always to give that "something else" its own table and link back with a foreign key.
- What is the highest normal form guaranteed by the stated dependencies?
Answer: Second normal form, because dependencies use the full key but WarehouseCity is transitively dependent on it.
When working through normalization questions, map each functional dependency to the appropriate normal form definition before evaluating the answer choices.
Here, ShipmentLine has composite key (ShipmentID, ProductID). First normal form (1NF) requires atomic values — satisfied. Second normal form (2NF) requires that every non-key attribute depend on the entire composite key, not just part of it. WarehouseID depends on the full key (neither column alone determines it), so there's no partial dependency — 2NF is satisfied. However, 2NF does not require the absence of transitive dependencies. We're told WarehouseID → WarehouseCity, meaning WarehouseCity is determined by a non-key attribute. This transitive dependency violates third normal form (3NF), which requires that non-key attributes depend only on keys. The relation is therefore guaranteed to be in 2NF but not 3NF, making B correct.
Choice A is wrong because a composite key does not automatically create a partial dependency — partial dependencies only exist if a non-key attribute depends on part of the key. That isn't true here for any attribute. Choice C is wrong because reaching 2NF (no partial dependencies) doesn't mean 3NF is achieved. The transitive chain (ShipmentID, ProductID) → WarehouseID → WarehouseCity is precisely the kind of dependency that violates 3NF. Choice D is wrong and makes no sense — storing related attributes together is the entire point of a relation; there's no rule prohibiting a warehouse ID and city in the same table.
A useful pattern: always check for transitive dependencies after confirming no partial dependencies exist. Passing 2NF doesn't guarantee 3NF.
- Which evaluation of this design decision is most accurate?
Answer: It is deliberate denormalization that may improve reads but reintroduces redundancy and requires a consistency strategy.
Whenever you see a question about normalization violations, ask yourself two things: what rule is being broken, and what are the real-world consequences? This scenario tests whether you can recognize a deliberate design trade-off versus an accidental violation — a critical distinction in database design.
Storing CustomerRegion in every sales row when it's already determined by CustomerID in another table creates a transitive dependency (a non-key attribute depending on another non-key attribute), which breaks third normal form (3NF). However, the team isn't doing this accidentally — they've consciously chosen to duplicate data to eliminate a join. This is textbook denormalization: a deliberate departure from normal forms to gain read performance. The trade-off is real: queries run faster, but now if a customer's region changes, you must update potentially thousands of sales rows, or risk inconsistent data. That's why C is correct — it accurately names the pattern, acknowledges the benefit, and flags the consistency burden.
Answer A is wrong because 3NF, not 1NF, governs redundancy introduced by functional dependencies. 1NF concerns atomic values and repeating groups — having the same value in multiple rows doesn't violate it.
Answer B is completely backwards. Query performance is never a criterion for normal-form compliance. Normal forms are defined by dependency rules, not execution speed — confusing the two reflects a fundamental misunderstanding.
Answer D is the sneakiest trap. Independent copies don't eliminate update anomalies — they guarantee them. If the source data changes and one row is missed, your data is now inconsistent.
Study tip: On SQL exams, "each row has its own copy" is almost always a red flag for update anomalies, not a solution to them.
- Why does the dependency
B -> C not, by itself, violate third normal form? Answer: Because C is a prime attribute, appearing in at least one candidate key, even though B is not a superkey.
When evaluating Third Normal Form (3NF), you need to remember the exact condition: a dependency X→Y violates 3NF only if X is not a superkey and Y is not a prime attribute. A prime attribute is any attribute that appears in at least one candidate key. This two-part escape clause is what makes 3NF more permissive than BCNF.
Here, the candidate keys are (A,B) and (A,C). That means the prime attributes are A, B, and C — all three attributes are prime. The dependency B→C has B as its determinant, which is not a superkey. However, C appears in the candidate key (A,C), making it a prime attribute. Because the dependent attribute C is prime, the second escape condition is satisfied, and 3NF is not violated. That makes D correct.
A is wrong because it invents a rule that doesn't exist — determining an attribute within a candidate key doesn't make the determinant a superkey. Superkey status depends on whether the determinant alone can identify every tuple.
B is wrong because being part of a composite key gives no automatic 3NF pass to the determinant. The escape clause applies to the dependent attribute, not the determinant's position.
C is wrong and represents a very common misconception. 3NF applies to all functional dependencies, not only those involving whichever key was designated "primary."
As a study tip: always check both sides of a dependency when testing 3NF — is the left side a superkey, or is the right side a prime attribute? Either condition alone is enough to avoid a violation. - Which statement best describes the relation's normalization status?
Answer: It satisfies first normal form but violates second normal form because CustomerName depends on only CustomerID.
When a question asks about normalization, your job is to walk through the normal forms in order: first check 1NF, then 2NF, then 3NF. Each builds on the previous, so a violation at an earlier stage rules out satisfaction of a later one.
This relation clears 1NF easily — every attribute holds a single atomic value, and rows are distinct. The composite key is (CustomerID, PhoneNumber), meaning both columns together uniquely identify each row. Now ask the 2NF question: does every non-key attribute depend on the whole key, or only part of it? CustomerName is a non-key attribute, and it depends solely on CustomerID — not on PhoneNumber at all. That's a partial dependency, which is exactly what 2NF forbids. Answer C correctly identifies this: the relation satisfies 1NF but violates 2NF because CustomerName → CustomerID alone.
Answer A is wrong because "atomic rows" describes 1NF, not 2NF. Storing one phone per row doesn't address whether non-key attributes depend on the full key. Answer B misunderstands 1NF entirely — having multiple rows with the same CustomerID is not a 1NF violation. 1NF concerns atomic values and row uniqueness, not whether an identifier repeats across rows. Answer D is wrong because 3NF cannot be satisfied when 2NF is already violated; you must achieve each lower normal form before claiming a higher one.
A handy study tip: whenever you see a composite key, immediately check for partial dependencies — that's almost always the 2NF trap hiding in the question.
- Which redesign most directly removes the third-normal-form violation without losing the association between an order and its customer?
Answer: Create OrderHeader(OrderID, CustomerID) and Customer(CustomerID, CustomerName), joining them through CustomerID.
Whenever you see a question about normalization, ask yourself: what dependency is causing the violation, and how do I isolate it? Here, OrderHeader(OrderID, CustomerID, CustomerName) violates Third Normal Form (3NF) because CustomerName depends on CustomerID, not directly on the key OrderID. That's a transitive dependency — the exact thing 3NF prohibits.
The fix is to move CustomerName into its own table where CustomerID is the key, and retain only CustomerID in OrderHeader as a foreign key. That's exactly what A does: OrderHeader(OrderID, CustomerID) and Customer(CustomerID, CustomerName). The association between an order and its customer is preserved through CustomerID, and the transitive dependency is eliminated. This is correct.
B is flawed because it joins on CustomerName, which is not a reliable or natural key — customer names can be duplicated or change. It also doesn't fix the underlying structural problem.
C simply duplicates the original table into an "archive," which does nothing to remove the transitive dependency. Copying a problematic schema doesn't fix it.
D moves CustomerName into a table keyed by OrderID, which actually replicates the original problem — you'd still be storing the customer name alongside order data, and multiple orders for the same customer would store the same name redundantly, risking update anomalies.
A useful rule of thumb: every non-key attribute should depend on the key, the whole key, and nothing but the key. When you spot an attribute that really belongs to a different entity (like a customer's name belonging to the customer, not the order), that's your signal to decompose.
- Which decomposition removes the partial dependencies that prevent the relation from satisfying second normal form?
Answer: Create Student(StudentID, StudentName), Course(CourseID, CourseTitle), and Enrollment(StudentID, CourseID, FinalGrade).
When a question asks about Second Normal Form (2NF), your job is to identify partial dependencies — situations where a non-key attribute depends on only part of a composite key, rather than the whole key. In Enrollment(StudentID, CourseID, StudentName, CourseTitle, FinalGrade), the composite key is (StudentID, CourseID). Notice that StudentName depends only on StudentID, and CourseTitle depends only on CourseID. These are partial dependencies that violate 2NF. FinalGrade, however, genuinely requires both parts of the key — you can't determine a final grade without knowing both the student and the course.
Option A correctly resolves this by separating each partial dependency into its own relation. Student(StudentID, StudentName) captures the fact that a name depends only on a student. Course(CourseID, CourseTitle) captures the fact that a title depends only on a course. Enrollment(StudentID, CourseID, FinalGrade) keeps the fully-dependent attribute with the full composite key. This is the textbook 2NF decomposition.
Option B keeps StudentName and CourseTitle together in one table but still ties them to the composite key (StudentID, CourseID) — the partial dependencies remain, just rearranged. Option C distributes FinalGrade into both the student and course tables, which makes no logical sense — a grade belongs to a combination, not to either alone. Option D explicitly retains the names and titles in every row, which is exactly the original problem you're trying to fix.
A useful rule of thumb: for each non-key attribute, ask "does this need the whole key, or just part of it?" If it only needs part, it belongs in a separate table.
- After this change, which assessment is most accurate?
Answer: The repeating skill values are resolved, but DepartmentName still has a transitive dependency that prevents third normal form.
When tackling normalization questions, work through each normal form systematically: 1NF eliminates repeating groups, 2NF eliminates partial dependencies, and 3NF eliminates transitive dependencies. The redesign here addresses two separate problems, so you need to check both relations for remaining violations.
The original schema had two issues: Skills stored as a comma-separated list (a 1NF violation) and DepartmentName depending on DepartmentID rather than EmployeeID (a transitive dependency, which violates 3NF). Splitting Skills into EmployeeSkill(EmployeeID, Skill) correctly resolves the 1NF problem — each row now holds one atomic value. However, the Employee table still contains EmployeeID → DepartmentID → DepartmentName, meaning DepartmentName is reachable from the key only through another non-key attribute, DepartmentID. That transitive dependency keeps Employee out of 3NF. Answer A correctly identifies both facts: the repeating-group problem is fixed, but the transitive dependency remains.
Answer B is wrong because fixing the skill atomicity issue alone doesn't achieve 3NF — you must also eliminate transitive dependencies, which this design has not done. Answer C reveals a common misconception: an employee appearing in multiple rows of EmployeeSkill is perfectly normal and expected in a relational design. 1NF requires atomic values per cell, not uniqueness of an entity across rows. Answer D misidentifies the violation — DepartmentName doesn't depend on part of EmployeeID (that would be a partial dependency violating 2NF); it depends on DepartmentID, a non-key attribute, which is a transitive dependency issue.
As a study tip, always ask two separate questions: "Are all values atomic?" (1NF) and "Does every non-key attribute depend directly and only on the whole key?" (3NF). One fix rarely solves both.
- Which redesign most directly brings the design into first normal form while retaining orders that contain multiple products?
Answer: Create Order(OrderID, CustomerID) and OrderLine(OrderID, ProductID, Quantity), with one line row per product.
When a question asks about First Normal Form (1NF), focus on two rules: every column must hold a single, atomic value, and there should be no repeating groups of columns. The current Order table violates 1NF on both counts — Product1/Quantity1 and Product2/Quantity2 are a repeating group, and nulls signal that the structure is trying to encode variable-length data into fixed columns.
The correct fix is A: decompose the table into Order(OrderID, CustomerID) and OrderLine(OrderID, ProductID, Quantity). Now each row in OrderLine holds exactly one atomic product-quantity pair. An order with two products simply gets two rows in OrderLine, and the relationship is preserved through the foreign key. This eliminates the repeating group and the nulls in one clean move — which is exactly what 1NF demands.
B makes things worse, not better. Storing a comma-separated list in a single column is the textbook violation of atomicity — one cell now hides multiple values, which is the opposite of 1NF. C splits the data into two separate tables based on how many products an order has, but this doesn't eliminate repeating groups — it just hard-codes them into different relations and creates a maintenance nightmare. It also fails to scale if a third product is ever needed. D forces Product2 and Quantity2 to be non-null, which means every order must have exactly two products. This doesn't remove the repeating group; it just makes it mandatory, which is arguably worse.
A useful rule of thumb: if you catch yourself adding numbered column suffixes (Product1, Product2…), that's a signal you need a child table instead.
- A developer claims the relation must satisfy second normal form because its declared primary key has only one column. Which response is correct?
Answer: The claim is incorrect because UserName depends on part of the composite candidate key (UserID, RoleID).
Whenever you see a question about normal forms, remember that 2NF applies to all candidate keys, not just the one a developer arbitrarily designates as the primary key. A relation is in 2NF only if no non-key attribute has a partial dependency on any candidate key.
Here, UserRole has two candidate keys: the single-column RowID, and the composite (UserID, RoleID). Because UserName is functionally determined by UserID alone, it depends on part of the composite candidate key (UserID, RoleID). That partial dependency violates 2NF — full stop. The fact that the declared primary key happens to be a single column (RowID) is irrelevant to this analysis, which makes B the correct answer.
A is wrong because it rests on a fundamental misconception: 2NF is not a rule about your chosen primary key alone. It governs all candidate keys in the relation. Ignoring (UserID, RoleID) doesn't make the violation disappear.
C is wrong because a unique constraint on (UserID, RoleID) makes it a candidate key, not a foreign key. A foreign key references a primary key in another table — that's an entirely different concept.
D is wrong because 2NF has nothing to do with whether duplicate values appear in a column. Allowing duplicate RowID values would violate the primary key constraint, which is a separate concern from normalization.
The key study tip: whenever a relation has a composite candidate key, always check whether any non-key attribute depends on only part of it — that's the 2NF trap, regardless of what the declared primary key looks like.
