Brian Agee

1

• Brian Agee
• Meghan Burkheart
• Janet Joslin
• Michael Kelly
• Melinda Pepper
• Chris Stevens

2
Background on Amazon.com

• Amazon.com as it name implies is a website, it is
  known as one of the largest retailers in the
  world and was at the forefront of the E-Commerce
  movement when they opened their virtual doors in
  July 1995. Now 7 years later they are serving 220
  countries across the globe.

3
Why use Amazon.com?

• We picked Amazon.com because we felt the design
  of the database would be interesting and the
  basic underlying design would replicate itself
  throughout the database.

4
Amazon.coms Database

• Amazon.com has literally millions of products
  including, greeting cards, online auctions,
  books, cds, video, DVD, toys, games, electronics
  .. And yes even kitchenware.

5
Focus of our project

• Amazon.com is a massive database, to even attempt
  to use even half of the entire site would be
  futile, we focused our interest in 3 areas.
• Books
• Computer / Video Game Software
• Magazines

6
Database Description

• Amazons database is a centralized Homogenous and
  non-autonomous, in nature. They keep all data
  in one datawarehouse, which is updated and backed
  up on a regular basis.
• Due to the nature of Amazon.coms business, if
  they lose their database they are out of business.

7
Book ERD
8
Magazine ERD
9
Computer/Video Game ERD
10
Amazon EERD
11
Book OOD
12
Magazine OOD
13
Video Game OOD
14
Normalizations
15
(No Transcript)
16
(No Transcript)
17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23
(No Transcript)
24
(No Transcript)
25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
(No Transcript)
31
The Process of Creating the Database in Oracle
create table MEDIA (ASIN char(10) Primary
Key, title char(25), List_Price
decimal(7,2), Sales_Rank char(7), C_Ratings
decimal(7,2))
32
The Database Is Populated Using This Command
Procedure
insert into MEDIA values ('013061970','Modern VSL
Design',89.00,'859632','0') insert into
Media values ('031669320','2nd Chance',26.95,'23',
4) insert into Media values ('0385335520','The
Cottage',26.95,'439',2.5)
33
You Can Check Your Work for Accuracy by Running a
Select Statement
SQLgt select from Media ASIN TITLE
LIST_PRICE SALES_R C_RATINGS ----------
------------------------------- -------
--------- 013061970 Modern VSL Design
89 859632 0 031669320 2nd Chance
26.95 23 4 0385335520 The
Cottage 26.95 439 2.5
34
Create Table Media_category(ASIN Char(25)
Primary Key,Category Char(25))SQLgt Insert
Into Media_category 2 Values 3
('0130619701','ProfessionalTechnical')1 Row
Created.SQLgt Insert Into Media_category 2
Values 3 ('0316693200','MysteryThriller')1
Row Created.SQLgt Insert Into Media_category 2
Values 3 ('0385335520','romance')1 Row
Created.
More Tables are Created and Tested
35

Checking reveals that the table is Functional
SQLgt Select from Media_Category ASIN
CATEGORY -------------------------
----------------------- 0130619701
ProfessionalTechnical 0316693200
MysteryThriller 0385335520
Romance 0765400627
ScienceFictionFantasy 1561584029
HomeGarden
36
SQLgt Run 1 Select From Media,
Media_category 2 Where Media_category.Category
'Romance' 3 and Media_category.ASIN
Media.ASINASIN TITLE
LIST_PRICE SALES_R C_RATINGS ASIN----------
------------------------- ---------- -------
--------- -------------------------CATEGORY-----
--------------------0385335520 The Cottage
26.95 439 2.5
0385335520Romance
We Can Run A Query To Find A Romance, But The
Results Dont Look Very Pretty And Are Difficult
To Read.
37
This Is Cleaner, But Does Not Give Enough
Information
SQLgt select Media_Category.category, Media.Title
2 from Media_Category, Media 3 where
Media_Category.Category 'Romance' 4 and
Media_Category.ASIN Media.ASIN CATEGORY
TITLE ----------------
-------------------- Romance
The Cottage
38
Now The Query Results Are More Meaningful.
SQLgt run 1 select Media_Category.category,
Media.Title, list_Price 2 from Media_Category,
Media 3 where Media_Category.Category
'Romance' 4 and Media_Category.ASIN
Media.ASIN CATEGORY TITLE
LIST_PRICE -------------------------
-------------------- ---------- Romance
The Cottage 26.95
39
Amazon Uses Queries and Views

• These views are embedded in the HTML code that is
  Amazons website.
• Giving the user a dynamic view of the items.
• That they want to see and the attributes they.
• Are interested in.
• In cells of information all over the page.
• From their site to your computer.

40
Conclusion Amazon.com

• As you can see, Amazon.com is a very complex
  database. It was a challenge to decide on which
  categories to analyze because we wanted to make a
  sufficient example of the entire database.
  Amazon.com almost seems to be too sophisticated
  for an entry-level project such as this.

41
Conclusion (cont)

• Although the database is very complex, it is
  similar in many ways. The categories we choose
  (magazines, computer/video games, and books) seem
  to be transparent to each other and items in the
  database as well.

42
Conclusion (cont)

• We applied many advanced database techniques to
  design the ER, EER, and, OO diagrams. We used
  many Access and Oracle commands, such as those in
  chapter 10 to build, create, and populate tables.
  We also gave examples of queries and the data
  that was retrieved from the query.

43
Conclusion (cont)

• As discussed in chapter 13, backup and recovery
  is very important. For Amazon.com it is top
  priority. Since Amazon.com is strictly an online
  retailer, their information must be frequently
  backed-up. Recovery is essential because
  customers must be able to access the website in
  order to make a purchase. That is what makes
  Amazon.com different from databases.

44
Team Questions

45
Melindas Question

• Why is it necessary for Amazon to be a
  Homogeneous non-autonomous database as apposed to
  a heterogeneous autonomous database?

46
Chris Stevens Question
What is the command to insert new data into the
media table of the database?
47
JANETS QUESTION

• What measures does Amazon.com go through to
  ensure stability of their database.

48
Meghans Question

• What is the main differences between an ER
  diagram and an EER diagram?

49
Mikes Question

• What is the difference between Amazons database
  and the other databases that are being studied?

50
Brians Question

• How is object-oriented diagrams used for
  designing databases?