1. Engineering
  2. Computer Science
  3. a curiosity shop wants to expand its database applications beyond...

Question: a curiosity shop wants to expand its database applications beyond...

Question details

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 Shop’s entities, relationships, attributes and identifiers. Use the IE Crow’s 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.

Solution by an expert tutor
Blurred Solution
This question has been solved
Subscribe to see this solution