Understanding Databases Use the following partial database tables to answer the required questions: Sales Table Sales Event # Dat Terms Salesperson ID Customer ID 11/5 11/5 11/5 2 3 2 10, net 30 2 10, net 30 COD 2 4 2543 635 1845 6 Price each 1.25 0.875 Sale-Inventory Table Sale Event # 1 1 1 2 2 3 3 3 3 Inventory Item # 876 674 451 887 513 736 001 302 224 Inventory Quantity 10 8 30 54 188 36 58 16 114 0.995 1.475 0.525 24.995 7.875 8.00 8.75 Salesperson Table Salesperson ID 2 4 6 8 Last Name Cleaves Warrick Peterson Janakowski First Name Mateen Peter Morris Sebastian Cashier Table Cashier ID 1 2 Last Name Weinke Outzen First Name Chris Marcus Date Sales Check # 11097 Cashier ID 1 Event # 2 Customer ID 635 Cash Account # 110146758 Amount Received $ 178.35 11/6 Cash Receipts Table Cash Receipt # 1001 Customer Table Customer ID 101 183 635 1845 2543 Last Name First Name Conrad Chris Anderson Paul Padgham Donna Oliver Andrew Cook Carol Address 5629 Longfellow Dr. 674 Sunderland Lane 1264 Algonquin Road 8512 Bonita Dr. 536 Secondary Ave. Zip 65323 63126 48854 33051 75518 Zip Code Table City Paragould Sioux City Mason Clearwater Fremont State Zip AK 65323 IA 63126 MI 48854 FL 33051 CA 75518 Cash Table Cash # 110146758 1203948102 Type of account Regular checking Payroll checking account Bank Name North First Credit Grantors Inventory Table Inventory Item # 001 224 302 451 513 674 736 876 887 TIIT TO Description XL T-shirt XL Sweatshirt XXL T-shirt Felt pennant Ping pong ball Golf ball XL Polo shirt Bumper sticker Foam football b. Required: a. What events, resources, and agents must have been included in the underlying conceptual model from which these relational tables were designed? Label the tables as events, resources, agents, data reductions and relationships 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? 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 and 20 units of item 876. 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? h. Understanding Databases Use the following partial database tables to answer the required questions: Sales Table Sales Event # Dat Terms Salesperson ID Customer ID 11/5 11/5 11/5 2 3 2 10, net 30 2 10, net 30 COD 2 4 2543 635 1845 6 Price each 1.25 0.875 Sale-Inventory Table Sale Event # 1 1 1 2 2 3 3 3 3 Inventory Item # 876 674 451 887 513 736 001 302 224 Inventory Quantity 10 8 30 54 188 36 58 16 114 0.995 1.475 0.525 24.995 7.875 8.00 8.75 Salesperson Table Salesperson ID 2 4 6 8 Last Name Cleaves Warrick Peterson Janakowski First Name Mateen Peter Morris Sebastian Cashier Table Cashier ID 1 2 Last Name Weinke Outzen First Name Chris Marcus Date Sales Check # 11097 Cashier ID 1 Event # 2 Customer ID 635 Cash Account # 110146758 Amount Received $ 178.35 11/6 Cash Receipts Table Cash Receipt # 1001 Customer Table Customer ID 101 183 635 1845 2543 Last Name First Name Conrad Chris Anderson Paul Padgham Donna Oliver Andrew Cook Carol Address 5629 Longfellow Dr. 674 Sunderland Lane 1264 Algonquin Road 8512 Bonita Dr. 536 Secondary Ave. Zip 65323 63126 48854 33051 75518 Zip Code Table City Paragould Sioux City Mason Clearwater Fremont State Zip AK 65323 IA 63126 MI 48854 FL 33051 CA 75518 Cash Table Cash # 110146758 1203948102 Type of account Regular checking Payroll checking account Bank Name North First Credit Grantors Inventory Table Inventory Item # 001 224 302 451 513 674 736 876 887 TIIT TO Description XL T-shirt XL Sweatshirt XXL T-shirt Felt pennant Ping pong ball Golf ball XL Polo shirt Bumper sticker Foam football b. Required: a. What events, resources, and agents must have been included in the underlying conceptual model from which these relational tables were designed? Label the tables as events, resources, agents, data reductions and relationships 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? 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 and 20 units of item 876. 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? h