ACCESS WORKBENCH EXERCISES Complete A through L under AW.3.1 using either the QBE or SQL querying method. Your choice.

Name queries using 'qry_ItemLetter'. For example, for item A. on page 239 use qry_A.

Check your answers: Be sure the tables used in the query statement have the correct set relationships (joins) between tables. This is especially true for query E, F, G, and H.

As a general rule, the tables used in the query statements need to be related (joined).

qry H = 4 records

qry_I = 2 qry_J = 270 qry_K = 135 qry_L = Accounting 1, Finance 2, Production 2, 'Sales and Marketing' 2

12 queries (A through L).

240 Part 1 Database Fundamentals K. Whai is the average MaxHours of projes being run by the Sales and Markering department Be sure to assign an appropriate colunn naume to the computed FIGURB Databa resulrs L. How many projects are being u by each departmen? Be sure to display cach DepartmentName and to assigi an appropriate column name to the computed Colu Scria Mak AW.3.2 Using Access QBE, create and run new queries to answer the questions in exer cise AW.3.1. Save each query using the query name for,uat QBEQuery-AWE-3 !-## where the HH sign is replacod by the letler designator of the question. For example, the first query Mod Proo Proc Mair Disk will be saved as QBEQuery AWF-3-1-A. AW.3.3 WP has decided to keep track ol computers used by the employces. In order to do this, two new tabbles will be added to the database. The schema ior these tables, as related to dhe existing EMPLOYEE table, is inote that we are purposely excluding the recursive relationship in EMPLOYEE at this timc): FIGUR WP Da Seria EMPLOYEE (EmployceNumber, First Namc, Last.Name, Departmest, Position, Sapervisor, OfficePhone, EmailAddress) MainMemory, DiskSize) DateReassigned) COMPUTER (SerialNunber, Make, Model, ProcessorType, ProcessorSpeed COMPUTER ASSIGNMENT SerialNumber, EmploveeNumber, DateAssigned. T'he referential integrity constraints are Serial Number in COMPUTER ASSIGNMENT must exist in SerialNumber EmployeeNumber in COMPUTER ASSIGNMENT must exist in 9871 987 987 in COMPUTER EmployeeNumber in EMPLOYEE ErmployeeNnmber is a surrogate key and never changes. Employee records are never delet- ed fron the database. SerialNumber is not a siurrogate key beause it is not generated by the database. However, a computer's SerialNumber never changes, and, herefore, there is no eed to cascade updatcs. When a computer is at its end of life, the record in COMPUTER for that computer and all associated records in COMPl ITER ASSIGNMENT are delted from the database. A. Figure 3-32 shows the colurmn charactetistics ior the WP COMPUTER table. ising the columi charsctcristics, use Access SQL to create the COMPUTER tabic and its associatcd constraints in the Waccd database. Are there any table characteristics that cannot be created in SQL II so, what are they? L'se the Access GUI to finish setiu table characteristics, it nccessary these data into your COMPUTER table, ASSIGNMENT table. Using the column characteristics, use Aecess SQL to crcate B. The data lor the COMPUTER table are in Figure 3-33. Use Access SQL to euter C. ligurc 34 shows the column characteristics for the WP COMPU TER thc COMPUTER ASSIGNMENT rable and the associated constraints in the Wacedb databasc. Are there ay table ot relationship settings or charactcristics that cannot be crcated iti SOL? If so, what are they? Use the Acvess GUI to finish seiting Lable characteristics and relationship settings, if necessary D. The data for the COMPUTER_ASSIGNMENT table are in Figure 3-35, Use Access SOL to ctiter thesc data into your COMPUTER ASSIGNMENT table