hi guys

i really need you tech boffins help for normalising some tables for my a database for my taxi company that i have brought. i'm quite a technical inthusiast and i have read on many websites that when designing a database it is important to have normalised tables. so can any one help me normalise my fields i have it in unormalised form. (below). i have looked at tutorials on websites and they don't seem relevant to my database and i would like to learn how to do this for my database. so could some one show me how to do first normal form and then second normal form and then third normal for please.

here is unnormalised form.


we have a number of offices located in the main cities of Scotland

office number
address
street city
postcode
phoneno.

Each office has four kinds of staff: a Manager, several taxi owners, administrators and a number of drivers. Staff can be located at more than one office


manager
first name
last name
address
salary
d.o.b


An owner provides one or more taxis

taxi owner
first name
last name
address
salary
d.o.b

administrator
first name
last name
address
salary
d.o.b

driver
first name
last name
address
salary
d.o.b

taxi
registration no.
model
make
colour
milage
M.O.T due date
capacity


taxis are not available for hire by the public hailing a taxi in the street but must be requested by first phoning the us to attend a given address. There are two kinds of clients-private and business


The business provided by private clients is on an ad hoc basis. The details of private clients are collected on the first booking of a taxi

Private customer
First name
last name
telephone no.
client no.


business provided by business clients is more formal and involves agreeing a contract of work with the business.A contract stipulates the maximum number of jobs that Critter-Cabs will undertake for a fixed fee


business customer
max jobs
fixed fee
start date
end date
Description //whether it is a night job or a day job
name
address
telephone no.
fax
contract no.

When a job comes in the appropriate details are recorded for either a private client or a business client.

If the job is requested by a business client then the contract number is also recorded

jobs
contract no.
pickup date
pick up time
pick up address
drop off address
milage used
charge made
job completed / yes or no

Does the Address for the manager mean their home address or the address of the office?
If a manager can be associated with more than one office then you need a relationship between a unique identifier for the Manager, maybe a staff id and an a code id for the office.

It seems to me that as yet you haven't really identified the fields you wish to
hold yet. The design is getting there though.

How will you uniquely label members of staff? Once you have all this down then group your tuples in a table design.

Work out what is primary key is each set of tuples and what refers to the other sets, the foriegn keys. The primary keys will probably be things like staff id, office id, taxi id, client/customer id.

Do you really need to store salary in this table? Who will be the main users? If it's just for storing bookings then that info isn't really needed.

Write down your schema. Then you can begin to see what normal form it is in and apply higher levels to remove redundancy. Once you have your tuples write them down in table form and we can see what normal form they are in.

Nick

yes the address for the manager means home address and i think your idea to have a staff id is good

it is necessary to store salary in these tables for my records- i will be the main user who will be inputting the paper records into the database so i can keep track of everyting and work out our strenght and weaknesses and generally keep n top of things.

here are the tables i have though of- i think i might have just done 1st normal form below- what do you think?

* = primary key ** = foriegn key// i was'nt sure what to put down for foriegn keys ,so i don't think they are all there
-----------------
*office id
street
city
postcode
phoneno

----------------

*Staff_id
Staff Type
first name
last name
address
salary
d.o.b
------------------

*taxi_id
registration no.
model
make
colour
milage
M.O.T due date
capacity

--------------------
//private clients

*client no.
First name
last name
telephone no.

-------------------
//business clients

*contract no.
max jobs
fixed fee
start date
end date
Description //whether it is a night job or a day job
name
address
telephone
fax
------------------------------

*job_id
**contract no.
pickup date
pick up time
pick up address
drop off address
milage used
charge made
job completed / yes or no

Yep that in 1st Normal Form.
Well the address worries me a bit. You need a standard format for it. On one table it's all in one field and in the other it's split in several fields.

It may even be in 2nd Normal form if we can sort out the address storage.

is this better? what about the primary/foriegn keys


* = primary key ** = foriegn key// i was'nt sure what to put down for foriegn keys ,so i don't think they are all there
-----------------
*office id
street
city
postcode
phoneno

----------------

*Staff_id
Staff Type
first name
last name
street
city
postcode
salary
d.o.b
------------------

*taxi_id
registration no.
model
make
colour
milage
M.O.T due date
capacity

--------------------
//private clients

*client no.
First name
last name
telephone no.

-------------------
//business clients

*contract no.
max jobs
fixed fee
start date
end date
Description //whether it is a night job or a day job
name
street
city
postcode
telephone
fax
------------------------------

*job_id
**contract no.
pickup date
pick up time
pick up address
drop off address
milage used
charge made
job completed / yes or no

Getting there. How do link the taxi used to the journey? I assume that will have to appear in the table with the start and end times.

If you have a table that has a primary key that isn't referenced in another table then you need to ask why you have that table. At the moment what links the job to the driver and taxi used?

In a true relational design you can't have a null value in a column so if you want to represent a private client then the business account field will be null at present. You may wish to have another relationship/table with a tuple of job id and business account id.

Then if this table has no row you know it's a private account.

Go through the tables and make sure that the primary key determines all the other values. If so you're looking good for 3rd normal form.

sorry but i think i have missed out 2nd normal form- what would my tables look like in second normal form is it as above.

It is probably in 2nd Normal Form.

The only thing that could give you repeated blocks of information is the address fields and it's unlikely you'll store so much information that the City implies the state.

By writing it down in a tuple form or table then you force 1 NF. You've split the information into seperate relations and I can't see any repeated groups that would require you to split it further.

You might wish to have an address table with a unique address id. Then all addresses could be stored in there and any addresses in the tables could be keyed off that. Useful if you have you pick up from the same address as the client is registed to.
Then you're in 2 nd Normal Form.

Looking at the tables it appears that the primary key does drive everything else in the table. You just need to link things properly.
In which case you're in 3rd Normal form if I understand your data correctly.

Does the driver imply the car? Can one driver have more than one car?
Is a particular job linked to a car, a driver or both?

A job id implies the pick up times but it may map to zero or 1 contract numbers so split the Job Id and Client Id tuple off into a separate table.

A good thing to do now is start filling these tables with real data and see if you could store a valid booked journey in them. With the additional foriegn keys I've suggested I believe you could.

You could then post a Use Case that I can sanity check.

sorry for the late reply but i have been looking at it the last few days and still quite work out for sure where i have 1st 2nd and 3rd normal form can u show me please

i know this sounds wierd but i would like to know so that i can create a good set of documentation for this database, if i need to sell this company on i need people to know i made a good go at it.

Read through something like

http://www.databasejournal.com/sqlet...6861_1428511_4

If you can put your rows in a table then you're in first normal form. Don't worry about it.

I can't tell from your column names alone any more info than that.
You have to look at the data to see if when you put it into your tables that there is no replication which is what 2nd and 3rd normal form are all about removing. I think you're there or nearly so.
Without having access to your data I can do no more.

Try a dry run putting in the data you think the tables should hold and working out if you can create queries that will work for what you want. Then it will become more obvious if any of the tables need decomposing further.

Hi

Please check the attachment in doc format. Hope the solution is upto thelevel of your expectataions. Please give the status of the solution.


Have a nice time

thanks for that- i have now done what i think is the solution and set it out in UNF 1NF 2NF 3NF for you to check. i have also included some sample data that we currently have from our paper system.

has n e 1 had a chance to have a look at it yet

hey ithink i've done what you've asked now can u see the file attachment in this post to see if i've done i right please

Looking at your data, it is not normalized. There are many violations of second normal form. For example you should have a make table and a model table because your make and model data repeats. Having a city table would be good normalization as well.

I want to point out that achieving 3rd normal form is not the ultimate goal. You have to balance the transaction efficiency of a normalized database against the reporting efficiency of a denormalized database. However, normalizing your data first is the right step. Normalize it to the limit, then denormalize it until the number of joins needed for reports is efficient.