Featured Snippet
Prepare for database design challenges with these normalization exercises with solutions. This guide provides a complete set of problems covering first through fourth normal forms. Each scenario includes a flawed table definition, the specific anomaly present, and a step-by-step solution to eliminate redundancy and ensure data integrity in your relational schema.

Introduction to Progressive Database Exercises

Mastering relational database design requires more than just theory. You must practice transforming unstructured data into efficient, anomaly-free schemas. These exercises are designed to test your ability to identify dependencies and apply normalization rules.

We begin with simple redundancy issues and gradually move to complex scenarios involving partial and transitive dependencies. Each section builds upon the previous logic.

By the end of this assessment, you will be able to confidently decompose large tables. You will also understand how to identify functional dependencies in real-world business scenarios.

Level 1: First Normal Form (1NF) Challenges

The first step in database design is ensuring atomicity. Data must be stored in individual cells without repeating groups or arrays. If a column contains multiple values, the table violates First Normal Form.

Exercise 1: The Repeating Group Problem

Consider a “StudentRegistration” table used by a university registrar. The current structure allows a student to register for multiple courses in a single row.

The table contains the following attributes: StudentID, StudentName, CourseCode, CourseName, and Grades. The Grade column is a repeating group, storing values like “Math-90, Physics-85” in a single cell.

• Violation: The table contains repeating groups in the Grade column.
• Consequence: You cannot insert a grade without a StudentID. You cannot easily query for the Physics grade of a specific student.
• Solution: Create a new table specifically for Grades. Link it back to the StudentID.

Solution 1

To achieve 1NF, split the data into two tables: Students and Enrollments. The Students table holds basic identity information. The Enrollments table holds the relationship between students and courses.

The new Enrollments table includes StudentID, CourseCode, and a single Grade value. This structure ensures every cell contains exactly one atomic value.

This change allows you to add as many courses as a student takes without altering the table structure. It also prevents data entry errors associated with mixed data types.

Level 2: Second Normal Form (2NF) Exercises

Once a table is in 1NF, you must check for partial dependencies. A table is in Second Normal Form if it is in 1NF and every non-key attribute is fully dependent on the primary key.

Exercise 2: Partial Dependencies in Order Management

Imagine an “OrderDetails” table with the following fields: OrderID, OrderDate, CustomerID, CustomerName, ProductID, ProductName, UnitPrice, and Quantity.

Assume the primary key is a composite key consisting of OrderID and ProductID. The data looks like this: OrderID 1001 has Product 101 and Product 102 on the same OrderDate.

• Issue: CustomerName depends only on CustomerID, which depends on OrderID. ProductName depends only on ProductID.
• Analysis: Neither CustomerName nor ProductName depends on the *entire* composite primary key.
• Result: The table suffers from partial dependency anomalies.

Solution 2

Decompose the table to resolve the partial dependencies. Separate the customer information from the product information.

Create a Customer table containing CustomerID and CustomerName. Create a Product table containing ProductID, ProductName, and UnitPrice. The remaining table will store only OrderID, ProductID, OrderDate, and Quantity.

This structure ensures that every non-key attribute relies on the entire primary key of its respective table. It reduces data duplication regarding customer and product details.

Level 3: Third Normal Form (3NF) Scenarios

Third Normal Form eliminates transitive dependencies. A table is in 3NF if it is in 2NF and no non-key attribute depends on another non-key attribute.

Exercise 3: The Transitive Dependency Trap

Consider a “SupplierInventory” table with attributes: SupplierID, SupplierCity, SupplierPhone, ItemID, ItemName, and SupplierCost.

In this scenario, SupplierCity and SupplierPhone depend on SupplierID, which is not the primary key (ItemID might be part of the key). If you change a supplier’s city, you must update every row for every item they supply.

• Dependency: SupplierCity depends on SupplierID.
• Primary Key: SupplierID (assuming a simple key for this example).
• Transitivity: ItemName depends on SupplierID? No. But SupplierCity depends on SupplierID, and SupplierID is not part of a composite key that requires it.

Let us assume a simpler view: ItemID is the primary key. SupplierID is a foreign key. SupplierCity depends on SupplierID. This means ItemID determines SupplierID, and SupplierID determines SupplierCity.

This is a transitive dependency. ItemID -> SupplierID -> SupplierCity. This creates update anomalies.

Solution 3

Separate the supplier information into its own table. The “Item” table should store ItemID and ItemName. It references SupplierID as a foreign key.

The “Supplier” table stores SupplierID, SupplierCity, and SupplierPhone. By doing this, the city depends only on the supplier. Updating a supplier’s location only affects one row in the Supplier table.

This approach is essential for maintaining data consistency. It is the standard practice for almost all relational applications.

Level 4: Boyce-Codd Normal Form (BCNF) Tasks

BCNF is a stricter version of 3NF. A table is in BCNF if, for every non-trivial functional dependency A -> B, A is a candidate key. In 3NF, A just needs to be a superkey or B a prime attribute.

Exercise 4: Multiple Candidate Keys

Consider a “ClassRegistration” table for a university. It tracks which student is taught by which instructor for which course.

The table contains: StudentName, InstructorName, and CourseName. We have two rules:

1. One instructor teaches only one course (InstructorName -> CourseName).
2. One course can be taught by multiple students (CourseName -> InstructorName, implying a many-to-many relationship or specific mapping).

Assume the functional dependencies are: InstructorName -> CourseName, and CourseName -> InstructorName (assuming strict 1-to-1 for this specific problem instance).

Here, both InstructorName and CourseName act as candidate keys. However, the primary key is likely a combination of StudentName and InstructorName. If InstructorName determines CourseName, but InstructorName is not the primary key, we have a problem.

Solution 4

To achieve BCNF, decompose the table based on the functional dependency InstructorName -> CourseName.

Create a “CourseAssignment” table that holds InstructorName and CourseName. Create a “StudentRegistration” table that holds StudentName and InstructorName.

This separation ensures that the determinant of any dependency is a candidate key. In the CourseAssignment table, InstructorName is the key. In the StudentRegistration table, the combination is the key. This prevents ambiguity in data entry.

Advanced Troubleshooting: Common Mistakes in Normalization

Even with clear rules, students often make specific errors during normalization exercises with solutions. These mistakes can lead to inefficient databases or lost data integrity.

Mistake 1: Over-Normalization

Students sometimes decompose tables to the point of absurdity. They create a separate table for every single attribute. This increases the number of joins required to retrieve data.

While technically normalized, this degrades query performance. You must balance normalization with practical performance needs. Denormalization is sometimes necessary for read-heavy applications.

Mistake 2: Ignoring Composite Keys

When checking for 2NF, students often look only at single-column primary keys. They miss the subtle partial dependencies that occur in composite keys.

Always list all attributes in your composite key. Check if any non-key attribute depends on only a part of that key. If it does, you are not in 2NF.

Mistake 3: Confusing Transitive with Partial

It is common to confuse partial dependencies (dependent on part of a key) with transitive dependencies (dependent on another non-key attribute).

Remember: 2NF deals with the relationship between the primary key and non-key attributes. 3NF deals with the relationship between non-key attributes themselves.

Step-by-Step Application of Normalization Rules

Use this workflow to analyze any table in your database projects. This systematic approach ensures you catch every anomaly.

Step 1: Identify the Primary Key

Determine the unique identifier for the table. Is it a single column or a combination of columns? This is the anchor for all dependency checks.

Step 2: Check for 1NF

Verify that every column contains atomic values. Ensure no repeating groups or arrays exist. If you find lists of values, split them into new rows or new tables.

Step 3: Check for 2NF

If the primary key is composite, check for partial dependencies. Move any attribute that depends on only part of the key to a new table with that part as the primary key.

Step 4: Check for 3NF

Look for transitive dependencies. If a non-key attribute determines another non-key attribute, move the determined attribute to a new table. The determinant becomes the primary key of the new table.

Step 5: Check for BCNF

Ensure that every determinant is a candidate key. If you find a dependency where the determinant is not a candidate key, you must decompose the table further.

Conclusion: The Value of Structured Practice

Applying normalization exercises with solutions is the best way to internalize these concepts. It moves you from theoretical understanding to practical implementation skills.

By following the structured approach outlined above, you can design robust databases. You will minimize redundancy and ensure that your data remains consistent and reliable.

Regular practice with these scenarios builds the intuition needed to tackle complex schema designs in professional environments.

Key Takeaways

• 1NF eliminates repeating groups and ensures atomic values in every cell.
• 2NF removes partial dependencies by ensuring all non-key attributes depend on the full primary key.
• 3NF eliminates transitive dependencies between non-key attributes.
• BCNF handles edge cases where multiple candidate keys exist and overlap with dependencies.
• Always verify your primary keys before starting the decomposition process.
• Over-normalization can hurt performance; balance integrity with efficiency.
• Use this step-by-step guide to troubleshoot and fix common database design errors.