Normalization of Database Tables to Third Normal Form (3NF)
This document explains the process of normalizing database tables to achieve Third Normal Form, enhancing data integrity.
Olivia Smith
Contributor
4.8
54
30 days ago
Preview (2 of 4)
Sign in to access the full document!
Normalization of Database Tables to Third Normal Form (3NF)
Order ID Order
Date
Customer
ID
Customer Name Product
ID
Product Desc. Product
Price
Quantity
Ordered
1006 10/24/10 2 Value Furniture 7
5
4
Dining Table
Writers Desk
Entertain Center
800.00
325.00
650.00
2
2
1
1007 10/25/10 6 Furniture Gallery 11
4
4 Drawer Dresser
Entertain Center
500.00
650.00
4
3
1. Describe the steps you would take to bring the table(s) into third normal form compliance. Display the resulting third
normal form compliant table or tables, identifying primary and foreign keys.
Answer:
R is in 3NF if and only if, for every FD X → A in R, at least one of the following is true:
1. X contains A (so the FD is trivial).
2. X is a superkey.
3. A is contained in a candidate key of R.
Step 1: Break the product table and create a price table to map the price of each product accurately.
Step 2: Create a product table and other Product Price table.
Product table:
Product ID: Primary key
Product Price ID: Foreign key
Product
ID
Product Desc.
7 Dining Table
5 Writers Desk
Order ID Order
Date
Customer
ID
Customer Name Product
ID
Product Desc. Product
Price
Quantity
Ordered
1006 10/24/10 2 Value Furniture 7
5
4
Dining Table
Writers Desk
Entertain Center
800.00
325.00
650.00
2
2
1
1007 10/25/10 6 Furniture Gallery 11
4
4 Drawer Dresser
Entertain Center
500.00
650.00
4
3
1. Describe the steps you would take to bring the table(s) into third normal form compliance. Display the resulting third
normal form compliant table or tables, identifying primary and foreign keys.
Answer:
R is in 3NF if and only if, for every FD X → A in R, at least one of the following is true:
1. X contains A (so the FD is trivial).
2. X is a superkey.
3. A is contained in a candidate key of R.
Step 1: Break the product table and create a price table to map the price of each product accurately.
Step 2: Create a product table and other Product Price table.
Product table:
Product ID: Primary key
Product Price ID: Foreign key
Product
ID
Product Desc.
7 Dining Table
5 Writers Desk
Preview Mode
Sign in to access the full document!
100%
Study Now!
XY-Copilot AI
Unlimited Access
Secure Payment
Instant Access
24/7 Support
Document Chat
Document Details
Subject
Information Technology