Normalisation Help
Apr 18, 2007
Hello. I am trying to understand normalisation at the moment. I'm still a bit lost but I have tried to normalise this example. Is it right?
0NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode, ItemID1, ItemName1, ItemPrice1, QuantityOrdered1, ItemID2, ItemName2, ItemPrice2, QuantityOrdered2, ItemID3, ItemName3, ItemPrice3, QuantityOrdered3)
==================================================
First Answer
1NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)
ITEM(OrderID, ItemID*, ItemName, ItemPrice, QuantityOrdered)
2NF
CUSTOMER(CustomerName*, HouseNumber, Street, Town, Postcode)
ORDER(OrderID*, OrderDate, CustomerName)
ORDERLINE(OrderID*, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)
3NF
CUSTOMER(CustomerName*, HouseNumber, Postcode)
ADDRESS(Street, Town, Postcode*)
ORDER(OrderID*, OrderDate, CustomerName)
ORDERLINE(OrderNumber, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)
or is it
Second Answer
1NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)
ITEM(OrderID, ItemID*, ItemName, ItemPrice, QuantityOrdered)
2NF
ORDER(OrderID*, OrderDate, CustomerName, HouseNumber, Street, Town, Postcode)
ORDERLINE(OrderID*, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)
3NF
CUSTOMER(CustomerName*, HouseNumber, Street, Town, Postcode)
ORDER(OrderID*, OrderDate, CustomerName)
ORDERLINE(OrderID*, ItemID*, QuantityOrdered)
ITEM(ItemID*, ItemName, ItemPrice)
Thank you for helping :D
View Replies
May 19, 2005
This is a bit of a wierd question but as part of my assessment for university I have had to build a video rental database. My problem, however, is with the normalisation.
In my head I go straight from the first step to the last and no matter how much i have tried I cannot manage to comprehend the 1st, 2nd and 3rd steps thing and we have to show these in the writeup. Can anyone help explain this to me?
Thanks in advance for your help.
View 1 Replies
View Related
Sep 16, 2005
I have a hire vehicles.
Vehicles have a colour.
Because vehicle details - ie colour change over time , I want to record this against the hire ( it was the colour of the vehicle at the time of hire) - the fact the colour of the equipment has phsically changed is not important to my hire table.
Say I had a field on the vehicle table which was a code linked to the colour table. I want to copy the colourID to the hire. - Would my database be normalised if I did this.
Further more - colour is a legal requirement - and is more than just a decription - although I can change all items from red to pink in my colour table, I need the hire to tell me what the description of the colour was (the description of the colour- the actual colour has not changed) when I hired the item.
How would I arrange this?
View 3 Replies
View Related
Sep 12, 2006
I am seeking advice on a database with currently over 40,000 records, which was pulled off the internet from NOAA for weather related records.
The spreadsheet that they were using looks like this:
GridNumber | Year | IntervalNumber | Index Value
Each grid has history back to 1948 and each year has 6 intervals with an index value to each interval.
What I think would be better is have:
GridNumber | Year | Interval 1 | Interval 2 | Interval 3|.....| Interval 6|
And have the index value for each interval by year.
I am wanting to add some unbound fields on a form and create a "rate of payment" for each interval's index value and some other thing.
Does anyone one know of a way of moving this data over to over to a new format in an easy manner? And would it be the thing to do?
I have attached the db for looking at as anyone would be able to pull this info off the net. I removed several 10,000s records to allow for space.
I would sincerely appreciate any insight anyone could give on the subject.
View 6 Replies
View Related
Mar 16, 2008
I have a table that tracks a series of (mostly) date-based events and I'm trying to decide whether to further normalise the table.
The process works like this:
1. We are notfied of a problem (PBTCID PK, PropertyID FK, DatePBTC)
2. We send a recorded letter (DateRecLet)
3. We send a 1st class letter (DateFirstLet)
If no response to letters then:
4. Problem assigned to a department (DateHousing)
AND
5. Problem assigned to specific person (HO - numeric, to lookup)
If no response after their involvement then:
6. Request possession order (DateReqNSP)
7. Possession order served (DateServNSP)
If no reponse after order is served then:
9. Go to court (DateCourt)
10. Record outcome of court proceedings (Outcome - numeric to lookup)
At present, all of the above is in one table. I would say that 70% of the time, the problem is resolved at step 3. A further 25% is resolved at step at 4/5. Another 4% at step step 7, with the remaining 1% going all the way to step 10.
Am I better off with one table, even though the majority of proplems won't go beyond step 3? If so, what would be the best way to break this out?
Any direction would be much appreciated.
View 2 Replies
View Related
Jul 14, 2006
I have a Table Structure that works fine, but I want to trim it down a little.
I know that a few tables aren't normalised properly.
Three of the tables (tblCustomer, tblSupplier, tblHaulier), could be trimmed down to two tables (tblCompany) with a link to (tlkpCompanyType).
This would then allow me to make just one "Contacts" table, etc etc.
My problem is, I do not know how to refer to both a Customer AND a Haulier in the tblShipments or the tblShipBookings, as both of these tables need to have the names of both the Customer AND haulier in them (or at least the foriegn key).
You can see a jpeg of the database relationships here (http://www.joyceandstevieb.com/dbasemap.htm)
Any advice would be most welcome.
Thanx
View 14 Replies
View Related
Jul 15, 2006
I have a Table Structure that works fine, but I want to trim it down a little.
I know that a few tables aren't normalised properly.
Three of the tables (tblCustomer, tblSupplier, tblHaulier), could be trimmed down to two tables (tblCompany) with a link to (tlkpCompanyType).
This would then allow me to make just one "Contacts" table, etc etc.
My problem is, I do not know how to refer to both a Customer AND a Haulier in the tblShipments or the tblShipBookings, as both of these tables need to have the names of both the Customer AND haulier in them (or at least the foriegn key).
You can see a jpeg of the database relationships here
Any advice would be most welcome.
Thanx
View 2 Replies
View Related
Oct 13, 2005
Why is it common to store address fields
ie Address1, Address2, Address3, Address4, Town etc in a table, seems to me that is as un normalisaed as
having fields like description1, description2, description3 etc which we would never do and put in another table as seperate records rather than fields.
Whats the differnace that makes address more acceptable to be un normalised?
Confused as ever , Paul
View 7 Replies
View Related
Feb 25, 2006
Hi all this is my first post so go easy
OK i have a project to do about a garage
these are the unormalised attributes
Customer_ID, First_Name, Surname, Address, Telephone_No, Postcode, Employee_ID, First_Name, Surname, Hours_worked, Service_ID, Name_of_service, Cost_of_service, Car_registraion, Engine_size, Colour_of_Car, Car_manufacturer, Invoice_No, Amount_due, Amount_paid, Outstanding_amount, VAT, Cost_of_service, Booking_code, Date_of_booking
these are the entities
CUSTOMER
SERVICE
CAR
PAYMENT
EMPLOYEE
BOOKING
ive never done normalisation before, and read online a bit about it but cant for the life of me understand it. anyone willing to give me a little help. on converting the above to 1NF, 2NF and 3NF?
Thanks in advance
alison
x
View 2 Replies
View Related
Nov 17, 2005
Hi
I have a timesheet db.
An employee can have upto classification for hours -
1. RT - Normal time
2. OT - Over time
3. DT - Double time
I have a main form which the theme is on the task and per day and this can have many employees, which is the sub form. If someone works RT, OT in a day it means there names appear twice when the hours are entered. At the weekend it is always DT.
I have the RT, OT & DT as a look up for the field.
Does this meant that i could set up the datasheet subform better?
Thanks
View 6 Replies
View Related
Mar 20, 2008
Anyone who can help, i have encountered a problem :confused: when trying to normalize tables. I have single paged word document attached for anyone capable to help. All advice and suggestion is welcomed.
View 5 Replies
View Related
Jun 8, 2005
Hi everyone,
I'm trying to get to grips with normalisation as it applies to a specific example database I'm working on (that I will ultimately be using to teach other people :eek: ). If anyone could help with the following issue I would be very grateful:
I have a table of football (or soccer) teams (TEAMS) with the fields
team_id
team_name
I also have a table of results (RESULTS) including the fields
result_id
result_hometeam
result_awayteam
result_homescore
result_awayscore
etc.....
The result_hometeam and result_awayteam fields both contain team_id as links to the TEAMS table.
Does the fact the team_id appears in two columns in the RESULTS table break any normalisation rules?
Should I have it split further so each row shows one team and their home/away status is included as another field?
I've confused myself with this stuff. :confused:
View 4 Replies
View Related
Aug 22, 2006
Hi,
I would like to get your advice on my table setup and relationships for this payroll project. The company is an engineering company with Projects (or construction sites) around the world.
The 'Candidates' are current or potential employees and contractors. There are three main pay categories:
1.Shift-workers
All shift workers doing a particular job on a particular project are paid the same rates e.g. all welders on a particular project or site in England are paid the same as each other. For that reason I want to link the pay rates with the job description for these workers. This avoides creating 50 records for 50 welders on the site in England to say that they make £10 an hour normal time (or whatever it is) etc.
2. Contract
Contract workers usually get paid a flat rate per hour. As these are negociated on an individual basis I would need to have this information linked to each individuals job (M_CandidateJobDetails).
3. Salary
Again this information needs to be input for each individuals job.
For the contract and salary people the pay frequency can vary (weekly, bi-weekly or monthly). So can the currency they are paid in. I haven't got as far as the currency issue yet.
The reason for the one-to-many relationship between M_JobClassifaction and M_CandidateJobDetails is that many candidates can have the same type of job e.g. there can be many employees that in the job classifaction of 'Electrician'. For many of the jobs at managerial level e.g. 'site manager' there will only be one.
I will have a table with the hours worked by each person per week. I can use this for those on shift work or contract to calculate what they will be paid.
One of the main reasons for this database is so that the company can print reports to see what is paid out in payroll for each site and in total (in euros). These will be gross figures and I don't need to take expenses, vacations, bonuses or taxes into account. They other thing we will need to be able to do is assign candidates to vacant positions and change them from one position to another - possibly between different projects.
So basically does anyone have any comments on the relationships, normalisation or anything else. Is this the best way to do it?
I've attached a screenshot of the relationships.
View 13 Replies
View Related
