Use the following partial database tables to answer the requ

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

Required:

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 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?

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 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)?

j) 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).

k) 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.

Solution

Primary keys in tables are
customerId in sales event table
sale event in Sale inventory table
sale person id in saleperson table
cashier id in casheier table
customer id in custoer table

Foriegn keys in tables are
Inventory iten in Inventory table
cash in cash table
Customer id in Custmer table
customerId in sales event table
sale event in Sale inventory table
sale person id in saleperson table
cashier id in casheier table