Create WPC DB Views Chapter 7 Kroenke This assignment requires that you to follo
ID: 3822860 • Letter: C
Question
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 employee’s 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
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
Explanation / Answer
// here not possible to create the ZIP FILE or any file so here just I am creating all the tables which your project
// required, so you can just create you app or project by using below table.
create sequence srseqeno increment by 1 start with 1;
create sequence srseqpid increment by 100 start with 1000;
create table DEPARTMENT (DepartmentName varchar(20),
BudgetCode varchar(20),
OfficeNumber int,
Phone int, primary key(DepartmentName));
create table EMPLOYEE (EmployeeNumber int ,
FirstName varchar(15),
LastName varchar(15),
Department varchar(20) references department(departmentName) on delete set null,
Phone long,
Email varchar(20));
create table PROJECT (ProjectID int,
Name varchar(20),
Department varchar(20) references department(departmentName) on delete set null,
MaxHours int,
StartDate date,
EndDate date );
create table ASSIGNMENT (ProjectID int references project(ProjectId) on delete set null,
EmployeeNumber int references Employee(EmployeeNumber) on delete set null,
HoursWorked int);
insert into PROJECT(ProjectID) values(srseqid.currval);
insert into EMPLOYEE(EmployeeNumber) values(srseqeno.currval);