ESP Document 5

The markdown for this file can be found in the source repository.

Shippers Details View

0NF

Shipper: (ShipperNumber, CompanyName, Address, City, Province, PostalCode, Phone)

1NF – 3NF

No changes to the table in 1NF to 3NF.

ERD

The following ERD represents the tables/entities from the Shipper Details View.

Order Shipment View

0NF

Order: (OrderNumber, CustomerFirstName, CustomerLastName, CustomerNumber, Address, City, Province, PostalCode, Phone, OrderDate, ItemId, ItemDescription, OrderQuantity, ShipQuantity, ShipperId, WaybillNumber, ShipDate)

1NF

Order: (OrderNumber, CustomerFirstName, CustomerLastName, CustomerNumber, Address, City, Province, PostalCode, Phone, OrderDate)

Note

When looking at the outermost repeating group, it might be tempting to take the ShipperId as part of the new composite key, but a closer look at the form's data reveals that a combination of ShipperId and OrderNumber would not produce a unique key.

We are told, however, that "At most, each customer will get a single shipment in a day." That means, for each outer group of repeating items, the ShipDate is the best candidate for pairing with the OrderNumber to produce a primary key.

OrderShipment: (OrderNumber, ShipDate, ShipperId, WaybillNumber)

OrderShipmentDetail: (OrderNumber, ShipDate, ItemId, ItemDescription, OrderQuantity, ShipQuantity)

2NF

OrderShipmentDetail: (OrderNumber, ShipDate, ItemId, OrderQuantity, ShipQuantity)

Item: (ItemId, ItemDescription)

3NF

Order: (OrderNumber, CustomerNumber, OrderDate)

Customer: (CustomerNumber, CustomerFirstName, CustomerLastName, Address, City, Province, PostalCode, Phone)

Note

For the OrderShipment, it would be tempting to associate the WaybillNumber with the ShipperId as a transitive dependency. However, the specifications said that we are not interested in managing waybill numbers for each shipper, so we will keep these attributes where they are.

OrderShipment: (OrderNumber, ShipDate, ShipperId, WaybillNumber)

ERD

---

(Alternate) Order Shipment View

In this alternate Order Shipment View, we are actually recording the WaybillNumbers for each shipper. That can be realistic only if the waybill numbers from shippers are unique (that is, no two shippers can use the same waybill number for their shipments). If we are generating our own waybill numbers on the forms, then the following normalization would be a valid solution.

0NF

After performing Zero-Normal Form, a single table was generated: Order

Order: (OrderNumber, CustomerFirstName, CustomerLastName, CustomerNumber, Address, City, Province, PostalCode, Phone, OrderDate, ItemId, ItemDescription, OrderQuantity, ShipQuantity , ShipperId, WaybillNumber, ShipDate)

1NF

After performing First-Normal Form, two additional tables were generated: OrderShipment and OrderShipmentDetail

Order: (OrderNumber, CustomerFirstName, CustomerLastName, CustomerNumber, Address, City, Province, PostalCode, Phone, OrderDate)

OrderShipment: (OrderNumber, WaybillNumber, ShipperId, ShipDate)

OrderShipmentDetail: (OrderNumber, WaybillNumber, ItemId, ItemDescription, OrderQuantity, ShipQuantity)

2NF

After performing Second-Normal Form, two additional tables were generated: Waybill and Item.

OrderShipment: (OrderNumber, WaybillNumber)

Waybill: (WaybillNumber, ShipperId, ShipDate)

OrderShipmentDetail: (OrderNumber, WaybillNumber, ItemId, OrderQuantity, ShipQuantity)

Item: (ItemId, ItemDescription)

3NF

After performing Third-Normal Form, an additional table was generated: Customer

Order: (OrderNumber, CustomerNumber, OrderDate)

Customer: (CustomerNumber, CustomerFirstName, CustomerLastName, Address, City, Province, PostalCode, Phone)

Final Tables

Order: (OrderNumber, CustomerNumber, OrderDate)

Customer: (CustomerNumber, CustomerFirstName, CustomerLastName, Address, City, Province, PostalCode, Phone)

OrderShipment: (OrderNumber, WaybillNumber)

Waybill: (WaybillNumber, ShipperId, ShipDate)

OrderShipmentDetail: (OrderNumber, WaybillNumber, ItemId, OrderQuantity, ShipQuantity)

Item: (ItemId, ItemDescription)

ERD