Desperate Normalisation help! Watch
I am taking the g064 ocr ict course and have got to the designing stage of normalisation. The company I am creating a database for is an ebay shop that sells model cars.
This is what I currently have.
CUSTOMER (CustomerID, CustomerForename, CustomerSurname, CustomerAddress 1, CustomerAddress 2, CustomerPostcode, CustomerMobileNumber, CustomerLandlineNumber, Customer Email Address, Existing customer?)
ORDER (OrderNumber,CustomerID, VehicleID, Cost of item, VAT, Total Cost of Order, Order Date)
VEHICLE (VehicleID, OrderNumber, SupplierID, Purchase Price, Sale Price, Dispatch Status, Brief Description)
MEETING (MeetingID,SupplierID, Meeting Address 1, Meeting Address 2, Meeting Postcode, Time of meeting, Date of meeting)
SUPPLIER (SupplierID, Supplier Forename, Supplier Surname, Supplier Address 1, Supplier Address 2, Supplier Postcode, Supplier Mobile Number, Supplier Landline)
PAYMENT (PaymentID, CustomerID, Payment Method, Payment Received? Date of payment received,Customer Owes, Customer past payments, Payment Prompt 1, Payment prompt 2)
The foreign keys are in italics with primary keys underlined.
Does this look correct for 1NF? I am unsure how to go about making it 2NF and 3NF as I do not have any many to many relationships. Well, any that I know of! I may have done this wrong and need you guys to help me.
If you were able to reply ASAP that would be of great help as I've looked all over online but cannot find any proper help. I dont want to fall behind further in lesson just because I am stuck and worried about this particular stage of the project.
Thanks very much
Whilst this database model would work, it won't work well.
I don't want to do this for you, so here are some thoughts to point you in the right direction...
You need to consider your two most important tables: CUSTOMER and VEHICLE.
In this scenario it's what you're selling, and to who.
Your CUSTOMER table should include all the information about your customer - so their name, address and contact details.
(BTW, what is 'Existing Customer?' for?)
Your VEHICLE table should include all the information about the model vehicle that is for sale - so it's name, description and price.
In an ebay shop how would their product (VEHICLE) be sold to a CUSTOMER?
The CUSTOMER would make an ORDER for a VEHICLE.
In your ORDER table you would need your foreign keys (to CUSTOMER and VEHICLE) so you know who's buying what. You would also want to check on the PAYMENT of the order here.
Take another look at your relationships and and check whether you have your fields in the right tables.