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: CustomerID in PURCHASE must exist in CustomerID in CUSTOMER 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: CustomerID Start at 1 Increment by 1 EmployeeID Start at 1 Increment by 1 VendorID Start at 1 Increment by 1 ItemID Start at 1 Increment by 1 SaleID Start at 1 Increment by 1 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. You need to create additional data to populate the tables. (Alternatively, your instructor may provide you with a data set.) 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. Write SQL statements and answer questions for this database as follows: A. Write SQL CREATE TABLE statements for each of these tables. Write foreign key constraints for the relationships in each of these tables. Make your own assumptions regarding cascading deletions and justify those assumptions. For Microsoft Access: The Microsoft Access AutoNumber data type supports a starting value of 1 and an increment of 1. All surrogate values specified in the original statement of the project will work in the Access versions of these tables. Create CustomerID, EmployeeID, VendorID, ItemID, and SaleID as NOT NULL, and then set the data type to AutoNumber in the GUI. IMPORTANT: Do this individually for each table immediately after it has been created! Microsoft Access SQL does not support the (m, n) extension of the Numeric data type. (See Does Not Work with Microsoft Access ANSI-89 SQL on p. 112.) Create all columns with a Numeric (m, n) data type as Numeric, and then set the appropriate column properties in the GUI. See example below: CREATE TABLE CUSTOMER( CustomerID Int NOT NULL, LastName Char(25) NOT NULL, FirstName Char(25) NOT NULL, Address Char(35) NULL, City Char(35) NULL, State Char(2) NULL, ZIP Char(10) NULL, Phone Char(12) NOT NULL, Email VarChar(100) NULL, CONSTRAINT CUSTOMER_PK PRIMARY KEY(CustomerID) ); B. Write SQL statements to insert at least three rows of data into each of these tables using the tables shown below. Assume that all surrogate key column values will be supplied by the DBMS. NOTE: If you are inserting data into MICROSOFT Access tables with an AutoNumber surrogate key. Example of MICROSOFT Access: INSERT INTO CUSTOMER (LastName, FirstName, Address, City, State, Zip, Phone, Email) VALUES( 'Shire', 'Robert', '6225 Evanston Ave N', 'Seattle', 'WA', '98103', '206-524-2433', 'RShire@somewhere.com'); CUSTOMER Table EMPLOYEE Table VENDOR Table ITEM Table SALE Table SALE_ITEM Table C. Write an SQL statement to list ItemID and ItemDescription for all items that cost $1000 or more. SELECT ItemID, ItemDescription FROM ITEM WHERE ItemPrice >= 1000; D. 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. Should result in this: E. Write an SQL statement to list LastName, FirstName, and Phone of the customer who made the purchase with SaleID 1. Use a subquery. Should result in this: F. 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. Should result in this: G. 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. Should result in this: H. 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. Should result in this: I. 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. Should result in this: