Description:
The goal is develop a database for the Wedgewood Pacific Corporation using MS SQL Server.
1. In MS SQL Server, create a new database called WPC.
2.
Create the DEPARTMENT and EMPLOYEE tables shown below along with the PROJECT and
ASSIGNMENT tables shown on the next page, and set the relationships between the tables explained below:
DEPARTMENT
Column Name
DepartmentName
BudgetCode
OfficeNumber
Phone
EMPLOYEE
Column Name
EmployeeNumber
FirstName
LastName
Department
Assignment #4: SQL Statements
Phone
Email
Type
nvarchar(35)
nvarchar(30)
nvarchar(15) No
Key
Primary Key
Type
bigint: Identity
nvarchar(12) No
No
nvarchar(35)
Key
Primary Key
nvarchar(25) No
nvarchar(25)
No
Foreign Key
nvarchar(12)
nvarchar(100) No
No
Required
Yes
Yes
Yes
Yes
Required
Yes
Yes
Yes
Yes
No
Yes
Remarks
Remarks
Surrogate Key
A. To set a column to be a Primary Key, right-click on the column name (while in the design mode) and
select Set Primary Key.
B. To set a column as a Foreign Key, right-click on the column name (while in the design mode) and select
Relationship. Click the Add button and then click on "Tables and Columns Specification" and then click
on the button next to it (with three dots) and complete the relationship by selecting the Primary Key table
and under that pick its column name, and match that with the Foreign Key column name in the current
table.
C. The Department column in the EMPLOYEE and PROJECT tables are both a Foreign Key of the
DepartmentName column from the DEPARTMENT table. ProjectID in the ASSIGNMENT table is a
Foreign Key of ProjectID in the PROJECT table, and EmployeeNumber in the ASSIGNMENT table is a
Foreign Key of EmployeeNumber in the EMPLOYEE table. Notice that ProjectID and EmployeeNumber
are also Primary Keys in the ASSIGNMENT table.
D. The EmployeeNumber column in the EMPLOYEE table is an Identity column, which means its value
will be automatically assigned (AutoNumber). To set it as an Identity column, click on the column name,
then go into the properties window below it and scroll to "Identity Specification" and open that property
line and click on “(Is Identity)” and then click on the down arrow on the right side and select "Yes". 3. Now create the PROJECT and ASSIGNMENT tables as shown here. Note the Foreign Key relationships in
these tables and set them up as explained above.
PROJECT
Column Name
ProjectID
Name
Department
MaxHours
StartDate
EndDate
ASSIGNMENT
Column Name
ProjectID
EmployeeNumber
HoursWorked
Administration
Legal
Accounting
Finance
Production
Marketing
InfoSystems
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
[AutoNumber]
Type
DEPARTMENT Data
DepartmentName BudgetCode
bigint
nvarchar(50)
nvarchar(35)
float
datetime
datetime
Type
bigint
bigint
BC-100-10
BC-200-10
Human Resources BC-500-10
float
BC-300-10
Tom
BC-400-10
Mary
BC-600-10
Tom
BC-700-10
BC-800-10
Ken
Rick
Rosalie
Richard
Heather
Mary
George
George
4. Populate the DEPARTMENT and EMPLOYEE tables with the same exact data provided here:
James
Jacobs
Jones
Smith
Key
Primary Key
Jones
No
Foreign Key
Nestor
No
Jackson
Brown
No
No
Key
Primary Key,
Foreign Key
Primary Key,
Foreign Key
OfficeNumber
No
BLDG01-300
Jackson
BLDG01-200
EMPLOYEE Data
EmployeeNumber FirstName LastName Department
Administration
BLDG01-100
Numoto
BLDG01-140
BLDG01-180
BLDG02-100
BLDG02-200
BLDG02-270
Abernathy
Bandalone Legal
Caruthers Accounting
Accounting
Required
Yes
Finance
Yes
Yes
Production
Yes
Production
No
No
Required
Yes
Yes
Administration
No
Phone
Marketing
InfoSystems
InfoSystems
Remarks
Long Integer
Double
Remarks
Long Integer
Long Integer
Do
360-285-8100
360-285-8200
Phone
360-285-8300
360-285-8400
360-285-8500
360-287-8600
360-287-8700
360-287-8800
360-285-8110 MJacobs@WPC.com
360-285-8120 RJackson@WPC.com
360-285-8210
360-285-8310
Email
Human Resources 360-285-8510 GSmith@WPC.com
RBandalone@WPC.com
360-285-8320 HJones@WPC.com
TCaruthers@WPC.com
360-285-8410 MAbernathy@WPC.com
360-287-8620
360-287-8710
360-287-8610 TJackson@WPC.com
GJones@WPC.com
KNumoto@WPC.com
JNestor@WPC.com
360-287-8820 RBrown@WPC.com 5. Populate the PROJECT and ASSIGNMENT tables with the following data:
PROJECT Data
ProjectID Name
1000
1100
1200
1300
1400
1000
1000
1000
ASSIGNMENT Data
ProjectID
1100
1100
1100
1200
1200
1200
1300
1300
1300
1400
1400
2008 Q3 Product Plan
1400
2008 Q3 Portfolio Analysis
2008 Q3 Tax Preparation
2008 Q4 Product Plan
2008 Q4 Portfolio Analysis
EmployeeNumber
1
8
10
4
6
1
4
5
1
8
10
4
5
6
Department MaxHours
Marketing
Finance
Accounting
Marketing
Finance
HoursWorked
30.0
75.0
55.0
40.0
45.0
25.0
20.0
45.0
40.0
35.0
80.0
50.0
15.0
10.0
27.5
135
120
145
150
140
StartDate
05/10/08
07/05/08
08/10/08
08/10/08
10/05/08
EndDate
06/15/08
07/25/08
10/25/08
09/15/08
6. Develop SQL queries (in one script) to answer the following questions A through L:
A. What projects are in the PROJECT table? Show all information for each project.
B. What are the ProjectID, Name, StartDate, and EndDate values of projects in the PROJECT
table?
C. What projects in the PROJECT table started before August 1, 2008? Show all the
information for each project.
D. What projects in the PROJECT table have not been completed? Show all the information for
each project.
E. Who are the employees assigned to each project? Show ProjectID, Employee-Number,
LastName, FirstName, and Phone.
F. Who are the employees assigned to each project? Show the ProjectID, Name, and
Department. Show EmployeeNumber, LastName, FirstName, and Phone. G. Who are the employees assigned to each project? Show ProjectID, Name, Department, and
Department Phone. Show Employee Number, LastName, FirstName, and Employee Phone.
Sort by ProjectID in ascending order.
H. Who are the employees assigned to projects run by the marketing department? Show
ProjectID, Name, Department, and Department Phone. Show Employee Number, LastName,
FirstName, and Employee Phone. Sort by ProjectID in ascending order.
I.
How many projects are being run by the marketing department? Be sure to assign an
appropriate column name to the computed results.
J. What is the total MaxHours of projects being run by the marketing department? Be sure to
assign an appropriate column name to the computed results.
K. What is the average MaxHours of projects being run by the marketing department? Be sure
to assign an appropriate column name to the computed results.
L. How many projects are being run by each department? Be sure to display each
DepartmentName and to assign an appropriate column name to the computed results.
Note: Place a comment block at the top of your script file:
/*
/* Name:
/* Class:
/* Term:
/* Assgn #:
/*****.
CS3410
Spring 2021
4
*** /
*
1
·1
*
.sql". For example, John
Smith's script file would be named “A4-Smith.sql”. Submit .sql file in dropbox area of D2L labled Assignment 4
Submission.
