This case study assignment is based on (but not same as) the Queen Anne Curiosit

Question

This case study assignment is based on (but not same as) the Queen Anne Curiosity Shop (QACS) Case Questions at the end of Chapters 7 and 8. Since there are important differences between the questions below and the questions on the textbook, please answer the questions on this document. Please prepare your answers in Microsoft Word and submit your case study in pdf format. For the questions that specifically ask for sql files, please follow the instructions described in the questions.

Case Description

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.

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 *** */

Explanation / Answer

part D 7 . update QACS_CH07 set ITEM.itemdescription ='Desk Lamps'

chapter 7 part E.

insert into QACS_CH07 ( customerid int(5),employee int(5), sale date, subtotal float (40),tax float (50), total float (50)) values (2,2,'17-feb-13',80.00,9.96,89.96);

insert into QACS_CH07(sale item int, itemid int , item price float , sale itemid int, itemid int, item price float) values (1,4,50.00,2,24,30.00)

drop QACS_CH07

create view CUSTOMERFULLNAMESALEHISTORYVIEW AS select SALE.SaleID, SALE.SaleDate, customer. fullname from QACS_CH07 group by SALE.SaleID, SALE.SaleDate customer. FullName sum(ITEM.ItemPrice) and avg ITEM.ItemPrice for each order for each customer.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.