Some customers require delivery, so the owner maintains relationships with several local movers.
The owner wants to keep track of his customers so that he can do a better job of marketing. In particular,
he knows that some customers are particularly interested in certain types of antiques, and he??™d like to be
able to find, for example, all those customers interested in cameo jewelry.
For business auditing and tax purposes, it??™s very important that the database track expenditures and
revenues. The owner wants to track what he spent for each item, and what he earned in selling it.
These requirements also mean that he has to track store expenses like rent and heat, and his
employee expenses (he employs 2 part-time people to help him run the store).
There is no single correct answer to such a challenge, but here is a possible E-R diagram
for an antique store database:
ANSWERS TO REVIEW QUESTIONS 207
Item
PK ItemID
Type
Description
Cost
FK2 SellerID
Price
Customer
PK CustomerID
Name
Address
Phone
Email
FK1 PrimaryInterest
Types of antiques
PK type
Seller
PK SellerID
Name
Address
Sale
PK InvoiceNo
Date
FK1 CustomerID
Cont??™d....
LineItem
PK,FK1 InvoiceNo
PK,FK2 ItemNo
Price
Antique Store
Item type Interest
Commission item
Fee(%)
208 ANSWERS TO REVIEW QUESTIONS
Delivery_Services
PK ServiceID
Name
Address
Phone
PrimaryContact
StoreExpense
PK StoreExpenseType
PK Date
Payee
Amount
CheckNo
FK1 ExpenseType
EmployeeExpense
PK EmpSSN
PK Date
FK1 EmpExpenseType
Amount
CheckNo
StoreExpenseType
PK ExpenseType
EmployeeExpenseType
PK EmpExpenseType
Delivery
PK CustomerID
PK Date /Time
FK1 ServiceID
Cost
FK2 InvoiceNo
Driver
Rent, heat, etc.
Pages:
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536