This case study assignment is based on (but not same as) the Queen Anne Curiosit
ID: 3914084 • Letter: T
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.
CREATE TABLE CUSTOMER(
CustomerID Int NOT NULL IDENTITY (1, 1),
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)
);
CREATE TABLE EMPLOYEE(
EmployeeID Int NOT NULL IDENTITY (1, 1),
LastName Char(25) NOT NULL,
FirstName Char(25) NOT NULL,
Phone Char(12) NULL,
Email VarChar(100) NOT NULL UNIQUE,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EmployeeID)
);
CREATE TABLE VENDOR(
VendorID Int NOT NULL IDENTITY (1, 1),
CompanyName Char(100) NULL,
ContactLastName Char(25) NOT NULL,
ContactFirstName 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,
Fax Char(12) NULL,
Email VarChar(100) NULL,
CONSTRAINT VENDOR_PK PRIMARY KEY(VendorID)
);
CREATE TABLE ITEM(
ItemID Int NOT NULL IDENTITY (1,1),
ItemDescription VarChar(255) NOT NULL,
PurchaseDate Date NOT NULL,
ItemCost Numeric(9,2) NOT NULL,
ItemPrice Numeric(9,2) NOT NULL,
VendorID Int NOT NULL,
CONSTRAINT ITEM_PK PRIMARY KEY (ItemID),
CONSTRAINT ITEM_VENDOR_FK FOREIGN KEY (VendorID)
REFERENCES VENDOR(VendorID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
CREATE TABLE SALE(
SaleID Int NOT NULL IDENTITY (1, 1),
CustomerID Int NOT NULL,
EmployeeID Int NOT NULL,
SaleDate Date NOT NULL,
SubTotal Numeric(15,2) NULL,
Tax Numeric(15,2) NULL,
Total Numeric(15,2) NULL,
CONSTRAINT SALE_PK PRIMARY KEY (SaleID),
CONSTRAINT SALE_CUSTOMER_FK FOREIGN KEY (CustomerID)
REFERENCES CUSTOMER(CustomerID)
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT SALE_EMPLOYEE_FK FOREIGN KEY(EmployeeID)
REFERENCES EMPLOYEE(EmployeeID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
CREATE TABLE SALE_ITEM(
SaleID Int NOT NULL,
SaleItemID Int NOT NULL,
ItemID Int NOT NULL,
ItemPrice Numeric(9,2) NOT NULL,
CONSTRAINT SALE_ITEM_PK PRIMARY KEY (SaleID, SaleItemID),
CONSTRAINT SALE_ITEM_SALE_FK FOREIGN KEY (SaleID)
REFERENCES SALE(SaleID)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT SALE_ITEM_ITEM_FK FOREIGN KEY (ItemID)
REFERENCES ITEM(ItemID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
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.
/***** CUSTOMER DATA ********************************************************/
INSERT INTO CUSTOMER VALUES(
'Shire', 'Robert', '6225 Evanston Ave N', 'Seattle', 'WA', '98103',
'206-524-2433', 'Robert.Shire@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Goodyear', 'Katherine', '7335 11th Ave NE', 'Seattle', 'WA', '98105',
'206-524-3544', 'Katherine.Goodyear@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Bancroft', 'Chris', '12605 NE 6th Street', 'Bellevue', 'WA', '98005',
'425-635-9788', 'Chris.Bancroft@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Griffith', 'John', '335 Aloha Street', 'Seattle', 'WA', '98109',
'206-524-4655', 'John.Griffith@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Tierney', 'Doris', '14510 NE 4th Street', 'Bellevue', 'WA', '98005',
'425-635-8677', 'Doris.Tierney@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Anderson', 'Donna', '1410 Hillcrest Parkway', 'Mt. Vernon', 'WA', '98273',
'360-538-7566', 'Donna.Anderson@elsewhere.com');
INSERT INTO CUSTOMER VALUES(
'Svane', 'Jack', '3211 42nd Street', 'Seattle', 'WA', '98115',
'206-524-5766', 'Jack.Svane@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Walsh', 'Denesha', '6712 24th Avenue NE', 'Redmond', 'WA', '98053',
'425-635-7566', 'Denesha.Walsh@somewhere.com');
INSERT INTO CUSTOMER VALUES(
'Enquist', 'Craig', '534 15th Street', 'Bellingham', 'WA', '98225',
'360-538-6455', 'Craig.Enquist@elsewhere.com');
INSERT INTO CUSTOMER VALUES(
'Anderson', 'Rose', '6823 17th Ave NE', 'Seattle', 'WA', '98105',
'206-524-6877', 'Rose.Anderson@elsewhere.com');
/***** EMPLOYEE DATA ********************************************************/
INSERT INTO EMPLOYEE VALUES(
'Stuart', 'Anne', '206-527-0010', 'Anne.Stuart@QACS.com');
INSERT INTO EMPLOYEE VALUES(
'Stuart', 'George', '206-527-0011', 'George.Stuart@QACS.com');
INSERT INTO EMPLOYEE VALUES(
'Stuart', 'Mary', '206-527-0012', 'Mary.Stuart@QACS.com');
INSERT INTO EMPLOYEE VALUES(
'Orange', 'William', '206-527-0013', 'William.Orange@QACS.com');
INSERT INTO EMPLOYEE VALUES(
'Griffith', 'John', '206-527-0014', 'John.Griffith@QACS.com');
/***** VENDOR DATA **********************************************************/
INSERT INTO VENDOR VALUES(
'Linens and Things', 'Huntington', 'Anne', '1515 NW Market Street',
'Seattle', 'WA', '98107', '206-325-6755', '206-329-9675', 'LAT@business.com');
INSERT INTO VENDOR VALUES(
'European Specialties', 'Tadema', 'Ken', '6123 15th Avenue NW',
'Seattle', 'WA', '98107', '206-325-7866', '206-329-9786', 'ES@business.com');
INSERT INTO VENDOR VALUES(
'Lamps and Lighting', 'Swanson', 'Sally', '506 Prospect Street',
'Seattle', 'WA', '98109', '206-325-8977', '206-329-9897', 'LAL@business.com');
INSERT INTO VENDOR
(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Email)
VALUES(
'Lee', 'Andrew', '1102 3rd Street',
'Kirkland', 'WA', '98033', '425-746-5433', 'Andrew.Lee@somewhere.com');
INSERT INTO VENDOR
(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Email)
VALUES(
'Harrison', 'Denise', '533 10th Avenue',
'Kirkland', 'WA', '98033', '425-746-4322', 'Denise.Harrison@somewhere.com');
INSERT INTO VENDOR VALUES(
'New York Brokerage', 'Smith', 'Mark', '621 Roy Street',
'Seattle', 'WA', '98109', '206-325-9088', '206-329-9908', 'NYB@business.com');
INSERT INTO VENDOR
(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Email)
VALUES(
'Walsh', 'Denesha', '6712 24th Avenue NE',
'Redmond', 'WA', '98053', '425-635-7566', 'Denesha.Walsh@somewhere.com');
INSERT INTO VENDOR
(ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, Email)
VALUES(
'Bancroft', 'Chris', '12605 NE 6th Street',
'Bellevue', 'WA', '98005', '425-635-9788', '425-639-9978', 'Chris.Bancroft@somewhere.com');
INSERT INTO VENDOR VALUES(
'Specialty Antiques', 'Nelson', 'Fred', '2512 Lucky Street',
'San Francisco', 'CA', '94110', '415-422-2121', '415-429-9212', 'SA@business.com');
INSERT INTO VENDOR VALUES(
'General Antiques', 'Garner', 'Patty', '2515 Lucky Street',
'San Francisco', 'CA', '94110', '415-422-3232', '415-429-9323', 'GA@business.com');
/***** ITEM DATA ************************************************************/
INSERT INTO ITEM VALUES(
'Antique Desk', '07-Nov-12', 1800.00, 3000.00, 2);
INSERT INTO ITEM VALUES(
'Antique Desk Chair', '10-Nov-12', 300.00, 500.00, 4);
INSERT INTO ITEM VALUES(
'Dining Table Linens', '14-Nov-12', 600.00, 1000.00, 1);
INSERT INTO ITEM VALUES(
'Candles', '14-Nov-12', 30.00, 50.00, 1);
INSERT INTO ITEM VALUES(
'Candles', '14-Nov-12', 27.00, 45.00, 1);
INSERT INTO ITEM VALUES(
'Desk Lamp', '14-Nov-12', 150.00, 250.00, 3);
INSERT INTO ITEM VALUES(
'Dining Table Linens', '14-Nov-12', 450.00, 750.00, 1);
INSERT INTO ITEM VALUES(
'Book Shelf', '21-Nov-12', 150.00, 250.00, 5);
INSERT INTO ITEM VALUES(
'Antique Chair', '21-Nov-12', 750.00, 1250.00, 6);
INSERT INTO ITEM VALUES(
'Antique Chair', '21-Nov-12', 1050.00, 1750.00, 6);
INSERT INTO ITEM VALUES(
'Antique Candle Holders', '28-Nov-12', 210.00, 350.00, 2);
INSERT INTO ITEM VALUES(
'Antique Desk', '05-Jan-13', 1920.00, 3200.00, 2);
INSERT INTO ITEM VALUES(
'Antique Desk', '05-Jan-13', 2100.00, 3500.00, 2);
INSERT INTO ITEM VALUES(
'Antique Desk Chair', '06-Jan-13', 285.00, 475.00, 9);
INSERT INTO ITEM VALUES(
'Antique Desk Chair', '06-Jan-13', 339.00, 565.00, 9);
INSERT INTO ITEM VALUES(
'Desk Lamp', '06-Jan-13', 150.00, 250.00, 10);
INSERT INTO ITEM VALUES(
'Desk Lamp', '06-Jan-13', 150.00, 250.00, 10);
INSERT INTO ITEM VALUES(
'Desk Lamp', '06-Jan-13', 144.00, 240.00, 3);
INSERT INTO ITEM VALUES(
'Antique Dining Table', '10-Jan-13', 3000.00, 5000.00, 7);
INSERT INTO ITEM VALUES(
'Antique Sideboard', '11-Jan-13', 2700.00, 4500.00, 8);
INSERT INTO ITEM VALUES(
'Dining Table Chairs', '11-Jan-13', 5100.00, 8500.00, 9);
INSERT INTO ITEM VALUES(
'Dining Table Linens', '12-Jan-13', 450.00, 750.00, 1);
INSERT INTO ITEM VALUES(
'Dining Table Linens', '12-Jan-13', 480.00, 800.00, 1);
INSERT INTO ITEM VALUES(
'Candles', '17-Jan-13', 30.00, 50.00, 1);
INSERT INTO ITEM VALUES(
'Candles', '17-Jan-13', 36.00, 60.00, 1);
/***** SALE DATA ************************************************************/
INSERT INTO SALE VALUES(1, 1, '14-Dec-12', 3500.00, 290.50, 3790.50);
INSERT INTO SALE VALUES(2, 2, '15-Dec-12', 1000.00, 83.00, 1083.00);
INSERT INTO SALE VALUES(3, 1, '15-Dec-12', 50.00, 4.15, 54.15);
INSERT INTO SALE VALUES(4, 3, '23-Dec-12', 45.00, 3.74, 48.74);
INSERT INTO SALE VALUES(1, 5, '05-Jan-13', 250.00, 20.75, 270.75);
INSERT INTO SALE VALUES(5, 5, '10-Jan-13', 750.00, 62.25, 812.25);
INSERT INTO SALE VALUES(6, 4, '12-Jan-13', 250.00, 20.75, 270.75);
INSERT INTO SALE VALUES(2, 1, '15-Jan-13', 3000.00, 249.00, 3249.00);
INSERT INTO SALE VALUES(5, 5, '25-Jan-13', 350.00, 29.05, 379.05);
INSERT INTO SALE VALUES(7, 1, '04-Feb-13', 14250.00, 1182.75, 15432.75);
INSERT INTO SALE VALUES(8, 5, '04-Feb-13', 250.00, 20.75, 270.75);
INSERT INTO SALE VALUES(5, 4, '07-Feb-13', 50.00, 4.15, 54.15);
INSERT INTO SALE VALUES(9, 2, '07-Feb-13', 4500.00, 373.50, 4873.50);
INSERT INTO SALE VALUES(10, 3, '11-Feb-13', 3675.00, 305.03, 3980.03);
INSERT INTO SALE VALUES(2, 2, '11-Feb-13', 800.00, 66.40, 866.40);
/***** SALE_ITEM DATA *******************************************************/
INSERT INTO SALE_ITEM VALUES(1, 1, 1, 3000.00);
INSERT INTO SALE_ITEM VALUES(1, 2, 2, 500.00);
INSERT INTO SALE_ITEM VALUES(2, 1, 3, 1000.00);
INSERT INTO SALE_ITEM VALUES(3, 1, 4, 50.00);
INSERT INTO SALE_ITEM VALUES(4, 1, 5, 45.00);
INSERT INTO SALE_ITEM VALUES(5, 1, 6, 250.00);
INSERT INTO SALE_ITEM VALUES(6, 1, 7, 750.00);
INSERT INTO SALE_ITEM VALUES(7, 1, 8, 250.00);
INSERT INTO SALE_ITEM VALUES(8, 1, 9, 1250.00);
INSERT INTO SALE_ITEM VALUES(8, 2, 10, 1750.00);
INSERT INTO SALE_ITEM VALUES(9, 1, 11, 350.00);
INSERT INTO SALE_ITEM VALUES(10, 1, 19, 5000.00);
INSERT INTO SALE_ITEM VALUES(10, 2, 21, 8500.00);
INSERT INTO SALE_ITEM VALUES(10, 3, 22, 750.00);
INSERT INTO SALE_ITEM VALUES(11, 1, 17, 250.00);
INSERT INTO SALE_ITEM VALUES(12, 1, 24, 50.00);
INSERT INTO SALE_ITEM VALUES(13, 1, 20, 4500.00);
INSERT INTO SALE_ITEM VALUES(14, 1, 12, 3200.00);
INSERT INTO SALE_ITEM VALUES(14, 2, 14, 475.00);
INSERT INTO SALE_ITEM VALUES(15, 1, 23, 800.00);
/********************************************************************************/
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 *** */
Explanation / Answer
There are multiple parts in the question, answering first 4 of them.
-- Chapter 7 Part D
Update ITEM Set ItemDescription = 'Desk Lamps' Where ItemDescription = 'Desk Lamp'
This query updates all ItemDescription from "Desk Lamp" to "Desk Lamps".
-- Chapter 7 Part E
execute entire script in one go in this part, as it involves a variable to hold SaleId.
Using Scope_Identity() function which returns the last identity value inserted into an identity column in the same scope.
Insert Into SALE (CustomerID,EmployeeID,SaleDate,SubTotal,Tax,Total)
Values (2, 2, '17-Feb-13', 80.00, 9.96, 89.96)
Declare @SaleId int
Select @SaleId = SCOPE_IDENTITY()
Insert Into SALE_ITEM (SaleID,SaleItemID,ItemID,ItemPrice)
Values (@SaleId, 1, 4, 50.00)
Insert Into SALE_ITEM (SaleID,SaleItemID,ItemID,ItemPrice)
Values (@SaleId, 2, 24, 30.00)
Delete From SALE Where SaleID = @SaleId
SALE_ITEM table refers SALE table (Foreign Key : SALE_ITEM_SALE_FK) with option CASCADE DELETE
Hence we do not need to delete SALE_ITEM table record, as it will be deleted automatically.
-- Chapter 7 Part F
IF OBJECT_ID (N'dbo.FullNameFunction', N'FN') IS NOT NULL
DROP FUNCTION dbo.FullNameFunction;
GO
CREATE FUNCTION dbo.FullNameFunction(@FirstName varchar(32), @LastName varchar(32))
RETURNS varchar(65)
AS
BEGIN
DECLARE @FullName varchar(65);
Set @FullName = RTRIM(LTRIM(@FirstName)) + ' ' + RTRIM(LTRIM(@LastName))
RETURN @FullName;
END;
-- Chapter 7 Part G
IF OBJECT_ID (N'dbo.CustomerFullNameSaleHistoryView', N'V') IS NOT NULL
DROP VIEW dbo.CustomerFullNameSaleHistoryView;
GO
CREATE VIEW dbo.CustomerFullNameSaleHistoryView
AS
Select s.SaleID, s.SaleDate,
dbo.FullNameFunction(c.FirstName, c.LastName) 'FullName',
SUM(i.ItemPrice)'SumItemPrice',
AVG(i.ItemPrice)'AveItemPrice'
From SALE s INNER JOIN CUSTOMER c
on s.CustomerID = c.CustomerID
INNER JOIN SALE_ITEM i
on s.SaleID = i.SaleID
Group By s.SaleID, s.SaleDate, dbo.FullNameFunction(c.FirstName, c.LastName)
Select * From dbo.CustomerFullNameSaleHistoryView
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.