Database Storage & Design Appendix N All Sections The Access Workbench Section Three Objectives Learn How Use

Unlock access to all the studying documents.

View Full Document

All Sections – The Access Workbench

SECTION THREE

 CHAPTER OBJECTIVES

• Learn how to use Microsoft Access SQL

• Learn how to run queries in single and multiple tables, using both SQL and Query by

Example (QBE)

• Learn how to manually set table and relationship properties that Microsoft Access

SQL does not support

 CHAPTER ERRATA

• [23-FEB-2017] In Access Workbench Exercise AW.3.3 question E on page 241,

the column name EmployeeID should be EmployeeNumber.

• [23-FEB-2017] In Access Workbench Exercise AW.3.3 question F on page 241,

the column name EmployeeID should be EmployeeNumber.

 TEACHING SUGGESTIONS

• This section of “The Access Workbench” is the basic introduction to creating

relationships between tables. Demonstrate the Microsoft Access 2016 Relationship

window in class, and discuss each of the operations covered in the text.

• Make sure your students actually work through The Access Workbench steps before

attempting the Access Workbench Exercises.

• You may want to compare SQL and QBE queries in lecture.

• Note that Microsoft Access SQL requires number signs (#) around some data and

other data in WHERE clauses. See the, for example, question AW.3.1.C.

• Show your class that while most SQL queries translate properly into QBE queries

(and vice versa) there will be exceptions. For example, the first (AW.3.1.A)1SQL

query SELECT * FROM PROPERY; results in the QBE shown on the next page.

This is obviously incorrect since no fields have been selected! This seems to usually

occur when the SQL statement refers to all columns using the asterisk symbol.

All Sections – The Access Workbench

• Review the material on Page 140-141 about the versions of SQL used in Microsoft

Access. Be sure students understand that by default Microsoft Access uses an

earlier version of the ANSI standard—Microsoft calls it ANSI-89 SQL or Microsoft Jet

SQL (after the Microsoft Jet DBMS used by Microsoft Access). Since the current

standard version of SQL is SQL:2011, which is based on SQL-92 (which Microsoft

refers to as ANSI-92 SQL), and since ANSI-89 SQL differs significantly from SQL-92

• On the other hand, note the MS Microsoft Access 2016 does contain a setting that

allows you to use SQL-92 instead of the default ANSI-89 SQL. For a particular

Microsoft Access database, use File | Microsoft Access Options to open the

Microsoft Access Options dialog box, and then set the SQL Server Compatible

Syntax (ANSI-92) property in the Query Design settings of the Object Designers

page.

• Note that the solutions in this section do not include PROJECT 1700—2017 Q4 Tax

Preparation run by the Accounting department (as per the INSERT statement on

page 156). If your students used the WP.accdb file to try out the commands in

All Sections – The Access Workbench

 ANSWERS TO ACCESS WORKBENCH EXERCISES

In the “Access Workbench Exercises” in Chapters 1 and 2, you created a database for

the Wedgewood Pacific (WP) of Seattle, Washington. In this set of exercises, you’ll:

• Create and run queries against the database by using Access SQL.

AW.3.1. Using Access SQL, create and run queries to answer the questions that follow. Save

each query using the query name format SQLQuery-AWE-3-1–## where the ## sign

is replaced by the letter designator of the question. For example, the first query will

be saved as SQLQuery-AWE-3-1–A.

A. What projects are in the PROJECT table? Show all information for each

project.

B. What are the ProjectID, ProjectName, StartDate, and EndDate values of

projects in the PROJECT table?

See the file: DBC-e08-WP–AW-03.accdb.

All Sections – The Access Workbench

C. What projects in the PROJECT table started before August 1, 2017? 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.

See the file: DBC-e08-WP–AW-03.accdb.

All Sections – The Access Workbench

E. Who are the employees assigned to each project? Show ProjectID,

EmployeeNumber, LastName, FirstName, and Phone.

All Sections – The Access Workbench

F. Who are the employees assigned to each project? Show ProjectID,

ProjectName, and Department. Show EmployeeNumber, LastName,

FirstName, and Phone.

All Sections – The Access Workbench

G. Who are the employees assigned to each project? Show ProjectID,

ProjectName, Department, and Department Phone. Show EmployeeNumber,

LastName, FirstName, and Employee Phone. Sort by ProjectID, in ascending

order.

See the file: DBC-e08-WP–AW-03.accdb.

All Sections – The Access Workbench

H. Who are the employees assigned to projects run by the Sales and Marketing

Department? Show ProjectID, ProjectName, Department, and Department

Phone. Show EmployeeNumber, LastName, FirstName, and Employee

Phone. Sort by ProjectID, in ascending order.

See the file: DBC-e08-WP–AW-03.accdb.

/***** Question H – SQLQuery-AWE-3-1-H ************************/

SELECT P.ProjectID, ProjectName,

I. How many projects are being run by the Sales and Marketing Department?

Be sure to assign an appropriate column name to the computed results.

See the file: DBC-e08-WP–AW-03.accdb.

/***** Question I – SQLQuery-AWE-3-1-I ************************/

All Sections – The Access Workbench

J. What is the total MaxHours of projects being run by the Sales and Marketing

Department? Be sure to assign an appropriate column name to the

computed results.

See the file: DBC-e08-WP–AW-03.accdb.

/***** Question J – SQLQuery-AWE-3-1-J ************************/

SELECT SUM(MaxHours)

K. What is the average MaxHours of projects being run by the Sales and

Marketing Department? Be sure to assign an appropriate column name to the

computed results.

See the file: DBC-e08-WP–AW-03.accdb.

/***** Question K – SQLQuery-AWE-3-1-K ************************/

SELECT AVG(MaxHours)

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.

All Sections – The Access Workbench

AW.3.2. Using Access QBE, create and run new queries to answer the questions in exercise

AW.3.1. Save each query using the query name format QBEQuery-AWE-3-1-##

where the ## sign is replaced by the letter designator of the question. For example,

the first query will be saved as QBEQuery-AWE-3-1-A.

A. What projects are in the PROJECT table? Show all information for each

project.

See the file: DBC-e08-WP–AW-03.accdb.

B. What are the ProjectID, ProjectName, StartDate, and EndDate values of

projects in the PROJECT table?

See the file: DBC-e08-WP–AW-03.accdb.

All Sections – The Access Workbench

C. What projects in the PROJECT table started before August 1, 2017? Show all

the information for each project.

See the file: DBC-e08-WP–AW-03.accdb.

D. What projects in the PROJECT table have not been completed? Show all the

information for each project.

All Sections – The Access Workbench

E. Who are the employees assigned to each project? Show ProjectID,

EmployeeNumber, LastName, FirstName, and OfficePhone.

F. Who are the employees assigned to each project? Show ProjectID,

ProjectName, and Department. Show the EmployeeNumber, LastName,

FirstName, and OfficePhone.

All Sections – The Access Workbench

G. Who are the employees assigned to each project? Show ProjectID,

ProjectName, Department, and DepartmentPhone. Show the

EmployeeNumber, LastName, FirstName, and OfficePhone. Sort by

ProjectID, in ascending order.

H. Who are the employees assigned to projects run by the Marketing

Department? Show ProjectID, ProjectName, Department, and Department

Phone. Show the EmployeeNumber, LastName, FirstName, and Employee

Phone. Sort by ProjectID, in ascending order.

All Sections – The Access Workbench

I. How many projects are being run by the Sales and Marketing Department?

Be sure to assign an appropriate column name to the computed results.

See the file: DBC-e08-WP–AW-03.accdb.

J. What is the total MaxHours of projects being run by the Sales and Marketing

Department? Be sure to assign an appropriate column name to the computed

results.

See the file: DBC-e08-WP–AW-03.accdb.

All Sections – The Access Workbench

K. What is the average MaxHours of projects being run by the Sales and

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.

See the file: DBC-e08-WP–AW-03.accdb.

All Sections – The Access Workbench

AW.3.3. WP has decided to keep track of computers used by the employees. In order to do

this, two new tables will be added to the database. The schema for these tables, as

related to the existing EMPLOYEE table, is:

EmployeeNumber is a surrogate key and never changes. Employee records are

never deleted from the database. SerialNumber is not a surrogate key because it is

not generated by the database. However, a computer’s SerialNumber never

changes, and, therefore, there is no need to cascade updates. When a computer is

at its end of life, the record in COMPUTER for that computer and all associated

records in COMPUTER_ASSIGNMENT are deleted from the database.

A. Figure 3-32 shows the column characteristics for the WP COMPUTER table.

Using the column characteristics, use Access SQL to create the COMPUTER

table and its associated constraints in the WP.accdb database. Are there any

table characteristics that cannot be created in SQL? If so, what are they? Use

the Access GUI to finish setting table characteristics, if necessary.

All Sections – The Access Workbench

The required work here is to create the COMPUTER table in the DBMS of choice

using SQL. Although Access SQL has limitations, there will be no SQL limitations in

SQL Server 2016, Oracle Database XE or MySQL 5.7. The following SQL code is for

SQL Server 2016.

CREATE TABLE COMPUTER(

SerialNumber Int NOT NULL,

Make Char(12) NOT NULL,

Note that Microsoft Access will correctly modify SQL Server 2016 data types into the

appropriate Microsoft Access datatypes. In the above SQL code, the following SQL

syntax will not work in Microsoft Access:

Therefore, the SQL statement for Microsoft Access is:

CREATE TABLE COMPUTER(

SerialNumber Int NOT NULL,

Make Char(12) NOT NULL,

All Sections – The Access Workbench

B. The data for the COMPUTER table are in Figure 3-33. Use Access SQL to

enter these data into your COMPUTER table.

INSERT INTO COMPUTER VALUES(

9871234, ‘HP’, ‘ProDesk 600 G1′, ‘Intel i5-4690’, 3.50, ‘16.0 GBytes’, ‘1.0 TBytes’

All Sections – The Access Workbench

INSERT INTO COMPUTER VALUES(

9871240, ‘HP’, ‘ProDesk 600 G1′, ‘Intel i5-4690’, 3.50, ‘16.0 GBytes’, ‘1.0 TBytes’

);

INSERT INTO COMPUTER VALUES(

9871241, ‘HP’, ‘ProDesk 600 G1′, ‘Intel i5-4690’, 3.50, ‘16.0 GBytes’, ‘1.0 TBytes’

);

INSERT INTO COMPUTER VALUES(

6541006, ‘Dell’, ‘OptiPlex 7040’, ‘Intel i7-6700’, 3.40, ‘32.0 GBytes’, ‘2.0 TBytes’

);

INSERT INTO COMPUTER VALUES(

6541007, ‘Dell’, ‘OptiPlex 7040’, ‘Intel i7-6700’, 3.40, ‘32.0 GBytes’, ‘2.0 TBytes’

);

INSERT INTO COMPUTER VALUES(

6541008, ‘Dell’, ‘OptiPlex 7040’, ‘Intel i7-6700’, 3.40, ‘32.0 GBytes’, ‘2.0 TBytes’

The result of this query is shown on the next page

All Sections – The Access Workbench

C. Figure 3-25 shows the column characteristics for the WP

COMPUTER_ASSIGNMENT table. Using the column characteristics, use

Access SQL to create the COMPUTER_ASSIGNMENT table and the

associated constraints in the WP.accdb database. Are there any table or

relationship settings or characteristics that cannot be created in SQL? If so,

what are they? Use the Access GUI to finish setting table characteristics and

relationship settings, if necessary.

The required work here is to create the COMPUTER_ASSIGNMENT table in the