Assessment 2 – Database Application Part A – Database Modelling and Normalization P/HE/F/3/040a Edition 3 Page 1 of 1 6 August 2015 Higher Education Assignment Cover Sheet Student ID: Surname: First Name: Course Name: Subject Code: Subject Name: Lecturer’s Name: Assessment Title: Due Date: 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 A – Database Modelling and Normalization P/HE/F/3/040a Edition 3 Page 1 of 1 6 August 2015 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 This represents the data as unnormalised and redundancy exists throughout the table. The operations (Business Rules) can be summarised as follows:Assessment 2 – Database Application Part A – Database Modelling and Normalization P/HE/F/3/040a Edition 3 Page 1 of 1 6 August 2015  The company manages many projects.  Each project requires the services of many employees.  An employee may be assigned to several different projects.  Some employees are not assigned and perform duties not specifically related to a project. Some employees are part of a labour pool, to be shared by all project team. For example, the company’s executive secretary would not be assigned to any one particular project.  Each employee has a single primary job classification. This job classification determines the hourly billing rate.  Many employees can have the same job classification. For example, the company employs more than one electrical engineer. TASKS 1. Draw an Entity Relationship (ER) Diagram for the database. Do show attributes on this diagram. Ensure the ER Diagram addresses all the Business Rules above. 6 marks 2. Construct a data dictionary containing: - Data Stores - Data Elements 3 marks 3. Answer the following questions: (a) What is functional dependence and how does it relate to your solution? (b) Why is it important to find the natural structure of data? (c) For the report, provide the unnormalised representation? (Hint: Refer to the workbook for further information) (d) Why would we use normalisation and how is it accomplished? 6 marks