with this database management work the instructions says, you determined functional dependencies, then converted the tables to
Question:
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)
Concepts of Database Management
ISBN: 978-1285427102
8th edition
Authors: Philip J. Pratt, Mary Z. Last