Assessment Details and Submission Guidelines
Unit Code MN405
Unit Title Data and Information Management
Assessment Type Assignment 1 (T1 2017)
Assessment Title Managing Data in Databases
Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop skills in managing data in databases and to gain understanding of data model development and implementation using a commercially available database management system development tool.
On completion of this assignment students will be able to:
a. Model organisational information requirements using conceptual data modelling techniques.
b. Convert the conceptual data models into relational data model
Weight 60 Marks
Total Marks 20% of total assessment for the unit
Word limit No specific word limit
Due Date Lab class week 8
Description of this assignment: Write SQL Queries to extract data from data tables
Submission Guidelines • All work must be submitted on Moodle by the due date.
• The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2.5 cm margins on all four sides of your page with appropriate section headings.
• Reference sources must be cited in the text of the report, and listed appropriately at the end in a reference list using IEEE referencing style.
Extension • If an extension of time to submit work is required, a Special Consideration Application must be submitted directly to the School's Administration Officer, in Melbourne on Level 6 or in Sydney on Level 7. You must submit this application three working days prior to the due date of the assignment. Further information is available at:
http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/specialconsiderationdeferment
Academic Misconduct
• Academic Misconduct is a serious offence. Depending on the seriousness of the case, penalties can vary from a written warning or zero marks to exclusion from the course or rescinding the degree. Students should make themselves familiar with the full policy and procedure available at: http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure. For further information, please refer to the Academic Integrity Section in your Unit Description.
Assignment Description
Total - 60 marks
1. ‘SmartProjects’ keeps track of their employees and sales projects. There are four relations/tables in their database describing Employee details, details of projects, work assigned for each employee and department, namely, EMPLOYEE, PROJECT, WORK and DEPARTMENT. The relationship diagram for these relations are given below.

a. Implement above database on MS Access. You are required to create the above 4 tables with the given attributes. Name the database as ‘SmartProjects’ .
(5 Marks)
b. Enter data to the database you created in (a) above. Here you have to enter data into all 4 tables, using the sample data given in the following tables.
(10 Marks)

Write SQL queries for the following questions and execute the queries on the ‘SmartProjects’ database you created above using MS Access.
c. How many Projects have a Project Budget greater than $85,000.
(05 Marks)
d. Display the First Name, Last Name of all employees with a salary greater than the average salary.
(10 Marks)
e. Find the First Name, Last Name, Department No and salary of employees who are assigned to Project N2.
(10 Marks)
f. Find details of employees attached to the Computer Science department.
(10 Marks)
2. Determine the Functional Dependencies that exist in the Student table given below.
Student (no, Sname, courseno, course_name, address, course_instructorID, InstructorName, Instructor_office)
(10 Marks)
Marking criteria:
Marks are allocated for each part as above:
Marking Rubric for Assignment 1 – Total Marks 60
Excellent Very Good Good Satisfactory Unsatisfactory
Query writing
skills Evidence of accurate and well-
written queries Evidence of good query writing skills. Generally relevant. Displayed reasonable query writing skills. Demonstrated little evidence of understanding the topic.
Identification
Demonstrated excellent ability to think critically. Demonstrated an ability to think critically. Demonstrated reasonable ability to think. Demonstrated some ability to think critically but not complete. Did not demonstrate ability to think critically.
Analyse
Logic is clear and easy to follow. Consistency logical and convincing. Mostly consistent logical and convincing. Adequate cohesion and conviction. Arguments are confused and disjointed.
Design /Presentation
All elements are present and very well integrated. Most of the components present and integrated. Components present and mostly well integrated. Most components present. Report lacks structure.
Evaluate/
justification
Excellent outstanding effort made. Extremely good effort. Good effort made but not outstanding. Made some effort. Very little attempt.
Lazy effort with inaccuracies.