Normalising to 1st, 2nd and 3rd normal form

[JamesBowtell]

hey all

i have a 2nd hand book about normalisation with some tutorials, but the answers to the questions are not provide. i was just wondering if any one could normalize the below data so that i can see how it's done.

i know the rules for 1st 2nd and 3rd NF but not sure where to exicute them.

Car Hire paper based un-normalised data below

Garage File:

garage ID
garage name
garage address.

Customer File:

Customer ID
Customer name
Customer address

Driver File:

Driver Employee No
Driver name
Date Joined
Date obtained license

Car File:

Registration No.
Date purchased,
Cost,
Number of seats,

and for each service performed on that car,

garage ID
garage name
garage address
service date
service cost

Hire Agreement File:

showing, for each agreement,
Hire agreement No.,
Customer ID,
Customer name and address,
total no of days hired,
start date,
end date,
total cost of hire

and, for each car in the agreement,

Car registration number
running total of mileage for this car on the agreement
and, for each day of a car on an agreement,
Driver Employee No.
name of the driver.

[Mike Hildner]

When you say "for each service performed on that car", are those repeating fields, or would it be a new record every time you have a new service? Same basic question for the Hire Agreement File. BTW, I'm no expert, just thinking a small exercise would be fun, and some guru might provide the real answer

[JamesBowtell]

I'm not sure, but i think they could be repeating groups.

[Mike Hildner]

Not sure what you're doing, maybe you could get sample data. Two way different things if it's repeating groups or if it's another row. How can you normalize a DB if you don't even know it's structure?

Edit: Hint - if this is homework, it's probably repeating fields. Is it homework? If so, wanna guess why I'm guessing repeating fields?

[erickwidya]

not sure if this correct..coz it seems the answer already given and not enough explanation what u want to achieve

Garage = @GarageID + GName + GAddress
Customers = @CustomerID + CName + CAddress
Drivers = @DriverID + DName + DJoin + DLicenseDate
Car = @RegistrationNo (i prefer @CarID) + CDatePurchased + CCost + CSeats + GarageID
EDIT : Services = @ServiceNo + RegistrationNo + SDate + SCost
HiresAgreement = @AgreementID + CustomerID + HiredDate + StartDate + EndDate + ACost + RegistrationNo
CarTotal = @AgreementID + TotalMileage
CarPerDay = @AgreementID + @Date + DriverID

cheers

[JamesBowtell]

What i want to know is how to get the data i provide into

1st 2nd and 3rd normal form

3rd Normal form would be somthing like this below, I THINK?


Driver
Driver Assignment
Hire Agreement
Customer
Car on Hire
Car
Maintenance
Garage


But not sure how to get 1st and 2nd NF, for me to understand normalisation i would need to understand the eariler steps

[erickwidya]

if then..u should not start it at lots of table..like at ur 1st post..
consider that u got whole field at one table..
ex: at ur HiresAgreement table..
then u execute the 1st nf against it..and so on step by step..

and read a lots example about it..so that u can understand what it's mean under different scenario..

cheers

[Ecniv]

This is probably the easiest way.

1)
Write out on a piece of paper every field you are going to use to hold the data (just the data)
2)
Group these fields together so that there is no repeating data anywhere. Example: Garage Group holds all the details of the garage and nothing else.
3)
In these groups, are there fields which are repeating, such as the same type of information but for different reasons (example would be telephone, fax and mobile numbers... essentially the same). In third normal you'd split this into another group.
4)
You should now have a list of groups, with fields under them which appear nowhere else in the structure (no duplicate data). This is also your table layouts (general) and require only foreign and primary keys adding to link them together.

Have a go and post up what you think is the structure with all the fields... You could put it in a text file and show the list of fields initially, the groups (2) and the split groups (3).


Vince

[JamesBowtell]

Sorry aint got a qlue what to do or where to start eventhough u have explained it.


So i have done what u said:

The below data is in un-normalised form

Garage ID
Garage name
Garage address

Customer ID
Customer name
Customer address

Driver Employee No
Driver name
Date Joined
Date obtained license

Registration No
Date purchased
Cost
Number of seats

Garage ID,
Garage name
Garage address
Service date
Service cost.

Hire agreement No
Customer ID
Customer name
Customer address
Total no of days hired
Start date
End date
Total cost of hire

And, for each car in the agreement,

Car registration number running total of mileage for this car on the agreement

And, for each day of a car on an agreement,

Driver No
Name of the driver


How do i get it to 1st Normal Form? I know i have to remove repeating groups and put them in another table, but what are the repeating groups???????????

[erickwidya]

PS : plz correct me if i'm wrong

ur table seem doesn't have repeating groups..so that is on 1nf

repeating groups is something like this
ex :Customers --> one customer can have maximum 3 address..so the wrong design should be something like this

Customer = CustomerID + CName + CAddress1 + CAddress2 + CPhone1 + CPhone2
value ex : ID1 + erick + myaddres1 + myaddress2 + myPhone1 + myPhone2

the CAddress1, CAddress2, Cphone1, Cphone2 is the repeating groups..so when u apply 1nf..the table should be something like

Customer = CustomerID + CName + CAddress + CPhone
value ex : ID1 + erick + myaddress1 + Cphone1
value ex : ID1 + erick + myaddress2 + Cphone2

the above is at 1nf..

for the 2nf..sorry i forgot what is the description..can u tell me?

[Mike Hildner]

ok, the only reason I thought it might be repeating fields is because that is what you do with the first normal form. I guess I was thinking it sounded like homework, and you might as well start from the beginning.

Second normal form, from what I understand, says to create a seperate table for records that represent a unique state of the master record, rather than create a new row in the same table. This is a classic header-detail scenerio. That being said, one may wish to "flatten" the data, because it's much easier for the app to deal with, and in reporting type databases, it' just plain faster.

As usual, I probably have no idea what I'm talking about.

Mike

[erickwidya]

thanks Mike..that's my description too..but i need the 'formal' description to keep in mind

anyhow..according to Mike
the 2nd NF should be something like this

Customer = @CustomerID + CName
CustomerDetails = CustomerID + CAddress + CPhone

u can't avoid the redundant CustomerID at CustomerDetails..it's part of the process (i think )

well..i think it's at 3rd NF too..(from case like above)
all u have to do..is to repeat each step for ur tables

PS : plz correct me if i'm wrong ok..
regards

1st NF is...???
2nd NF is...???
3rd NF is...???

[Ecniv]

Originally posted by JamesBowtell
The below data is in un-normalised form

Garage ID
Garage name
Garage address

Customer ID
Customer name
Customer address

Driver Employee No
Driver name
Date Joined
Date obtained license

Registration No
Date purchased
Cost
Number of seats

Garage ID,
Garage name
Garage address
Service date
Service cost.

Hire agreement No
Customer ID
Customer name
Customer address
Total no of days hired
Start date
End date
Total cost of hire

You should be able to group them without duplicating it.

Here's a link and a quote

First Normal
Recall that 1NF sets the very basic rules for an organized database:
· Eliminate duplicative columns from the same table.

· Create separate tables for each group of related data and identify each row with a unique column (the primary key).

So going back to your structure...
You need to group things so that they appear once in the structure (forget the ids for now). Once you have that, you add Primary key IDs for each group. Then in the groups that relate to another group you need to add foreign keys (the id of the related group).

Example:
Garages - GarageID - GarageName - GarageAdd1 - GarageAdd2 - GarageAdd3 - GarageAdd4 - Postcode - Tel
Serviced - ServicedID - ServiceDate - ServiceCost - GarageID

You are pretty close. It may be worth sketching boxes for each group to see how they relate to each other. If it is for a class, check your books, they never ask for stuff you haven't covered unless its the next section.


Vince

[JamesBowtell]

I have read all the articals u have suggested and came up with this as 1st Normal Form...


1st Normal form

Garage File:

Garage ID,
Garage name and address.

Customer File:

Customer ID,
Customer name and address.

Driver File:

Driver Employee No,
Driver name
Date Joined
Date obtained license

Car File:

Registration No
Date purchased
Cost
Number of seats

Maintenance File:

Garage ID
Name and address of garage
Service date
Service cost.

Hire Agreement:

Hire agreement no.
Customer ID
Customer name and address
Total no of days hired
Start date
End date
Total cost of hire


Driver Assignment:

Driver Employee No.
Name of driver.

Please correct me if i'm wrong..

[erickwidya]

i'm sorry i deleted my previous post..
my opinion is the result of normalization will be tables that have a relationship between them..

ur table is 'missing' something..there should a relation that makes u want to separate this into other table.
like Car File and Garage File..it should be related right?
each garage can have one or more car..is this correct? if it's then the UNF maybe like this

Car File = RegistrationNo + Date purchased + Cost + Number of seats + GarageName + GarageAddress
this is already at 1nf coz there's no repeating group (see my prev post about it)..

confuse?

i have an example about it..but it use for different scenario..do u want it? so it'll help u see other scenario..
regards