I need someone to normalize a table of data. The data is on a Doc file that I can provide.
clasic1
normal-win-13-canamazingcom-a2.doc
![image1.png]()
Asst 2
Normalization of CanAmazing.com User Views
_______________________________________________________________
1) Assignments will be marked on
a) Correctness - All 3NF relations have been identified with correct attributes, Primary Keys and all Foreign Keys.
b) Meaningfulness - use of meaningful relation (table) and attribute names.
c) Relations are shown in alphabetic order when requested (i.e. after step 2.6 for each user view and after step 7).
TO HAND-IN
2) For each user view:
2.1) Find the UNF relation including the Primary Key and Multi-Valued Dependencies.
2.2) Convert the UNF to 1st Normal Form.
2.3) Convert the 1st Normal Form to 2nd Normal Form.
2.4) Convert the 2nd Normal Form to 3rd Normal Form.
2.5) Simplify primary keys wherever possible. (Note: often no simplification is possible)
2.6) Name the relations (tables).
NOTE: When showing the results of step 2.6, the tables must be shown in alphabetic order by table name!
3) Merge all 3rd Normal Form relations from step 2.6 coming from the six User Views.
4) If any of the merged relations is in 2nd Normal Form, convert it to 3rd Normal Form.
5) Perform any new merging opportunities and repeat steps 4 and 5 until there are no more merging or 2NF-to-3NF conversions possible.
6) Identify all Foreign Keys
7) Explode multi-field attributes into many fields and list your final list of tables that you recommend for the CanAmazing.com database.
NOTE: When showing the results of step 7, the tables must be shown in alphabetic order by table name!
CanAmazing.com
CanAmazing.com is an internet based company that sells books of all types. It is a great Canadian success story. There are hard cover books, paperback books, audio books, downloadable books and various other types as well.
The following 6 user views allow you to understand their business. By using normalization you will be able to recommend the best database design. The database you recommend will be free of data redundancies and data inconsistencies as well as update, insert and deletion anomalies.
Hints:
The final answer has approximately 16 tables.
The final answer has approximately 12 tables having a 1-part Primary Key.
The final answer has approximately 4 tables having a 2-part Primary Key.
The final answer has approximately 16 single-attribute Foreign Keys.
I hope you will enjoy this assignment:
UV#1
CANAMAZING.COM
BOOKS BY ISBN
CURRENT
Our Sell BOOK LENGTH
ISBN# title cost Price categ format PAGES MINS
9780310205715 the purpose driven life 23.53 32.99 17-MOTIVATIONAL 2-hard cover 334
9780310258171 the purpose driven life 14.82 20.99 17-MOTIVATIONAL 8-audio cd 48
abridged
9780310258971 the purpose driven life 9.64 13.99 17-MOTIVATIONAL 1-paperback 376
9780310258983 the purpose driven life 14.82 20.99 17-MOTIVATIONAL 9-audio cd 77
Unabridged
9780310270600 the purpose driven life 3.75 5.99 17-MOTIVATIONAL 5-audio Down 77
Load
9781416513285 the pursuit of happiness 12.02 16.99 21-health, mind 1-paperback 221
And body
9781880000731 Purple Flowers From 16.84 23.99 1-Fiction 2-Hard Cover 301
________________________________________________________________________________________________________________________
Page 89 of 188
UV#2
CANAMAZING.COM
BOOK INFORMATION
PRICE OWNER OF THIS
CUSTOMER ADDRESS INV# DATE ISBN PUBLISHER PUBLISHER QTY INVOICE
C00452 – Ron Beetle 194 Dundas Street I-1900 08/12/12 9781416513285 #097 Victor Books #21-CanWest Media 1 14.44
Toronto, ON
M9G 2B9
9781880000731 #121 Young Books #19-American Media 2 20.00
Corporation
I-1946 08/12/24 9780310258971 #311 Zondervan #23-Partrige 1 12.44
Communications
Corporation
C00453 - Henry Ford 299 Manchester St. I-1192 08/09/23 9780310205715 #097 Victor Books #21- CanWest Media 1 29.44
Toronto, ON
M4J 2A8
C00454 – Jane Buick 1820 St. John Street I-0167 08/09/07 9780310270600 #097 Victor Books #21-CanWest Media 1 5.44
Apartment #3
Toronto, ON
M4K 2B9
9781880000731 #121 Young Books #19-American Media 3 21.44
Corporation
________________________________________________________________________________________________________________________.
Page 67 of 128
UV#3
CANAMAZING.COM
BOOK PRIZES
Book Prize
Prize ISBN Title Amount Author(s) .
Annual Presentation Los Angeles 9781416513285 THE PURSUIT OF HAPPINESS $55,000. May Looking
Times Book Prize Alice Fore
Alison Joy
Kiriyama-Leno Pacific Rim 9780310205715 THE ASSOCIATE $235,000. John Grisham
Book Prize
Man Booker International 9780743296410 HANDLE WITH MUCH CARE $145,000. Jodi Picoult
Prize
National Book Critics Circle 9780310205715 THE ASSOCIATE $100,000. John Grisham
Awards
Page 13 of 22
________________________________________________________________________________________________________________________.
Note: CanAmazing.com keeps track of the current year’s winners only. On Jan 1 of the new year the prize information is copied to a “historical” database to make room for current year prizes. After copying, the information for each prize is set to null. Do not include any tables or attributes for any historical data.
UV#4
CANAMAZING.COM
AUTHORS, BOOKS AND ACCOMPLISHMENTS
.
YEAR OF THE
DATE THIS AUTHOR FIRST BOOK
OF MILLION HAS WRITTEN WRITTEN IN
AUTHOR BIRTH SOLD(Y/N) BOOKS CATEGORY BOOKS IN THIS LANGUAGE
1009- John Grisham 03/03/1953 Y The Associate 1-Fiction English 1982
Hitler’s Years 5-History Italian 1984
Una Littera da Parigi 1-Fiction
1042- Tom Dooley 07/09/1969 N Abe Lincoln: Building A Nation 5-History English 2003
America’s Economic Collapse 6-Politics French 1999
Napoleon Apres La Guerre Waterloo 5-History
The Real Canadian Challenge 6- Politics
Le Schism Civile du Sudan 6-Politics
2114- Harry Oduro 06/12/1973 Y Your Best – Always 17-Motivational Arabic 2003
English 1998
French 1999
Swahili 2001
Urdu 2003
Page 29 of 57
.
|
Invoice #: I-2804 Date: 2009-04-01 Customer #: C00452 Mr. Ron Beetle, 194 Dundas Street, Toronto, ON M9G 2B9
|
||||||||
|
Quantity |
ISBN |
Title |
Warehouse |
Book Unit Price This Invoice |
Charge |
Shipping Option |
Total Shipping Charges This Book
|
Line Total |
|
2 |
9781416513285 |
THE PURSUIT OF HAPPINESS |
W12 – Montreal |
16.99
|
33.98 |
4- Next Day |
3.18 |
37.16 |
|
2 |
9781880000731 |
PURPLE FLOWERS FROM GRAMA’S GARDEN
|
W12 – Montreal |
23.99 |
47.98 |
1-Parcel Post |
1.38 |
49.36 |
|
4 |
9780310258971 |
THE PURPOSE DRIVEN LIFE
|
W14-Toronto |
13.99
|
55.96 |
4- Next Day |
6.36 |
62.32 |
|
2 |
9780310258983 |
THE PURPOSE DRIVEN LIFE
|
W12-Montreal |
20.99 |
41.98 |
2-Within 5 Days |
1.98 |
43.96 |
|
|
|
. .
|
|
|
|
|
|
|
|
|
Shipping Options: Opt # Cost per Item. Guarantee . 1: $0.69 Parcel Post 2: $0.99 Received within 5 days 3: $1.29 Received Within 3 days 4: $1.59 Received Next Day |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Total |
$192.80 |
|
|
|
|
|
|
|
|
GST(5%) |
$9.64 |
|
|
|
|
|
|
|
|
PST (8%) |
$15.42 |
|
|
|
|
|
|
|
|
Grand Total Owing |
$217.86 |
UV#6
CANAMAZING.COM
INVOICE HISTORY BY CUSTOMER
SEPTEMBER 1, 2008 – DECEMBER 31, 2008
SHIPPING UNIT
CHARGES PRICE FOR EACH THIS
CUSTOMER ADDRESS INV# DATE ISBN TITLE QTY BOOK INVOICE
C00452 – Ron Beetle 194 Dundas Street I-1900 08/12/23 9781416513285 The Pursuit Of Happiness 1 1.19 14.44
[email protected] Toronto, ON
M9G 2B9
9781880000731 Purple Flowers From 2 1.39 20.00
Grama’s Garden
I-1946 08/12/24 9780310258971 The Purpose Driven Life 1 1.19 12.44
C00453 - Henry Ford 299 Manchester St. I-0417 08/09/23 9780310205715 The Purpose Driven Life 1 1.39 29.34
[email protected] Toronto, ON
M4J 2A8
C00454 – Jane Buick 1820 St. John Street I-0167 08/09/07 9780310270600 The Purpose Driven Life 1 0.00 5.44
[email protected] Apartment #3
Toronto, ON
M4K 2B9
9781880000731 Purple Flowers From 3 1.39 21.44
Grama’s Garden
Page 93 of 128
