March 27, 2012 at 5:55 am
Hi Guys
i relly need help i need to do normalisation but i cannot get my head wraped around it
can someone please help me and normalise this data plz
Customer ID
Customer Forename
Customer Surname
Street
Town
Postcode
Category
Order ID
Order Date
Stock ID
Stock Description
Price
Qty in Stock
Supplier ID
Supplier Name
Supplier Address
Supplier Town
Supplier Postcode
Supplier Tel No
March 27, 2012 at 6:02 am
First step: Identify entities and candidate keys. What different 'things' are there in that set of attributes? What column or set of columns uniquely identifies a row in that each of those things?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 6:10 am
customer id
order id
stock id
supplier id
March 27, 2012 at 6:19 am
So the 4 entities that you've identified are:
Customer
Order
Stock
Supplier
Next step, which of the attributes that you've identified belong to which entity?
p.s. CustomerID sounds like an artificial primary key, like a identity column. Way, way, way too early in design to polan or use that. For now ignore the artificial primary keys (customer id, order id, stock id, supplier id) and look at the meaningful data columns only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 6:29 am
Customer
Customer Forename
Customer Surname
Street
Town
Postcode
Category
Order
Order Date
Stock
Stock Description
Price
Qty in Stock
Supplier
Supplier Name
Supplier Address
Supplier Town
Supplier Postcode
Supplier Tel No
March 27, 2012 at 6:34 am
Looks to me like that's mostly done.
Does orders really only have an order date? No other information about what was ordered and how much was paid?
Last step here I think, which tables refer to which other tables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 6:39 am
what else can i put in there as i have to make this up
i want to keep it simple as possible
what is my 1nf 2nd 3nf
March 27, 2012 at 6:51 am
I had a feeling this was homework...
Seriously, what else constitutes an order? Think about orders you've placed before, online sites or the like.
These tables and this design is so simple that it will likely be in 3rd normal form automatically once a basic design is done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 6:57 am
description & price
March 27, 2012 at 7:01 am
If you think they are part of order then put them there, if they don't belong it'll be picked up later. Just don't say description, that doesn't give enough details. What description? What is it, what data is there?
What else?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 7:04 am
qty ordered
description - what product was ordered
March 27, 2012 at 7:06 am
Ok, put them in for now. So what do you have at this point for entities and attributes?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 7:08 am
Customer
Customer Forename
Customer Surname
Street
Town
Postcode
Category
Order
Order Date
description
price
qty ordered
Stock
Stock Description
Price
Qty in Stock
i have a price in stock and order shall i take out the one in stock
Supplier
Supplier Name
Supplier Address
Supplier Town
Supplier Postcode
Supplier Tel No
March 27, 2012 at 7:10 am
Which of those tables needs to reference one or more of the others? So what 'foreign keys' do you need?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2012 at 7:12 am
order and supplier has a foreign keys
Viewing 15 posts - 1 through 15 (of 59 total)
You must be logged in to reply to this topic. Login to reply