Using the SQL DBMS of your choice, complete steps A through E in exercise AW.3.3, but ex...

Using the SQL DBMS of your choice, complete steps A through E in exercise AW.3.3, but exclude step F.

Reference Exercise AW.3.3

In the “Access Workbench Exercises” in Chapters 1 and 2, you created a database for the Wedgewood Pacific Corporation (WPC) of Seattle, Washington. In this set of exercises, you’ll:

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

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

• Create tables and relationships by using Access SQL.

• Populate tables by using Access SQL.

WPC 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:

EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)

COMPUTER (SerialNumber, Make, Model, ProcessorType, ProcessorSpeed, MainMemory, DiskSize)

COMPUTER_ASSIGNMENT (SerialNumber, EmployeeNumber, DateAssigned, DateReassigned)

The referential integrity constraints are:

Serial Number in COMPUTER_ASSIGNMENT must exist in SerialNumber in COMPUTER

EmployeeNumber in COMPUTER_ASSIGNMENT must exist in EmployeeNumber in EMPLOYEE

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-23 shows the column characteristics for the WPC COMPUTER table. Using the column characteristics, use Access SQL to create the COMPUTER table and its associated constraints in the WPC.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.

B. The data for the COMPUTER table are in Figure 3-24. Use Access SQL to enter these data into your COMPUTER table.

C. Figure 3-25 shows the column characteristics for the WPC COMPUTER_ ASSIGNMENT table. Using the column characteristics, use Access SQL to create the COMPUTER_ASSIGNMENT table and the associated constraints in the WPC.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.

D. The data for the COMPUTER_ASSIGNMENT table are in Figure 3-26. Use Access SQL to enter these data into your COMPUTER_ASSIGNMENT table.

E. Who is currently using which computer at WPC? Create an appropriate SQL query to answer this question. Show SerialNumber, Make, and Model. Show EmployeeID, LastName, FirstName, Department, and Employee Phone. Sort first by Department and then by employee LastName. Save this query using the query naming rules in exercise AW.3.1.

F. Who is currently using which computer at WPC? Create an appropriate QBE query to answer this question. Show SerialNumber, Make, Model, ProcessorType, and ProcessorSpeed. Show the EmployeeID, LastName, FirstName, Department, and Employee Phone. Sort first by Department and then by employee LastName. Save this query using the query naming rules in exercise AW.3.2.