Extra Credit

Understanding Databases

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

Sales Table

Sales Event # Dat

e

Terms Salesperson ID Customer ID

$111/5210,$ net $3022543$

$211/5210,$ net $304635$

$311/5$ COD $61845$

Sale$-$Inventory Table

Sale Event # Inventory Item # Inventory Quantity Price each

$1876101\mathrm{.}25$

$167480\mathrm{.}875$

$1451300\mathrm{.}995$

$2887541\mathrm{.}475$

$25131880\mathrm{.}525$

$37363624\mathrm{.}995$

$3001587\mathrm{.}875$

$3302168\mathrm{.}00$

$32241148\mathrm{.}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 Table

Cash

Receipt #

Date Check

#

Cashier

ID

Sales

Event #

Customer

ID

Cash

Account #

Amount

Received

$100111/61109712635110146758$ $ $178\mathrm{.}35$

Customer Table

Customer ID Last Name First Name Address Zip

$101$ Conrad Chris $5629$ Longfellow Dr$.65323$

$183$ Anderson Paul $674$ Sunderland Lane $63126$

$635$ Padgham Donna $1264$ Algonquin Road $48854$

$1845$ Oliver Andrew $8512$ Bonita Dr$.33051$

$2543$ Cook Carol $536$ Secondary Ave. $75518$

ZipCode Table

City State Zip

Paragould AK $65323$

Sioux City IA $63126$

Mason MI $48854$

Clearwater FL $33051$

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:

a$.$ What events, resources, agents $,$ relationship, and data reduction $($or types$)$ 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 $($or types$)$ and relationships

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\mathrm{.}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\mathrm{.}35$ deposited into

cash account # $110146758$

What table$($s$)$ would you use? How many record$($s$)$ would you add or modify in the tables