TraderMo Case Study, Part $2$

To begin, please review the initial database requirements of the TraderMo Case Study, Part

During Part $1,$ you determined functional dependencies, then converted the tables to $3^{rd}$

normal form. This is the starting point for this phase of the case study.

List all the tables in DBDL format. Show all tables, and keys. $(8$ points$)$

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.

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.

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$)$