PROJECT
A construction company requires a database to record details about building projects. Each project has its own project number, name and employees assigned to it. Each employee has an employee number, name and job classification, such as engineer or computer technician.
The company charges its clients by billing the hours spent on each contract. The hourly billing rate is dependent upon the employee's position. For example, one hour of a computer technician's time is billed at a different rate than one hour of an engineer's time.
The first step is to examine the data provided in the report below, which contains the relevant information.
|
Proj No
|
Project Name
|
Emp No
|
Employee Name
|
Job Class
|
Charge/ Hour
|
Hours Billed
|
|
15
|
Evergreen
|
103
|
June Arbough
|
Electrical Engineer
|
$84.50
|
23.8
|
|
|
|
101
|
John New
|
Database Designer
|
$105.00
|
19.4
|
|
|
|
105
|
Alice Johnson
|
Database Designer
|
$105.00
|
35.7
|
|
|
|
106
|
Bill Smithfield
|
Programmer
|
$37.75
|
12.6
|
|
|
|
102
|
David Senior
|
System Analyst
|
$96.75
|
23.8
|
|
18
|
Amber Wave
|
114
|
Annelise Jones
|
Application Designer
|
$48.10
|
24.6
|
|
|
|
118
|
James Frommer
|
General Support
|
$18.36
|
45.3
|
|
|
|
104
|
Anne Romares
|
System Analyst
|
$96.75
|
32.4
|
|
|
|
112
|
Darlene Smithson
|
System Designer
|
$45.95
|
44.0
|
|
22
|
Rolling Tide
|
105
|
Alice Johnson
|
Database Designer
|
$105.00
|
64.7
|
|
|
|
104
|
Anne Romares
|
System Analyst
|
$96.75
|
48.4
|
|
|
|
113
|
Dilbert Joenbrood
|
Application Designer
|
$48.10
|
23.6
|
|
|
|
111
|
Geoff Wabash
|
Clerical Support
|
$26.87
|
22.0
|
|
|
|
106
|
Bill Smithfield
|
Programmer
|
$37.75
|
12.8
|
|
25
|
Star Flight
|
107
|
Maria Alonzo
|
Programmer
|
$37.75
|
24.6
|
|
|
|
115
|
Travis Bawanyi
|
System Analyst
|
$96.75
|
45.8
|
|
|
|
101
|
John New
|
Database Designer
|
$105.00
|
56.3
|
|
|
|
114
|
Annelise Jones
|
Application Designer
|
$48.10
|
33.1
|
|
|
|
108
|
Ralph Washington
|
System Analyst
|
$96.75
|
23.6
|
|
|
|
118
|
James Frommer
|
General Support
|
$18.36
|
30.5
|
|
|
|
112
|
Darlene Smithson
|
System Designer
|
$45.95
|
41.4
|
TASKS
1. Using your ER Diagram from Assessment 1, build the application using Access to:
(a) CREATE TABLE for every table (entity) represented on the ER Diagram
(b) Insert one record from the table above into the table (entity) represented on the ER Diagram
2. Manipulate the data with the database you have created and perform the following queries:
PART A
(a) Retrieve all the data in the Project table
(b) Retrieve all the data from the Job Class table
(c) Retrieve the charge hour for the Database Designer
(d) List all the employees in alphabetical order (ASC)
(e) List the charge hours that are greater than $75 per hour and the Job Classes
(f) List all the Job Classes (do not show duplicates if they exist)
PART B
(a) List all the Job Class in the Charge Hour range of $45 and $85
(b) Find all projects with "'t" in their name
(c) Show all the employees who work on the Amber Wave Project
(d) Show which employee is a Database Designer
(e) Show which projects bill hours between 37 and 55 hours
(f) How many employees are associated with each project
(g) What is the smallest and largest individual for Charge Hour for Job Class (2 marks)
PART C
(a) How many projects do John New and Alice Johnson work on
(b) Which Job Class is the second highest Charge Hour in the table
(c) Which Project does not utilise a Application Designer
(d) Identify the employees who are General Support and which project have they been associated with?