Scribd
Upload
9 views2 pages

Normalize Tables to 3NF: Exercises

The document presents three problems involving the normalization of tables to Third Normal Form (3NF). Problem 1 involves orders and items, Problem 2 focuses on transportation projects and their management, and Problem 3 details shipments of car parts. Each problem outlines the relationships and unique identifiers for the data entities involved.

Uploaded by

yugrigoryeva01
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
9 views2 pages

Normalize Tables to 3NF: Exercises

The document presents three problems involving the normalization of tables to Third Normal Form (3NF). Problem 1 involves orders and items, Problem 2 focuses on transportation projects and their management, and Problem 3 details shipments of car parts. Each problem outlines the relationships and unique identifiers for the data entities involved.

Uploaded by

yugrigoryeva01
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

In-class Exercises

Normalize the tables in 3NF.

Problem 1.

OrderID CustomerID OrderDate Items


1 1 2018.10.22 1 Nuts q=5, 2 Bolts q=10
2 1 2018.10.24 3 Screws q=12
3 2 2018.09.15 1 Nuts q=3, 5 Screws q=3
4 3 2018.10.22 2 Bolts q=5

Problem 2.

CountyI County Project ProejctManager ProjectMiles


ProejctID ProjectName D Name ManagerID Name WithinCounty
1 Road X 1 Wilson M1 Bob 10
1 Road X 2 Ottawa M1 Bob 17
1 Road X 3 Davis M1 Bob 12
2 Road Y 3 Davis M2 Sue 23
3 Bridge A 1 Wilson M3 Lee 0.5
3 Bridge A 2 Ottawa M3 Lee 0.3
4 Tunnel Q 2 Ottawa M1 Bob 2
5 Road W 4 Pony M4 Bob 23

• The DEPARTMENT OF TRANSPORTATION (DOT) PROEJCT TABLE captures the data about projects
and their length (in miles).
• Each project has a unique Project ID and Project Name.
• Each county has a unique County ID and County Name.
• Each project manager has a unique Project Manager ID and Project Manager Name.
• Each project has one project manager.
• A project manager can manage several projects.
• A project can span across several counties.
• This table records the length of the project in a county in the ProejctMilesWIthinCounty column.
Problem 3.

ShipmentI
D ShipmentDate TruckID TruckType Product ID ProductType Quantity

111 1-Jan T-111 Semi-Trailer A Tire 180

111 1-Jan T-111 Semi-Trailer B Battery 120

222 2-Jan T-222 Van Truck C SparkPlug 10000

333 3-Jan T-333 Semi-Trailer D Wiper 5000

333 3-Jan T-333 Semi-Trailer A Tire 200

444 3-Jan T-222 Van Truck C SparkPlug 25000

555 3-Jan T-111 Semi-Trailer B Battery 180

• The SHIPMENTS table for a car part company captures the data about car shipments.
• Each shipment has a unique Shipment ID and a Shipment Date.
• Each shipment is shipped on one truck.
• Each truck has a unique Truck ID, and a Truck Type.
• Each shipment can ship multiple products.
• Each product has a unique Product ID and a Product Type.
• Each quantity of a product shipped in a shipment is recorded in a table in the column Quantity.

You might also like