Reference no: EM131241635

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?