S10_Chap7_RQ_WPC-Database Lab

This assignment is from the Kroenke book Chap 7 Review Questions (RQ).

Create a new folder, WPC-Database, in the Projects folder, found in the SQL Server Management Studio folder. This new folder is where you will store the .sql script(s) you RUN, downloaded from the assignment, and the .sql scripts you CREATE, which will also include written answers, stored as comment lines.

FIRST, You need to run the SQL solutions, to the following questions, against an actual database, so create the WPC-Database.

You may want to review the instructions on how to create a Database in SSMS. The most recent Database you created was RQ_TABLES, in Session 09. Refer to the Session 09 RQ_TABLES SQL instructions, but use the database name, WPC-Database.

SECOND, review the WPC-Database Design.docx, attached to the Assignment. After reviewing the WPC-Database Design.docx, you will answer the S10 Kroenke book Review Questions (RQ), repeated below.

The RQ have been edited to provide SQL Server information only.

To summarize: Follow the directions below to create the SQL scripts listed below, and answer the Questions identified. Insert written answers, as needed, into each script as text, commented as

/* questions written answer */.

THIRD, using the directions below, you will either Run or Create the seven (7) .sql scripts listed below. Copy these files to a folder, named WPC-Database. Compress/Zip this folder and submit it, attached to the assignment link, to be graded.

Question 7.41-7.43 RUN WPC-Create-3Tables.sql

Question 7.44 & 7.46(skip 7.45, 7.47 - 7.50) CREATE WPC-Create-ASSIGNMENT.sql

Question 7.51-7.53 RUN WPC-Insert-Data-3Tables.sql

Question 7.54-7.55 (skip 7.56) CREATE WPC-Insert-ASSIGNMENT.sql

Questions 7.57 through 7.61 CREATE WPC-Update-Data.sql

Questions 7.67 - 7.70 CREATE WPC-Create-Views.sql

Questions 7.84 - 7.85 CREATE WPC-Create-FunctionViews.sql

Question 7.41-7.43 RUN WPC-Create-3Tables.sql

The SQL code below is for the SQL Server Management Studio Only. If you need to use another software package your instructor will have to approve it, and you can use the additional information provided in the book.

Submit the SQL script, WPC-CH07-Create3-Tables.sql, located in the attached folder, S10_WPC-CH07-ReviewQuestions. This file includes the answers to Review Questions 7.41 through 7.43. You will create the last INSERT script for the Assignment table, for the next questions.

Test and run the SQL statements for RQ 7.41, 7.42, &7.43.

Question 7.44 & 7.46 (skip 7.45-7.50) CREATE WPC-Create-Assignment.sql

7.44 Write a CREATE TABLE SQL script for the ASSIGNMENT table, saved as WPC-Create-ASSIGNMENT.sql in the WPC-Database folder

Cascade only deletions from PROJECT to ASSIGNMENT; do not cascade either deletions or updates from EMPLOYEE to ASSIGNMENT.

Question 7.51-7.53 RUN WPC-Insert-Data-3Tables.sql

The SQL code provided is for the SQL Server Management Studio. If you need to use another software package your instructor will have to approve it, and you can use the additional information provided in the book.

Submit the SQL script, WPC-Insert-Data-3Tables.sql, located in the attached folder, S10 WPC-CH07-ReviewQuestions. This file includes the answers to Review Questions 7.51 through 7.53. You will create the last INSERT script for the Assignment table.

Test and run the SQL statements, in the file above, for Review Questions 7.51, 7.52, and 7.53.

Question 7.54 (skip 7.55-7.56) CREATE WPC-Insert-ASSIGNMENT.sql

7.54 Write INSERT statements to add the data shown in Figure 2-45, below, to the ASSIGNMENT table. Run these statements to populate the ASSIGNMENT table. Save script as WPC-Insert-ASSIGNMENT-Data.sql.

Figure 2-45 - Sample Data for the PROJECT Table

SKIP RQ 7.55, and 7.56. If you accidentally ran one of these two scripts you could damage your table(s), so we will NOT run these scripts.

Questions 7.57 - 7.61 (Skip 7.62) CREATE WPC-Update-Data.sql

7.57 Write an UPDATE statement to change the phone number of employee with EmployeeNumber 11 to 360-287-8810. Run this SQL statement, and document/copy it to CREATE WPC-Update-Data.sql.

7.58 Write an UPDATE statement to change the department of employee with EmployeeNumber 5 to Finance. Run this SQL statement, and document/copy it to CREATE WPC-Update-Data.sql.

7.59 Write an UPDATE statement to change the phone number of employee with EmployeeNumber 5 to 360-287-8420. Run this SQL statement, and document/copy it to CREATE WPC-Update-Data.sql.

7.60 Combine your answers to Review Questions 7.58 and 7.59 into one SQL statement. Run this statement, and document/copy it to CREATE WPC-Update-Data.sql.

7.61 Write an UPDATE statement to set HoursWorked to 60 for every row in ASSIGNMENT having the value 10 for EmployeeNumber. Run this statement, and document/copy it to CREATE WPC-Update-Data.sql.

Skip question 7.62 7.63. If you accidentally ran one of these two scripts you could damage your table(s), so we will NOT run these scripts.

Questions 7.67 - 7.70 CREATE WPC-Create-Views.sql

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, and then test the view with an 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.First-Name as EmployeeFirstName, and EMPLOYEE.Phone as EmployeePhone for employees who work in the Finance department. Run this statement, and then test the view with an 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, and then test the view with an 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.First-Name as EmployeeFirstName, EMPLOYEE.Phone as EmployeePhone, and PROJECT.Name as ProjectName. Run this statement, and then test the view with an SQL SELECT statement.

SKIP 7.71 THROUGH 7.83.

Questions 7.84 - 7.85 CREATE WPC-Create-FunctionViews.sql

7.84 Create and test a user-defined function named LastNameFirst that combines two parameters named FirstName and LastName into a concatenated name field formatted LastName, FirstName (including the comma and space).

7.85 Create and test a view called EmployeeDepartmentDataView that contains the employee name concatenated and formatted as LastName, FirstName in a field named EmployeeName, EMPLOYEE.Department, DEPARTMENT.OfficeNumber,DEPARTMENT.Phone as DepartmentPhone, 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.

SKIP 7.86 THROUGH 7.94

Copy the 7 scripts you created, names repeated below, in the folder named, WPC-Database. Compress/Zip the folder, and submit, using the assignment link, to be graded.

Question 7.41-7.43 RUN WPC-Create-3Tables.sql

Question 7.44 & 7.46(skip 7.45, 7.47 - 7.50) CREATE WPC-Create-ASSIGNMENT.sql

Question 7.51-7.53 RUN WPC-Insert-Data-3Tables.sql

Question 7.54-7.55 (skip 7.56) CREATE WPC-Insert-ASSIGNMENT.sql

Questions 7.57 through 7.61 CREATE WPC-Update-Data.sql

Questions 7.67 - 7.70 CREATE WPC-Create-Views.sql

Questions 7.84 - 7.85 CREATE WPC-Create-FunctionViews.sql

Create WPC DB Views Chapter 7 Kroenke

This assignment requires that you to follow Review Questions (RQ), which have been repeated below. You will write some of the code to create a database, named WPC-Database.

Create a folder, for this project, in the same folder you stored the Create and Insert SQL for the Cape_Codd database. This is where you will store the .sql script(s) containing the SQL statements that you are asked to create in the Review questions below.

You will also want to review the instructions for creating a Database in SSMS. You have created two databases at this time. The first one you created was Cape_Codd, in Session 02 and more recently RQ_TABLES in Session 09. Use the Session 09 RQ_TABLES SQL code, but use the database name, WPC_Database.

This document contains the WPC-Database Design Documentation, describing the database.

Using the Design Documentation, you will complete the assignment, S10Chap7_RQ_Kroenke(2).docx, below. The RQ have been edited to provide SQL Server information only.

To summarize: Follow the directions below to create the scripts listed below, and answer the Questions. Insert the questions into each script as text, commented out as /* question written answer */.

Using the directions below, you will either Run or Create, and then copy the seven (7) .sql scripts to a folder, named WPC-Database. Compress/Zip this folder and submit it, attached to the assignment link, to be graded.

1. RUN WPC-Create-3Tables.sql	Question 7.41-7.43
2. CREATE WPC-Create-Assignment.sql	Question 7.44-7.45
3. RUN WPC-Insert-Data-3Tables_Data.sql	Question 7.51-7.53
4. CREATE WPC-Insert-ASSIGNMENT.sql	Question 7.54
5. CREATE WPC -Update-Data.sql	Questions 7.57 through 7.62
6. CREATE WPC-Create-Views.sql	Questions 7.67 through 7.72
7. CREATE WPC-Create-FunctionViews.sql	Questions 7.84 through 7.85

WPC DATABASE DESIGN DOCUMENTATION

This Design Document is to be used to Design and create a database for the Wedgewood Pacific Corporation (WPC). This database is similar to the Microsoft Access database used in Chapter 1.

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.

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)

SURROGATE KEYS

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 RELATIONSHIPS

EMPLOYEE to ASSIGNMENT is 1:N, M-O

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 unnecessary to maintain records of assignment to that project.

Chapter Seven SQL For Database Construction and Application Processing

The column characteristics for these tables are shown in the figures below.

Figure 1-28 - Column Characteristics for the DEPARTMENT Table

Figures 1-29 The data for these tables are shown in Figures 1-29 (DEPARTMENT),

Figure 1-30 - Column Characteristics for the EMPLOYEE Table

Figure 1.31 WPC EMPLOYEE data

Figure 2-42 - Column Characteristics for the PROJECT Table

Figure 2-43 - Sample Data for the PROJECT Table

Figure 2-44 - Column Characteristics for the ASSIGNMENT Table

Figure 2-45 - Sample Data for the PROJECT Table