I need someone to normalize a table of data. The data is on a Doc file that I can provide.

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

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

.

ISBN

Shipping

Charges

This Book

2

W12 – Montreal

4- Next
Day

2

THE PURPOSE DRIVEN LIFE

Total

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

Still stressed with your coursework?
Get quality coursework help from an expert!