Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 106

The Queen Anne Curiosity Shop is an upscale home furnishings store in a well-to-do urban neigh-
borhood. 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 table-
cloths. 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 busi-
nesses. 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-41.
The column characteristics for the tables are shown in Figures 2-42, 2-43, 2-44, and 2-45.
The relationships CUSTOMER-to-SALE and ITEM-to-SALE_ITEM should enforce referential
integrity, but not cascade updates nor deletions, while the relationship between SALE and
SALE_ITEM should enforce referential integrity and cascade both updates and deletions. The
data for these tables are shown in Figures 2-46, 2-47, 2-48, and 2-49.

Figure 2-41
The QACS Database

The ITEM table

The CUSTOMER table

The SALE table

The SALE_ITEM table