MySQL Learning Team Assignment

Build a 1-page Entity Relationship Diagram that models the required tables and relationships using Microsoft® Visio®, Lucidchart, or another software tool of your choice. The table design and relationships must support the following functionality:

· Customers - Each customer is assigned a unique customer number but may have multiple locations to ship to. The sales team needs to be able to select the customer and the location for preparing sales orders.

· Inventory Table - The sales team will want to query the inventory to find diamonds that meet the following specifications of their customers:

· Cost - what SD has paid for the stone

· Price - what SD will sell the stone for

· Carat - weight

· Clarity - clarity scale for diamonds offered are IF, VVS1, VVS2, VS1, VS2*

· Cut - cut scale for diamonds offered are Excellent, Very Good, Good

· Color  - color scale for diamond offered are D, E, F, G, H, I, J*

· Order Table - The sales team will want to prepare a sales order for existing customers and sell only diamonds that are in inventory. The DBMS must be designed such that customers can purchase multiple stones on one sales order.

*Diamond Scale Reference*

· Clarity:

· IF- Internally Flawless

· VVS1 - Very, very slightly included (pavilion)

· VVS2 - Very, very slightly included (crown)

· VS1 - Very slightly included (under 10X magnification)

· VS2 - Very slightly included (to the naked eye)

· Color:

· D, E, F - Colorless - color between D, E, and F diamonds can be detected only by a gemologist in side by side comparisons, and rarely by the untrained eye

· G, H, I, J, K - Near Colorless - diamonds color rated by a gemologist

Week 3 Assignment:

Normalize your tables designed in Week Two.

Using the MySQL™ Workbench, or through a script, construct the tables in MySQL™ from the logical database design from your normalized ERD.

Populate each table with at least ten data records that would support the user's functional requirements.

Week 4 Assignment:

?????