CSCI A114 / INFO I111
Practical Two

First semester 2000-2001

You need to develop an example application for a fictitious business that we call Dependable Database Developers (or D3 for short). The business constructs database applications for its customers, billing the customer for the time spent in developing each application. The background of this process can be taken from your textbook (Sengupta & Groth, chapter 6).

Step 0: Here's the entity relationship diagram:

Step 1: Use Access to define a database "Second Practical" that contains the required tables.

The description of the tables should be clear from the data presented at step 2. All tables (with the exception EMPLOYEE) will have their first columns (tableName_id) contain the primary key (auto generated). For EMPLOYEE we use the regular IDs of our technicians at Bavant.
Step 2: Add data to the tables.

CUSTOMER table
customer_id customer_name customer_address
1 AD57 314 Central, Burton, MI 49611
2 BL72 281 Robin, Burton, MI 49611
3 EL25 462 River, Torino, MI 48268
4 FB96 36 Bayview, Cavela, MI 47926
5 FM22 283 Waterfront, Burton, MI 49611
6 PM34 27 Lake, Masondale, 49832
7 TR72 92 East Bay, Woodview, MI 47212

PROJECT table
project_id project_name project_desc customer_id
1 A21 3 1
2 B14 4 1
3 129 1 2
4 146 2 2
5 11A 5 3
6 15A 4 3
7 43B 2 3
8 79 2 4
9 A21 6 5
10 D14 8 5
11 D31 3 5
12 56 8 6
13 88 2 6
14 B11 5 7
15 B15 7 7

EMPLOYEE table
employee_id last_name first_name
23 Anderson Trista
36 Nichols Ashton
49 Gomez Teresa

CONTRACT table
contract_id project_id employee_id billing_rate
1 1 23 24.00
2 2 23 24.00
3 3 36 21.00
4 4 36 21.00
5 5 49 22.00
6 6 49 22.00
7 7 49 22.00
8 8 23 24.00
9 9 36 21.00
10 10 36 21.00
11 11 36 21.00
12 12 49 22.00
13 13 49 22.00
14 14 36 21.00
15 15 36 21.00

TIMESHEET table
timesheet_id contract_id work_date work_hours
1 1 10/1/2000 2
2 5 10/9/2000 2
3 8 10/7/2000 1
4 11 10/3/2000 1
5 13 10/10/2000 2
6 15 10/14/2000 3

Step 3: Write the SQL for the following three queries.

  1. Query1: List total hours spent on projects for each customer.
  2. Query2: Calculate total amount each customer has to pay (number of hours times rate).
  3. Query3: List names of those customers for which the employees have already worked more than 6 hours.
Step 4: Define a macro for each one of the queries (that opens the query)

  1. Macro1: Opens Query1 in Datasheet View
  2. Macro2: Opens Query2 in Datasheet View
  3. Macro3: Opens Query3 in Datasheet View
Step 5: Define a switchboard that always appears at startup, and has four items as described below.

  1. Item1: Runs Macro1
  2. Item2: Runs Macro2
  3. Item3: Runs Macro3
  4. Item4: Exit Application
Save and turn this in at the latest on Friday at 6pm. It is recommended that you turn this in during your last lab this week, if possible, if not I will be in LH016 5-6pm Friday. For macros and switchboards please check lab notes 11 (eleven). For SQL refer to lab notes 14 (fourteen) and your text.

This exercise adapted from Sengupta and Groth chapter 6

Last updated: October 18, 2000 by Adrian German for A114/I111