The Queen Anne Curiosity Shop is an upscale home furnishings store in a well-to-do urban neighborhood. It sells both antiques and current-production household items that complement or are useful with the antiques.
data mining
Description
The Queen Anne Curiosity
Shop Case Study
Chapter 2
v ANSWERS TO THE QUEEN ANNE CURIOSITY SHOP PROJECT
QUESTIONS
The Queen Anne Curiosity Shop is an upscale home
furnishings store in a well-to-do urban neighborhood. It sells both antiques
and current-production household items that complement or are useful with the
antiques. For example, the store sells antique dining room tables and new
tablecloths. The antiques are purchased from both individuals and wholesalers,
and the new items are purchased from distributors. The store’s customers
include individuals, owners of bed-and-breakfast operations, and local interior
designers who work with both individuals and small businesses. The antiques are
unique, though some multiple items, such as dining room chairs, may be
available as a set (sets are never broken). The new items are not unique, and
an item may be reordered if it is out of stock. New items are also available in
various sizes and colors (for example, a particular style of tablecloth may be available
in several sizes and in a variety of colors).
Assume that The Queen Anne Curiosity Shop designs a
database with the following tables:
CUSTOMER (CustomerID,
LastName, FirstName, Address, City, State, ZIP, Phone,
Email)
ITEM (ItemID,
ItemDescription, CompanyName, PurchaseDate, ItemCost,
ItemPrice)
SALE (SaleID,
CustomerID, SaleDate, SubTotal, Tax,
Total)
SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice)
The referential integrity constraints are:
CustomerID in
SALE must exist in CustomerID in CUSTOMER
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, ItemID of ITEM,
SaleID of SALE, and SaleItemID of SALE_ITEM are all surrogate keys with values
as follows:
CustomerID Start
at 1 Increment by 1
ItemID Start
at 1 Increment by 1
SaleID Start
at 1 Increment by 1
The database that The Queen Anne Curiosity Shop has
created is named QACS, and the four tables in the QACS database schema are
shown in Figure 2-53.
