Design Assignment: Queen Anne Curiosity Shop

For this assignment, we are going to take instructions directly from the book with a few minor changes. These minor changes it make it so that copying directly from the instructor manual will be obvious.

Part I - E-R Data Modeling

At the end of Chapter 5, in the Case Questions, please find the heading "The Queen Anne Curiosity Shop" Complete A - E in this section using LucidChart. Complete A-E of this section with these changes.

In A, do not go looking for "the Queen Anne Curiosity Shop database schema shown in Chapter 3's The Queen Anne Curiosity Shop Project Questions". Instead, use the description below to create the data model. (note: each line is probably a separate entity but definitely is not all of the entities needed) (Need total of 6 entities for the first data model iteration) (I'd suggest including a surrogate attribute for each entity)

• The QACS keeps track of their customers (CUSTOMER) by having them fill out a form asking for their first and last name, address, city, state, zip code, phone number and email address.
• They also keep track of their employees (EMPLOYEE) by their first and last name, phone number and email address.
• They also have records of their vendors (VENDOR) with the company name, the contact's first and last name, company's address, city, state, zip code, phone number, fax number and email address.
• They have records for their items purchased (ITEM) from the vendors too. It is recorded with the item description (name), purchase date, item cost and item price.
• Finally, they keep track of their sales (SALE) with the sale date, subtotal, tax, total of each sale.
• A sixth entity called SALE_ITEM exists which serves as a connection between other entities (SALE, ITEM) and it also has an Item price attribute.

Heres how the business works:

• A customer doesn't have to have sale(s), but a sale is required to have one customer.
• An employee doesn't have to have sale(s), but a sale is required to have one employee.
• A vendor doesn't have to have item(s) sold to QACS, but an item is required to have one vendor.
• Even though the item price isn't recorded within the sale (notice above), it is still accessible as its recorded in the record of the item. (This must mean they are connected, but not ... directly. Be careful on creating this entity which isn't obvious. You will have to determine maximum and minimum cardinality.)

Complete B - D as it is written. I would suggest creating a separate data model for B and one for C (copy and paste the one from A, then edit for B and do the same for C). Then you will turn in D.

You will be creating an entity called ORDER for B. It should include attributes to handle the date of the order, date received, quantity ordered, item cost, subtotal, tax and total cost. You will then need to modify the ITEM and SALE_ITEM entities accordingly.

For C, you are going to be creating a PERSON entity which will have some of the attributes from CUSTOMER and EMPLOYEE that are the same.

For D, combine the new elements of C and D together into one data model.

Note, part of completing each data model is to complete a table describing the entities and such. The only table like this you need to submit is the one for D which will include everything. It does help to create one for each data model though. See example below with row item from the homework example we did in class for Chapter 5. Use the template below as an example:

RELATIONSHIP			CARDINALITY
PARENT	CHILD	TYPE	MAX	MIN
INDEX	STOCK_QUOTE	ID-Dependent	1:N	M-M

Part II - Database Design

At the end of Chapter 6 in your book under Case Questions, find the heading called "The Queen Anne Curiosity Shop". Complete B - E with some slight modifications. Note, the tables below are not the same as the tables above but modified with entities added and such. You do not have to do Part A as it is redundant.

Do them in this order: B, E, C, D. Use LucidChart to create these database models. Below is the database model outline with the new modifications:

PERSON (PersonID, isEmployee, isCustomer, LastName, FirstName, Address, City, State, ZIP, Phone, Email)

CUSTOMER (PersonID, CreditCardType, CreditCardNumber, CreditCardExpirationDate)

EMPLOYEE (PersonID, DateOfHire, HourlyPayRate)

SALE (SaleID, SaleDate, SubTotal, Tax, Total, CustomerPersonID, EmployeePersonID)

SALE_ITEM (SaleID, SaleItemID, Quantity, ItemPrice, ExtendedPrice, ItemID)

ITEM (ItemID, ItemDescription, ReorderPointQuantity, QuantityOnHand, ItemPrice)

VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)

ORDER (InvoiceNumber, DateOrdered, DatedReceived, QuantityOrdered, ItemCost, OrderSubTotal, OrderTax, OrderTotalCost, ItemID, VendorID)

For Part D, create the database model in LucidChart, export it to a PDF for submission and you will submit a table of the format below. Be sure to create the DBMS ER diagram with the data types in the tables.

For Data Type, if the attribute name has the word "Date" in it, it will be a Date data type. If it has to do with money such as hourly rate, it will have Decimal(9,2) as the Data Type. Please use your best discretion on the rest. Recall if a field has both letters and numbers, Varchar is best. Please specify lengths for all data types such as Char(4) or Varchar(50).

For Part E, fill it out as you think it should be. I have already filled in the PARENT column for you as well as the Referential Integrity Constraint of the first one. Your final answers may vary from mine and that's okay. When we use this table to create our database in Chapter 7, we will have a table that is filled in to use.

Relationship		Referential Integrity Constraint	Cascading Behavior on Child
PARENT	CHILD		ON UPDATE PARENT PK	ON DELETE PARENT PK
PERSON	CUSTOMER	PersonID in CUSTOMER must exist in PersonID in PERSON	NO (PROHIBIT)	YES
PERSON	EMPLOYEE
CUSTOMER	SALE
EMPLOYEE	SALE
SALE	SALE_ITEM
ITEM	SALE_ITEM
ITEM	ORDER
VENDOR	ORDER

For B and C, answer the questions.

Please submit your LucidChart files for Part I and Part II (PDF), and a Word document outlining the necessary components of the assignment that require documentation (tables of cardinality and relationships).