Big City Store Sales Database

1
Big City Store Sales Database
CUSTOMERS First Last Address City State Zip
PRODUCTS Product Supplier Description Units Cost P
rice
SALES Customer Product Date Quantity
2
Big City Store Sales Database
CUSTOMERS First Last Address City State Zip
PRODUCTS Product Supplier Description Units Cost P
rice
3
Big City Store Sales Database
CUSTOMERS First Last Address City State Zip
PRODUCTS Product Supplier Description Units Cost P
rice
Many to Many Relationship
4
Big City Store Sales Database
Main Table
Main Table
CUSTOMERS First Last Address City State Zip
PRODUCTS Product Supplier Description Units Cost P
rice
Junction Table
SALES Customer Product Date Quantity
5
Big City Store Sales Database
CUSTOMERS First Last Address City State Zip
PRODUCTS Product Supplier Description Units Cost P
rice
SALES Customer Product Date Quantity
To set these relationships, the main tables must
have primary keys.
6
(No Transcript)
7
PRODUCTS Product Supplier Description Units Cost P
rice Hats Hats, Inc Felt Each 5.50
6.00 Socks Socks Limited Wool Pair 2.25
3.50 Shoes Shoes Are Us Wooden Pair 9.99 12.15 P
urses Bag for All Silk Each 8.45 9.15
8
SALES Customer Product Date Qty
Name Address City State Zip
Product Supplier Description Units Cost Price
Qty
Date
BIG CITY STORE SALES INVOICE Date Customer
Name Customer Address Customer City, ST
Zip Product Description Qty Price Total
9
SALES First Last Street Address City ST Zip Produ
ct Supplier Address City ST Zip Date Qty Jack John
son 123 South Main St Gainesville FL 32608 Hats Ha
ts, Inc PO Box 123 Waldo FL 32658 01/24/02 1
Jack bought a Hat on 1/24/02
10
CUSTOMERS Cust First Last Street
Address City ST Zip C1 Jack Johnson 123 South
Main St Gainesville FL 32608 C2 Jill Jones 28000
SW 583 Ave Gainesville FL 32597 C3 Jerry Jacks 270
1 Wilson Road Gainesville FL 32655 C4 John Jinks 3
243 SE 4th Terrace Gainesville FL 32684

• PRIMARY KEY
• No Duplicates

11
SALES Customer Product Date Qty
Name Address City State Zip
Product Supplier Description Units Cost Price
Qty
Date
Cust
12
(No Transcript)
13

• PRIMARY KEY
• No Duplicates

14
SALES Customer Product Date Qty
Product Supplier Description Units Cost Price
Qty
Date
Cust
15
SALES
Customer Product Date Qty
Qty
Date
Cust
Prod
What do we use for a Primary Key here?
One way would be to have an auto number
16
SALES
Customer Product Date Qty
Qty
Date
Cust
Prod
What do we use for a Primary Key here?
Another would be to have multiple key
Customer Product Date Qty
Qty
Date
Cust
Prod
This would assume that a customer would not
purchase the same product twice in one day.
17
Sales
Jack bought Hats
Jill bought Shoes
Jill bought Purses
John bought Socks
18
Big City Store Sales Database
CUSTOMERS Cust First Last Address City State Zip
PRODUCTS Prod Product Supplier Description Units
Cost Price
1
1
SALES Customer Product Date Quantity