Assessment Details and Submission Guidelines
Unit Code MN405
Unit Title Data and Information Management
Assessment Type Assignment 2 (T1 2017)
Assessment Title Model Development and implementation
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 11
Description of this assignment: This assignment is to develop a data models, map and implement a Database System in a standard development environment to gain understanding of data model development and implementation
Submission Guidelines • All work must be submitted on Moodle by the due date along with a completed Assignment Cover Page.
• 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:
• 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.
The officials at Frankston Airport have decided that all information related to the airport should be organized using a DBMS, and you’ve been hired to design the database. Your first task is to organize the information about all the airplanes that are stationed and maintained at the airport. The relevant information is as follows:
User view 1 requirement: Every airplane has a registration number, and each airplane is of a specific model.
User view 2 requirement: The airport accommodates a number of airplane models, and each model is identified by a model number (e.g., DC-10) and has a capacity and a weight.
User view 3 requirement: A number of technicians work at the airport. You need to store the name, SSN, address, phone number, and salary of each technician.
User view 4 requirement : Each technician is an expert on one or more plane model(s), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded.
User view 5 requirement : Engine tests on a plane must be conducted by a technician who is an expert on that model.
User view 6 Requirement : Traffic controllers must have an annual medical examination. For each traffic controller, you must store the date of the most recent exam.
User view 7 requirement: All airport employees (including technicians) belong to a union. You must store the union membership number of each employee. You can assume that each employee is uniquely identified by the social security number.
a. Identify the entities in given business case above.
b. Identify suitable attributes for all entities identified in the above (a)
c. What are the statements which describe the relationships between entities? List all the statements in the business case.
d. Represent the structure of your database visually by using the entity-relationship (E-R) diagram.
2. Build this model using MS Access by creating these tables and Relationships in Access. Populate these tables with appropriate data, at least 3 records in each table. Include:
a. Relationship diagram created in Access. (Select database tools ?Relationships in the Access menu)
b. Data sheet view and design view of your tables.
3. Determine the functional Dependencies that exist in the following table and convert it to an equivalent collection of tables that are in third normal form.
Patient (PatientNum, HouseholdNum, PatientName, Street, City, State,
PostalCode, Balance,( ServiceCode, Description, Fee, date)
Marks are allocated for each part as above:
Marking Rubric for Assignment 1 –Part B: Total Marks 60
Excellent Very Good Good Satisfactory Unsatisfactory
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.
Demonstrated excellent ability to think critically Demonstrated good 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
Logic is clear and easy to follow Consistency logical and convincing Mostly consistent logical and convincing Adequate cohesion and conviction Argument is confused and disjointed
All elements are present and very well integrated. Components present with good cohesive Components present and mostly well integrated Most components present Report lacks structure.
Excellent outstanding effort made Extremely good effort Good effort made but not outstanding Made some effort. Very little attempt.
Lazy effort with inaccuracies