USA: +1(669)289-8683 
Sign in
I need someone to normalize a table of data. The data is on a Doc file that I can provide.
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
4
8
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
2
21
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
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
.
|
UV#5 CANAMAZING.COM – INVOICE Invoice #: I-2804 Date: 2009-04-01 Customer #: C00452 Mr. Ron Beetle, 194 Dundas Street, Toronto, ON M9G 2B9 |
||||||||||||
|
Quantity |
Title |
Warehouse |
Book
Unit Price This Invoice |
Charge |
Shipping Option |
Total |
Line Total |
|||||
| 2 | 9781416513285 | THE PURSUIT OF HAPPINESS |
W12 – Montreal |
16.99 |
33.98 |
4- Next Day |
3.18 |
37.16 |
||||
| 9781880000731 |
PURPLE FLOWERS FROM GRAMA’S GARDEN |
23.99 |
47.98 |
1-Parcel Post |
1.38 |
49.36 |
||||||
| 4 | 9780310258971 |
THE PURPOSE DRIVEN LIFE |
W14-Toronto |
13.99 |
55.96 |
6.36 |
62.32 |
|||||
| 9780310258983 |
W12-Montreal |
20.99 |
4 1.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 |
||||||||||||
|
$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
Ron.beetle@hotmail.com
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
hford77@gmail.com
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
JB.wisedom@yahoo.com
Apartment #3
Toronto, ON
M4K 2B9
9781880000731
Purple Flowers From
3
1.39
21.44
Grama’s Garden
Page 93 of 128
