Reference no: EM132518645
BIT231 Database Systems Assignment - Melbourne Polytechnic
Assessment - Software Application
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
|
Assessment Tasks
Question 1:
Build a database/library using SAS PROC SQL
? Create a new library with last 3 digits of your Student ID
? Import all data from Project Schema document (Download it from Moodle - Will be available in Week 9) into SAS Studio to run the queries in Question 2. Due in Week 10. Show to your lecturer that you have imported the schema in SAS.TASKS
Question 2:
Manipulate the data with the database you have created in Question 1 and perform the following queries:
PART A
1. Retrieve all the data in the Project table
2. Retrieve all the data from the Job Class
3. Retrieve the charge hour for the Mechanical Engineer
4. List all the employees in alphabetical order (DSC)
5. List the charge hours that are greater than $75 per hour and the Job Classes
6. List all the Job Classes (do not show duplicates if they exist)
PART B
1. List all the Job Class in the Charge Hour range of $55 and $95
2. Find all projects with "e" in their name
3. Show all the employees who work on the Amber Wave Project
4. Show which employee is a Database Designer
5. Show which projects bill hours between 45 and 100 hours
6. How many employees are associated with each project?
7. What is the smallest and largest individual for Charge Hour for Job Class?
PART C
1. How many projects do Jones Annelise and Ramoras Anne work on?
2. Which Job Class is the second highest Charge Hour in the table?
3. Which Project does not utilise an Application Designer?
4. Identify the employees who are Database Designer and which project have they been associated with?
Attachment:- Software Application.rar
|
Make journal entries to record acquisition by swan river
: Swan River Ltd, Make journal entries to record the acquisition by Swan River Ltd, assuming that The cost of acquisition was $144,000 cash.
|
|
Confidence interval for the proportion
: Construct a 95% confidence interval for the proportion of 12th grade females in the population who always use a seatbelt when driving
|
|
Demonstrate what is the amount of payment
: An item retailing for P10,000, subject to a trade discount of 25%, is paid for within the discount period on terms of 2/10, n/30. What is the amount of payment?
|
|
Discuss on federated systems
: Discuss on federated systems. Use at least three sources. Include total of 3 quotes in each paragraph from your sources enclosed in quotation marks
|
|
How many employees are associated with each project
: How many employees are associated with each project - What is the smallest and largest individual for Charge Hour for Job Class
|
|
What would be a fair price to charge clients
: A detective figures that he has a one in nine chance of recovering stolen property. His out-of-pockets expenses for the investigation are $9,000.
|
|
Provide comment on financial performance for the period
: Was it getting better / worse compare to 2018? Comment and provide some reasons of why the performance in 2019 had been better or worse
|
|
How many ways are there to distribute a red ball
: How many ways are there to distribute a red ball, a yellow ball, a green ball, a blue ball, a purple ball and 3 identical white balls
|
|
How much will be in the account in twenty-five years
: If you deposit $500 into an account at the end of each month that pays 6% compounded monthly, how much will be in the account in 25 years?
|