Understand database normalization forms and how to eliminate data redundancy
When designing a database, storing all information in a single massive table might seem simple at first, but it quickly becomes problematic. You'll encounter duplicate data, inconsistencies, and complex updates. Database normalization solves these problems by structuring your database logically and efficiently.
Normalization is a systematic approach to decomposing tables to eliminate data redundancy and undesirable characteristics. In this lesson, you'll learn about the first three normal forms (1NF, 2NF, and 3NF) and how to apply them to create well-structured, maintainable database schemas.
By mastering normalization principles, you'll create database designs that maintain data integrity, reduce storage requirements, and simplify data maintenance. Let's explore how to transform chaotic data into clean, structured, and efficient database tables!
Database Normalization is the process of structuring a database in accordance with a series of "normal forms" to reduce data redundancy and improve data integrity.
Developed by Edgar F. Codd in the 1970s, normalization provides a systematic way to ensure that a database structure is suitable for general-purpose querying and free of certain undesirable characteristics: insertion, update, and deletion anomalies.
Normalization delivers several critical benefits:
| Benefit | Description |
|---------|-------------|
| Minimize Redundancy | Store each piece of information in only one place |
| Avoid Anomalies | Prevent insertion, update, and deletion problems |
| Ensure Data Consistency | Make it impossible for data to become inconsistent |
| Simplify Maintenance | Make the database easier to modify and extend |
| Improve Query Performance | (For many types of queries, but not all) |
Poorly designed databases suffer from three types of anomalies:
Consider this example of an unnormalized table tracking university courses and enrollments:
1COURSE_ENROLLMENTS Table:
2+------------+-------------+---------------+--------------+-----------------+-------------+----------------+
3| CourseID | CourseName | Department | StudentID | StudentName | EnrollDate | InstructorName |
4+------------+-------------+---------------+--------------+-----------------+-------------+----------------+
5| CS101 | Intro to CS | Computer Sci | S1001 | John Smith | 2023-01-10 | Dr. Brown |
6| CS101 | Intro to CS | Computer Sci | S1002 | Mary Johnson | 2023-01-15 | Dr. Brown |
7| CS101 | Intro to CS | Computer Sci | S1003 | Steve Williams | 2023-01-12 | Dr. Brown |
8| MATH201 | Calculus | Mathematics | S1001 | John Smith | 2023-01-14 | Dr. Wilson |
9| MATH201 | Calculus | Mathematics | S1004 | Susan Miller | 2023-01-18 | Dr. Wilson |
10| PHYS101 | Physics I | Physics | S1002 | Mary Johnson | 2023-01-20 | Dr. Roberts |
11+------------+-------------+---------------+--------------+-----------------+-------------+----------------+Problems with this table:
Normalization typically involves these steps:
In most business applications, reaching 3NF is sufficient. Higher normal forms address more specialized cases that we'll discuss in advanced courses.
A table is in First Normal Form (1NF) when:
In other words, 1NF eliminates repeating groups by ensuring each cell contains exactly one value.
Let's look at a table that violates 1NF and fix it:
Original Table (Violates 1NF):
1STUDENT_COURSES Table:
2+------------+------------------+------------------------------------------+
3| StudentID | StudentName | Courses |
4+------------+------------------+------------------------------------------+
5| S1001 | John Smith | CS101, MATH201 |
6| S1002 | Mary Johnson | CS101, PHYS101 |
7| S1003 | Steve Williams | CS101 |
8| S1004 | Susan Miller | MATH201 |
9+------------+------------------+------------------------------------------+Problem: The "Courses" column violates 1NF because it contains multiple values.
Solution 1: Expand the table
1STUDENT_COURSES_1NF Table:
2+------------+------------------+------------+
3| StudentID | StudentName | CourseID |
4+------------+------------------+------------+
5| S1001 | John Smith | CS101 |
6| S1001 | John Smith | MATH201 |
7| S1002 | Mary Johnson | CS101 |
8| S1002 | Mary Johnson | PHYS101 |
9| S1003 | Steve Williams | CS101 |
10| S1004 | Susan Miller | MATH201 |
11+------------+------------------+------------+This table is now in 1NF, but notice that student information is still duplicated.
Solution 2: Create separate tables (better approach)
1STUDENTS Table:
2+------------+------------------+
3| StudentID | StudentName |
4+------------+------------------+
5| S1001 | John Smith |
6| S1002 | Mary Johnson |
7| S1003 | Steve Williams |
8| S1004 | Susan Miller |
9+------------+------------------+
10
11ENROLLMENTS Table:
12+------------+------------+
13| StudentID | CourseID |
14+------------+------------+
15| S1001 | CS101 |
16| S1001 | MATH201 |
17| S1002 | CS101 |
18| S1002 | PHYS101 |
19| S1003 | CS101 |
20| S1004 | MATH201 |
21+------------+------------+Original Table (Violates 1NF):
1CUSTOMERS Table:
2+------------+------------------+---------------------------------------------+
3| CustomerID | CustomerName | Address |
4+------------+------------------+---------------------------------------------+
5| C1001 | ABC Company | 123 Main St, Suite 200, New York, NY 10001 |
6| C1002 | XYZ Corporation | 456 Park Ave, Chicago, IL 60601 |
7+------------+------------------+---------------------------------------------+Problem: The "Address" column contains multiple pieces of information.
Solution: Make address components atomic
1CUSTOMERS_1NF Table:
2+------------+------------------+---------------+------------+------------+-------------+
3| CustomerID | CustomerName | Street | Suite | City | State | Zip |
4+------------+------------------+---------------+------------+------------+-------------+------------+
5| C1001 | ABC Company | 123 Main St | Suite 200 | New York | NY | 10001 |
6| C1002 | XYZ Corporation | 456 Park Ave | NULL | Chicago | IL | 60601 |
7+------------+------------------+---------------+------------+------------+-------------+------------+A table in 1NF can still have:
This is why we need to apply further normalization techniques.
A table is in Second Normal Form (2NF) when:
In simpler terms, 2NF ensures that all non-key columns depend on the entire primary key, not just part of it. This is especially important when dealing with composite primary keys.
A partial dependency occurs when a non-key attribute depends on only part of a composite primary key. 2NF requires eliminating these partial dependencies.
2NF only applies to tables with composite primary keys. If your table has a single-column primary key, it's automatically in 2NF (assuming it's already in 1NF).
Let's examine a table that's in 1NF but violates 2NF:
1NF Table (Violates 2NF):
1ENROLLMENTS Table:
2+------------+------------+-------------+---------------+-----------------+
3| StudentID | CourseID | StudentName | CourseName | Department |
4+------------+------------+-------------+---------------+-----------------+
5| S1001 | CS101 | John Smith | Intro to CS | Computer Science|
6| S1001 | MATH201 | John Smith | Calculus | Mathematics |
7| S1002 | CS101 | Mary Johnson| Intro to CS | Computer Science|
8| S1002 | PHYS101 | Mary Johnson| Physics I | Physics |
9| S1003 | CS101 | Steve Wilson| Intro to CS | Computer Science|
10| S1004 | MATH201 | Susan Miller| Calculus | Mathematics |
11+------------+------------+-------------+---------------+-----------------+Primary Key: (StudentID, CourseID) - composite key
Partial Dependencies:
Solution: Decompose into three tables
1STUDENTS Table:
2+------------+-------------+
3| StudentID | StudentName |
4+------------+-------------+
5| S1001 | John Smith |
6| S1002 | Mary Johnson|
7| S1003 | Steve Wilson|
8| S1004 | Susan Miller|
9+------------+-------------+
10
11COURSES Table:
12+------------+---------------+-----------------+
13| CourseID | CourseName | Department |
14+------------+---------------+-----------------+
15| CS101 | Intro to CS | Computer Science|
16| MATH201 | Calculus | Mathematics |
17| PHYS101 | Physics I | Physics |
18+------------+---------------+-----------------+
19
20ENROLLMENTS Table:
21+------------+------------+
22| StudentID | CourseID |
23+------------+------------+
24| S1001 | CS101 |
25| S1001 | MATH201 |
26| S1002 | CS101 |
27| S1002 | PHYS101 |
28| S1003 | CS101 |
29| S1004 | MATH201 |
30+------------+------------+Now each table is in 2NF because:
1NF Table (Violates 2NF):
1ORDER_DETAILS Table:
2+----------+------------+-------------+---------------+--------------+----------+
3| OrderID | ProductID | OrderDate | ProductName | ProductPrice | Quantity |
4+----------+------------+-------------+---------------+--------------+----------+
5| O1001 | P100 | 2023-03-15 | Laptop | 999.99 | 1 |
6| O1001 | P200 | 2023-03-15 | Mouse | 24.99 | 2 |
7| O1002 | P100 | 2023-03-16 | Laptop | 999.99 | 1 |
8| O1002 | P300 | 2023-03-16 | Keyboard | 49.99 | 1 |
9| O1003 | P400 | 2023-03-17 | Monitor | 249.99 | 2 |
10+----------+------------+-------------+---------------+--------------+----------+Primary Key: (OrderID, ProductID) - composite key
Partial Dependencies:
Solution: Decompose into three tables
1ORDERS Table:
2+----------+-------------+
3| OrderID | OrderDate |
4+----------+-------------+
5| O1001 | 2023-03-15 |
6| O1002 | 2023-03-16 |
7| O1003 | 2023-03-17 |
8+----------+-------------+
9
10PRODUCTS Table:
11+------------+---------------+--------------+
12| ProductID | ProductName | ProductPrice |
13+------------+---------------+--------------+
14| P100 | Laptop | 999.99 |
15| P200 | Mouse | 24.99 |
16| P300 | Keyboard | 49.99 |
17| P400 | Monitor | 249.99 |
18+------------+---------------+--------------+
19
20ORDER_DETAILS Table:
21+----------+------------+----------+
22| OrderID | ProductID | Quantity |
23+----------+------------+----------+
24| O1001 | P100 | 1 |
25| O1001 | P200 | 2 |
26| O1002 | P100 | 1 |
27| O1002 | P300 | 1 |
28| O1003 | P400 | 2 |
29+----------+------------+----------+A table in 2NF can still have:
These issues are addressed in Third Normal Form (3NF).
A table is in Third Normal Form (3NF) when:
In other words, non-key columns must depend directly on the primary key, not on other non-key columns.
A transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key. The chain of dependencies creates an indirect relationship between the primary key and some attributes.
Formally: If A → B and B → C, then A → C (transitively).
Let's look at a table that's in 2NF but violates 3NF:
2NF Table (Violates 3NF):
1COURSES Table:
2+------------+---------------+-----------------+-------------------+
3| CourseID | CourseName | DepartmentID | DepartmentName |
4+------------+---------------+-----------------+-------------------+
5| CS101 | Intro to CS | DEPT1 | Computer Science |
6| CS102 | Advanced CS | DEPT1 | Computer Science |
7| MATH201 | Calculus | DEPT2 | Mathematics |
8| PHYS101 | Physics I | DEPT3 | Physics |
9| PHYS102 | Physics II | DEPT3 | Physics |
10+------------+---------------+-----------------+-------------------+Primary Key: CourseID
Transitive Dependency:
Solution: Decompose into two tables
1DEPARTMENTS Table:
2+--------------+-------------------+
3| DepartmentID | DepartmentName |
4+--------------+-------------------+
5| DEPT1 | Computer Science |
6| DEPT2 | Mathematics |
7| DEPT3 | Physics |
8+--------------+-------------------+
9
10COURSES Table:
11+------------+---------------+-----------------+
12| CourseID | CourseName | DepartmentID |
13+------------+---------------+-----------------+
14| CS101 | Intro to CS | DEPT1 |
15| CS102 | Advanced CS | DEPT1 |
16| MATH201 | Calculus | DEPT2 |
17| PHYS101 | Physics I | DEPT3 |
18| PHYS102 | Physics II | DEPT3 |
19+------------+---------------+-----------------+Now each table is in 3NF because:
2NF Table (Violates 3NF):
1EMPLOYEES Table:
2+------------+-------------+-----------------+----------------+
3| EmployeeID | EmployeeName| ZipCode | City |
4+------------+-------------+-----------------+----------------+
5| E1001 | John Smith | 10001 | New York |
6| E1002 | Mary Johnson| 90210 | Beverly Hills |
7| E1003 | Robert Davis| 10001 | New York |
8| E1004 | Susan Wilson| 60601 | Chicago |
9+------------+-------------+-----------------+----------------+Primary Key: EmployeeID
Transitive Dependency:
Solution: Decompose into two tables
1LOCATIONS Table:
2+-----------+----------------+
3| ZipCode | City |
4+-----------+----------------+
5| 10001 | New York |
6| 90210 | Beverly Hills |
7| 60601 | Chicago |
8+-----------+----------------+
9
10EMPLOYEES Table:
11+------------+-------------+-----------+
12| EmployeeID | EmployeeName| ZipCode |
13+------------+-------------+-----------+
14| E1001 | John Smith | 10001 |
15| E1002 | Mary Johnson| 90210 |
16| E1003 | Robert Davis| 10001 |
17| E1004 | Susan Wilson| 60601 |
18+------------+-------------+-----------+Calculated fields technically violate 3NF but are sometimes kept for performance:
1ORDERS Table:
2+----------+-------------+-----------+--------+
3| OrderID | OrderDate | Subtotal | Tax | Total |
4+----------+-------------+-----------+--------+---------+
5| O1001 | 2023-03-15 | 100.00 | 8.00 | 108.00 |
6+----------+-------------+-----------+--------+---------+Here, Total = Subtotal + Tax (derived). In strict 3NF, Total shouldn't be stored.
Some tables keep historical values that would otherwise change:
1ORDER_ITEMS Table:
2+----------+------------+---------------+--------------+----------+
3| OrderID | ProductID | ProductName | PriceAtOrder | Quantity |
4+----------+------------+---------------+--------------+----------+
5| O1001 | P100 | Laptop | 999.99 | 1 |
6+----------+------------+---------------+--------------+----------+PriceAtOrder captures the price at the time of order, even though it depends on ProductID.
For most business applications, 3NF is sufficient. There are higher normal forms:
However, these address increasingly specialized cases. In practice, most database designers aim for 3NF and consider the specific needs of their applications.
Let's walk through a complete normalization example from start to finish using a realistic scenario.
Imagine a school tracking student enrollments in an Excel-like format:
SCHOOL_DATA (Unnormalized):
1+------------+---------------+---------------+------------------+-------------------+-----------------+------------------+
2| StudentID | StudentName | ContactNumbers| Course1 | Course1Instructor | Course2 | Course2Instructor|
3+------------+---------------+---------------+------------------+-------------------+-----------------+------------------+
4| S1001 | John Smith | 555-1234, | CS101: Intro CS, | Dr. Jane Brown, | MATH201: | Dr. Robert |
5| | | 555-5678 | TR 10-11:30, | ComputerScience | Calculus, | Wilson, |
6| | | | Room H101 | Dept | MWF 9-10, | Mathematics |
7| | | | | | Room M105 | Dept |
8+------------+---------------+---------------+------------------+-------------------+-----------------+------------------+
9| S1002 | Mary Johnson | 555-9876 | PHYS101: Physics,| Dr. Alan Roberts, | CS101: Intro CS,| Dr. Jane Brown, |
10| | | | MWF 11-12, | Physics Dept | TR 10-11:30, | ComputerScience |
11| | | | Room P202 | | Room H101 | Dept |
12+------------+---------------+---------------+------------------+-------------------+-----------------+------------------+
13| S1003 | Steve Wilson | 555-4321, | CS101: Intro CS, | Dr. Jane Brown, | NULL | NULL |
14| | | 555-8765, | TR 10-11:30, | ComputerScience | | |
15| | | 555-2468 | Room H101 | Dept | | |
16+------------+---------------+---------------+------------------+-------------------+-----------------+------------------+This table has numerous issues:
To achieve 1NF, we need to eliminate repeating groups and ensure atomic values:
STUDENTS Table (1NF):
1+------------+---------------+
2| StudentID | StudentName |
3+------------+---------------+
4| S1001 | John Smith |
5| S1002 | Mary Johnson |
6| S1003 | Steve Wilson |
7+------------+---------------+STUDENT_PHONES Table (1NF):
1+------------+----------------+
2| StudentID | PhoneNumber |
3+------------+----------------+
4| S1001 | 555-1234 |
5| S1001 | 555-5678 |
6| S1002 | 555-9876 |
7| S1003 | 555-4321 |
8| S1003 | 555-8765 |
9| S1003 | 555-2468 |
10+------------+----------------+ENROLLMENTS Table (1NF):
1+------------+------------+------------------+-------------------+--------------+-----------------+
2| StudentID | CourseID | CourseName | Schedule | Room | InstructorName | InstructorDept |
3+------------+------------+------------------+-------------------+--------------+-----------------+
4| S1001 | CS101 | Intro CS | TR 10-11:30 | H101 | Dr. Jane Brown | ComputerScience |
5| S1001 | MATH201 | Calculus | MWF 9-10 | M105 | Dr. Robert Wilson| Mathematics |
6| S1002 | PHYS101 | Physics | MWF 11-12 | P202 | Dr. Alan Roberts| Physics |
7| S1002 | CS101 | Intro CS | TR 10-11:30 | H101 | Dr. Jane Brown | ComputerScience |
8| S1003 | CS101 | Intro CS | TR 10-11:30 | H101 | Dr. Jane Brown | ComputerScience |
9+------------+------------+------------------+-------------------+--------------+-----------------+The ENROLLMENTS table has a composite primary key (StudentID, CourseID). We need to remove partial dependencies:
COURSES Table (2NF):
1+------------+------------------+-------------------+--------------+
2| CourseID | CourseName | Schedule | Room |
3+------------+------------------+-------------------+--------------+
4| CS101 | Intro CS | TR 10-11:30 | H101 |
5| MATH201 | Calculus | MWF 9-10 | M105 |
6| PHYS101 | Physics | MWF 11-12 | P202 |
7+------------+------------------+-------------------+--------------+INSTRUCTORS Table (2NF):
1+------------------+-----------------+
2| InstructorName | InstructorDept |
3+------------------+-----------------+
4| Dr. Jane Brown | ComputerScience |
5| Dr. Robert Wilson| Mathematics |
6| Dr. Alan Roberts | Physics |
7+------------------+-----------------+COURSE_INSTRUCTORS Table (2NF):
1+------------+------------------+
2| CourseID | InstructorName |
3+------------+------------------+
4| CS101 | Dr. Jane Brown |
5| MATH201 | Dr. Robert Wilson|
6| PHYS101 | Dr. Alan Roberts |
7+------------+------------------+ENROLLMENTS Table (2NF):
1+------------+------------+
2| StudentID | CourseID |
3+------------+------------+
4| S1001 | CS101 |
5| S1001 | MATH201 |
6| S1002 | PHYS101 |
7| S1002 | CS101 |
8| S1003 | CS101 |
9+------------+------------+Now we need to eliminate transitive dependencies:
DEPARTMENTS Table (3NF):
1+------------------+----------------+
2| DepartmentID | DepartmentName |
3+------------------+----------------+
4| DEPT_CS | ComputerScience|
5| DEPT_MATH | Mathematics |
6| DEPT_PHYS | Physics |
7+------------------+----------------+INSTRUCTORS Table (3NF):
1+------------------+------------------+
2| InstructorID | InstructorName | DepartmentID |
3+------------------+------------------+------------------+
4| INST1 | Dr. Jane Brown | DEPT_CS |
5| INST2 | Dr. Robert Wilson| DEPT_MATH |
6| INST3 | Dr. Alan Roberts | DEPT_PHYS |
7+------------------+------------------+------------------+After applying 1NF, 2NF, and 3NF, we have these tables:
STUDENTS:
1+------------+---------------+
2| StudentID | StudentName |
3+------------+---------------+
4| S1001 | John Smith |
5| S1002 | Mary Johnson |
6| S1003 | Steve Wilson |
7+------------+---------------+STUDENT_PHONES:
1+------------+----------------+
2| StudentID | PhoneNumber |
3+------------+----------------+
4| S1001 | 555-1234 |
5| S1001 | 555-5678 |
6| S1002 | 555-9876 |
7| S1003 | 555-4321 |
8| S1003 | 555-8765 |
9| S1003 | 555-2468 |
10+------------+----------------+DEPARTMENTS:
1+---------------+------------------+
2| DepartmentID | DepartmentName |
3+---------------+------------------+
4| DEPT_CS | ComputerScience |
5| DEPT_MATH | Mathematics |
6| DEPT_PHYS | Physics |
7+---------------+------------------+INSTRUCTORS:
1+---------------+------------------+---------------+
2| InstructorID | InstructorName | DepartmentID |
3+---------------+------------------+---------------+
4| INST1 | Dr. Jane Brown | DEPT_CS |
5| INST2 | Dr. Robert Wilson| DEPT_MATH |
6| INST3 | Dr. Alan Roberts | DEPT_PHYS |
7+---------------+------------------+---------------+COURSES:
1+------------+------------------+-------------------+--------------+---------------+
2| CourseID | CourseName | Schedule | Room | InstructorID |
3+------------+------------------+-------------------+--------------+---------------+
4| CS101 | Intro CS | TR 10-11:30 | H101 | INST1 |
5| MATH201 | Calculus | MWF 9-10 | M105 | INST2 |
6| PHYS101 | Physics | MWF 11-12 | P202 | INST3 |
7+------------+------------------+-------------------+--------------+---------------+ENROLLMENTS:
1+------------+------------+-------------+
2| EnrollID | StudentID | CourseID |
3+------------+------------+-------------+
4| E1001 | S1001 | CS101 |
5| E1002 | S1001 | MATH201 |
6| E1003 | S1002 | PHYS101 |
7| E1004 | S1002 | CS101 |
8| E1005 | S1003 | CS101 |
9+------------+------------+-------------+Let's verify that our design eliminates the initial problems:
The normalized design offers many benefits:
While normalization improves data integrity and reduces anomalies, it sometimes has performance costs. Some queries become more complex and may run slower due to the need to join multiple tables.
Denormalization is the process of deliberately introducing redundancy into a database design to improve read performance. Consider denormalization when:
Add derived or frequently accessed columns to reduce joins.
Example: Add DepartmentName to the COURSES table:
1COURSES Table (Denormalized):
2+------------+------------------+---------------+------------------+
3| CourseID | CourseName | DepartmentID | DepartmentName |
4+------------+------------------+---------------+------------------+
5| CS101 | Intro to CS | DEPT1 | Computer Science |
6| MATH201 | Calculus | DEPT2 | Mathematics |
7+------------+------------------+---------------+------------------+Store calculated values that are frequently queried.
Example: Add TotalAmount to an ORDERS table:
1ORDERS Table (Denormalized):
2+----------+-------------+-------------+--------------+
3| OrderID | CustomerID | ItemCount | TotalAmount |
4+----------+-------------+-------------+--------------+
5| O1001 | C101 | 3 | 149.97 |
6| O1002 | C102 | 1 | 999.99 |
7+----------+-------------+-------------+--------------+Create views or materialized tables that combine frequently joined data.
Example: Create a COURSE_DETAILS view:
1COURSE_DETAILS View (Denormalized):
2+------------+------------------+---------------+------------------+------------------+
3| CourseID | CourseName | InstructorID | InstructorName | DepartmentName |
4+------------+------------------+---------------+------------------+------------------+
5| CS101 | Intro to CS | INST1 | Dr. Jane Brown | Computer Science |
6| MATH201 | Calculus | INST2 | Dr. Wilson | Mathematics |
7+------------+------------------+---------------+------------------+------------------+Create specialized tables for reporting needs.
Example: Create a MONTHLY_SALES table:
1MONTHLY_SALES Table (Denormalized):
2+---------+----------+--------------+-------------+-------------+
3| Year | Month | ProductID | TotalUnits | TotalSales |
4+---------+----------+--------------+-------------+-------------+
5| 2023 | 1 | P100 | 120 | 119,998.80 |
6| 2023 | 1 | P200 | 250 | 6,247.50 |
7+---------+----------+--------------+-------------+-------------+In some NoSQL databases or specific scenarios, controlled repeating groups make sense.
Example: Document store approach for a CUSTOMER document:
1{
2 "customerId": "C101",
3 "name": "John Smith",
4 "addresses": [
5 {
6 "type": "home",
7 "street": "123 Main St",
8 "city": "New York",
9 "zipCode": "10001"
10 },
11 {
12 "type": "work",
13 "street": "456 Park Ave",
14 "city": "New York",
15 "zipCode": "10022"
16 }
17 ]
18}| Risk | Description | Mitigation |
|------|-------------|------------|
| Data Inconsistency | Redundant data gets out of sync | Use triggers or application logic to keep redundant data consistent |
| Increased Storage | Redundancy increases storage needs | Monitor storage usage and ensure benefits outweigh costs |
| Complex Updates | Updates must maintain all redundant copies | Create update procedures or use materialized views |
| Increased Maintenance | More complex to manage and change | Document denormalization decisions and trade-offs |
Instead of fully denormalizing, consider a hybrid approach:
Let's consider an e-commerce platform with millions of orders:
Normalized Schema (for OLTP):
Denormalized Reporting Tables (for OLAP):
With this approach, regular operations use the normalized tables, while analytics and dashboards use the denormalized tables, getting the best of both worlds.
Many databases now offer features to help manage the normalization/performance balance:
The ultimate goal is to create a database that:
Don't over-normalize at the expense of performance, and don't denormalize prematurely without measuring actual performance bottlenecks.
Apply normalization principles to transform a problematic database design into an efficient, normalized schema.
1<!DOCTYPE html>
2<html lang="en">
3<head>
4 <meta charset="UTF-8">
5 <meta name="viewport" content="width=device-width, initial-scale=1.0">
6 <title>Database Normalization Exercise</title>
7 <style>
8 body {
9 font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
10 max-width: 900px;
11 margin: 0 auto;
12 padding: 30px;
13 background: linear-gradient(135deg, #f0f4f8 0%, #d9e2ec 100%);
14 color: #333;
15 }
16
17 .container {
18 background: white;
19 border-radius: 12px;
20 padding: 30px;
21 box-shadow: 0 10px 30px rgba(0,0,0,0.1);
22 }
23
24 h1 {
25 color: #334e68;
26 text-align: center;
27 margin-bottom: 10px;
28 }
29
30 .subtitle {
31 text-align: center;
32 color: #627d98;
33 margin-bottom: 30px;
34 font-style: italic;
35 }
36
37 .section {
38 margin: 30px 0;
39 }
40
41 .table-container {
42 overflow-x: auto;
43 margin: 20px 0;
44 background: #f0f4f8;
45 padding: 15px;
46 border-radius: 8px;
47 }
48
49 table {
50 border-collapse: collapse;
51 width: 100%;
52 margin: 10px 0;
53 }
54
55 th, td {
56 border: 1px solid #d9e2ec;
57 padding: 8px 12px;
58 text-align: left;
59 }
60
61 th {
62 background-color: #e6f0ff;
63 color: #334e68;
64 font-weight: 600;
65 }
66
67 .code-block {
68 background: #2d2d2d;
69 color: #f8f8f2;
70 padding: 20px;
71 border-radius: 8px;
72 margin: 20px 0;
73 font-family: 'Courier New', monospace;
74 font-size: 14px;
75 line-height: 1.6;
76 overflow-x: auto;
77 }
78
79 .comment {
80 color: #6a9955;
81 }
82
83 .keyword {
84 color: #569cd6;
85 font-weight: bold;
86 }
87
88 .string {
89 color: #ce9178;
90 }
91
92 .step-header {
93 background: #334e68;
94 color: white;
95 padding: 10px 15px;
96 border-radius: 6px;
97 margin-bottom: 15px;
98 margin-top: 30px;
99 }
100
101 .note {
102 background: #fffbea;
103 border-left: 4px solid #f0b429;
104 padding: 15px;
105 margin: 20px 0;
106 border-radius: 4px;
107 }
108
109 .note h4 {
110 margin-top: 0;
111 color: #8a6116;
112 }
113
114 .solution-toggle {
115 background: #334e68;
116 color: white;
117 border: none;
118 padding: 10px 15px;
119 border-radius: 4px;
120 cursor: pointer;
121 font-size: 14px;
122 margin-top: 10px;
123 }
124
125 .solution-toggle:hover {
126 background: #486581;
127 }
128
129 .solution {
130 display: none;
131 margin-top: 15px;
132 }
133
134 .alert {
135 background: #fff2f2;
136 border-left: 4px solid #e12d39;
137 padding: 15px;
138 margin: 20px 0;
139 border-radius: 4px;
140 }
141
142 .alert h4 {
143 margin-top: 0;
144 color: #ab091e;
145 }
146
147 .success {
148 background: #f0fff4;
149 border-left: 4px solid #38b2ac;
150 padding: 15px;
151 margin: 20px 0;
152 border-radius: 4px;
153 }
154
155 .success h4 {
156 margin-top: 0;
157 color: #047481;
158 }
159 </style>
160</head>
161<body>
162 <div class="container">
163 <h1>Normalizing an Online Store Database</h1>
164 <p class="subtitle">Applying 1NF, 2NF, and 3NF to a real-world scenario</p>
165
166 <div class="section">
167 <h2>The Problem Scenario</h2>
168 <p>You've been hired to improve the database design for a small online bookstore. The current database has a single table that's causing data management problems. The owners are experiencing:</p>
169 <ul>
170 <li>Data inconsistencies when updating book information</li>
171 <li>Storage inefficiency with repeated author and publisher details</li>
172 <li>Difficulty adding new books that haven't been ordered yet</li>
173 <li>Problems tracking customer order history</li>
174 </ul>
175
176 <p>Here's the current design:</p>
177
178 <div class="table-container">
179 <h3>BOOKSTORE Table (Current Design)</h3>
180 <table>
181 <thead>
182 <tr>
183 <th>OrderID</th>
184 <th>OrderDate</th>
185 <th>CustomerName</th>
186 <th>CustomerEmail</th>
187 <th>CustomerPhone</th>
188 <th>CustomerAddress</th>
189 <th>BookID</th>
190 <th>BookTitle</th>
191 <th>AuthorName</th>
192 <th>AuthorBio</th>
193 <th>PublisherName</th>
194 <th>PublisherAddress</th>
195 <th>Category</th>
196 <th>Price</th>
197 <th>Quantity</th>
198 </tr>
199 </thead>
200 <tbody>
201 <tr>
202 <td>1001</td>
203 <td>2023-01-15</td>
204 <td>Alice Smith</td>
205 <td>[email protected]</td>
206 <td>555-1234</td>
207 <td>123 Main St, Anytown</td>
208 <td>B101</td>
209 <td>Database Design</td>
210 <td>John Doe</td>
211 <td>Professor at Tech University</td>
212 <td>Tech Books Inc</td>
213 <td>456 Publishing Ave, Booktown</td>
214 <td>Computer Science</td>
215 <td>39.99</td>
216 <td>1</td>
217 </tr>
218 <tr>
219 <td>1001</td>
220 <td>2023-01-15</td>
221 <td>Alice Smith</td>
222 <td>[email protected]</td>
223 <td>555-1234</td>
224 <td>123 Main St, Anytown</td>
225 <td>B102</td>
226 <td>SQL Mastery</td>
227 <td>John Doe</td>
228 <td>Professor at Tech University</td>
229 <td>Tech Books Inc</td>
230 <td>456 Publishing Ave, Booktown</td>
231 <td>Computer Science</td>
232 <td>44.99</td>
233 <td>1</td>
234 </tr>
235 <tr>
236 <td>1002</td>
237 <td>2023-01-20</td>
238 <td>Bob Jones</td>
239 <td>[email protected]</td>
240 <td>555-5678</td>
241 <td>789 Oak St, Anytown</td>
242 <td>B103</td>
243 <td>History of Time</td>
244 <td>Jane Smith</td>
245 <td>Historian and author of 10+ books</td>
246 <td>History Press</td>
247 <td>789 Academic Blvd, Edutown</td>
248 <td>History</td>
249 <td>29.99</td>
250 <td>2</td>
251 </tr>
252 <tr>
253 <td>1003</td>
254 <td>2023-02-05</td>
255 <td>Charlie Brown</td>
256 <td>[email protected]</td>
257 <td>555-9012</td>
258 <td>101 Pine St, Anytown</td>
259 <td>B101</td>
260 <td>Database Design</td>
261 <td>John Doe</td>
262 <td>Professor at Tech University</td>
263 <td>Tech Books Inc</td>
264 <td>456 Publishing Ave, Booktown</td>
265 <td>Computer Science</td>
266 <td>39.99</td>
267 <td>1</td>
268 </tr>
269 </tbody>
270 </table>
271 </div>
272
273 <div class="alert">
274 <h4>Problems with this Design:</h4>
275 <ul>
276 <li>Redundant data (author, publisher, customer information repeated)</li>
277 <li>Update anomalies (changing an author bio requires updating multiple rows)</li>
278 <li>Insert anomalies (can't add a new book unless it's ordered)</li>
279 <li>Delete anomalies (deleting the last order for a book loses all book information)</li>
280 </ul>
281 </div>
282 </div>
283
284 <div class="section">
285 <h2 class="step-header">Step 1: Identify Entities and Functional Dependencies</h2>
286
287 <p>First, let's identify the main entities and their dependencies:</p>
288
289 <ul>
290 <li><strong>Customers</strong>: CustomerName, CustomerEmail, CustomerPhone, CustomerAddress</li>
291 <li><strong>Books</strong>: BookID, BookTitle, Category, Price</li>
292 <li><strong>Authors</strong>: AuthorName, AuthorBio</li>
293 <li><strong>Publishers</strong>: PublisherName, PublisherAddress</li>
294 <li><strong>Orders</strong>: OrderID, OrderDate</li>
295 <li><strong>OrderItems</strong>: OrderID, BookID, Quantity</li>
296 </ul>
297
298 <p>Next, let's identify the functional dependencies:</p>
299
300 <ul>
301 <li>OrderID → OrderDate, CustomerName, CustomerEmail, CustomerPhone, CustomerAddress</li>
302 <li>BookID → BookTitle, AuthorName, PublisherName, Category, Price</li>
303 <li>AuthorName → AuthorBio</li>
304 <li>PublisherName → PublisherAddress</li>
305 <li>(OrderID, BookID) → Quantity</li>
306 </ul>
307 </div>
308
309 <div class="section">
310 <h2 class="step-header">Step 2: Apply First Normal Form (1NF)</h2>
311
312 <p>The current table is already in 1NF since it has:</p>
313 <ul>
314 <li>A primary key (combination of OrderID and BookID)</li>
315 <li>All attributes are atomic (single-valued)</li>
316 <li>No repeating groups</li>
317 </ul>
318
319 <p>However, we still have redundancy issues to address in the next steps.</p>
320 </div>
321
322 <div class="section">
323 <h2 class="step-header">Step 3: Apply Second Normal Form (2NF)</h2>
324
325 <p>To achieve 2NF, we need to remove partial dependencies from the composite key (OrderID, BookID).</p>
326
327 <button class="solution-toggle" onclick="document.getElementById('solution-2nf').style.display = document.getElementById('solution-2nf').style.display === 'none' ? 'block' : 'none';">Show/Hide 2NF Solution</button>
328
329 <div id="solution-2nf" class="solution">
330 <p>Here's how we'll split the table:</p>
331
332 <div class="table-container">
333 <h3>CUSTOMERS Table (2NF)</h3>
334 <table>
335 <thead>
336 <tr>
337 <th>CustomerEmail</th>
338 <th>CustomerName</th>
339 <th>CustomerPhone</th>
340 <th>CustomerAddress</th>
341 </tr>
342 </thead>
343 <tbody>
344 <tr>
345 <td>[email protected]</td>
346 <td>Alice Smith</td>
347 <td>555-1234</td>
348 <td>123 Main St, Anytown</td>
349 </tr>
350 <tr>
351 <td>[email protected]</td>
352 <td>Bob Jones</td>
353 <td>555-5678</td>
354 <td>789 Oak St, Anytown</td>
355 </tr>
356 <tr>
357 <td>[email protected]</td>
358 <td>Charlie Brown</td>
359 <td>555-9012</td>
360 <td>101 Pine St, Anytown</td>
361 </tr>
362 </tbody>
363 </table>
364 </div>
365
366 <div class="table-container">
367 <h3>BOOKS Table (2NF)</h3>
368 <table>
369 <thead>
370 <tr>
371 <th>BookID</th>
372 <th>BookTitle</th>
373 <th>AuthorName</th>
374 <th>AuthorBio</th>
375 <th>PublisherName</th>
376 <th>PublisherAddress</th>
377 <th>Category</th>
378 <th>Price</th>
379 </tr>
380 </thead>
381 <tbody>
382 <tr>
383 <td>B101</td>
384 <td>Database Design</td>
385 <td>John Doe</td>
386 <td>Professor at Tech University</td>
387 <td>Tech Books Inc</td>
388 <td>456 Publishing Ave, Booktown</td>
389 <td>Computer Science</td>
390 <td>39.99</td>
391 </tr>
392 <tr>
393 <td>B102</td>
394 <td>SQL Mastery</td>
395 <td>John Doe</td>
396 <td>Professor at Tech University</td>
397 <td>Tech Books Inc</td>
398 <td>456 Publishing Ave, Booktown</td>
399 <td>Computer Science</td>
400 <td>44.99</td>
401 </tr>
402 <tr>
403 <td>B103</td>
404 <td>History of Time</td>
405 <td>Jane Smith</td>
406 <td>Historian and author of 10+ books</td>
407 <td>History Press</td>
408 <td>789 Academic Blvd, Edutown</td>
409 <td>History</td>
410 <td>29.99</td>
411 </tr>
412 </tbody>
413 </table>
414 </div>
415
416 <div class="table-container">
417 <h3>ORDERS Table (2NF)</h3>
418 <table>
419 <thead>
420 <tr>
421 <th>OrderID</th>
422 <th>CustomerEmail</th>
423 <th>OrderDate</th>
424 </tr>
425 </thead>
426 <tbody>
427 <tr>
428 <td>1001</td>
429 <td>[email protected]</td>
430 <td>2023-01-15</td>
431 </tr>
432 <tr>
433 <td>1002</td>
434 <td>[email protected]</td>
435 <td>2023-01-20</td>
436 </tr>
437 <tr>
438 <td>1003</td>
439 <td>[email protected]</td>
440 <td>2023-02-05</td>
441 </tr>
442 </tbody>
443 </table>
444 </div>
445
446 <div class="table-container">
447 <h3>ORDER_ITEMS Table (2NF)</h3>
448 <table>
449 <thead>
450 <tr>
451 <th>OrderID</th>
452 <th>BookID</th>
453 <th>Quantity</th>
454 </tr>
455 </thead>
456 <tbody>
457 <tr>
458 <td>1001</td>
459 <td>B101</td>
460 <td>1</td>
461 </tr>
462 <tr>
463 <td>1001</td>
464 <td>B102</td>
465 <td>1</td>
466 </tr>
467 <tr>
468 <td>1002</td>
469 <td>B103</td>
470 <td>2</td>
471 </tr>
472 <tr>
473 <td>1003</td>
474 <td>B101</td>
475 <td>1</td>
476 </tr>
477 </tbody>
478 </table>
479 </div>
480
481 <p>Now we've addressed the partial dependencies, but there are still transitive dependencies in the BOOKS table that we need to fix in 3NF.</p>
482 </div>
483 </div>
484
485 <div class="section">
486 <h2 class="step-header">Step 4: Apply Third Normal Form (3NF)</h2>
487
488 <p>To achieve 3NF, we need to eliminate transitive dependencies where non-key attributes depend on other non-key attributes.</p>
489
490 <button class="solution-toggle" onclick="document.getElementById('solution-3nf').style.display = document.getElementById('solution-3nf').style.display === 'none' ? 'block' : 'none';">Show/Hide 3NF Solution</button>
491
492 <div id="solution-3nf" class="solution">
493 <p>Let's further decompose our tables to achieve 3NF:</p>
494
495 <div class="table-container">
496 <h3>AUTHORS Table (3NF)</h3>
497 <table>
498 <thead>
499 <tr>
500 <th>AuthorID</th>
501 <th>AuthorName</th>
502 <th>AuthorBio</th>
503 </tr>
504 </thead>
505 <tbody>
506 <tr>
507 <td>A101</td>
508 <td>John Doe</td>
509 <td>Professor at Tech University</td>
510 </tr>
511 <tr>
512 <td>A102</td>
513 <td>Jane Smith</td>
514 <td>Historian and author of 10+ books</td>
515 </tr>
516 </tbody>
517 </table>
518 </div>
519
520 <div class="table-container">
521 <h3>PUBLISHERS Table (3NF)</h3>
522 <table>
523 <thead>
524 <tr>
525 <th>PublisherID</th>
526 <th>PublisherName</th>
527 <th>PublisherAddress</th>
528 </tr>
529 </thead>
530 <tbody>
531 <tr>
532 <td>P101</td>
533 <td>Tech Books Inc</td>
534 <td>456 Publishing Ave, Booktown</td>
535 </tr>
536 <tr>
537 <td>P102</td>
538 <td>History Press</td>
539 <td>789 Academic Blvd, Edutown</td>
540 </tr>
541 </tbody>
542 </table>
543 </div>
544
545 <div class="table-container">
546 <h3>BOOKS Table (3NF)</h3>
547 <table>
548 <thead>
549 <tr>
550 <th>BookID</th>
551 <th>BookTitle</th>
552 <th>AuthorID</th>
553 <th>PublisherID</th>
554 <th>Category</th>
555 <th>Price</th>
556 </tr>
557 </thead>
558 <tbody>
559 <tr>
560 <td>B101</td>
561 <td>Database Design</td>
562 <td>A101</td>
563 <td>P101</td>
564 <td>Computer Science</td>
565 <td>39.99</td>
566 </tr>
567 <tr>
568 <td>B102</td>
569 <td>SQL Mastery</td>
570 <td>A101</td>
571 <td>P101</td>
572 <td>Computer Science</td>
573 <td>44.99</td>
574 </tr>
575 <tr>
576 <td>B103</td>
577 <td>History of Time</td>
578 <td>A102</td>
579 <td>P102</td>
580 <td>History</td>
581 <td>29.99</td>
582 </tr>
583 </tbody>
584 </table>
585 </div>
586
587 <p>Our final 3NF database schema includes the following tables:</p>
588 <ul>
589 <li>CUSTOMERS (CustomerEmail PK, CustomerName, CustomerPhone, CustomerAddress)</li>
590 <li>AUTHORS (AuthorID PK, AuthorName, AuthorBio)</li>
591 <li>PUBLISHERS (PublisherID PK, PublisherName, PublisherAddress)</li>
592 <li>BOOKS (BookID PK, BookTitle, AuthorID FK, PublisherID FK, Category, Price)</li>
593 <li>ORDERS (OrderID PK, CustomerEmail FK, OrderDate)</li>
594 <li>ORDER_ITEMS (OrderID FK, BookID FK, Quantity)</li>
595 </ul>
596 </div>
597 </div>
598
599 <div class="section">
600 <h2 class="step-header">Step 5: Create SQL Schema</h2>
601
602 <p>Now let's create the SQL statements to implement our normalized database:</p>
603
604 <button class="solution-toggle" onclick="document.getElementById('solution-sql').style.display = document.getElementById('solution-sql').style.display === 'none' ? 'block' : 'none';">Show/Hide SQL Solution</button>
605
606 <div id="solution-sql" class="solution">
607 <div class="code-block">
608<span class="comment">-- Create the CUSTOMERS table</span>
609<span class="keyword">CREATE TABLE</span> CUSTOMERS (
610 CustomerEmail <span class="keyword">VARCHAR</span>(100) <span class="keyword">PRIMARY KEY</span>,
611 CustomerName <span class="keyword">VARCHAR</span>(100) <span class="keyword">NOT NULL</span>,
612 CustomerPhone <span class="keyword">VARCHAR</span>(20),
613 CustomerAddress <span class="keyword">VARCHAR</span>(200)
614);
615
616<span class="comment">-- Create the AUTHORS table</span>
617<span class="keyword">CREATE TABLE</span> AUTHORS (
618 AuthorID <span class="keyword">VARCHAR</span>(10) <span class="keyword">PRIMARY KEY</span>,
619 AuthorName <span class="keyword">VARCHAR</span>(100) <span class="keyword">NOT NULL</span>,
620 AuthorBio <span class="keyword">TEXT</span>
621);
622
623<span class="comment">-- Create the PUBLISHERS table</span>
624<span class="keyword">CREATE TABLE</span> PUBLISHERS (
625 PublisherID <span class="keyword">VARCHAR</span>(10) <span class="keyword">PRIMARY KEY</span>,
626 PublisherName <span class="keyword">VARCHAR</span>(100) <span class="keyword">NOT NULL</span>,
627 PublisherAddress <span class="keyword">VARCHAR</span>(200)
628);
629
630<span class="comment">-- Create the BOOKS table</span>
631<span class="keyword">CREATE TABLE</span> BOOKS (
632 BookID <span class="keyword">VARCHAR</span>(10) <span class="keyword">PRIMARY KEY</span>,
633 BookTitle <span class="keyword">VARCHAR</span>(200) <span class="keyword">NOT NULL</span>,
634 AuthorID <span class="keyword">VARCHAR</span>(10) <span class="keyword">NOT NULL</span>,
635 PublisherID <span class="keyword">VARCHAR</span>(10) <span class="keyword">NOT NULL</span>,
636 Category <span class="keyword">VARCHAR</span>(50),
637 Price <span class="keyword">DECIMAL</span>(10, 2) <span class="keyword">NOT NULL</span>,
638 <span class="keyword">FOREIGN KEY</span> (AuthorID) <span class="keyword">REFERENCES</span> AUTHORS(AuthorID),
639 <span class="keyword">FOREIGN KEY</span> (PublisherID) <span class="keyword">REFERENCES</span> PUBLISHERS(PublisherID)
640);
641
642<span class="comment">-- Create the ORDERS table</span>
643<span class="keyword">CREATE TABLE</span> ORDERS (
644 OrderID <span class="keyword">INT PRIMARY KEY</span>,
645 CustomerEmail <span class="keyword">VARCHAR</span>(100) <span class="keyword">NOT NULL</span>,
646 OrderDate <span class="keyword">DATE NOT NULL</span>,
647 <span class="keyword">FOREIGN KEY</span> (CustomerEmail) <span class="keyword">REFERENCES</span> CUSTOMERS(CustomerEmail)
648);
649
650<span class="comment">-- Create the ORDER_ITEMS table</span>
651<span class="keyword">CREATE TABLE</span> ORDER_ITEMS (
652 OrderID <span class="keyword">INT</span>,
653 BookID <span class="keyword">VARCHAR</span>(10),
654 Quantity <span class="keyword">INT NOT NULL</span>,
655 <span class="keyword">PRIMARY KEY</span> (OrderID, BookID),
656 <span class="keyword">FOREIGN KEY</span> (OrderID) <span class="keyword">REFERENCES</span> ORDERS(OrderID),
657 <span class="keyword">FOREIGN KEY</span> (BookID) <span class="keyword">REFERENCES</span> BOOKS(BookID)
658);
659 </div>
660 </div>
661 </div>
662
663 <div class="section">
664 <h2 class="step-header">Step 6: Verify the Solution</h2>
665
666 <p>Let's verify that our normalized design addresses the original problems:</p>
667
668 <div class="success">
669 <h4>Problems Solved by Normalization:</h4>
670 <ul>
671 <li><strong>Redundancy Eliminated:</strong> Author, publisher, and customer information are stored only once</li>
672 <li><strong>Update Anomalies Fixed:</strong> Updating an author's bio only requires changing one row in the AUTHORS table</li>
673 <li><strong>Insert Anomalies Fixed:</strong> Books can be added to the BOOKS table without requiring an order</li>
674 <li><strong>Delete Anomalies Fixed:</strong> Deleting an order doesn't affect book, author, or publisher information</li>
675 </ul>
676 </div>
677
678 <div class="note">
679 <h4>Performance Considerations:</h4>
680 <p>While our normalized design improves data integrity and maintenance, it does require more joins for certain queries. For a small bookstore, this is likely not an issue, but for very large operations, some strategic denormalization might be considered for frequently accessed data.</p>
681 </div>
682 </div>
683
684 <div class="section">
685 <h2>Your Challenge</h2>
686 <p>Now try applying these same principles to normalize another common database scenario:</p>
687
688 <div class="table-container">
689 <h3>MOVIE_RENTALS Table</h3>
690 <table>
691 <thead>
692 <tr>
693 <th>RentalID</th>
694 <th>CustomerID</th>
695 <th>CustomerName</th>
696 <th>CustomerEmail</th>
697 <th>MovieID</th>
698 <th>MovieTitle</th>
699 <th>DirectorName</th>
700 <th>ReleaseYear</th>
701 <th>Genre</th>
702 <th>RentalDate</th>
703 <th>ReturnDate</th>
704 <th>RentalFee</th>
705 </tr>
706 </thead>
707 <tbody>
708 <tr>
709 <td>R1001</td>
710 <td>C101</td>
711 <td>John Smith</td>
712 <td>[email protected]</td>
713 <td>M101</td>
714 <td>The Matrix</td>
715 <td>Wachowski Sisters</td>
716 <td>1999</td>
717 <td>Sci-Fi</td>
718 <td>2023-06-01</td>
719 <td>2023-06-03</td>
720 <td>3.99</td>
721 </tr>
722 <tr>
723 <td>R1002</td>
724 <td>C102</td>
725 <td>Mary Johnson</td>
726 <td>[email protected]</td>
727 <td>M102</td>
728 <td>Inception</td>
729 <td>Christopher Nolan</td>
730 <td>2010</td>
731 <td>Sci-Fi</td>
732 <td>2023-06-02</td>
733 <td>2023-06-04</td>
734 <td>4.99</td>
735 </tr>
736 <tr>
737 <td>R1003</td>
738 <td>C101</td>
739 <td>John Smith</td>
740 <td>[email protected]</td>
741 <td>M103</td>
742 <td>Interstellar</td>
743 <td>Christopher Nolan</td>
744 <td>2014</td>
745 <td>Sci-Fi</td>
746 <td>2023-06-03</td>
747 <td>2023-06-05</td>
748 <td>4.99</td>
749 </tr>
750 </tbody>
751 </table>
752 </div>
753
754 <p>Try to normalize this table to 3NF following the steps we used for the bookstore example.</p>
755
756 <button class="solution-toggle" onclick="document.getElementById('challenge-solution').style.display = document.getElementById('challenge-solution').style.display === 'none' ? 'block' : 'none';">Show/Hide Challenge Solution</button>
757
758 <div id="challenge-solution" class="solution">
759 <p>Here's a 3NF solution for the MOVIE_RENTALS table:</p>
760
761 <div class="code-block">
762<span class="comment">-- Create CUSTOMERS table</span>
763<span class="keyword">CREATE TABLE</span> CUSTOMERS (
764 CustomerID <span class="keyword">VARCHAR</span>(10) <span class="keyword">PRIMARY KEY</span>,
765 CustomerName <span class="keyword">VARCHAR</span>(100) <span class="keyword">NOT NULL</span>,
766 CustomerEmail <span class="keyword">VARCHAR</span>(100) <span class="keyword">UNIQUE NOT NULL</span>
767);
768
769<span class="comment">-- Create DIRECTORS table</span>
770<span class="keyword">CREATE TABLE</span> DIRECTORS (
771 DirectorID <span class="keyword">VARCHAR</span>(10) <span class="keyword">PRIMARY KEY</span>,
772 DirectorName <span class="keyword">VARCHAR</span>(100) <span class="keyword">NOT NULL</span>
773);
774
775<span class="comment">-- Create MOVIES table</span>
776<span class="keyword">CREATE TABLE</span> MOVIES (
777 MovieID <span class="keyword">VARCHAR</span>(10) <span class="keyword">PRIMARY KEY</span>,
778 MovieTitle <span class="keyword">VARCHAR</span>(200) <span class="keyword">NOT NULL</span>,
779 DirectorID <span class="keyword">VARCHAR</span>(10) <span class="keyword">NOT NULL</span>,
780 ReleaseYear <span class="keyword">INT</span>,
781 Genre <span class="keyword">VARCHAR</span>(50),
782 <span class="keyword">FOREIGN KEY</span> (DirectorID) <span class="keyword">REFERENCES</span> DIRECTORS(DirectorID)
783);
784
785<span class="comment">-- Create RENTALS table</span>
786<span class="keyword">CREATE TABLE</span> RENTALS (
787 RentalID <span class="keyword">VARCHAR</span>(10) <span class="keyword">PRIMARY KEY</span>,
788 CustomerID <span class="keyword">VARCHAR</span>(10) <span class="keyword">NOT NULL</span>,
789 MovieID <span class="keyword">VARCHAR</span>(10) <span class="keyword">NOT NULL</span>,
790 RentalDate <span class="keyword">DATE NOT NULL</span>,
791 ReturnDate <span class="keyword">DATE</span>,
792 RentalFee <span class="keyword">DECIMAL</span>(5,2) <span class="keyword">NOT NULL</span>,
793 <span class="keyword">FOREIGN KEY</span> (CustomerID) <span class="keyword">REFERENCES</span> CUSTOMERS(CustomerID),
794 <span class="keyword">FOREIGN KEY</span> (MovieID) <span class="keyword">REFERENCES</span> MOVIES(MovieID)
795);
796 </div>
797
798 <div class="table-container">
799 <h3>CUSTOMERS Table</h3>
800 <table>
801 <thead>
802 <tr>
803 <th>CustomerID</th>
804 <th>CustomerName</th>
805 <th>CustomerEmail</th>
806 </tr>
807 </thead>
808 <tbody>
809 <tr>
810 <td>C101</td>
811 <td>John Smith</td>
812 <td>[email protected]</td>
813 </tr>
814 <tr>
815 <td>C102</td>
816 <td>Mary Johnson</td>
817 <td>[email protected]</td>
818 </tr>
819 </tbody>
820 </table>
821 </div>
822
823 <div class="table-container">
824 <h3>DIRECTORS Table</h3>
825 <table>
826 <thead>
827 <tr>
828 <th>DirectorID</th>
829 <th>DirectorName</th>
830 </tr>
831 </thead>
832 <tbody>
833 <tr>
834 <td>D101</td>
835 <td>Wachowski Sisters</td>
836 </tr>
837 <tr>
838 <td>D102</td>
839 <td>Christopher Nolan</td>
840 </tr>
841 </tbody>
842 </table>
843 </div>
844
845 <div class="table-container">
846 <h3>MOVIES Table</h3>
847 <table>
848 <thead>
849 <tr>
850 <th>MovieID</th>
851 <th>MovieTitle</th>
852 <th>DirectorID</th>
853 <th>ReleaseYear</th>
854 <th>Genre</th>
855 </tr>
856 </thead>
857 <tbody>
858 <tr>
859 <td>M101</td>
860 <td>The Matrix</td>
861 <td>D101</td>
862 <td>1999</td>
863 <td>Sci-Fi</td>
864 </tr>
865 <tr>
866 <td>M102</td>
867 <td>Inception</td>
868 <td>D102</td>
869 <td>2010</td>
870 <td>Sci-Fi</td>
871 </tr>
872 <tr>
873 <td>M103</td>
874 <td>Interstellar</td>
875 <td>D102</td>
876 <td>2014</td>
877 <td>Sci-Fi</td>
878 </tr>
879 </tbody>
880 </table>
881 </div>
882
883 <div class="table-container">
884 <h3>RENTALS Table</h3>
885 <table>
886 <thead>
887 <tr>
888 <th>RentalID</th>
889 <th>CustomerID</th>
890 <th>MovieID</th>
891 <th>RentalDate</th>
892 <th>ReturnDate</th>
893 <th>RentalFee</th>
894 </tr>
895 </thead>
896 <tbody>
897 <tr>
898 <td>R1001</td>
899 <td>C101</td>
900 <td>M101</td>
901 <td>2023-06-01</td>
902 <td>2023-06-03</td>
903 <td>3.99</td>
904 </tr>
905 <tr>
906 <td>R1002</td>
907 <td>C102</td>
908 <td>M102</td>
909 <td>2023-06-02</td>
910 <td>2023-06-04</td>
911 <td>4.99</td>
912 </tr>
913 <tr>
914 <td>R1003</td>
915 <td>C101</td>
916 <td>M103</td>
917 <td>2023-06-03</td>
918 <td>2023-06-05</td>
919 <td>4.99</td>
920 </tr>
921 </tbody>
922 </table>
923 </div>
924 </div>
925 </div>
926 </div>
927 <script>
928 // Simple toggle function
929 function toggleSolution(id) {
930 const solution = document.getElementById(id);
931 if (solution.style.display === "none") {
932 solution.style.display = "block";
933 } else {
934 solution.style.display = "none";
935 }
936 }
937 </script>
938</body>
939</html>This practical example demonstrates the complete normalization process for a real-world scenario - an online bookstore database. By working through this exercise, you can see how to apply normalization principles to transform a problematic database design into a well-structured, efficient schema.
The exercise begins by showing a common problem: a single, denormalized table that tries to store all information about orders, customers, books, authors, and publishers. This design leads to redundancy and anomalies:
By identifying the functional dependencies (what determines what), we lay the groundwork for normalization:
In this case, the original table is already in 1NF because:
But it still has redundancy issues that need to be addressed in higher normal forms.
To achieve 2NF, we remove partial dependencies by creating separate tables:
Now customer and book information are no longer duplicated for each order line, but we still have transitive dependencies in the BOOKS table.
To reach 3NF, we eliminate transitive dependencies by further decomposing tables:
Now, all non-key attributes depend directly on the primary key, not on other non-key attributes.
The exercise provides SQL statements to implement the normalized design, showing how the logical model translates into a physical database schema. The SQL includes:
The final sections verify that our normalized design solves the original problems and provides a challenge to apply the same principles to another scenario (movie rentals).
This exercise closely mirrors real-world database design tasks:
By working through this example, you gain practical experience in identifying and fixing common database design problems through systematic normalization.
Congratulations on mastering database normalization! You now understand how to structure databases efficiently by applying the first three normal forms.
In your database design journey, you might want to explore these advanced topics next:
Remember that normalization is a means to an end - creating databases that maintain data integrity while meeting performance requirements. Always balance theoretical purity with practical considerations based on your specific application needs.
Your progress is saved in this browser, on this device only.