Question

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 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 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.41–7.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.47–7.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 7.57–7.62. Write the answer to Review Question 7.62 as an SQL comment
so that it cannot be run.
7.57 Write an UPDATE statement to change the phone number of the employee with
EmployeeNumber 11 to 360-287-8810. Run this SQL statement.

396 Part 3 Database Implementation

7.58 Write an UPDATE statement to change the department of the employee with
        EmployeeNumber 5 to Finance. Run this SQL statement.
7.59   Write an UPDATE statement to change the phone number of the employee with
        EmployeeNumber 5 to 360-287-8420. Run this SQL statement.
7.60   Combine your answers to Review Questions 7.58 and 7.59 into one SQL statement.
         Run this statement.
7.61    Write an UPDATE statement to set the HoursWorked to 60 for every row in
          ASSIGNMENT having the value 10 for EmployeeNumber. Run this statement.

Please make the table and write the SQL as it should. I need these answers immediately Please, so I can run it. This is Database System. Be sure and Accurate

Answer 1

I have created tables as given in the requirement and tables are structured as

Department

• In this table Department name is unique

Employee

• In this table EmployeeNumber is unique and it increments by 1, and Department column is Foreign Key reference of Department table

Project

• In this table ProjectID is unique and it get increment by 100 where the strating number is 1000, and Department column is Foreign Key reference of Department table

Assignment

• In this table both EmployeeNumber and ProjectID are unique, EmployeeNumber is Foreign Key reference of Employee and ProjectID is FK reference of Project table

**********************************SQL CREATE AND INSERT DATA QUERY**************************

/****** Object: Table [dbo].[Assignment] Script Date: 2/27/2018 12:48:56 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Assignment](

[ProjectID] [int] NOT NULL,

[EmployeeNumber] [int] NOT NULL,

[HoursWorked] [int] NOT NULL

) ON [PRIMARY]

GO

/****** Object: Table [dbo].[Department] Script Date: 2/27/2018 12:48:56 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Department](

[DepartmentName] [varchar](150) NOT NULL,

[BudgetCode] [varchar](150) NOT NULL,

[OfficeNumber] [int] NOT NULL,

[Phone] [int] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Employee] Script Date: 2/27/2018 12:48:56 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Employee](

[EmployeeNumber] [int] IDENTITY(1,1) NOT NULL,

[FirstName] [varchar](150) NOT NULL,

[LastName] [varchar](150) NULL,

[Department] [varchar](150) NOT NULL,

[Phone] [int] NULL,

[Email] [varchar](150) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Project] Script Date: 2/27/2018 12:48:56 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Project](

[ProjectID] [int] IDENTITY(1000,100) NOT NULL,

[Name] [varchar](150) NOT NULL,

[Department] [varchar](150) NOT NULL,

[MaxHours] [int] NOT NULL,

[StartDate] [datetime] NOT NULL,

[EndDate] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[Assignment] ([ProjectID], [EmployeeNumber], [HoursWorked]) VALUES (1000, 1, 34)
INSERT [dbo].[Assignment] ([ProjectID], [EmployeeNumber], [HoursWorked]) VALUES (1100, 10, 60)
INSERT [dbo].[Assignment] ([ProjectID], [EmployeeNumber], [HoursWorked]) VALUES (1200, 10, 60)
INSERT [dbo].[Assignment] ([ProjectID], [EmployeeNumber], [HoursWorked]) VALUES (1000, 10, 60)
INSERT [dbo].[Assignment] ([ProjectID], [EmployeeNumber], [HoursWorked]) VALUES (1300, 5, 100)
INSERT [dbo].[Assignment] ([ProjectID], [EmployeeNumber], [HoursWorked]) VALUES (1400, 11, 300)
INSERT [dbo].[Assignment] ([ProjectID], [EmployeeNumber], [HoursWorked]) VALUES (1400, 10, 60)
INSERT [dbo].[Department] ([DepartmentName], [BudgetCode], [OfficeNumber], [Phone]) VALUES (N'Account', N'ACC123', 999999999, 444444444)
INSERT [dbo].[Department] ([DepartmentName], [BudgetCode], [OfficeNumber], [Phone]) VALUES (N'Admin', N'ADM123', 888888888, 555555555)
INSERT [dbo].[Department] ([DepartmentName], [BudgetCode], [OfficeNumber], [Phone]) VALUES (N'Technology', N'TEC3456', 333333333, 777777777)
INSERT [dbo].[Department] ([DepartmentName], [BudgetCode], [OfficeNumber], [Phone]) VALUES (N'House Keeping', N'HK2345', 555555555, 222222222)
INSERT [dbo].[Department] ([DepartmentName], [BudgetCode], [OfficeNumber], [Phone]) VALUES (N'FirstAid', N'FA12345', 111111111, 338765432)
SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (1, N'Emp1', N'EmpL1', N'Account', 235346534, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (4, N'Emp2', N'EmpL2', N'FirstAid', 355647645, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (5, N'Emp3', N'EmpL3', N'Technology', 456546564, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (6, N'Emp4', N'EmpL4', N'Admin', 988437335, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (7, N'Emp5', N'EmpL5', N'FirstAid', 234234324, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (8, N'Emp6', N'EmpL6', N'Admin', 546775464, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (9, N'Emp7', N'EmpL7', N'Account', 328342349, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (10, N'Emp8', N'EmpL8', N'Technology', 989858543, N'[email protected]')
INSERT [dbo].[Employee] ([EmployeeNumber], [FirstName], [LastName], [Department], [Phone], [Email]) VALUES (11, N'Emp9', N'EmpL9', N'Admin', 734547578, N'[email protected]')
SET IDENTITY_INSERT [dbo].[Employee] OFF
SET IDENTITY_INSERT [dbo].[Project] ON

INSERT [dbo].[Project] ([ProjectID], [Name], [Department], [MaxHours], [StartDate], [EndDate]) VALUES (1000, N'Proj1', N'Account', 12, CAST(N'2018-02-17 12:16:32.897' AS DateTime), NULL)
INSERT [dbo].[Project] ([ProjectID], [Name], [Department], [MaxHours], [StartDate], [EndDate]) VALUES (1100, N'Proj2', N'Technology', 34, CAST(N'2018-02-01 12:16:32.897' AS DateTime), NULL)
INSERT [dbo].[Project] ([ProjectID], [Name], [Department], [MaxHours], [StartDate], [EndDate]) VALUES (1200, N'Proj3', N'FirstAid', 100, CAST(N'2018-01-01 12:16:32.897' AS DateTime), NULL)
INSERT [dbo].[Project] ([ProjectID], [Name], [Department], [MaxHours], [StartDate], [EndDate]) VALUES (1300, N'Proj4', N'Admin', 25, CAST(N'2018-02-27 12:16:32.897' AS DateTime), NULL)
INSERT [dbo].[Project] ([ProjectID], [Name], [Department], [MaxHours], [StartDate], [EndDate]) VALUES (1400, N'Proj5', N'House Keeping', 150, CAST(N'2017-12-01 12:16:32.897' AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[Project] OFF
SET ANSI_PADDING ON

GO

********************************END****************************************

• Below are the script for creating unique column in the tables

*******************SETTING UNIQUE COLUMN************************

/****** Object: Index [WPC_Department_Name] Script Date: 2/27/2018 12:48:56 PM ******/

ALTER TABLE [dbo].[Department] ADD CONSTRAINT [WPC_Department_Name] UNIQUE NONCLUSTERED

(

[DepartmentName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object: Index [WPC_Employee_EmpNum] Script Date: 2/27/2018 12:48:56 PM ******/

ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [WPC_Employee_EmpNum] UNIQUE NONCLUSTERED

(

[EmployeeNumber] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

/****** Object: Index [WPC_Project_ProjectID] Script Date: 2/27/2018 12:48:56 PM ******/

ALTER TABLE [dbo].[Project] ADD CONSTRAINT [WPC_Project_ProjectID] UNIQUE NONCLUSTERED

(

[ProjectID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

************************END****************************************

• And now for setting Foreign Key constraints in each table please execute the below script

*********************************FOREIGN KEY CONTRAINTS********************************

ALTER TABLE [dbo].[Assignment] WITH CHECK ADD CONSTRAINT [FK_Assignment_Employee] FOREIGN KEY([EmployeeNumber])
REFERENCES [dbo].[Employee] ([EmployeeNumber])
GO
ALTER TABLE [dbo].[Assignment] CHECK CONSTRAINT [FK_Assignment_Employee]
GO
ALTER TABLE [dbo].[Assignment] WITH CHECK ADD CONSTRAINT [FK_Assignment_Project] FOREIGN KEY([ProjectID])
REFERENCES [dbo].[Project] ([ProjectID])
GO
ALTER TABLE [dbo].[Assignment] CHECK CONSTRAINT [FK_Assignment_Project]
GO
ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Department] FOREIGN KEY([Department])
REFERENCES [dbo].[Department] ([DepartmentName])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]
GO
ALTER TABLE [dbo].[Project] WITH CHECK ADD CONSTRAINT [FK_Project_Department] FOREIGN KEY([Department])
REFERENCES [dbo].[Department] ([DepartmentName])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[Project] CHECK CONSTRAINT [FK_Project_Department]
GO

**************************************END***********************************

• If you noticed the above script the Employee and Project table has an update cascade constraint on DepartmentName column in Department table

Output result of all the table data are shown below

SQLQuery 12.sql - (..p.balraj.kumar (52)* X select *from [dbo]. [Department] select from [dbo]. [Employee] select from [dbo]. [Project] select from [dbol.[Assignmentl 100 % ResultsMessages DepartmentName BudgetCode OfficeNumberPhone 1 Account 2 Admin 3 Technology 4House Keeping 5First Aid ACC123 ADM123 TEC3456 HK2345 FA12345 888888888 555555555 333333333 777777777 555555555 222222222 EmployeeNumber FirstName LastName Department Phone Emp1 Emp2 EmpL2 FirstAid Emp3 Emp4 Emp5 EmpL5FirstAid Emp6 EmpL6 Admin Emp7 Emp8 Emp9 EmpL9 Admin EmpL1 Account 235346534 [email protected] 355647645 [email protected] EmpL3 Technology 456546564 [email protected] EmpL4 Admin 988437335 [email protected] 234234324 [email protected] 546775464 [email protected] EmpL7 Account 328342349 [email protected] EmpL8 Technology 989858543 [email protected] 8 10 9 1 734547578 [email protected] ProjectID Name Department MaxHours Start Date End Date 1000 2 1100 3 1200 4 1300 5 1400 Proj1 Account Proj2 Technology 34 Proj3 FirstAid Proj4 Admin Proj5 House Keeping 150 12 2018-02-17 12:16:32.897 NULL 2018-02-01 12:16:32.897 NULL 2018-01-01 12:16:32.897 NULL 2018-02-27 12:16:32.897 NULL 2017-12-01 12:16:32.897 NULL 1 25 ProjectIDEmployeeNumber HoursWorked 1000 2 1100 3 1200 4 1000 5 1300 6 1400 7 1400 34 50 40 70 1 10 10 300

Below are the answers for the Question

7.57

Update e
set e.Phone = 3602878810
From employee e
where e.EmployeeNumber = 11

• This query updates the phone number to 3602878810 in column Phone of table employee where employeenumber equals to 11

7.58

Update d
set d.DepartmentName = 'Finance'
From Department d
inner join employee e on e.Department = d.DepartmentName
where e.EmployeeNumber = 5

• The above query updates the department name in department table for employee with employee number equal to 5. The other tables which has reference to this column will automatically updates as cascade

7.59

Update e
set e.Phone = 3602878420
From employee e
where e.EmployeeNumber = 5

• This query updates the phone number to 3602878420in column Phone of table employee where employeenumber equals to 5

7.60

BEGIN TRY

BEGIN TRAN

Update d
set d.DepartmentName = 'Finance'
From Department d
inner join employee e on e.Department = d.DepartmentName
where e.EmployeeNumber = 5

Update e
set e.Phone = 3602878420
From employee e
where e.EmployeeNumber = 5

COMMIT

END TRY

BEGIN CATCH

ROLLBACK

END CATCH

• In SQL in single statement we can't update the two tables but we can do it by combining both the update statement in single try catch block so that if any error happens the old data will be reverted.

7.61

Update a
set a.HoursWorked = 60
From Assignment a
inner join employee e on e.EmployeeNumber = a.EmployeeNumber
where e.EmployeeNumber = 10

• This updates the HoursWorked to 60 hours with assignment that matches the employee with employee number equals to 10