This case study assignment is based on (but not the same as) the Queen Anne Curiosity Shop (QACS) Case Questions at the end of Chapters 5 and 6. Since there are important differences between the questions below and the questions in the textbook, please make sure that you answer the questions on this document.

You will need to use a data modeling tool to create E-R diagrams. You are free to choose a tool that you are most comfortable with, as long as you can produce E-R Diagrams using IE Crow's Foot model. I would recommend lucidchart, since it is easy to use and export from. Please see the video below to get familiar with lucidchart:

Launch External Tool

Please prepare your answers in Microsoft Word, including E-R diagrams imported from your choice of data modeling tool to MS Word and submit your case study in pdf format. Note: This link shows multiple ways to get your diagrams from Lucidchart to MS Word.

Case Description

All Supplies Medical is a medical supply company. It sells both custom-made large medical devices and mass-produced medical supplies. The custom-made large devices are ordered from manufacturing companies, and the mass-produced medical supplies are purchased from distributors. Their customers include clinics, hospitals, and health systems. The custom-made large medical devices are unique, though some can be ordered as multiples for hospitals of a health system. The mass-produced medical supplies are not unique, and an item may be reordered if it is out of stock. They are also available in various sizes.

Chapter 5 Part A

Initially, consider the following tables, constraints, assumptions for the All Supplies Medical database and create an IE Crow's Foot E-R model for it. Make sure you indicate identifying relationships with solid and non-identifying relationships with dashed lines. Also, indicate weak entities with orange and strong entities with green color; ID-dependent entities with round and non-id-dependent entities with squared corners. Note: At this stage, primary and foreign keys don't have to be indicated yet. You will have to indicate them as part of your answers to Chapter 6 questions.

Tables:

• CUSTOMER_CONTACT (CustomerID, Organization, LastName, FirstName, EmailAddress, Address, City, State, ZIP, Phone, ReferredBy)
• EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)
• SALE (SaleID, CustomerID, EmployeeID, InvoiceDate, PreTaxTotal, Tax, Total)
• PURCHASE (PurchaseID, VendorID, PurchaseItem, PurchaseDate, PurchasePrice, SalePrice)
• SALE_ITEM (SaleID, SaleItemID, PurchaseID, SalePrice)
• VENDOR (VendorID, VendorName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)

Constraints:

• ReferredBy in CUSTOMER_CONTACT must exist in CustomerID in CUSTOMER_CONTACT
• CustomerID in SALE must exist in CustomerID in CUSTOMER_CONTACT
• EmployeeID in SALE must exist in EmployeeID in EMPLOYEE
• SaleID in SALE_ITEM must exist in SaleID in SALE
• PurchaseID in SALE_ITEM must exist in PurchaseID in PURCHASE
• VendorID in PURCHASE must exist in VendorID in VENDOR

Assumptions:

• Assume that CustomerID of CUSTOMER_CONTACT, EmployeeID of EMPLOYEE, SaleID of SALE, PurchaseID of PURCHASE, and VendorID of VENDOR are all surrogate keys starting from 1 with increments of 1.
• One customer can make many purchases (expressed in this model by the SALE entity), and CUSTOMER_CONTACT data may be entered into the database before any associated purchase is made or data for that purchase is entered.
• One employee can make many sales, and EMPLOYEE data may be entered into the database before any associated sale is made or data for that sale is entered. However, every sale must be associated with an employee.
• One sale must have one or more sale-items as part of that sale; and when SALE data is entered into the database, data for at least one sale-item must be entered with it.
• One purchase is associated with only one sale-item, but PURCHASE data may be entered into the database before the purchase is sold as a sale-item. The sale-item, however, must be a purchase already entered into PURCHASE.
• One vendor can provide zero, one, or many items, and VENDOR data may be entered into the database before any associated purchase is made by the store or data for that purchase is entered in PURCHASE. However, every purchase must be associated with a preexisting vendor.

Chapter 5 Part B

Consider the following as additional requirements for inventory and extend and modify the E-R data model that you prepared in Part A accordingly. Create appropriate identifiers and attributes for each entity and explain assumptions you make to determine minimum and maximum cardinality (similar to the assumptions listed above). Keep in mind that you will need additional entity(ies) and you may need to move one or more attributes from their current entity to another.

Description of additional requirements

All Supplies Medical wants to expand its database applications beyond the current recording of sales. The company still wants to maintain data on customer contacts, employees, vendors, sales, and items, but it also wants to modify the way it handles inventory. Currently, each purchase is considered unique, which means that one sale-item consists of only one quantity of an item, and that multiple units of the item in stock must be treated as separate items (records) in the PURCHASE table. The management wants the database modified to include an inventory system that will allow multiple units of an item to be stored under one PurchaseID. The system should allow columns for quantity on hand, quantity on order (ordered from a vendor), and 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.

Hint: This can be done by adding a single new entity and two relationships between the new entity and two existing entities. You may also have to change the type of one of the existing relationships.

Chapter 5 Part C

Consider the following as additional requirements for more efficient storage of CUSTOMER_CONTACT and EMPLOYEE data and extend and modify the E-R data model that you prepared in Part B accordingly. Create appropriate identifiers and attributes for each entity and explain assumptions you make to determine supertype/subtype entities and relationships between them (similar to the assumptions listed above). Note: For supertype/subtype relationships, you do not have to have the cardinality symbols, having inclusive/exclusive symbols will be sufficient. Also, LucidChart does not have specific symbols for inclusive/exclusive. You can just use some of the generic symbols to create something similar.

Description of additional requirements

All Supplies Medical wants to simplify the storage of customer and employee data. The company management has noticed that some of the fields in CUSTOMER_CONTACT and EMPLOYEE entities store similar data. Since their policies allow their employees to do a side job as a representative for a customer, under the current system, when an employee buys something for a customer, his or her data has to be reentered into the CUSTOMER_CONTACT table. The managers would like to have the CUSTOMER_CONTACT and EMPLOYEE tables redesigned using subtypes.

Chapter 6 Part A

Create a visual representation of your database design as an IE Crows Foot E-R diagram similar to the one in Figure 6-39 (Hint: Review the process of going from Figure 6-37 to Figure 6-38 to Figure 6-39). Make sure you indicate identifying relationships with solid and non-identifying relationships with dashed lines. Indicate weak entities with orange and strong entities with green color; ID-dependent entities with round and non-id-dependent entities with squared corners. Also, indicate Primary Keys and Foreign Keys as shown in the above video.

Chapter 6 Part B

Convert the E-R data model that you prepared in Chapter 5 Part C to a database design. Show your design by specifying column properties similar to Figure 6-43 of the textbook.

Chapter 6 Part C

Document your minimum cardinality enforcement for required parents, if any (pg. 308-310) and for required children, if any (pg. 310-311).