Assume that The Queen Anne Curiosity Shop designs a database with the following
ID: 3915239 • Letter: A
Question
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 *** */
Rubric
Explanation / Answer
Since sample figures are not given in the question, solution is provided based on the common practices -
Chapter 7 Part A
CUSTOMER (CustomerID NOT NULL, LastName NOT NULL, FirstName NOT NULL, Address, City, State, ZIP, Phone NOT NULL, Email)
Alternate Key - LastName, FirstName, Phone.
EMPLOYEE (EmployeeID NOT NULL, LastName NOT NULL, FirstName NOT NULL, Phone NOT NULL, Email)
Alternate Key - LastName, FirstName, Phone.
VENDOR (VendorID NOT NULL, CompanyName, ContactLastName NOT NULL, ContactFirstName NOT NULL, Address, City, State, ZIP, Phone, Fax, Email)
ITEM (ItemID NOT NULL, ItemDescription, PurchaseDate NOT NULL, ItemCost NOT NULL, ItemPrice NOT NULL, VendorID)
SALE (SaleID NOT NULL, CustomerID , EmployeeID, SaleDate NOT NULL, SubTotal NOT NULL, Tax NOT NULL, Total NOT NULL)
SALE_ITEM (SaleID NOT NULL, SaleItemID NOT NULL, ItemID NOT NULL, ItemPrice NOT NULL)
Chapter 7 Part B - Foreign Keys Colmns in each table.
CUSTOMER - No foreign keys
EMPLOYEE - No foreign keys
VENDOR - No foreign keys
ITEM - Vendor Id. One to many relationship.
SALE - CustomerID , EmployeeID. Many to many relationship.
SALE_ITEM - SaleID, ItemID.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.