Relational Database concepts like Primary Key identication, Foreign Key identification, etc.

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?

Attached file(s):

Accounting Access.doc  View File