Re: help me with normalization

You'd probably want to start of by breaking the basic info into tables for Drivers, Trucks, Customers and Items (Manifests?). Then you'd need a tale listing the Trips, which would reference the truck and driver. And finally a table for Pickups and Deliveries, which would reference the trip, a customer and the item to be picked up/dropped of.

Re: help me with normalization

Posted 02 December 2012 - 10:57 PM

Take a step back and think about this in terms of the data and how the data relate. You have five "kernel entities": trips, drivers, vehicles, customers, and items. See which of these entities each field belongs to. Then ask yourself how they relate. Each driver has multiple trips, each trip has one driver. Each customer has multiple items, each item has one customer. Each trip has multiple items, each item has one trip. Now, assign a primary key to each table. In the "many" side of each relationship, add the primary key of the "one" side. This is called a "foreign key".

Once you have this laid out, check the forms to make sure that you haven't violated any of them. Generally, if you organize your data properly in terms of "one-to-many" relationships, you'll find that you are in at least 3nf.

Your 2nf has two violations in it. Since a manifest can only go on one trip, a trip id is superfluous to identify a manifest. Therefore, manifest instances are not dependent on the trip that they are going on, therefore not dependent on the entire key. Furthermore, customers relate to manifests and not to trips, so the customer data also represent a 2nf violation. Break these apart into the kernel entities I mentioned earlier and you should resolve these problems.

Your 3nf has two violations in it. First, 2nf violations are also 3nf violations. Second, driver data is not directly dependent on the trip key. There should be a separate driver table with a driverid as PK, and Trip should contain a driverid as a foreign key.

Finally, go back over the requirements and do a better job of identifying the data you have to keep. I don't see, for example, anything in your structure that deals with "fragile items" as specified in the requirements.

A good mnemonic to remember the forms is "the key, the whole key, and nothing but the key (so help me Codd). The key is 1nf, the whole key is 2nf, and nothing but the key is 3nf.