SQL Quiz: Normalization
10 questions · exam conditions
0:00
NormalizationQuestion 1 of 10

An order system stores each row as Order(OrderID, CustomerID, Product1, Quantity1, Product2, Quantity2). An order may contain one or two products, so the second product columns are sometimes null.

Which redesign most directly brings the design into first normal form while retaining orders that contain multiple products?

Create Order(OrderID, CustomerID) and OrderLine(OrderID, ProductID, Quantity), with one line row per product.
Keep one order row and replace the product columns with a comma-separated list of product and quantity values.
Create separate OneProductOrder and TwoProductOrder relations, each having the appropriate number of product columns.
Keep the current columns but require Product2 and Quantity2 to be non-null for every stored order.
← Back to quizzes

SQL Quiz

SQL Quiz: Normalization

Practice Normalization in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Normalization, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An order system stores each row as Order(OrderID, CustomerID, Product1, Quantity1, Product2, Quantity2). An order may contain one or two products, so the second product columns are sometimes null.

Which redesign most directly brings the design into first normal form while retaining orders that contain multiple products?

  1. Create Order(OrderID, CustomerID) and OrderLine(OrderID, ProductID, Quantity), with one line row per product. (correct answer)
  2. Keep one order row and replace the product columns with a comma-separated list of product and quantity values.
  3. Create separate OneProductOrder and TwoProductOrder relations, each having the appropriate number of product columns.
  4. Keep the current columns but require Product2 and Quantity2 to be non-null for every stored order.
Explanation: 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.

Question 2

A relation UserRole(RowID, UserID, RoleID, UserName) uses RowID as its primary key. A unique constraint on (UserID, RoleID) makes that pair another candidate key. Each UserID determines exactly one UserName.

A developer claims the relation must satisfy second normal form because its declared primary key has only one column. Which response is correct?

  1. The claim is correct because second normal form considers only the key selected as the declared primary key.
  2. The claim is incorrect because UserName depends on part of the composite candidate key (UserID, RoleID). (correct answer)
  3. The claim is correct because the unique constraint makes (UserID, RoleID) a foreign key rather than a candidate key.
  4. The claim is incorrect only if two rows are permitted to contain the same RowID value.
Explanation: 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.

Question 3

A relation R(A, B, C) has candidate keys (A, B) and (A, C). It also has the dependency B -> C. No other attributes exist.

Why does the dependency B -> C not, by itself, violate third normal form?

  1. Because B becomes a superkey whenever it determines any attribute that belongs to a candidate key.
  2. Because every dependency whose determinant is part of a composite key automatically satisfies third normal form.
  3. Because only dependencies involving the primary key are considered when evaluating third normal form.
  4. Because C is a prime attribute, appearing in at least one candidate key, even though B is not a superkey. (correct answer)
Explanation: When evaluating Third Normal Form (3NF), you need to remember the exact condition: a dependency X→YX \rightarrow Y violates 3NF only if XX is not a superkey and YY 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)(A, B) and (A,C)(A, C). That means the prime attributes are AA, BB, and CC — all three attributes are prime. The dependency B→CB \rightarrow C has BB as its determinant, which is not a superkey. However, CC appears in the candidate key (A,C)(A, C), making it a prime attribute. Because the dependent attribute CC 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.

Question 4

A product relation stores ProductID, ProductName, SupplierID, and SupplierPhone. One supplier may provide many products, so the same phone number appears in many rows. The system cannot store a new supplier until that supplier provides a product.

Which normalization change best addresses both anomalies described?

  1. Combine SupplierID and SupplierPhone into one formatted text value stored with each product.
  2. Move product names to ProductName(ProductID, ProductName) and retain supplier phones with each product.
  3. Add a surrogate row identifier while continuing to store the supplier phone in every product row.
  4. Move supplier details to Supplier(SupplierID, SupplierPhone) and retain SupplierID with each product. (correct answer)
Explanation: 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.

Question 5

A relation ShipmentLine(ShipmentID, ProductID, WarehouseID, WarehouseCity) has the composite key (ShipmentID, ProductID). The full key determines WarehouseID; neither key column alone determines it. Each WarehouseID determines one WarehouseCity, and all values are atomic.

What is the highest normal form guaranteed by the stated dependencies?

  1. First normal form, because every relation with a composite key necessarily has a partial dependency.
  2. Second normal form, because dependencies use the full key but WarehouseCity is transitively dependent on it. (correct answer)
  3. Third normal form, because WarehouseID depends on the complete composite key rather than one key column.
  4. No normal form, because storing a warehouse identifier and its city in the same relation is always invalid.
Explanation: 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.

Question 6

A reporting team copies CustomerRegion into every sales row even though CustomerID determines CustomerRegion and customer data already exists in a normalized relation. The team states that avoiding a join makes reports faster.

Which evaluation of this design decision is most accurate?

  1. It violates first normal form because the same region value may occur in more than one sales row.
  2. It preserves third normal form because query performance is the deciding criterion for normal-form compliance.
  3. It is deliberate denormalization that may improve reads but reintroduces redundancy and requires a consistency strategy. (correct answer)
  4. It eliminates update anomalies because each sales row retains its own independent copy of the region.
Explanation: 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.

Question 7

A relation Employee(EmployeeID, DepartmentID, DepartmentName, Skills) stores Skills as a comma-separated list. EmployeeID is the key, and each DepartmentID identifies one DepartmentName. A designer creates Employee(EmployeeID, DepartmentID, DepartmentName) and EmployeeSkill(EmployeeID, Skill), with one skill per row.

After this change, which assessment is most accurate?

  1. The repeating skill values are resolved, but DepartmentName still has a transitive dependency that prevents third normal form. (correct answer)
  2. The design is in third normal form because every skill is now stored in an individual relation row.
  3. The design still violates first normal form because an employee may appear in several EmployeeSkill rows.
  4. The design violates second normal form because DepartmentName depends on only part of the EmployeeID key.
Explanation: 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.

Question 8

A relation Enrollment(StudentID, CourseID, StudentName, CourseTitle, FinalGrade) has the composite key (StudentID, CourseID). Each student has one name, each course has one title, and a final grade applies to a particular student-course combination.

Which decomposition removes the partial dependencies that prevent the relation from satisfying second normal form?

  1. Create Student(StudentID, StudentName), Course(CourseID, CourseTitle), and Enrollment(StudentID, CourseID, FinalGrade). (correct answer)
  2. Create StudentCourse(StudentID, CourseID, StudentName, CourseTitle) and Grade(StudentID, CourseID, FinalGrade).
  3. Create Student(StudentID, StudentName, FinalGrade) and Course(CourseID, CourseTitle, FinalGrade).
  4. Create Enrollment(StudentID, CourseID, FinalGrade) and retain both names and titles in every enrollment row.
Explanation: 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.

Question 9

A relation CustomerPhone(CustomerID, CustomerName, PhoneNumber) stores one phone number per row. Its key is (CustomerID, PhoneNumber), and each CustomerID determines exactly one CustomerName. Customers with several phone numbers therefore have several rows.

Which statement best describes the relation's normalization status?

  1. It satisfies second normal form because each phone number is stored in a separate atomic row.
  2. It violates first normal form because several rows may contain the same customer identifier.
  3. It satisfies first normal form but violates second normal form because CustomerName depends on only CustomerID. (correct answer)
  4. It satisfies third normal form because CustomerName is consistent for every occurrence of a customer.
Explanation: 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.

Question 10

A relation OrderHeader(OrderID, CustomerID, CustomerName) uses OrderID as its key. Each order belongs to one customer, and each customer identifier determines one customer name.

Which redesign most directly removes the third-normal-form violation without losing the association between an order and its customer?

  1. Create OrderHeader(OrderID, CustomerID) and Customer(CustomerID, CustomerName), joining them through CustomerID. (correct answer)
  2. Create OrderHeader(OrderID, CustomerName) and Customer(CustomerID, CustomerName), joining them through CustomerName.
  3. Create OrderHeader(OrderID, CustomerID, CustomerName) and a second copy named CustomerArchive.
  4. Create OrderHeader(OrderID, CustomerID) and CustomerName(OrderID, CustomerName), joining them through OrderID.
Explanation: 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.