Normalization Assignment
clasic1
dbs201-normal-133-ebookstore.doc
![image1.png]()
DBS201 Asst 3 (10% of final mark)
Normalization of CanAmazing.com Starting With User Views
Due Date: Friday Dec 6, 2013
Hand in your printed solution of this assignment. Email submissions will not be accepted.
Late assignments incur a penalty of 20% per “working day”.
Assignment is to be done in groups of 2 or 3. An assignment done by a single person will incur a penalty of 20%.
All group members must sign the assignment submission form at: Assignment Submission Form .
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 must be shown in alphabetic order when requested (i.e. after step 2.6 for each user view and after step 7).
d) All tables must be named according to their Primary Key.
TO HAND-IN
2) For each user view please provide:
2.1) The UNF relation including the Primary Key and Multi-Valued Dependencies.
2.2) The 1st Normal Form by converting the UNF to 1NF.
2.3) The 2nd Normal Form by converting the 1NF to 2NF.
2.4) The 3rd Normal Form by converting the 2NF to 3NF.
2.5) Simplify primary keys wherever possible and show any relations that changed. (Note: usually no simplification is possible)
2.6) Name the relations (tables) and provide the final list of recommended tables from this User View.
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 steps 2.6 which arise from the six User Views.
4) If any of the merged relations is in 2nd Normal Form (i.e. it has a transtive dependancy), 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, paperpback books, audio books, downloadable books and various other types of books 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 14-16 tables.
The final answer has approximately 10-12 tables having a 1-part Primary Key.
The final answer has approximately 3-4 tables having a 2-part Primary Key.
The final answer has approximately 14-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
DOWNLOAD 77
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
LIST of CURRENT (UNPAID) INVOICES BY CUSTOMER
UNIT .
PRICE OWNER OF THIS
CUSTOMER ADDRESS INV# DATE ISBN PUBLISHER PUBLISHER QTY INVOICE
C00452 – Ron Beetle 194 Dundas Street I-1900 03/05/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 03/06/12 9780310258971 #311 Zondervan #21-CanWest Media 1 12.44
C00453 - Henry Ford 299 Manchester St. I-1192 03/05/12 9780310205715 #097 Victor Books #21- CanWest Media 1 29.44
Toronto, ON
M4J 2A8
C00454 – Jane Buick 1820 St. John Street I-0167 02/01/12 9780310270600 #097 Victor Books #21-CanWest Media 1 5.44
Apartment #3
Toronto, ON
M4K 2B9
9781880000731 #121 Young Books #19-American Media 3 18.50
Corporation
________________________________________________________________________________________________________________________.
Page 67 of 128
UV#3
CANAMAZING.COM
BOOK PRIZES
Book Prize
Prize ISBN Title Amount Author .
Annual Presentation Los Angeles 9781416513285 THE PURSUIT OF HAPPINESS $55,000. May Looking
Times Book Prize
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: 2012-03-29 Customer #: C00455 Mr. Paul Smith, 3194 Bloor Street, Toronto, ON M7T 3W3
|
||||||||
|
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 |
9780310258983 |
THE PURPOSE DRIVEN LIFE
|
W14 -Toronto |
20.99
|
83.96 |
4- Next Day |
6.36 |
90.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 |
$220.80 |
|
|
|
|
|
|
|
|
HST(13%) |
$28.70 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Grand Total Owing |
$249.50 |
UV#6
CANAMAZING.COM
INVOICE HISTORY (PAID AND UNPAID) BY CUSTOMER
SEPTEMBER 1, 2011 – DECEMBER 31, 2011
SHIPPING UNIT
CHARGES PRICE FOR EACH THIS
CUSTOMER ADDRESS INV# DATE ISBN TITLE QTY BOOK INVOICE
C00452 – Ron Beetle 194 Dundas Street I-1900 03/05/12 9781416513285 The Pursuit Of Happiness 1 1.24 14.44
[email protected] Toronto, ON
M9G 2B9
9781880000731 Purple Flowers From 2 1.54 20.00
Grama’s Garden
I-1946 03/06/12 9780310258971 The Purpose Driven Life 1 1.24 12.44
C00453 - Henry Ford 299 Manchester St. I-0417 02/20/12 9780310205715 The Purpose Driven Life 1 1.54 29.34
[email protected] Toronto, ON
M4J 2A8
I-0882 02/28/12 9780310270600 The Purpose Driven Life 1 0.00 5.44
9781880000731 Purple Flowers From 3 1.54 18.50
Grama’s Garden
I-1192 03/05/12 9780310205715 The Purpose Driven Life 1 1.24 29.44
Page 93 of 128
