Use the following partial database tables to answer the required questions: Sale
ID: 3930038 • Letter: U
Question
Use the following partial database tables to answer the required questions:
Sales Event Table
Sales Event #
Date
Terms
Salesperson 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
Sale-Inventory Table
Sale Event #
Inventory Item #
Inventory Quantity
Price each
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
Date
Check
Cashier
Sales
Customer
Cash
Amount
Receipt #
#
ID
Event #
ID
Account #
Received
1001
11/6
11097
1
2
635
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 Dr.
Clearwater
FL
33051
2543
Cook
Carol
536 Secondary Ave.
Fremont
CA
75518
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
Required:
What events, resources, and agents must have been included in the underlying conceptual model from which these relational tables were designed?
Identify the primary key of each table.
Identify each foreign key in the database.
List the resources and agents involved in Sale event 2.
List the resources and agents involved in Cash Receipt 1001.
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?
Suppose you wanted 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?
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?
If you need to record the following sale:
Sale event 4; on 11/10; COD terms; Salesperson 2; Customer 101; 30 units of item 887, for a total of $44.25.
What table(s) would you use? How many record(s) would you add or modify in the table(s)?
If you need to maintain your records to reflect a change in Donna Padgham's last name and address, what table(s) would you use? How many record(s) would you add or modify in the table(s).
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 table(s) would you use? How many record(s) would you add or modify in the tables.
Sales Event #
Date
Terms
Salesperson 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
Explanation / Answer
Inventory(Inventory_item #, description)
primary key: Inventory item #
cash (cash#, type_of_account, bank_name)
primary key: cash#
customer(Customer ID, Last Name, First Name, Address, City, State, Zip)
primary key: Customer ID
cashReceiptsEvent(cashReceipt#, Date, check #, cashier ID,sales event#, customer ID, cashAmt#, AmtReceived)
primary key: cashReceipt#
foreign key: cashier ID, sales event#, customer ID
cashier(ID, lastname, firstname)
primary key: ID
salesperson(ID, lastname, firstname)
primary key: ID
sale-inventory(saleEvent#, InventoryItem#, InventoryQuantity,PriceEach)
foreign key: saleEvent#, InventoryItem#
sales Event(saleEvent#, Date, Terms, salesperson ID, customer ID)
primary key: saleEvent#
foreign key: salesperson ID, customer ID
Resources and agents involved in sales event 2 are:
resources: foam, football with inventory item# 887, and ping pong ball with inventory item# 513
agents:
Sales person: Peter Warrick with sales ID 4 and
Customer : Donna Padgham with customer ID 635
Resources and agents involved in cash receipt 1001 are:
resources: foam, football with inventory item# 887, and ping pong ball with inventory item# 513
agents:
cashier: Chris Weinke with ID 1
Sales person: Peter Warrick with sales ID 4 and
Customer : Donna Padgham with customer ID 635
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?
We need the following tables:
Suppose you wanted 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?
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?
Ans. The "total sales amount" need not be included because that could be obtained easily from the available data.
Pros: Decreases data redundancy
Cons: We need to calculate and add the column whenever it is required.
***If you need to record the following sale:
Sale event 4; on 11/10; COD terms; Salesperson 2; Customer 101; 30 units of item 887, for a total of $44.25.
What table(s) would you use? How many record(s) would you add or modify in the table(s)?
Ans. To get the required record we need 1. Sales Event table and 2. Sale Inventory Table. Here we need to add a new column Total which was obtained by multiplying InventoryQuantity and PriceEach from sales inventory table with each other.
*** If you need to maintain your records to reflect a change in Donna Padgham's last name and address, what table(s) would you use? How many record(s) would you add or modify in the table(s).
Ans. To reflect the change in Donna Padgham's last name and address we just use the Customer table and need to modify one record with first name='Donna'
***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 table(s) would you use? How many record(s) would you add or modify in the tables.
Ans. To print a receipt with the given columns we just need CashReceiptsEvent table. We need not add or modify any records.