SurfingHamster
asked on
Normalization to 3NF; shopping cart data
Could you normalize all this data (to 3NF) showing the (1) data layout at each stage, (2) the data type it should be set to in MSSQL Server and (3) the relationships of entities by prefixing field names (e.g. PK_UserID) or other appropriate means. I realise it's set out into entities already (I believe this is 1NF - yes?).
1.9.1 Users
User ID, Password, Telephone Number, Email Address,
Name, Company, Billing Street, Billing Town,
Billing Postal Town, Billing County, Billing Postcode, Billing Country
Registration Date, Mailing Subscription
1.9.2 Administrators
Admin ID, Password, Name, Last Login
1.9.3 Categories
Category ID, Description , Parent Category, Default Image,
Category Status
1.9.4 Products
Product ID, Description, Details, Price Per Unit
Default Image, Stock Level, Shipping Cost, Lead Time
Product Rating, Total Ratings
1.9.5 Orders
Order ID, User ID, Description , Order Date,
NET Total, Total, Address, Shipping Street,
Shipping Town, Shipping Postal Town, Shipping County, Shipping Postcode,
Shipping Country, Tracking Status,
1.9.6 Billing Details
Order ID, Card Type, Card Number, Expiration
CVV Code, Name on Card, Card Street, Card Town
Card Postal Town, Card County, Card Postcode, Card Country
1.9.7 Wish List
User ID, Product ID, Description, Price Per Unit
1.9.8 Cart
User ID, Product ID Quantity
1.9.9 Settings
Setting ID, Store Status
1.9.1 Users
User ID, Password, Telephone Number, Email Address,
Name, Company, Billing Street, Billing Town,
Billing Postal Town, Billing County, Billing Postcode, Billing Country
Registration Date, Mailing Subscription
1.9.2 Administrators
Admin ID, Password, Name, Last Login
1.9.3 Categories
Category ID, Description , Parent Category, Default Image,
Category Status
1.9.4 Products
Product ID, Description, Details, Price Per Unit
Default Image, Stock Level, Shipping Cost, Lead Time
Product Rating, Total Ratings
1.9.5 Orders
Order ID, User ID, Description , Order Date,
NET Total, Total, Address, Shipping Street,
Shipping Town, Shipping Postal Town, Shipping County, Shipping Postcode,
Shipping Country, Tracking Status,
1.9.6 Billing Details
Order ID, Card Type, Card Number, Expiration
CVV Code, Name on Card, Card Street, Card Town
Card Postal Town, Card County, Card Postcode, Card Country
1.9.7 Wish List
User ID, Product ID, Description, Price Per Unit
1.9.8 Cart
User ID, Product ID Quantity
1.9.9 Settings
Setting ID, Store Status
this looks suspiciously like a homework question. . .?
ASKER
I must admit I'm a little insulted by that! I simply structured the question in order to give precise instructions, only to be branded a highschool student.
If you don't want the 250 points then don't answer, but please don't clutter up the thread with useless comments.
If you don't want the 250 points then don't answer, but please don't clutter up the thread with useless comments.
your question "I believe this is 1NF - yes?", in which you're asking if this is 1st Normal Form is something only an exam or homework exercise would ask. A professional real-world app couldn't care less about the "normal forms levels". it's good to understand this concept so that you can design the DB correctly, but only academia will be asking questions like this.
ASKER
I'm not willing to argue about this, because that shouldn't concern you anyway.
The correct answer will be awarded 250 points - simple as that.
The correct answer will be awarded 250 points - simple as that.
SurfingHamster, if it is a homework then we can not give you direct answer, it is EE's rule:
https://www.experts-exchange.com/help/member.jsp#12
anyway, if you need, we can offer a right approach on your problme, but NOT a direct answer.
https://www.experts-exchange.com/help/mistakes.jsp#7
https://www.experts-exchange.com/help/member.jsp#12
anyway, if you need, we can offer a right approach on your problme, but NOT a direct answer.
https://www.experts-exchange.com/help/mistakes.jsp#7
ASKER
I cannot stress enough that this isn't a homework question.
Could I ask a moderator/administrator to confirm this with my member profile which was created when I registered last year, and leaving a comment here.
Could I ask a moderator/administrator to confirm this with my member profile which was created when I registered last year, and leaving a comment here.
sure, good idea. btw, i just had a look into your profile, it seems you registered THIS year. anyway, a few days later, it will be "last year" ;)
as for your question, i am thinking how to illustrate the ER entities in text only post if i give my comments, just list the fieldnames as yours?
as for your question, i am thinking how to illustrate the ER entities in text only post if i give my comments, just list the fieldnames as yours?
ASKER
I must admit, it seems like last year with how slow this year's gone!
Field names can be listed as the following as I wrote in a report - YES, I already have the normalised data - I just need to verify it as I'm a bit rusty!
Users (User ID, Password, Telephone Number, Email Address, Name, Company, Billing Street, Billing Town, Billing Postal Town, Billing County, Billing Postcode, Billing Country, Registration Date, Mailing Subscription)
Administrators (Admin ID, Password, Name, Last Login)
Categories (Category ID, Description, Parent Category, Default Image, Category Status)
Products (Product ID, Description, Details, Price per Unit, Default Image, Stock Level, Shipping Cost, Lead Time, Product Rating, Total Ratings)
Orders (Order ID, User ID, Description, Order Date, NET Total, Total, Address, Shipping Street, Shipping Town, Shipping Postal Town, Shipping County, Shipping Postcode, Shipping Country, Tracking Status)
Billing Details (Order ID, Card Type, Card Number, Expiration, CVV Code, Name on Card, Card Street, Card Town, Card Postal Town, Card County, Card Postcode, Card Country)
Wish List (User ID, Product ID, Description, Price per Unit)
Cart (User ID, Product ID, Quantity)
Settings (Setting ID, Store Status)
Field names can be listed as the following as I wrote in a report - YES, I already have the normalised data - I just need to verify it as I'm a bit rusty!
Users (User ID, Password, Telephone Number, Email Address, Name, Company, Billing Street, Billing Town, Billing Postal Town, Billing County, Billing Postcode, Billing Country, Registration Date, Mailing Subscription)
Administrators (Admin ID, Password, Name, Last Login)
Categories (Category ID, Description, Parent Category, Default Image, Category Status)
Products (Product ID, Description, Details, Price per Unit, Default Image, Stock Level, Shipping Cost, Lead Time, Product Rating, Total Ratings)
Orders (Order ID, User ID, Description, Order Date, NET Total, Total, Address, Shipping Street, Shipping Town, Shipping Postal Town, Shipping County, Shipping Postcode, Shipping Country, Tracking Status)
Billing Details (Order ID, Card Type, Card Number, Expiration, CVV Code, Name on Card, Card Street, Card Town, Card Postal Town, Card County, Card Postcode, Card Country)
Wish List (User ID, Product ID, Description, Price per Unit)
Cart (User ID, Product ID, Quantity)
Settings (Setting ID, Store Status)
it looks 3-nf to me
...
2 things...
1) you could have your address as an entity and link to it
that way customers can maintain a set of billing / order /card addresses...
2) you ignore history...
is this a conceptual or physical model?
explain the business requirements behind your data, if you want further assistance...
...
2 things...
1) you could have your address as an entity and link to it
that way customers can maintain a set of billing / order /card addresses...
2) you ignore history...
is this a conceptual or physical model?
explain the business requirements behind your data, if you want further assistance...
"Wish List " looks wrong to me.
description and price per unit are deducible from product table, unless of course a wish is to have the product at a different price :)
description and price per unit are deducible from product table, unless of course a wish is to have the product at a different price :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Strictly speaking you can't have postcode, town and country in the same table violates 1-NF: from country and postcode you can always work out the town. However this dependency is pretty unlikely to change so you can get a way with it.
SurfingHamster, any feedback?
mlmcc