Assume that The Queen Anne Curiosity Shop designs a database with the following tables:...

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:

VendorID in ITEM must exist in VendorID in VENDOR

CustomerID in SALE must exist in CustomerID in CUSTOMER

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 SaleItemID of SALE_ITEM are all surrogate keys with values as follows:

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.

Sample data are shown in Figures 3-35, 3-36, 3-37, 3-38, 3-39, and 3-40. These tables, referential integrity constraints, and data are used as the basis for the SQL statements you will create in the exercises that follow. If possible, run these statements in an actual DBMS, as appropriate, to obtain your results. Name your database QACS.

Use data types consistent with the DBMS you are using. If you are not using an actual DBMS, consistently represent data types using either the SQL Server, Oracle Database, or MySQL data types shown in Figure 3-5. For each SQL statement you write, show the results based on your data.

Write SQL statements and answer questions for this database as follows:

A. Write SQL CREATE TABLE statements for each of these tables.

B. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading deletions and justify those assumptions. (Hint: You can combine the SQL for your answers to parts A and B.)

C. Write SQL statements to insert the data into each of these tables. Assume that all surrogate key column values will be supplied by the DBMS. Use the data in Figures 3-35, 3-36, 3-37, 3-38, 3-39, and 3-40.

D. Write SQL statements to list all columns for all tables.

E. Write an SQL statement to list ItemID and ItemDescription for all items that cost $1000 or more.

F. Write an SQL statement to list ItemNumber and Description for all items that cost $1000 or more and were purchased from a vendor whose CompanyName starts with the letters New.

G. Write an SQL statement to list LastName, FirstName, and Phone of the customer who made the purchase with SaleID 1. Use a subquery.

H. Answer part G but use a join using JOIN ON syntax.

I. Write an SQL statement to list LastName, FirstName, and Phone of the customers who made the purchase with SaleIDs 1, 2, and 3. Use a subquery.

J. Answer part I but use a join using JOIN ON syntax.

K. Write an SQL statement to list LastName, FirstName, and Phone of customers who have made at least one purchase with SubTotal greater than $500. Use a subquery.

L. Answer part K but use a join using JOIN ON syntax.

M. Write an SQL statement to list LastName, FirstName, and Phone of customers who have purchased an item that has an ItemPrice of $500 or more. Use a subquery.

N. Answer part M but use a join using JOIN ON syntax.

O. Write an SQL statement to list LastName, FirstName, and Phone of customers who have purchased an item that was supplied by a vendor with a CompanyName that begins with the letter L. Use a subquery.

P. Write an SQL statement to show all customers and the items these customers have purchased. The output from this statement should include any items (if any) that have not been purchased by a customer. The SQL statement output should list CustomerID, LastName, FirstName, SaleID, SaleItemID, and ItemDescription. (Hint: Use JOIN ON syntax.)

Q. Write an SQL statement to show all customers and the items these customers have purchased. The output from this statement should include any customers (if any) that have not purchased any item. The SQL statement output should list CustomerID, LastName, FirstName, SaleID, SaleItemID, and ItemDescription. (Hint: Use JOIN ON syntax.)

R. Answer part O but use a join using JOIN ON syntax.

S. Write an SQL statement to show the sum of SubTotal for each customer. List CustomerID, LastName, FirstName, Phone, and the calculated result. Name the sum of SubTotal as SumOfSubTotal and sort the results by CustomerID, in descending order.

T. Write an SQL statement to modify the vendor with CompanyName of Linens and Things to Linens and Other Stuff.

U. Write SQL statements to switch the values of vendor CompanyName so that all rows currently having the value Linens and Things will have the value Lamps and Lighting and all rows currently having the value Lamps and Lighting will have the value Linens and Things.

V. Given your assumptions about cascading deletions in your answer to part B, write the fewest number of DELETE statements possible to remove all the data in your database but leave the table structures intact. Do not run these statements if you are using an actual database!