SAssessment 2 – Database Application Part B – Database Creation and SQL Query Page 1 of 3 Higher Education Assignment Cover Sheet Student ID: Surname: First Name: Course Name: Subject Code: Subject Name: Lecturer’s Name: Assessment Title: Due Date: Prahran: Preston: Date submitted: Definitions: Academic Fraud Form of misconduct that enables a student(s) to obtain an unfair academic or general advantage through false representation. Cheating Behaviour which is engaged in by a student or another person on behalf of a student (and / or students or persons acting in collusion) to provide that student or group of students with an academic advantage to which that student or group of students is not entitled. Plagiarism The use of all or part of another person or entity's work without appropriate acknowledgment of the author or source. Collusion Agreement between individuals (students / other persons) to act together secretly or without permission to achieve an unfair advantage. Refer to the Higher Education Academic Integrity Policy and Institute Rule: Student Discipline Rule for further information. Privacy Statement: The information on this form is collected for the primary purpose of assessing your assignment. Other purposes of collection include recording your plagiarism and collusion declaration, attending to administrative matters and statistical analyses. You have a right to access personal information that Melbourne Polytechnic holds about you, subject to any exceptions in relevant legislation. Refer to the Student Privacy Statement. Late Submission: Assessment items submitted after the due date without an approved extension and / or special consideration will be penalised 5% per day off the mark awarded for a total of ten (10) working days. If the item is submitted after this time or not submitted, the item will not be marked and a fail grade awarded. Higher Education Assessment Policy P/HE/D/3/040 Section 9. Submission of Assessment. Student Declaration: I understand that:  If there is any doubt as to the authenticity of any piece of my assessment then I can be orally examined.  Assignments without cover sheets will not be marked.  Cheating, plagiarism and other acts of academic misconduct are subject to Melbourne Polytechnic disciplinary action.  Penalties apply for late submission of assessment and that this could result in a fail grade being awarded. Student Signature: Date: ----------------------------------------------------------------------------------------------------------------- Assignment Receipt If you require a receipt for your assignment please complete the section below and have it signed/stamped at the Department Office. Student Name: Student Id: Subject: Assessment Title:Assessment 2 – Database Application Part B – Database Creation and SQL Query Page 2 of 3 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.4Assessment 2 – Database Application Part B – Database Creation and SQL Query Page 3 of 3 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 12 marks (b) Insert one record from the table above into the table (entity) represented on the ER Diagram 4 marks 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) 3 marks 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) 8 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? 8 marks