DBMS Assign 2

Normalization Practice 2 Solution BIS 638 – Database Management for Business Systems

The following table shows a partial list of orders from an organization’s order management system:

OrderInfo

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

1. Use arrows to show the functional dependencies in the table. Before we begin identifying functional dependencies please remember that, as a rule, we don’t use fields with free form text (ItemDesc), currency values (UnitPrice), or quantities (OrderQty) as determinants. We also avoid using fields with dates (OrderDate) and phone numbers (ShipperPhone) when possible. With that said, let’s look for functional dependencies moving left to right, starting with OrderNbr.

OrderNbr determines OrderDate, ShipperNbr, ShipperName, and ShipperPhone. OrderNbr does not determine ItemNbr, ItemDesc, or UnitPrice because a given order can include multiple items (e.g., OrderNbr 100 includes ItemNbr 111 and ItemNbr 333). OrderNbr also does not determine OrderQty because a given order can have different quantities, depending on the item (e.g., the OrderQty for OrderNbr 100 is either 32 or 51, depending on the ItemNbr).

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

ItemNbr determines ItemDesc and UnitPrice. ItemNbr does not determine OrderNbr, OrderDate, ShipperNbr, ShipperName, or ShipperPhone because a given item can be included on multiple orders (e.g., ItemNbr 333 is included on OrderNbr 100, 101, and 103). ItemNbr also does not determine OrderQty because a given item can have different quantities, depending on the order (e.g., the OrderQty for ItemNbr 111 is 32 for OrderNbr 100 and 41 for OrderNbr 103).

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

ShipperNbr determines ShipperName and ShipperPhone. ShipperNbr does not determine OrderNbr, OrderDate, ItemNbr, ItemDesc, UnitPrice, or OrderQty because a given shipper will handle multiple orders and multiple items (e.g., ShipperNbr 300 handles OrderNbr 100 and 103). Please note that ShipperPhone functionally determines ShipperNbr and ShipperName which might lead you to choose it as the determinant instead of ShipperNbr. Since a phone number could be used by multiple people (e.g., if you call the BIS department office a couple of different people might answer) it is safer to use ShipperNbr as the determinant.

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

OrderQty is determined by OrderNbr and ItemNbr together (you need to know the order and the item on the order to know how much of the item was ordered).

OrderNbr OrderDate ItemNbr ItemDesc UnitPrice OrderQty ShipperNbr ShipperName ShipperPhone

100 09/02/10 111 Widget $25 32 300 UPS 989-674-0223

100 09/02/10 333 Super Widget $70 51 300 UPS 989-674-0223

101 09/03/10 333 Super Widget $70 47 100 DHL 989-543-9878

102 09/03/10 222 Widget Plus $45 54 200 Fedex 989-322-5541

102 09/03/10 444 Mega Widget $115 34 200 Fedex 989-322-5541

103 09/04/10 111 Widget $25 41 300 UPS 989-674-0223

103 09/04/10 333 Super Widget $70 15 300 UPS 989-674-0223

103 09/04/10 444 Mega Widget $115 77 300 UPS 989-674-0223

2. Convert the table into 3NF relations. Using the four functional dependencies identified in Task 1, the following tables were created: Order (OrderNbr, OrderDate, ShipperNbr, ShipperName, ShipperPhone) Item (ItemNbr, ItemDesc, UnitPrice) Shipping (ShipperNbr, ShipperName, ShipperPhone) Quantity (OrderNbr, ItemNbr, OrderQty) In each table, the determinant of the functional dependency becomes the primary key (e.g., OrderNbr is the primary key in the Order table). Since OrderQty has two determinants (OrderNbr and ItemNbr), the OrderQty table has a composite primary key. Having created the tables based on the functional dependencies, we now need to review them to make sure each table is 3NF. Let’s review each table in turn. Order (OrderNbr, OrderDate, ShipperNbr, ShipperName, ShipperPhone)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not composite so a partial functional dependency is not possible).

 3NF – No. There is a transitive functional dependency. A non-key attribute (ShipperNbr) determines another non-key attribute (ShipperName and ShipperPhone). To fix the transitive functional dependency remove ShipperName and ShipperPhone (they’re already in the Shipping table anyway). Make sure to indicate that ShipperNbr is a foreign key (it relates the Order table to the Shipping table).

Order (OrderNbr, OrderDate, ShipperNbr)

Item (ItemNbr, ItemDesc, UnitPrice)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies. Shipping (ShipperNbr, ShipperName, ShipperPhone)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies (the primary key is not composite so a partial functional dependency is not possible).

 3NF – Yes. There are not transitive functional dependencies. Quantity (OrderNbr, ItemNbr, OrderQty)

 1NF – Yes. The six requirements of a relation at met.

 2NF – Yes. There are no partial functional dependencies. OrderQty is not determined by OrderNbr or ItemNbr alone.

 3NF – Yes. There are not transitive functional dependencies. The 3NF tables are as follows: Order (OrderNbr, OrderDate, ShipperNbr) Item (ItemNbr, ItemDesc, UnitPrice) Shipping (ShipperNbr, ShipperName, ShipperPhone) Quantity (OrderNbr, ItemNbr, OrderQty)