Sparkle Diamonds Project - I need the following created in MySQL only!
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
Populate each table with at least ten data records that would support the user's functional requirements.
Execute the following queries in the Diamond Broker database:
Create an 1-page inventory list showing all the attributes for each diamond in inventory (both in stock and not in stock)
Show the total price and costs for all in stock inventory items
Show a 1-page list of all diamonds that are in stock and have a carat weight of over 1.0 carats
Show a 1-page list of customer names and order numbers for those customers that have placed an order in the past twelve months
Show a 1-page list of all orders with customer name and include all items that are on that order
Compress the output of the queries including all of the scripts to create the database tables, to establish keys and constraints, to populate data within the database, and queries into a single ZIP folder.
So to recap, I need the tables created, ten data records per table added then the queries ran. MySQL will not run on my machine, so I will need the model and query files and scripts for this assignment. Bonus to anyone that can complete this in the next five hours.