Use the following partial database tables to answer the requ

Use the following partial database tables to answer the required questions:

Sales Event Table

Sales Event #

Date

Terms

Salesperson ID

Customer ID

11/5

2 10, net 30

2543

11/5

2 10, net 30

635

11/5

COD

1845

Sale-Inventory Table

Sale Event #

Inventory Item #

Inventory Quantity

Price each

876

1.25

674

0.875

451

0.995

887

1.475

513

188

0.525

736

24.995

001

7.875

302

8.00

224

114

8.75

Salesperson Table

Salesperson ID

Last Name

First Name

Cleaves

Mateen

Warrick

Peter

Peterson

Morris

Janakowski

Sebastian

Cashier Table

Cashier ID

Last Name

First Name

Weinke

Chris

Outzen

Marcus

Cash Receipts Event Table

Cash

Date

Check

Cashier

Sales

Customer

Cash

Amount

Receipt #

Event #

Account #

Received

1001

11/6

11097

635

110146758

$ 178.35

customer table

Customer ID

Last Name

First Name

Address

City

State

Zip

101

Conrad

Chris

5629 Longfellow Dr.

Paragould

65323

183

Anderson

Paul

674 Sunderland Lane

Sioux City

63126

635

Padgham

Donna

1264 Algonquin Road

Mason

48854

1845

Oliver

Andrew

8512 Bonita Dr.

Clearwater

33051

2543

Cook

Carol

536 Secondary Ave.

Fremont

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

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

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.