Your first assignment in the Fine Diamond Database Project is to create database design alternatives for the IT Director.Review the Retail Database Requirements described in the Week One Learning Team

Fine Diamond Company Background and Learning Team Project Outline

You work for Fine Diamond Company, a company that procures and sells diamonds through a distribution center, a network of 150 retail stores, and a website.  Although financials are not disclosed in this industry, companies of this size typically sell 200 diamonds per month at $5,000 per diamond.  It is estimated that Fine Diamond Company retail stores produce approximately one million dollars per month in sales per store.  The website monthly revenues are approximately eight times the sales of the typical store.

The organization is growing and the IT Director has decided that a more complex, advanced database is needed to manage Fine Diamond data. The data includes by-store inventory and retail databases, consolidated inventory and retail databases, and a customer database.

The IT Director wants a distributed database. Therefore, the additional database requirements will include the following four tables: 

• Retail Stores Table - Each retail store is assigned a unique retail location number and will have a single ship-to location.  Diamonds can be shipped to retail locations or drop-shipped to a customer.

• Customers Table - Each customer is assigned a unique customer number. Customers can ship their orders to multiple locations. The sales team needs to be able to select the customer and the ship-to location when preparing sales orders.

• Inventory Table - The sales team will need to query the inventory to find diamonds that meet the following customer specifications:

  • Cost - the price Fine Diamond Company has paid for the stone

  • Price - what Fine Diamond Company will sell the stone for to customers

  • Carat - weight of the diamond

  • Clarity - see the Clarity Scale for details below

  • Cut - the cut scale for Fine Diamond Company diamonds are Excellent, Very Good, and Good

  • Color - see the Color Scale for details below

  • Order Table - The sales team will need to prepare a sales order for existing and new customers.  They also must ensure they only sell diamonds that are currently in inventory (i.e., they want to avoid backorders or selling diamonds already ordered by another customer). The DBMS must be designed so customers can purchase multiple stones on one sales order.

Clarity Scale

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

• SI1 - Slight included (under 10x magnification)

• SI2 - Slightly included (to the naked eye)

• I1 - Inclusions obvious (under 10X magnification)

• I2 - Inclusions obvious (with pronounced inclusions)

• I3 - Inclusions obvious (with durability issues)

Color Scale

• 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 - Near Colorless - diamonds color rated by a Gemologist

• K, L, M - Faint Color

• N, R - Very Light Color

• S, Z - Light Color