Solved

# DBDL Normalization 3NF Need Help With Practice Exercise Confused

Posted on 2011-10-16
3,465 Views
Ok so I am working on practice problems in my textbook to try to understand how to make a normalization 3NF. So Im trying to convert these following items into a 3NF DBDL in word. The things are: Company Name, Contact Name,Billing Address, City, State, WorkOrder ID, Date Recieved, Date Required,Make/Model, Serial Number, Employee ID, Billable Hours, Billing Rate, Part, Quantity, Unit Price.

Each company can submit as many work orders as they want
A separate work order is required for each piece of equipment to be repaired
Each piece of equipment has a unique serial number
Each work order may require multiple parts
Each work order may be worked on by more than one employee
Each part can appear on many work orders, but only once on each.
Each part is always the same price
Each employee can work on  many work orders
An employee is only listed once on each work order
Each employee has an hourly rate that is the same for all customers

Here is what I have I think Im off on a few things and I really wanna understand this and get this right:

Tbl Contacts (Contact ID, Company ID, First Name, Last Name)
FK: Company ID¿ Tbl Company
Tbl Company (Company ID, Name, Address, City, State)
Tbl WorkOrders (WorkOrder ID, Date Received, Date Required, Contact ID)
FK: Contact ID¿ Tbl Contacts
Tbl Computers (Serial Number, Make/ Model, WorkOrder ID, Employee ID)
FK: WorkOrder ID¿ Tbl WorkOrders
FK: Employee ID¿ Tbl Employees
Tbl Parts (Part, Quantity, Unit Price, WorkOrder ID)
FK: WorkOrder ID¿ Tbl WorkOrders
Tbl Employees (Employee ID, First Name Last Name)
Tbl Employee Pay (WorkOrder ID, Employee ID, Billable Hours, Billing Rate)
FK: WorkOrder ID¿ Tbl WorkOrder
FK: Employee ID¿ Tbl Employees

0
Question by:lessthanmark

LVL 84

Assisted Solution

When storing data, you should strive to insure that a single table stores only those attributes that are relevant to that object. For example, if you're storing information about a company's automobiles and their employees, it wouldn't make much sense to store the value of an automobile's paint color in the Driver table since that has nothing to do with the Driver.

So review your tables with an eye toward that. You've got some inaccurate data storage in some tables, and you need to really decide which data should be stored in the correct tables before moving forward through the normalization tree.

Other things to consider:

How do you determine which parts are associated with a WorkOrder?

Also be aware of the concept of "one to many" vs "many to many" - for example, you state that a Workorder may require multiple parts, and that a Part can appear on multiple WorkOrders, yet you don't have any sort of convention to store this sort of relationship (hint: look for information on Join or Junction or CrossReference tables). In essence, you would need to define a Many-to-Many relationship between workorders and parts.

Can more than one Employee work on the same WorkOrder?

What is your intent for the EmployeePay table?

Side note:

Please please please review the concept of "nameing conventions". You can search for this to get more information, but in essence the concept is to properly name your objects so that they are uniquely named, have some sort of indication as to what they do, and conform to standard database storage expectations. For example, a column named Date doesn't really tell you anything about the contents of that column, but a column named EmpHireDate lets you known instantly that the data stored in that column is almost certainly the Date and Employee was Hired.

Also be very aware of spaces and non-alpha characters in your names. For example, the column WorkOrder ID should instead be WorkOrderID, and the column "Make/Model" should really be "MakeMode" - or, better yet, 2 separate columns.

0

LVL 56

Assisted Solution

3nf means three things:

1. Every data value is atomic (is a single value) - Name: "Jim Dettman" is not atomic.  It's a first and last name, so it should be two fields.  What's in the field represents two pieces of data, which are sperate things.

2. Every nonkey column should depend on the entire primary key - That's a fancy way of saying what LSM said; each field should belong to one "thing" and since each table represents a different type of "thing" (i.e. Companies), Company Name belongs there and not in the table for work orders.

3. Fields should be mutually exclusive - That is they should not depend on one another for their values.  Extended labor in your case would be an example of this  (hrs * rate = cost).  You would not store the cost in the work order detail, but rather hrs and rate.   Cost would be calculated as needed.

If you did store the cost, and someone updated the hrs, but forgot to change the cost field, then you'd have inaccurate data.

Before you even get to the normalization part however, first list every attribute (field) that you will need to statisfy the statements you gave.

For example, "Each employee has an hourly rate that is the same for all customers"  that means that you need a single employee pay rate.

After that, group the attributes where you think they belong together.  For example, for companies, you will have name, address, phone #, etc.   Ditto for Employees plus the aforementioned pay rate.  Then think about work orders.

When you have that, determine your primary key for each grouping (you may need to add an attribute to do that, such as Work Order Number).

Now start applying the normalization rules.  #1 is easy.  #2 may lead you to breaking out fields into seperate tables (ie. work orders can have *many* employess and you don't want "Emp1", "Emp2", "Emp3"... in the work order table because you never know how many you will have).

Give that some thought and let us know if you have any questions.

Jim.
0

LVL 41

Assisted Solution

The best (and short enough) help is probably here: http://www.bkent.net/Doc/simple5.htm
0

LVL 26

Accepted Solution

I personally like the tutorial at http://www.phlonx.com/resources/nf3/ best.

The hardest thing is the first thing: what is the central unique item?
In this case it is the WorkOrder.
Create the table.
Put in it only the things that depend directly on the work order, and only one datum
<and only one datum> What does that mean?
If one and only one company can originate each work order then a CompanyID belongs in the WorkOrder table.
More than one employee can work on each work order so EmployeeID does NOT belong there.

Now do the same for all the other unique things (equipment, employees, companies etc)

The next hardest part: how to model the one to many relationships
So, many employees can work on one work order.
We need a table for that.
It'll have a foreign key for work order, and a foreign key for employee.
What else?
It'll have an auto-number primary key that uniquely identifies each record!
That's the key part.  The auto-number doesn't come from the data itself--it comes fr0m your need to identify records uniquely.
What else?
Since employees can only be on a work order once, the hours and rate can go here too.

Parts will be similar.

How many contacts can a company have?
If only one, then it can go in the company table.

Are contacts work order specific or company specific?
If a work order can have only one contact, then the contact could go in the workorder table.

If multiple contacts per company or per work order are possible, then they need their own table.

It's only 12 pages.
You'll get there.

A picture is worth a thousand words.
This image doesn't model your data and is by no means perfect, but tblJobs is the main table, and tblVehicles has lots of repetitive data that could be separated out
You'll get the idea
Relations.jpg
0

LVL 142

Expert Comment

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

## Featured Post

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…