A1. Use the following partial database tables to answer the required questions below:
Cash Table___________________________________________________________
Cash #________________ Type of Account_______________Bank Name____
110146758 Regular Checking North First
1203948102 Payroll Checking Account Credit Grantors
Inventory Table______________________
Inventory Item # Description__
001 XL T-shirt
224 XL Sweatshirt
302 XXL T-shirt
451 Felt pennant
513 Ping-pong ball
674 Golf ball
736 XL Polo shirt
876 Bumper sticker
887 Foam football
Sales Event Table
________________________________________________________________________
Sales Event# Date Terms Saleperson ID Customer ID
1 11/5 2, 10 net 30 2 2543
2. 11/5 2, 10 net 30 4 635
3 11/5 COD 6 1845
Sales Inventory Table
Sales Event# Inventory Item # Inventory Quantity Price
1 876 10 1.25
1 674 8 0.875
1 451 30 0.995
2 887 54 1.475
2 513 188 0.525
3 736 36 24.995
3 001 58 7.875
3 302 16 8.00
3 224 114 8.75
Salesperson Table
Salesperson ID Last Name First Name
2 Cleaves Mateen
4 Warrick Peter
6 Peterson Morris
8 Janakowski Sebastian
Cashier Table
Cashier ID Last Name First Name
1 Weinke Chris
2 Outzen Marcus
Cash Receipts Event Table
Cash Recepit # Date Chk# Cashier ID Sales Event # Customer ID
1001 11/6 11097 1 2 635
Cash Account # Amount Received
110146758 $178.35
Customer Table
Customer ID Last Name First Name Address City State Zip
101 Conrad Chris 5629 Longfellow Dr Paragould AK 65323
183 Anderson Paul 674 Sunderland Lane Sioux City IA 63126
635 Padgham Donna 1264 Algonquin Road Mason MI 48854
1845 Oliver Andrew 8512 Bonita Drive Clearwater FL 33051
2543 Cook Carol 536 Secondary Ave. Fremont CA 75518
Questions to Answer Based on the Above Tables:
a. What events, resources, and agents must have been included in the underlying conceptual model from which these relational tables were designed?
b. Identify the primary key of each table.
c. Identify each foreign key in the database
d. List the resources and agents involved in Sale event 2.
e. List the resources and agents involved in Cash Receipt 1001.
f. Suppose you wanted to generate an invoice (bill) for customer 2543 that lists the customer name and address, the salesperson name, and all other information about the sale, including the items sold. Which tables contain the data you will need to generate the invoice?
g. Suppose you want to generate a report listing each customer name and the amount due from each customer. Which tables contain the data you need to generate the report?
h. Explain why "total sales amount" did not need to be included as an attribute in the sales table What are the pros and cons associated with leaving this attribute out of the database tables?
i. If you need to record the following sale:
Sale event 4; on 11/10; COD terms; Salesperson 2; Customer 101; 30 unites of item 887, for a total of $44.25.
What tables would you use? How many records would you add or modify in the tables.
j. If you need to record the following cash receipt:
Cash receipt 1002; on 11/10; from customer 2543 to pay off sale event 1; in the amount of $49.35 deposited into cash account 110146758
What tables would you use? How many records would you add or modify in the tables?