Database Models part 2

1
Database Models (part 2)
2
Retail Sales Database

• Major data categories
• Products
• Customers
• Credit cards
• Shopping trips
• Major relationships
• Customers can shop one or more times
• Customers can have zero or more credit cards
• Shopping trips can be for one or more products

3
Retail Sales Database

• Facts, Observations Assumptions
• Shopping trips cannot involve multiple customers
  or credit cards
• Some shopping trips may not involve a known
  customer (i.e., cash sales)
• Not all products have to be sold (e.g., new
  items, overpriced items)
• Wholesale suppliers and employees are outside of
  the scope of today's model... but could easily be
  added to it at a later date

4
Retail Sales Database
1N
Customers
Credit Cards
Products
1N
1N
MN
Shopping Trips
5
Retail Sales Database
1N
Customers
Credit Cards
Products
1N
1N
Purchase Master
1N
1N
Purchase Detail
6
(No Transcript)
7
Explicit MN Bridge
Implicit MN Relationship
8
Extension

• How would you expand the model to allow customers
  to return previously purchased items?

9
Some or all of these items can be returned at a
later date
10
(No Transcript)
11
Extension

• How would you expand the model to keep track of
  the cashiers (employees), paychecks, etc?

12
Pointers to the employees
13
(No Transcript)
14
Related Application

• How would the basic database model differ for an
  online or mail order store?
• Key Differences
• No "unknown" cash customers
• Alternative mailing addresses
• Shipping options

15
Unknown Customer
Shipping Address
Shipping Method
16
(No Transcript)
17
Related Application

• How would the basic database model differ for a
  chain of retail stores?
• Key Differences
• Store locations
• Inventory at each store (QOHs, Prices)
• Shopping trips for specific stores

18
(No Transcript)
19
Related Application

• How would the basic database model differ for a
  new (or used) car dealership?
• Key Differences
• Every car is unique (Eliminate QOH Always 1)
• No shopping "cart" purchases (Always 1)
• Negotiated selling price
• No credit card sales
• Financing options

20
Every car is unique
Financing options replace credit cards
Customers do not buy cars in bulk
21
(No Transcript)