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