4 Replies - 1120 Views - Last Post: 02 December 2012 - 10:57 PM Rate Topic: -----

#1 Oggie25  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-November 12

help me with normalization

Posted 11 November 2012 - 10:26 AM

Can you help me Normalize to 1,2,3 Normal form and identify Primary and Foreign keys?

TASK

The company has also supplied some information about the procedures that are used for managing trips:

A single trip may last between 2 and 10 days

Trips are grouped by their start dates. A trip starting in May for example is therefore deemed a May trip even if it ends in June.

During a trip the driver visits a number of customer sites to pick up items of cargo which are then delivered to other customers

Items may be picked up from a customer early in the trip, and other items delivered back to the same customer later on

Routing is complex and is handled separately. This means you do not need to consider routing when converting the spreadsheet system to a relational database

Some items are fragile and must therefore have their condition checked and signed off by both pickup and delivery customers

Some items are hazardous and may only be transported by drivers with appropriate qualifications
Manifest is the term used for the items of cargo in transit

Each manifest item is identified by a barcode which is used for checking and billing

GVW stands for gross vehicle weight - information that is important for licensing and insurance purposes.


Trip_ID
Departure
Return
Duration
Driver_First_Name
Driver_Last_Name
Vehicle_Registration
Manifest_Barcode
Item_Weight
Pickup_Customer
Delivery_Customer
Category
Description
Requirements

Is This A Good Question/Topic? 0
  • +

Replies To: help me with normalization

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: help me with normalization

Posted 11 November 2012 - 12:17 PM

I wrote a Relational Database Design Normalization tutorial a while back. Reading that would be a good start.

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.
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9497
  • View blog
  • Posts: 35,844
  • Joined: 12-June 08

Re: help me with normalization

Posted 11 November 2012 - 12:20 PM

Let's also back up and ask "what were your thoughts on this, and what have you tried?".
Was This Post Helpful? 0
  • +
  • -

#4 Oggie25  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-November 12

Re: help me with normalization

Posted 11 November 2012 - 12:29 PM

View Postmodi123_1, on 11 November 2012 - 12:20 PM, said:

Let's also back up and ask "what were your thoughts on this, and what have you tried?".


That's what I created and I have no idea is that right.

(1NF)
Trip_id-------Primary key
Departure
Return
Duration
Driver_first_name
Driver_last_name
Vehicle_registration
Manifest_barcode
Item_weight
Pickup_customer
Delivery_customer
Category
Description
Requirements


(2NF)
Trip_id--------Primary key
Departure
Return
Duration
Driver_first_name
Driver_last_name
Vehicle_registration


Trip_id-----------------Primary key
Manifest_barcode-------Primary key
Item_weight
Pickup_customer
Delivery_customer
Category
Description
requirements





(3NF)
Trip_id-----------Primary key
Departure
Return
Duration
Driver_first_name
Driver_last_name


Trip_id----------------Primary key
Manifest_barcode-----------Foreign key
Item_weight
Pickup_customer
Delivery_customer


Manifest_barcode-----------Primary key
Vehicle_registration------------Foregin key
Category
Description
Requirements


Is this right ?
or I am doing something wrong and WHY?
Was This Post Helpful? 0
  • +
  • -

#5 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

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.

This post has been edited by BobRodes: 02 December 2012 - 11:00 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1