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 SALE must exist in CustomerID in CUSTOMER

VendorID in ITEM must exist in VendorID in VENDOR

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 VendorID of VENDOR 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.

Chapter 7 Part A: Specify NULL/NOT NULL constraints for each table column and indicate alternate keys, if any. You can show this in a table similar to Figures 7-4 and 7-5 (without the type column).

Chapter 7 Part B: State relationships as implied by foreign keys and specify the maximum and minimum cardinality of each relationship. You can show this in a table similar to Figure 7-6.

Chapter 7 Part C: Create a database named QACS_CH07 in SQL Server. Download this DDL file, open it in SQL Server Management Studio, and review its content to make sure it would create the tables described above with surrogate keys, appropriate primary key constraints, and appropriate foreign key constraints with appropriate UPDATE and DELETE behavior. Then, execute it to create tables for QACS_CH07 database.

Download this DML file, open it in SQL Server Management Studio, review its content, and insert data into the tables of QACS_CH07 database. Please notice a difference between some of the INSERT statements for the VENDOR table, where column names are specified and other INSERT statements for the same table, where column names are not specified. Please explain the reason for this difference in one sentence. Execute this script to insert data into the tables of QACS_CH07 database.

Important Note: Please prepare and submit a single SQL script file (named QACS_CH07_CaseStudy4_Answers.sql) prepared and saved in SQL Server Management Studio that includes your SQL statements that answer each of the following Chapter 7 questions in order. Each answer should start with a comment line that looks like the following: /* *** SQL-Statement-QACS_CH07-D *** */

Chapter 7 Part D: Write an UPDATE statement for QACS_CH07 database to change values of ITEM.ItemDescription from "Desk Lamp" to "Desk Lamps".

Chapter 7 Part E: Write INSERT statements to add new data records to QACS_CH07 database to record a sale (CustomerID: 2, EmployeeID: 2, SaleDate: '17-Feb-13', SubTotal: 80.00, Tax: 9.96, Total: 89.96) and the sale items (SaleItemID: 1, ItemID: 4, ItemPrice: 50.00 and SaleItemID: 2, ItemID: 24, ItemPrice: 30.00) for that sale. Then write a DELETE statement(s) to delete that sale and all of the items on that sale. Please pay attention to cascading delete for the relationship between SALE and SALE_ITEM.

Chapter 7 Part F: Write a SQL statement to create a user-defined function for QACS_CH07 database named FullNameFunction that combines two parameters named FirstName and LastName into a concatenated name field formatted FirstName LastName (including the space). Please use Figure 7-21 as a reference.

Chapter 7 Part G: Write a SQL statement to create a view for QACS_CH07 database called CustomerFullNameSaleHistoryView that (1) includes SALE.SaleID, SALE.SaleDate, customer full name (named as FullName) obtained by the FullNameFunction function using CUSTOMER.FirstName and CUSTOMER.LastName, and ITEM.ItemPrice (ItemPrice will not be included as is, but only as sum and average aggregates as described in (3)); (2) groups sales by SALE.SaleID, SALE.SaleDate, and then by FullName; and (3) sums and averages ITEM.ItemPrice for each order for each customer. As a result, this view should look like the following:

CustomerFullNameSaleHistoryView (SaleID, SaleDate, FullName, SumItemPrice, AveItemPrice)

Write a simple query to see the rows of this view to make sure the result is what you expected.

Chapter 8 Part A: Create a dependency graph that shows dependencies among the tables, the view, and the user-defined function of the QACS_CH07 database.

Chapter 8 Part B: Suppose that The Queen Anne Curiosity Shop owners decided to allow multiple customers per order (e.g., for customers spouses). Write blocks of SQL statements to modify the design of the tables of the QACS_CH07 database to accommodate this change. Please refer to "Changing a 1:N Relationship to an N:M Relationship" on page 440. Add your answer to the end of the QACS_CH07_CaseStudy4_Answers.sql script file, with the following comment line leading it: /* *** SQL-Statement-QACS_CH08-B *** */

Rubric