The Curiosity Shop Data Model

March 5, 2022 

The Curiosity Shop is expanding its database applications to include a simplified inventory system and employee and customer data storage. Management would like to see an entity-relationship diagram that establishes a blueprint for the new design. The shop has provided sample tables for Sales and Purchased Items with some overlapping columns and a list of requirements. This project identifies the appropriate entities, defines primary and foreign keys, establishes attributes for each entity, and connects the tables with proper relationship notation.     

The Requirements

For efficient and productive database development, it is critical to understand the company’s requirements. The second phase of the information systems development process describes the database’s processes, data, relationships, and interactions using diagramming techniques (Mannino, 2019). This phase is similar to the software development lifecycle (SDLC) analysis phase, which involves acquiring information from various sources like interviews, observation, and documentation analysis (Valacich & George, 2020). In this scenario, management has provided us with a written list of requirements and sample data to glean information for the database setup. 

The following is a list of requirements provided by management:

1. Items are unique and must be sold as a whole.
2. Multiple units are treated separately in the ITEM table.
3. Include a simplified inventory system that allows multiple units to be stored under a single ItemID. The design needs to include quantity on hand, quantity on order, and order due date.
4. If multiple vendors supply identical items, the system must show that the item can be ordered from any vendor.
5. The SALE_ITEM table should include Quantity and ExtendedPrice columns to sell multiple units of an item.
6. CUSTOMER and EMPLOYEE tables should be redesigned as subtypes.

The Entities, Attributes, and Cardinalities

Eight entities with multiple attributes were identified for this database project, including the following:

1. Person entity includes a primary key for PersonID and attributes Employee, Customer, FirstName, LastName, Phone, Email, Address, City, State, and Zip.
2. Customer is a subtype of Person and includes the unique CustomerID, as well as the foreign key for the PersonID.
3. Employee is a subtype of Person and includes the primary key EmployeeID, the foreign key PersonID, and employee-specific information like hire date and pay rate. 
4. Sale entity includes a primary key for SaleID and attributes SaleDate, Subtotal, SaleTax, and Total.
5. Sale Item is an existence-dependent entity to the Sale entity and includes the PK from the Sale entity, as well as its own unique SaleItemID. It includes attributes for ItemPrice, Quantity, and ExtendedPrice as listed in the business requirements.
6. Item entity includes attributes for an ItemID, a Foreign Key for ItemPrice from the Sale Item entity, ItemDescription, QtyOnHand, and ReorderPointQty.
7. Order entity includes a primary key for InvoiceNo and attributes OrderDate, OrderQty, ItemCost, Subtotal, Tax, and TotalCost for the invoice.
8. Vendor entity is identified by the primary key VendorID and attributes for CoName, ContactLName, ContactFName, Email, Phone, Address, City, State, and Zip.

The list below outlines the relationships and cardinalities determined from reviewing the available tables, listed requirements, and the current database setup. The notation method used in this diagram is the Crow’s Foot to help indicate the many sides of the relationships (Dybka, 2016).

1. The generalization hierarchy classifies a person as either a CUSTOMER or EMPLOYEE subtype.
2. A one-to-many relationship with an optional cardinality of zero exists between the CUSTOMER, EMPLOYEE, and SALE tables. Customers can buy zero or many sales while employees can sell zero to many sales.
3. The SALE_ITEM table is existence dependent on the SALE table, giving it two primary keywords. PK1 comes from the SALE table, and PK2 identifies the SaleItemID. This table demonstrates what items are pulled from inventory to complete the sale, so columns include the Quantity, ItemPrice, and ExtendedPrice. There is a one-to-many relationship with a minimum cardinality of one between these tables, as at least one sale will be connected to one-to-many sale items. 
4. There is a one-to-one relationship with minimum cardinality of one on both sides between the SALE_ITEM and ITEM tables to satisfy the requirement that items are unique and each unit is treated separately. One SALE_ITEM can be connected to one ITEM.
5. The ITEM table connects to the ORDER table with a one-to-many relationship and a minimal cardinality of one on both sides, satisfying the requirement that multiple units be stored under a single ItemID.
6. The ORDER table is connected to the VENDOR table in a one-to-many relationship with a minimum cardinality of one on each side. One invoice can be related to at least one or many vendors, satisfying the requirement that multiple vendors supply identical items.

The following page includes the data model in an entity-relationship diagram using the Crow’s Foot notation. As with most information systems, this phase will require management attention and approval, as a few items likely need to be adjusted, added, or removed. Questions to cover would center around the connection between the VENDOR and ORDER tables. For instance, is one vendor associated with a single invoice, or were the requirements specifying that multiple vendors should be connected to the ITEM table? The E-R diagram in Figure 1 serves as a good starting point for developing a simplified and streamlined database for The Curiosity Shop to adequately store and utilize customer, employee, sales, and inventory data. 

Figure 1

The E-R Diagram for The Curiosity Shop

References

Dybka, P. (2016, March 31). Crow’s Foot Notation. Vertabelo. Retrieved on March 5, 2022, from https://vertabelo.com/blog/crow-s-foot-notation/

Mannino, M. V. (2019). Database design, application development and administration, 7e. http://platform.virdocs.com/r/s/0/doc/592088/sp/176800379/mi/566374934?cfi=%2F4%2F4

Valacich, J. S., & George, J. F. (2020). Modern systems analysis and design (9th ed.). Pearson Education, Inc.