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

Create and run an SQL script named WPC-Create-Views.sql to answer Review Questions 7.677.72. 7.67 Write an SQL statement to create a view named EmployeePhoneView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, and EMPLOYEE.Phone as EmployeePhone. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement. 7.68 Write an SQL statement to create a view named FinanceEmployeePhoneView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE .FirstName as EmployeeFirstName, and EMPLOYEE.Phone as EmployeePhone for employees who work in the Finance department. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement. 7.69 Write an SQL statement to create a view named CombinedNameEmployeePhoneView that shows the values of EMPLOYEE.LastName, EMPLOYEE.FirstName, and EMPLOYEE.Phone as EmployeePhone but that combines EMPLOYEE.LastName and EMPLOYEE.FirstName into one column named EmployeeName that displays the employee name first name first. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement. 7.70 Write an SQL statement to create a view named EmployeeProjectAssignmentView that shows the values of EMPLOYEE.LastName as EmployeeLastName, EMPLOYEE .FirstName as EmployeeFirstName, EMPLOYEE.Phone as EmployeePhone, and PROJECT.Name as ProjectName. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

7.71 Write an SQL statement to create a view named DepartmentEmployee- ProjectAssignmentView that shows the values of EMPLOYEE.LastName as

EmployeeLastName, EMPLOYEE.FirstName as EmployeeFirstName, EMPLOYEE .Phone as EmployeePhone, DEPARTMENT.DepartmentName, Department.PHONE as DepartmentPhone, and PROJECT.Name as ProjectName. Run this statement to

CHAPTER 7 SQL for Database Construction and Application Processing 397 create the view, and then test the view by writing and running an appropriate SQL SELECT statement. 7.72 Write an SQL statement to create a view named ProjectHoursToDateView that shows the values of PROJECT.ProjectID, PROJECT.Name as ProjectName, PROJECT .MaxHours as ProjectMaxHours and the sum of ASSIGNMENT.HoursWorked as ProjectHoursWorkedToDate. Run this statement to create the view, and then test the view by writing and running an appropriate SQL SELECT statement.

Please this is Database Systems, make sure that the answers are accurate, so I can run the SQL. I need these answers immediately. Thanks