programming
beginner
10 sample questions
Database Normalization MCQ Practice Test
Reducing redundancy by organizing data into tables.
Understanding 1:1, 1:N, and N:M relationships.
Q1. You have a database schema designed to track scientific publications, including authors, journals, and keywords. The `Publication` table has attributes: `publicationID`, `title`, `journalID`, `publicationDate`, and a multi-valued attribute `authors` represented as a JSON array of author IDs. The `Journal` table has `journalID`, `journalName`, and `ISSN`. The `Author` table contains `authorID`, `authorName`, and `affiliation`. To achieve Boyce-Codd Normal Form (BCNF), which of the following refactoring strategies is MOST appropriate and efficient, considering potential performance impacts of frequent lookups and joins?
-
A. Create a separate table `PublicationAuthors` with `publicationID` and `authorID`, enforcing a foreign key constraint on both. Maintain the JSON array in `Publication` for convenience, but ensure all integrity is enforced through the new table. ✓
-
B. Normalize to 4NF by splitting the JSON array in `Publication` into separate tables for each author, linking back to `Publication` via `publicationID`.
-
C. Maintain the current schema; BCNF is unnecessary given the JSON array's structure.
-
D. Introduce a new table `KeywordPublication` with `keyword` and `publicationID` and eliminate the current keyword field from the `Publication` table. Then, create a separate `PublicationAuthor` table as described in Option A.
Explanation: Option A provides the most efficient path to BCNF. While Option B achieves 4NF, it's significantly more complex and likely less performant for frequent queries involving authors and publications. Option C ignores the normalization issue. Option D addresses a separate normalization concern (keywords) but doesn't fully address the multi-valued author attribute problem. The approach in Option A balances normalization requirements with practical performance considerations; the JSON array can provide performance benefits for certain queries while the linked table guarantees referential integrity and avoids redundancy.
Q2. A database designer has a table named “orders” with the following columns: “order_id”, “customer_id”, “product_id”, and “order_date”. The designer wants to normalize this table by moving the “customer_id” column to a separate table. However, there is a dependency between the “order_id” column and the “customer_id” column. Which of the following normalization techniques can be used to resolve this dependency?
-
A. Insertion, deletion, and update anomalies can be resolved using the 3NF normalization technique.
-
B. Functional dependencies can be resolved using the BCNF (Boyce-Codd Normal Form) normalization technique. ✓
-
C. Transitive dependencies can be resolved using the 4NF (Fourth Normal Form) normalization technique.
-
D. Mutual dependencies can be resolved using the 5NF (Fifth Normal Form) normalization technique.
Explanation: In this scenario, the designer wants to move the customer_id column to a separate table, but there is a dependency between the order_id column and the customer_id column. This is an example of a functional dependency, where the order_id column determines the customer_id column. The BCNF normalization technique is used to resolve functional dependencies, ensuring that each non-key attribute depends on the entire primary key, not just one part of it.
Q3. A university stores course grades in a table with columns StudentID, CourseID, and Grade, where each student receives exactly one grade per course. What is the most appropriate primary key for this table?
-
A. StudentID alone
-
B. CourseID alone
-
C. (StudentID, CourseID) ✓
-
D. Grade
Explanation: Neither StudentID (a student takes many courses) nor CourseID (a course has many students) is unique on its own, and Grade carries no identity — but the pair (StudentID, CourseID) identifies exactly one row each, making it the natural composite primary key. The original sample data (duplicate Column A with different C, and B dependent on A) actually demanded 2NF decomposition, which made 'the' 3NF key ill-defined.
Q4. A database designer has a table with the following structure:
| Customer ID (PK) | Name | Order ID (FK) | Order Date |
| --- | --- | --- | --- |
| 1 | John | 101 | 2022-01-01 |
| 2 | Jane | 102 | 2022-01-15 |
| 1 | John | 103 | 2022-02-01 |
Which of the following is a correct normalization step to eliminate the redundancy in the Order Date field?
-
A. Create a separate table for orders and store the order date in the orders table ✓
-
B. Create a separate table for customers and store the order date in the customers table
-
C. Create a separate table for orders and modify the existing table to store the order date in the customer table
-
D. Create a separate table for customers and modify the existing table to store the order date in the orders table
Explanation: In this scenario, the Order Date field is not dependent on the Customer ID, but rather on the Order ID. Therefore, it should be stored in a separate table for orders, which is a correct normalization step to eliminate redundancy.
Q5. A database designer has a table called “Employees” with columns “EmployeeID”, “EmployeeName”, and “EmployeeDependents”, where EmployeeDependents stores a comma-separated list of each employee’s dependents. Which change best normalizes this design?
-
A. Split the EmployeeDependents column into separate columns for Dependent1, Dependent2, and Dependent3.
-
B. Keep the comma-separated list but add an index on the EmployeeDependents column.
-
C. Create a separate Dependents table with a one-to-many relationship to the Employees table. ✓
-
D. Create a second copy of the Employees table, one row per dependent.
Explanation: A comma-separated list in one column is a multi-valued attribute, violating first normal form. The correct fix is a separate Dependents table keyed by EmployeeID (a one-to-many relationship). Splitting into Dependent1/2/3 columns still repeats a attribute group and caps the count; indexing or duplicating rows do not remove the violation.
Q6. A database designer has a table called 'Orders' with columns 'OrderID', 'CustomerID', 'OrderDate', and 'ProductID'. The designer wants to normalize the table to avoid data redundancy. Which of the following is the correct step to take?
-
A. Split the 'Orders' table into two tables: 'OrderInfo' and 'OrderDetails'. ✓
-
B. Create a separate table for 'CustomerInfo' and establish a relationship with the 'Orders' table.
-
C. Remove the 'ProductID' column from the 'Orders' table to avoid data redundancy.
-
D. Move the 'OrderDate' column to a separate table called 'OrderHistory'.
Explanation: To normalize the table, the designer should split the 'Orders' table into two tables: 'OrderInfo' and 'OrderDetails'. The 'OrderInfo' table would contain columns like 'OrderID', 'CustomerID', and 'OrderDate', while the 'OrderDetails' table would contain columns like 'OrderID' and 'ProductID'. This helps to avoid data redundancy and improves data integrity.
Q7. A company has a database with an "orders" table containing customer information, order details, and a foreign key referencing the "customers" table. However, the "orders" table also stores customer addresses. Which of the following is the best course of action to normalize the database?
-
A. Split the customer address into separate tables for billing and shipping, each with a foreign key referencing the "customers" table. ✓
-
B. Move the customer address to the "customers" table, eliminating the need for a separate "orders" table.
-
C. Create a separate "addresses" table with a foreign key referencing both the "customers" and "orders" tables.
-
D. Remove the customer address from the "orders" table and store it only in the "customers" table, as it is not necessary for orders.
Explanation: This option is the best course of action because it properly normalizes the data by removing repeating groups and eliminating data redundancy. It also allows for efficient querying and maintenance of customer addresses.
Q8. A database designer has a table named "Orders" with the following attributes: OrderID, CustomerID, OrderDate, ProductID, and Quantity. To normalize the table, which of the following options is a correct step?
-
A. Split the OrderDate attribute into separate Date and Time attributes.
-
B. Create a separate table for the Customer entity and establish a one-to-many relationship with the Orders table. ✓
-
C. Remove the ProductID attribute as it is not essential for the Orders table.
-
D. Create a separate table for the Product entity and establish a many-to-many relationship with the Orders table using a bridge table.
Explanation: Normalizing removes redundancy and transitive dependencies: customer details should live once in a Customer table linked to Orders through CustomerID in a one-to-many relationship, so they don't repeat for every order (this serves 2NF/3NF, not 1NF - 1NF concerns atomic values). Splitting OrderDate into Date and Time is not required normalization, dropping ProductID loses essential data, and Orders-to-Product is one-to-many from the product side, not many-to-many.
Q9. In a database with a many-to-many relationship between customers and orders, which of the following normal forms addresses the issue of data redundancy in the customer table when storing order information?
-
A. First Normal Form (1NF) eliminates repeating groups.
-
B. Second Normal Form (2NF) eliminates partial dependencies.
-
C. Boyce-Codd Normal Form (BCNF) eliminates transitive dependencies.
-
D. Fourth Normal Form (4NF) eliminates multi-level dependencies in a many-to-many relationship. ✓
Explanation: In a many-to-many relationship between customers and orders, Fourth Normal Form (4NF) is necessary to eliminate data redundancy in the customer table when storing order information. This is because each customer can have multiple orders, and each order can be associated with multiple customers, resulting in multi-level dependencies. By applying 4NF, the order information is stored in a separate table, eliminating the need for repeating groups in the customer table and addressing the issue of data redundancy.
Q10. In database normalization, a 'partial dependency' exists when:
-
A. A non-key attribute depends on only part of a composite primary key ✓
-
B. A non-key attribute depends on the whole primary key
-
C. A non-key attribute depends on another non-key attribute
-
D. A non-key attribute is completely independent of the key
Explanation: A partial dependency occurs when a non-key attribute is determined by only a portion of a composite key — for example, in a table keyed by (order_id, product_id), a product_name that depends only on product_id. Removing partial dependencies is exactly what second normal form (2NF) requires; transitive dependencies (non-key depending on non-key) are removed for 3NF.
That was just a sample. Sign up to unlock the full question bank with timed tests and certificates.
Sign Up Free