106 Part 1 Getting Started The Queen Anne Curiosity Shop 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 stores 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 (Customer. LastName, FirstName Address, City, State, ZIP, Phone, Email) ITEM (ItemID. ItemDescription. Company Name, Purchase Date, ItemCost, ItemPrice) SALE (Saleid. CustomerID, SaleDate, SubTotal, Tax, Total) SALE ITEM(Salern. SalettemID. Iem . ItemPrice) The referential integrity constraints are: Customer in SALE must exist in Customer in CUSTOMER Sale in SALE ITEM must exist in Sale in SALE ItemID in SALE ITEM must exist in ItemID in ITEM Assume that Customerid of CUSTOMER, ItemID of ITEM, Saleld of SALE, and SaleItemID of SALE ITEM are all surrogate keys with values as follows: CustomerID ItemID Start at Start at 1 Start at 1 Increment by 1 Increment by 1 Increment by 1 SaleID 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. N Figure 2-41 The QACS Database