Reference no: EM131498387

Question 1:

Ashton wants to store the following data about S&S's purchases of inventory:
item number
date of purchase
vendor number
vendor address
vendor name
purchase price
quantity purchased
employee number
employee name
purchase order number
description
quantity on hand
extended amount
total amount of purchase

REQUIRED

a. Design a set of relational tables to store this data. Do all of the data items need to be stored in a table? If not, which ones do not need to be stored, and why do they not need to be stored?

b. Identify the primary key for each table.

c. Identify the foreign keys needed in the tables to implement reference.

Question 2:

You want to extend the schema shown in Table 4-16 to include information about customer payments. Some customers make installment payments on each invoice. Others write a check to pay for several different invoices. You want to store the following information:

amount applied to a specific invoice
cash receipt number
customer name
customer number
date of receipt
employee processing payment
invoice payment applies to
total amount received

REQUIRED
a. Modify the set of tables in Table 4-16 to store this additional data.
b. Identify the primary key for each new table you create.
c. Implement your schema using any relational DBMS package. Indicate which attributes are primary and foreign keys, and enter sample data in each table you create.

Question 3:

Create relational tables that solve the update, insert, and delete anomalies in Table 4-17.

INVOICE #	DATE	ORDER
DATE	CUSTOMER
ID	CUSTOMER
NAME	ITEM #	DESCRIPTION	QUANTITY
6/19/2018	5/25/2018	201	Johnson	103	Trek 9000	5
6/19/2018	5/25/2018	201	Johnson	122	Nimbus 4000	8
6/19/2018	5/25/2018	201	Johnson	10	Izzod 3000	11
6/19/2018	5/25/2018	201	Johnson	71	LD Trainer	12
6/20/2018	6/1/2018	305	Henry	535	TR Standard	18
6/20/2018	6/1/2018	305	Henry	115	NT 2000	15
6/20/2018	6/1/2018	305	Henry	122	Nimbus 4000

Question 4:

Create relational tables that solve the update, insert, and delete anomalies in Table 4-18.

TABLE 4-18 Purchase Order (PO) Table
PURCHASE	PURCHASE	ORDER	QUANTITY				VENDOR
ORDER#	DATE	PART#	DESCRIPTION	UNIT PRICE	ORDERED	VENDOR#	VENDOR NAME	VENDOR ADDRESS
2	3/9/2018	334	XYZ	$30	3	504	KL Supply	75 Stevens Dr.
2	3/9/2018	231	PDQ	$50	5	504	KL Supply	75 Stevens Dr:
2	3/9/2018	444	YYM	$80	6	504	KL Supply	75 Stevens Dr.
3	4/5/2018	231	PDQ	$50	2	889	OSCAN INC	55 Cougar Cir.