A Curiosity Shop wants to expand its database applications beyond the current recording of sales. The company still wants to maintain data on customers, employees, vendors, sales, and items, but it wants to simplify the storage of inventory and customer and employee data.

Sales Table

LastName	FirstName	Phone	InvoiceDate	InvoiceItem	Price	Tax	Total
Shire	Robert	206-524-2422	12/14/2017	Antique Desk	3000.00	249.00	3249.00
Shire	Robert	206-524-2422	12/14/2017	Antique Desk Chair	500.00	41.50	541.50
Goodyear	Katherine	206-524-3544	12/15/2017	Dining Table Linens	1000.00	83.00	1083.00
Bancroft	Chris	426-635-9788	12/15/2017	Candles	50.00	4.16	54.16
Griffith	John	206-524-4656	12/23/2017	Candles	45.00	3.74	48.74
Shire	Robert	206-524-2422	1/5/2018	Desk Lamp	250.00	20.75	270.75
Tierney	Doris	425-635-8677	1/10/2018	Dining Table Linens	750.00	62.25	812.25
Anderson	Donna	360-538-3544	1/12/2018	Book Shelf	250.00	20.75	270.75
Goodyear	Katherine	206-524-3544	1/15/2018	Antique Chair	1250.00	103.75	1353.75
Goodyear	Katherine	206-524-3544	1/15/2018	Antique Chair	1750.00	145.25	1895.25
Tierney	Doris	425-635-8677	1/25/2018	Antique Candle Holders	350.00	29.05	379.05

Purchased Items Table

PurchaseItem	PurchasePrice	Purchase Date	Vendor	Phone
Antique Desk	1800.00	11/7/2017	European Specialties	206-325-7868
Antique Desk	1750.00	11/7/2017	European Specialties	206-325-7868
Antique Candle Holders	210.00	11/7/2017	European Specialties	206-325-7868
Antique Candle Holders	200.00	11/7/2017	European Specialties	206-325-7868
Dining Table Linens	600.00	11/14/2017	Linen and Things	206-325-6755
Candles	30.00	11/14/2017	Linen and Things	206-325-6755
Desk Lamp	150.00	11/14/2017	Lamps and Lighting	206-325-8977
Floor Lamp	300.00	11/14/2017	Lamps and Lighting	206-325-8977
Dining Table Linens	450.00	11/21/2017	Linen and Things	206-325-6755
Candles	27.00	11/21/2017	Linen and Things	206-325-6755
Book Shelf	150.00	11/21/2017	Harrison, Denise	425-746-4332
Antique Desk	1000.00	11/28/2017	Lee, Andrew	425-746-5433
Antique Desk Chair	300.00	11/28/2017	Lee, Andrew	425-746-5433
Antique Chair	750.00	11/28/2017	New York Brokerage	206-325-9088
Antique Chair	1050.00	11/28/2017	New York Brokerage	206-325-9088

Currently, each item is considered unique, which means that the item must be sold as a whole, and that multiple units of the item in stock must be treated as separate items in the ITEM table. The Curiosity Shop management wants the database modified to include an inventory system that will allow multiple units of an item to be stored under one ItemID. The system should allow for a quantity on hand, a quantity on order, and an order due date. If the identical item is stocked by multiple vendors, the item should be orderable from any of these vendors. The SALE_ITEM table should then include Quantity and ExtendedPrice columns to allow for sales of multiple units of an item.

The Curiosity Shop management has noticed that some of the fields in CUSTOMER and EMPLOYEE store similar data. Under the current system, when an employee buys something at the store, his or her data has to be reentered into the CUSTOMER table. The managers would like to have the CUSTOMER and EMPLOYEE tables redesigned using subtypes.

Create the entities for an ERD model based on the Sales and Purchased Items tables, extending the entities to include the Inventory and Sales and Employee data requirement. Specify identifiers and attributes for all entities. Remove unnecessary attributes from the original entities as the three new entities are added.

Draw an ERD model for the Curiosity Shops entities, relationships, attributes and identifiers. Use the IE Crows Foot ERD model for your diagrams. Justify the decisions you make regarding minimum and maximum cardinalities. Deciding on cardinalities is an important skill for data modelers

Submit your deliverable in a Word document. You can use Visio or Lucid-chart to create the Entity-Relationship Diagram.