This case study assignment is based on (but not same as) the Queen Anne Curiosit

Question

This case study assignment is based on (but not same as) the Queen Anne Curiosity Shop (QACS) Case Questions at the end of Chapters 7 and 8. Since there are important differences between the questions below and the questions on the textbook, please answer the questions on this document. Please prepare your answers in Microsoft Word and submit your case study in pdf format. For the questions that specifically ask for sql files, please follow the instructions described in the questions.

Case Description

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:

CustomerID in SALE must exist in CustomerID in CUSTOMER

VendorID in ITEM must exist in VendorID in VENDOR

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

Assume that CustomerID of CUSTOMER, EmployeeID of EMPLOYEE, ItemID of ITEM, SaleID of SALE, and VendorID of VENDOR are all surrogate keys with values as follows:

CustomerID Start at 1 Increment by 1

EmployeeID Start at 1 Increment by 1

VendorID Start at 1 Increment by 1

ItemID Start at 1 Increment by 1

SaleID Start at 1 Increment by 1

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.

Chapter 7 Part A: Specify NULL/NOT NULL constraints for each table column and indicate alternate keys, if any. You can show this in a table similar to Figures 7-4 and 7-5 (without the type column).

Chapter 7 Part B: State relationships as implied by foreign keys and specify the maximum and minimum cardinality of each relationship. You can show this in a table similar to Figure 7-6.

Chapter 7 Part D: Write an UPDATE statement for QACS_CH07 database to change values of ITEM.ItemDescription from "Desk Lamp" to "Desk Lamps".

Explanation / Answer

PART A: Figures 7-4 and 7-5 are not given so SALE table is used.

UNIQUE keyword is used to define an alternate key. It indicates that value must remain unique.
create table SALE(SaleID integer not null unique,CustomerID integer not null unique, EmployeeID integer not null, SaleDate date, SubTotal integer, Tax integer, Total integer)

PART B:When the primary key of a table is used as a foreign key in another table, a relationship is established between the two.
CUSTOMER table has primary key customer id which is foreign key in SALE table.
It has many to one relationship. Hence maximum cardinality is many and minimum cardinality is one.
ITEM table has primary key ItemId and SALE_ITEM table has foreign key itemid.
It has one to one relationship. Hence maximum cardinality is one and minimum cardinality is one.
VENDOR table has primary key vendorid and ITEM table has vendorid as foreign key. It has one to many relationship. Hence maximum cardinality is many and minimum cardinality is one.

PART D: QACS_CH07 database is not given so update query is used on ITEM table.

Syntax:

update ITEM set ItemDescription = "Desk Lamps" where ItemDescription = "Desk Lamp"

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.