Solution Manual TO ACCOMPANY Database Processing Fundamentals, Design, and Implementation 13th Edition CHAPTER SEVEN SQL FOR DATABASE CONSTRUCTION AND APPLICATION PROCESSING

Solution Manual TO ACCOMPANY Database Processing Fundamentals, Design, and Implementation 13th Edition CHAPTER SEVEN SQL FOR DATABASE CONSTRUCTION AND APPLICATION PROCESSING Prepared By David J. Auer Western Washington University DAVID M. KROENKE AND DAVID J. AUER ScholarStock Chapter Seven – SQL For Database Construction and Application Processing  CHAPTER OBJECTIVES  To be able to create and manage table structures using SQL statements  To understand how referential integrity actions are implemented in SQL statements  To be able to create and use SQL constraints  To understand several uses for SQL views  To be able to use SQL statements to create and use views  To gain an understanding of how SQL is used in an application program  To understand how to create and use triggers  To understand how to create and use stored procedures  ERRATA  Page 346. [6-NOV-2013 – Corrected in the Solution Manual solutions and associated files] Continued Figure 7-41 in the Heather Sweeney Designs Case Questions contains an error. The third line of the L_I_INVOICE_FK constraint should read ON UPDATE NO ACTION instead of ON IPDATE NO ACTION. The corrected continued figure should appear as: Page 7-3 ScholarStock Chapter Seven – SQL For Database Construction and Application Processing   Page 353. [15-JUN-2013 – Corrected in the Solution Manual solutions and associated files] Heather Sweeney Designs Case Question R contains an extraneous table and column reference. The question should read: R. Write an SQL statement to create a view called InvoiceSummaryView that contains INVOICE.InvoiceNumber, INVOICE.InvoiceDate, LINE_ITEM.LineNumber, SALE_ITEM.ItemID, PRODUCT.Description, and LINE_ITEM.UnitPrice. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.  Page 355. [01-NOV-2013 – Corrected in the Solution Manual solutions and associated files] The Queen Anne Curiosity Shop Project Questions sample data in Figure 7-43 has an error in the email address for Donna Anderson (CustomerID 6). The correct email address is:  Page 356. [15-JUN-2013 – Corrected in the Solution Manual solutions and associated files] The Queen Anne Curiosity Shop Project Question P should read: P. Write an SQL statement to create a view called CustomerSaleHistoryView that (1) includes all columns of CustomerSaleSummaryView except SALE_ITEM.SaleItemID, ITEM.ItemID, and SALE_ITEM.ItemDescription; (2) groups orders by SALE.SaleID, Page 7-4 ScholarStock Chapter Seven – SQL For Database Construction and Application Processing CUSTOMER.LastName, and CUSTOMER.FirstName, and in that order; and (3) sums and averages SALE_ITEM.ItemPrice for each order for each customer. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.  Page 356. [5-NOV-2013 – Corrected in the Solution Manual solutions and associated files] The Queen Anne Curiosity Shop Project Question Q should read: Q. Write an SQL statement to create a view called CustomerSaleCheckView that uses CustomerSaleHistoryView and that shows that any customers for whom CustomerSaleHistoryView.SumItemPrice is not equal to SALE.SubTotal. Run the statement to create the view, and then test the view with an appropriate SQL SELECT statement.  Page 7-5 ScholarStock Chapter Seven – SQL For Database Construction and Application Processing  Page 358. [15-OCT-2013 – Corrected in the Solution Manual solutions and associated files] The Morgan Importing Project Questions sample data in Figure 7-46 has an error in the subtotal for the sale with SaleID 2. The correct subtotal amount is: $1000.00  TEACHING SUGGESTIONS  If your students have been using Access, now is really the time to switch to SQL Server 2012, Oracle Database 11g Release 2, or MySQL 5.6. Refer your students to the beginning of Chapter 10 for SQL Server 2012, Online Chapter 10B for Oracle Database 11g Release 2, and Online Chapter 10C for MySQL 5.6 instructions.  The SQL examples shown in Chapter 7 and the questions in the end of chapter material work the best with the Transact-SQL (T-SQL) used in Microsoft SQL Server 2012. If your students are using Microsoft SQL Server 2012, they should be able to create the tables, populate the tables and run the other SQL commands with little trouble. The SQL used in Microsoft Access 2013, Oracle Database 11g Release 2 (PL/SQL), and MySQL 5.6 varies in their ability to support all the SQL commands used here. Oracle Database and MySQL do a better job of supporting standard SQL, while Microsoft Access has significant variations from the standard. In the answers to the end of chapter questions I have often shown the solution using two or more of the three DBMSs. Otherwise, I primarily use SQL Server 2012. If your students are using Microsoft Access 2013, Oracle Database 11g Release 2, or MySQL 5.6 check the solutions to the questions before you assign them so that you can tell your students what to watch out for!  As discussed in the IM Chapter 2 suggestions, there is a useful teaching technique that will allow you to demonstrate the SQL queries in the text using MS SQL Server if you have it available.  Create a new SQL Server database named Cape-Codd.  Use the SQL statements in the *.sql text file DBP-e13-MSSQL-Cape-CoddCreate-T to create the RETAIL_ORDER, ORDER_ITEM and SKU_DATA tables (the WAREHOUSE and INVENTORY tables, used in the Chapter 2 Review Questions, are also created).  Use the SQL statements in the *.sql text file DBP-e13-MSSQL-Cape-CoddInsert-D to populate the RETAIL_ORDER, ORDER_ITEM and SKU_DATA tables (the WAREHOUSE and INVENTORY tables, used in the Chapter 2 Review Questions, are also populated).  Open the Microsoft SQL Server Management Studio and select the CapeCodd database.  In the Microsoft SQL Server Management Studio, open the *.sql text file DBP-e13-MSSQL-Cape-Codd-Query-Set-CH. This file contains all the queries shown in the Chapter 2 text.  Highlight the query you want to run, and then click the Execute Query button to display the results of the q