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 Chapters 1 and 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.

394 Part 3 Database Implementation

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 DepartmentName in DEPARTMENT Department in PROJECT must exist in DepartmentName in DEPARTMENT ProjectID in ASSIGNMENT must exist in ProjectID in PROJECT 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: If an EMPLOYEE row is to be deleted and that row is connected to any ASSIGNMENT, the EMPLOYEE row deletion will be disallowed. 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: If an EMPLOYEE row is deleted (e.g., if the employee is transferred), then someone must take over that employees assignments. Thus, the application needs someone to reassign assignments before deleting the employee row.

If a PROJECT row is deleted, then the project has been canceled, and it is unneces- sary to maintain records of assignments to that project.

The column characteristics for these tables are shown in Figures 1-28 (DEPARTMENT), 1-30 (EMPLOYEE), 2-42 (PROJECT), and 2-44 (ASSIGNMENT). The data for these tables are shown in Figures 1-29 (DEPARTMENT), 1-31 (EMPLOYEE), 2-43 (PROJECT), and 2-45 (ASSIGNMENT). 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 an SQL-oriented DBMS such as Microsoft SQL Server 2014, Oracle Database, or MySQL 5.6 in these exercises. Create a database named WPC, and create a folder in your My Documents folder to save and store the *.sql scripts containing the SQL statements that you are asked to create in the remaining questions pertaining to the WPC database in this section and the following Project Questions section. For the SQL Server Management Studio, create a folder named WPC-Database in the Projects folder structure in your SQL Server Management Studio folder. In the Oracle SQL Developer folder structure in your SQL Developer folder, create a folder named WPC-Database. For the MySQL Workbench, create a folder named WPC-Database in the Schemas folder in your MySQL Workbench folder. If that is not possible, create a new Microsoft Access database named WPC-CH07.accdb,

and use the SQL capabilities in these exercises. In all the exercises, use the data types appro- priate for the DBMS you are using.

Write and save an SQL script named WPC-Create-Tables.sql that includes the answers to

Review Questions 7.417.50. Use SQL script commenting (/* and */ symbols) to write your an- swers to Review Questions 7.45 and 7.46 as comments so that they cannot be run! Test and

CHAPTER 7 SQL for Database Construction and Application Processing 395

run your SQL statements for Review Questions 7.41, 7.42, 7.43, and 7.44 only. After the ta- bles are created, run your answers to Review Questions 7.477.50. Note that after these four

statements have been run the table structure is exactly the same as it was before you ran them.

QUESTIONS

Create and run an SQL script named WPC-Update-Data.sql to answer Review Questions 7.577.62. Write the answer to Review Question 7.62 as an SQL comment so that it cannot be run. 7.57 Write an UPDATE statement to change the phone number of the employee with EmployeeNumber 11 to 360-287-8810. Run this SQL statement.

396 Part 3 Database Implementation

7.58 Write an UPDATE statement to change the department of the employee with EmployeeNumber 5 to Finance. Run this SQL statement. 7.59 Write an UPDATE statement to change the phone number of the employee with EmployeeNumber 5 to 360-287-8420. Run this SQL statement. 7.60 Combine your answers to Review Questions 7.58 and 7.59 into one SQL statement. Run this statement. 7.61 Write an UPDATE statement to set the HoursWorked to 60 for every row in ASSIGNMENT having the value 10 for EmployeeNumber. Run this statement.

Please make the table and write the SQL as it should. I need these answers immediately Please, so I can run it. This is Database System. Be sure and Accurate