Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 100

2.57 Create the relationship and referential integrity constraint between PROJECT and
DEPARTMENT. Enable enforcing of referential integrity and cascading of data updates,
but do not enable cascading of data from deleted records.
2.58 Figure 2-31 shows the data for the WPC PROJECT table. Using the Datasheet view, en-
ter the data shown in Figure 2-31 into your PROJECT table.
2.59 Figure 2-32 shows the column characteristics for the WPC ASSIGNMENT table.
Using the column characteristics, create the ASSIGNMENT table in the WPC.accdb
database.
2.60 Create the relationship and referential integrity constraint between ASSIGNMENT and
EMPLOYEE. Enable enforcing of referential integrity, but do not enable either cascad-
ing updates or the cascading of data from deleted records.
2.61 Create the relationship and referential integrity constraint between ASSIGNMENT and
PROJECT. Enable enforcing of referential integrity and cascading of deletes, but do not
enable cascading updates.
2.62 Figure 2-33 shows the data for the WPC ASSIGNMENT table. Using the Datasheet
view, enter the data shown in Figure 2-33 into your ASSIGNMENT table.
2.63 In Review Question 2.58, the table data was entered after referential integrity con-
straints were created in Review Question 2.57. In Review Question 2.62, the table data
was entered after referential integrity constraints were created in Review Questions
2.59 and 2.60. Why was the data entered after the referential integrity constraints were
created instead of before the constraints were created?
2.64 Using Microsoft Access SQL, create and run queries to answer the following questions.
Save each query using the query name format SQL-Query-02-##, where the ## sign is
replaced by the letter designator of the question. For example, the first query will be
saved as SQL-Query-02-A.
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, 2013? Show all the
information for each project.
D. What projects in the PROJECT table have not been completed? Show all the infor-
mation for each project.
e. Who are the employees assigned to each project? Show ProjectID, EmployeeNumber,
LastName, FirstName, and Phone.
F. Who are the employees assigned to each project? 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 EmployeeNumber, 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 EmployeeNumber,
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.