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. AUERScholarStockChapter 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 ScholarStockChapter 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 ScholarStockChapter 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 ScholarStockChapter 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 query. An example of this is shown in the Page 7-6 ScholarStockChapter Seven – SQL For Database Construction and Application Processing following screenshot.  All of the *.sql text files needed to do this are available in the Instructor’s Resource Center on the text’s Web site (   The trick shown for SQL Server 2012 also works for Oracle Database using the Oracle SQL Developer and for MySQL 5.6 using the MySQL workbench.  Remind your students that Microsoft Access does not support all SQL constructs.  Tell your students that a check constraint that provides an enumerated list is often implemented with a table and a relationship. For example, the constraint CHECK (Rank IN (‘FR’, ‘SO’, ‘JR’, ‘SR’)) could be implemented by creating a Rank table and placing the list in that table. Now the Rank attribute becomes a foreign key and referential integrity enforces the constraint. Changing the list means adding and deleting from the RANK table.  The relationship of database applications and the DBMS is sometimes confusing. For a simple application using a personal DBMS such as Microsoft Access, the application and the DBMS are nearly indistinguishable. If an application has only a few forms and reports, and all of these are created using DBMS facilities, then the application and the DBMS are the same. On the other hand, for an organizational Page 7-7 ScholarStockChapter Seven – SQL For Database Construction and Application Processing database processed by say, Oracle Database, any application elements discussed in this chapter would be provided by application program code completely separate from the DBMS. It’s easier to understand all of this by focusing on application functions that must be provided — in some cases by facilities in the DBMS and in other cases by separate application programs.  It is important to distinguish between an SQL view (the logical structure of data elements) and a materialization of the view (a form or report). One SQL view can have many materializations. While this distinction has always been important, it has become even more so in light of three-tier architecture.  Remind students that views can be used to implement certain types of security. Most commonly, they are used to restrict access to attributes and to restrict actions on tables. This is discussed in detail in Chapter 9.  You might also remind students that sometimes SQL views are necessary to complete certain queries.  Too often students understand how SQL can be used for interactive query, but do not really understand its role in application processing. In fact, SQL is far more frequently used for SQL view processing as described here than it is as an interactive query tool.  SQL/Persistent Stored Modules (SQL/PSM), stored procedures and triggers complete a student’s understanding of how database systems work. Often, we talk about designing database systems to enforce business rules but find many rules that we cannot enforce through design alone. Triggers will help enforce most rules that design cannot enforce.  SQL *sql files containing the solutions to the questions and projects at the end of the chapter are available on the text’s Web site (  Page 7-8 ScholarStockChapter Seven – SQL For Database Construction and Application Processing  ANSWERS TO REVIEW QUESTIONS 7.1 What does DDL stand for? List the SQL DDL statements.  DDL stands for Data Definition Language. DDL statements include:  CREATE TABLE  ALTER TABLE  DROP TABLE  TRUNCATE TABLE 7.2 What does DML stand for? List the SQL DML statements.  DML stands for Data Manipulation Language. DML statements include:  INSERT  UPDATE  DELETE  MERGE 7.3 Explain the meaning of the following expression: IDENTITY (4000, 5).  The IDENTITY keyword is used to modify a column name, and is used to specify surrogate keys. The first number parameter after IDENTITY specifies the starting value for the surrogate key, and the second number specifies the increment value for each additional record. Thus a column named RelationID and modified by IDENTITY (4000, 5) will be a surrogate key named RelationID with an initial value of 4000 (for the first record in the relation), and with following values incremented by 5: 4000, 4005, 4010, etc. For this set of Review Questions, we will create and use a database for the Wedgewood Pacific Corporation (WPC) that is similar to the Microsoft Access database we created and used in Chapter 1 and Chapter 2. Founded in 1957 in Seattle, Washington, WPC has grown into an internationally recognized organization. The company is located in two buildings. One building houses the Administration, Accounting, Finance, and Human Resources departments, and the second houses the Production, Marketing, and Information Systems departments. The company database contains data about employees; departments; projects; assets, such as computer equipment; and other aspects of company operations.  Page 7-9 ScholarStockChapter Seven – SQL For Database Construction and Application Processing The database will be named WPC and will contain the following four tables: DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone) EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email) PROJECT (ProjectID, Name, Department, MaxHours, StartDate, EndDate) ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked) EmployeeNumber is a surrogate key that starts at 1 and increments by 1. ProjectID is a surrogate key that starts at 1000 and increases by 100. DepartmentName is the text name of the department, and is therefore not a surrogate key. The WPC database has the following referential integrity constraints: Department in EMPLOYEE must exist in Department in DEPARTMENT Department in PROJECT must exist in Department in DEPARTMENT ProjectID in ASSIGNMENT must exist in ProjectID in PROJECT EmployeeNumber in ASSIGNMENT must exist in EmployeeNumber in EMPLOYEE The relationship from EMPLOYEE to ASSIGNMENT is 1:N, M-O and the relationship from PROJECT to ASSIGNMENT is 1:N, M-O. The database also has the following business rules: o If an EMPLOYEE row is to be deleted and that row is connected to any ASSIGNMENT, the EMPLOYEE row deletion will be disallowed. o If a PROJECT row is deleted, then all the ASSIGNMENT rows that are connected to the deleted PROJECT row will also be deleted. The business sense of these rules is as follows: o If an EMPLOYEE row is deleted (e.g., if the employee is transferred), then someone must take over that employee’s assignments. Thus, the application needs someone to reassign assignments before deleting the employee row. o If a PROJECT row is deleted, then the project has been canceled, and it is unnecessary to maintain records of assignments to that project. Page 7-10 ScholarStockChapter Seven – SQL For Database Construction and Application Processing The column characteristics for these tables are shown in Figures 1-26 (DEPARTMENT), 1-28 (EMPLOYEE), 2-30 (PROJECT), and 2-32 (ASSIGNMENT). The data for these tables are shown in Figures 1-27 (DEPARTMENT), 1-29 (EMPLOYEE), 2-31 (PROJECT), and 2-33 (ASSIGNMENT).  Figure 1-26 - Column Characteristics for the DEPARTMENT Table  Page 7-11 ScholarStockChapter Seven – SQL For Database Construction and Application Processing Figure 1-28 - Column Characteristics for the EMPLOYEE Table  Figure 2-30 - Column Characteristics for the PROJECT Table  Figure 2-32 - Column Characteristics for the ASSIGNMENT Table   Page 7-12 ScholarStockChapter Seven – SQL For Database Construction and Application Processing  Figure 1-27 - Sample Data for the DEPARTMENT Table  Figure 1-29 - Sample Data for the EMPLOYEE Table Page 7-13 ScholarStockChapter Seven – SQL For Database Construction and Application Processing   Figure 2-31 - Sample Data for the PROJECT Table   Figure 2-33 - Sample Data for the PROJECT Table If at all possible, you should run your SQL solutions to the following questions against an actual database. Because we have already created this database in Microsoft Access, you should use Page 7-14 ScholarStoc