Unit $2$ Assignment #$2$: THE QUEEN ANNE CURIOSITY SHOP PROJECT

Due Apr $22$ by $11$:$59$pm Points $45$ Submitting a file upload File Types sql$,$ docx, doc, and pdf Available until Apr $22$ at $11$:$59$pm

$9$

Q Assignment Instructions

Read carefully and follow all steps. When finished with your assignment, submit two separate files:

The SQL script file. $($a sql file$)$

A "screenshot" file showing the results of the SQL script. $($a Word or PDF file$)$

You can check this website for some assistance and guidance: Saving SQL Scripts

Add a comment to name your queries according to the question number. Begin the comment with $--($two hyphens$)$

Example:

$--$ Query Number

SELECT column$1,$ column$2$

FROM table$\_$name;

To view the results of a specific query, select the query you want to execute and then click on the execute button. This will show the output related to the selected query.

Capture a screenshot of the entire DB browser window, including the query and its output.

Combine the screenshots by copying and pasting them into a single Word document file. Afterward, submit the file in either Word or PDF format.

THE QUEEN ANNE CURIOSITY SHOP PROJECT

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 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, EmailAddress, EncryptedPassword, Address, City, State, ZIP, Phone, ReferredBy$)$

ITEM $($ItemID$,$ ItemDescription, CompanyName, PurchaseDate, ItemCost, ItemPrice$)$

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

SALE$\_$ITEM $($SaleID$,$ SaleltemID, ItemID, ItemPrice$)$

The referential integrity constraints are:

ReferredBy in CUSTOMER must exist in CustomerID in CUSTOMER

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 SaleltemID of SALE$\_$ITEM are all surrogate kevs with values as follows:

Assume that CustomerID of CUSTOMER, ItemID of IIEM, SaleID of SALE, and SaleltemID of SALE$\_$IIEM 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-54.$ Note that CUTOMER contains a recursive relationship between

$(9)$ ReferredBy and CustomerID, where ReferredBy contains the CustomerID value of the existing customer who referred the new customer to the Queen Anne Curiosity Shop.

Figure $2-54-$ The QACS Database

The column characteristics for the tables are shown in Figures $2-55,2-56,2-57,$ and $2-58.$ The relationships CUSTOMER$-$to$-$SALE and ITEM$-$to$-$SALE$\_$ITEM should enforce referential integrity, but not cascade updates or deletions, whereas the relationship between SALE and SALE$\_$ITEM should enforce referential integrity and

cascade both updates and deletions.

CUSTOMER

$\backslash$table$[[$Column Name,Type,Key,Required,Remarks$],[$CustomerID$,$Integer,Primary Key,Yes,Surrogate Key$],[$LastName$,$Character $(25),$No$,$Yes,$],[$FirstName$,$Character $(25),$No$,$Yes,$],[$EmailAddress$,$Character $(100),$No$,$No$,$Use Varchar$],[$EncryptedPassword$,$Character$(50),$No$,$No$,$Use Varchar$],[$Address$,$Character $(35),$No$,$No$,],[$City$,$Character $(35),$No$,$No$,],[$State$,$Character $(2),$No$,$No$,],[$ZIP$,$Character $(10),$No$,$No$,],[$Phone$,$Character $(12),$No$,$Yes,$],[$ReferredBy$,$Integer,Forelgn Key,No$,$REF: CustomerID$]]$

Figure $2-55-$ Column Characteristics for the QACS Database CUSTOMER Table

SALE

$\backslash$table$[[$Column Name,Type,Key,Required,Remarks$],[$SaleID$,$Integer,Primary Key,Yes,Surrogate Key$],[$CustomerID$,$Integer,Forelgn Key,Yes,REF: CUSTOMER$],[$SaleDate$,$Date,No$,$Yes,$],[$SubTotal$,$Number $(15,2),$No$,$No$,],[$Tax$,$Number $(15,2),$No$,$No$,],[$Total$,$Number $(15,2),$No$,$No$,]]$

Figure $2-56-$ Column Characteristics for the QACS Database SALE Table

SALE$\_$ITEM

$\backslash$table$[[$Column Name,Type,Key,Required,Remarks$],[$SaleID$,$Integer,$\backslash$table$[[$Primary Key,$],[$Foreign Key$]],$Yes,REF: SALE$],[$SaleltemI