A1.2 QACS Schema

In this assignment you will create the Queen Ann Curiosity Shop (QACS) Database

Assume that The Queen Anne Curiosity Shop designs a database with the following tables:

CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, Email)

EMPLOYEE (EmployeeID, LastName, FirstName, Phone, Email)

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

ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID)

SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total)

SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)

The referential integrity constraints are:

VendorID in ITEM must exist in VendorID in VENDOR

CustomerID in SALE must exist in CustomerID in CUSTOMER

EmployeeID in SALE must exist in EmployeeID in EMPLOYEE

SaleID in SALE_ITEM must exist in SaleID in SALE

ItemID in SALE_ITEM must exist in ItemID in ITEM

Cascade updates and restrict deletes.

Set it up so that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, ItemID of ITEM and SaleID of SALE are surrogate keys with start at 1 and increment by 1 automatically each time a new record is inserted. SaleID with SaleItemID make up the composite key of SALE_ITEM. Set SaleItemID default to 1.

Note that a vendor may be an individual or a company. If the vendor is an individual, the CompanyName field is left blank, while the ContactLastName and ContactFirstName fields must have data values. If the vendor is a company, the company name is recorded in the CompanyName field, and the name of the primary contact at the company is recorded in the ContactLastName and ContactFirstName fields.

A. Create the database and name your database QACS. 

Import the tables described in the Queen Ann Database Google sheet into your QACS database. Note there is a worksheet for each table. Each worksheet contain the data, along with the field type and size specifications. Start by creating a .csv file or tab delimited spreadsheet of just the data for each table. Then import each file into your QACS database using phpMyAdmin. (See the video.) Next, modify each field parameter so it matches the data field and type specifications.

B. Add the referential integrity (foreign-key) constraints described above (see video).

C. Then, add a seventh table: ZIPCODE (ZIP, City, State) (see video)

Populate the ZIPCODE table from the ZIP, City and State fields from CUSTOMER and VENDOR. Make the ZIP field in CUSTOMER and VENDOR a foreign key linking to the ZIP field in ZIPCODE for the City and State. Delete the City and State fields from CUSTOMER and VENDOR. Make sure to add the referential integrity constraint that any ZIP in CUSTOMER or VENDOR must exist in ZIPCODE and that a change or correction in a ZIP field in ZIPCODE is cascaded. (1)

When you are finished, make sure you imported the database correctly using the grading assistant indicated above. Make sure you leave the correct database as is throughout the semester to insure you get full credit at the end when the grades are recomputed.