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

a. Design a set of relational tables to store this data. Name each table and field in accordance with good database design. Do all 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 referential integrity.

d. Implement your tables using any relational database product to which you have access.

e. Test your specification by entering sample data in each table.

f. Create a few queries to retrieve or analyze the data you stored.