with this database management work the instructions says, you determined functional dependencies, then converted the tables to 3rd normal form. This is the starting point for this phase of the case study

1. List all the tables in DBDL format. Show all tables, and keys. (8 points)
2. User Views (Marketing Department): (10 points each)
A. Upon interviewing the Marketing Department, it is learned that under certain circumstances, a sales rep may work with a loyal customer, offering that customer a lower price (orderPrice) than that of the product list price. As an example, the normal price of the 450-watt modular power supply is $75. In securing an order, the sales rep may grant a lower price of $65 for a particular customer.
i. List all of the changes that you would make to the table structure to incorporate this user view.
ii. List all of the tables in DBDL format along with all keys.
B. The Marketing Department wants to have a website that is searchable for their customers. One way to search the products offered is via category (hard drives, monitors, cases, etc.). How might you incorporate product categories into the database schema given that the company will have CategoryID, Category, and CategoryImage as fields?
i. List all of the changes that you would make to the table structure to incorporate this user view.
ii. List all of the tables in DBDL format along with all keys.
3. User view (Shipping and Receiving): (10 points)
A. In interviewing the folks in Shipping and Receiving, it is determined that each product in inventory is provided by outside vendors. In this situation, a vendor can supply many products, and a product can have many vendors. Further, there has to be a mechanism in place to alert employees when inventory is at a predetermined minimum level for a particular product, so that an order can be placed.
Vendor information includes: VendorID, VendorName, Address, City, State, Zip Code, and ContactPhone.
i. List all of the changes that you would make to the table structure to incorporate this user view.
ii. List all of the tables in DBDL format along with all keys.
4. Create an Entity-Relationship Diagram based upon the table structure of just the tables modified in 2 & 3 above. Show tables, keys, and relationships. (12 points)

 

This was part one just for example

 

 

Transcribed Image Text:

1. From the information provided above, list the functional dependencies derived from the following un-normalized tables (the inner parenthesis represents a repeating group):: a. Products (ProductID, product Brand, product Description, product ListPrice, quantityOnHand (OrderID, OrderDate, CustomerID, ProductID, quantityOrdered, EstimatedShipDate)) b. Sales Reps (repID, repFirstName, repLastName (CustomerID, customer FirstName, customerLastName, customerAddress, customerPhoneNum, customerEmail)) Note: customerAddress includes street, city, state, and zip code. ProductID -> product Brand, product Description, product ListPrice, quantityOnHand OrderID -> Order Date, CustomerID, EstimatedShipDate ProductID, OrderID -> quantityOrdered RepID> repFirstName, repLastName CustomerID -> customerFirst Name, customer Last Name, customerAddress, ctomerPhoneNum, customerEmail, RepID 2. With the result from question 1 above, convert ALL tables to 3rd normal form. List the tables in shorthand notation. Products (ProductID, product Brand, product Description, product ListPrice, quantityOnHand) Orders (OrderID, Order Date, CustomerID, EstimatedShipDate) ProductsOrdered (ProductID, OrderID, quantityOrdered) SalesReps (RepID, repFirstName, repLastName) Customers (CustomerID, customer First Name, customer LastName, customerAddress, customerPhoneNum, customerEmail, RepID) 1. From the information provided above, list the functional dependencies derived from the following un-normalized tables (the inner parenthesis represents a repeating group):: a. Products (ProductID, product Brand, product Description, product ListPrice, quantityOnHand (OrderID, OrderDate, CustomerID, ProductID, quantityOrdered, EstimatedShipDate)) b. Sales Reps (repID, repFirstName, repLastName (CustomerID, customer FirstName, customerLastName, customerAddress, customerPhoneNum, customerEmail)) Note: customerAddress includes street, city, state, and zip code. ProductID -> product Brand, product Description, product ListPrice, quantityOnHand OrderID -> Order Date, CustomerID, EstimatedShipDate ProductID, OrderID -> quantityOrdered RepID> repFirstName, repLastName CustomerID -> customerFirst Name, customer Last Name, customerAddress, ctomerPhoneNum, customerEmail, RepID 2. With the result from question 1 above, convert ALL tables to 3rd normal form. List the tables in shorthand notation. Products (ProductID, product Brand, product Description, product ListPrice, quantityOnHand) Orders (OrderID, Order Date, CustomerID, EstimatedShipDate) ProductsOrdered (ProductID, OrderID, quantityOrdered) SalesReps (RepID, repFirstName, repLastName) Customers (CustomerID, customer First Name, customer LastName, customerAddress, customerPhoneNum, customerEmail, RepID)