INFO90002 Assignment 1 ER Modelling
Date Due: Thursday 6th February 09:00 AEDT 2020 Weighting: 20% of your total assessment
Case Study: California Motor Vehicle Emissions & Safety
California is the 5th largest economy in the world and the law makers believe they have a leading role to play in reducing greenhouse gas emissions and saving the world. The state of California needs to create a database to store the emissions and safety ratings of every passenger vehicle that is registered in California. This includes buses, minibuses, and cars and includes vehicles that are electric, hybrid, diesel and gasoline (petrol).
Emissions
All motovehicles registered, sold and re-sold in California require a traffic light rating displayed on their windscreens. To support this initiative, California legislators have introduced a three tier surcharge on annual registration on vehicles.
A “green light” label indicates low or zero emissions and currently attracts no surcharge. An “amber light” indicates emissions that are moderate and will attract a 10% surcharge on annual vehicle registration. A “red light” indicates high emissions and a 25% surcharge on annual vehicle registration. The categorization of vehicle emissions as “red”, “amber” or “green” is calculated from information defined in the Californian legislation. This emissions formula is not expected to change in the future.
Next to the traffic light label a summary of important emission standards must be provided:
• The emissions of Total Hydrocarbons (THC) in grams per kilometre of travel (g/km);
• The emissions of Carbon Monoxide (CO) in grams per kilometre of travel (g/km);
• The emissions of Non Methane Hydrocarbons (NMHC) in grams per kilometre of
travel (g/km);
• The emissions of Nitrogen Oxides (NOx) in grams per kilometre of travel (g/km);
• The emissions of total Particulate Matter (PM) in grams per kilometre of travel
(g/km)
INFO90002 A1 SUM-2020 1
Figure 1: An example of Traffic Light Label as required by the Californian legislation.
The State of California also requires vehicle emission facts to be displayed on all forms of advertising including brochures, online advertising, printed advertising, television commercials and social media posts promoting the make and model.
The following information must be displayed in the advertising:
• The make of the passenger vehicle;
• The model of the passenger vehicle;
• The model’s seating capacity;
• The model’s fuel tank capacity;
• The model’s fuel economy per 100km;
• The model’s total NOx emissions per 100km
• The model’s THC emissions per 100km
• The model’s Non Methane Hydrocarbons (NMHC) per 100km
• The model’s Particulate Matter (PM) per 100km
INFO90002 A1 SUM-2020 2
An example of the advertisement brochure is shown in Figure 2 (see below).
Figure 2. A demonstration label used in promotional material
There must be a description identifying the registration surcharge for moderate and higher emitting vehicles (For example, “This vehicle attracts a 25% surcharge”).
There are other details which must be recorded including:
• The majority country of origin where the model was manufactured (e.g. “Made in South Korea”, “Made in Spain”). This may change over time as vehicle manufacturers have a global supply chain;
• Website address related to the vehicle model and manufacturer;
• Maximum capacity of passengers;
• A QR code to be scanned for obtaining the publicly available documentation of the
mode’s emissions testing documentation as required by the State of California.
INFO90002 A1 SUM-2020 3
Safety Standards
As well as storing information about vehicle emissions the database needs to store information about the safety features of all new cars sold. Features are classified into four categories:
• Adult Occupants
• Child Occupants
• Pedestrian Safety
• Safety Assist (Overall vehicle safety category)
Within each category there are multiple features. A vehicle either meets, fails to meet, or is absent. A feature that fails to meet the standard means the vehicle has this feature, but it doesn’t meet the safety standard required by the Californian legislation.
The safety features are classified in the table below
Safety Category
Safety Feature
Adult Occupant
Frontal Airbag
Knee Airbag
Seat Belt Pretensioner Seat Belt Load Limiter Whiplash Rear Impact Side Head Airbag
Side Chest Airbag
Side Pelvis Airbag
Child Occupant
Manufacturer Fitted Child Restraint Anchors Three point seat belts for row-three seats Airbag Cut-off Switch
Pedestrian Safety
Head Impact
Pelvis Impact
Legs Impact
Infrared Collision Alert
General Safety
Seatbelt warning Timed Seatbelt warning Persistent First Aid Kit
Headlight Lamp failure Taillight Lamp failure Caribou roll test result Electronic Stability Assist Cruise Control
Table 1. List of safety features in each safety category
INFO90002 A1 SUM-2020 4
The OLTP Relational Database
It is important that your OLTP relational database design be able to easily answer queries similar to those listed below.
What NOx values would have appeared on a label for a 2006 Subaru 2.0 litre gasoline Forrester compared to the 2019 model?
What year and what model of the Honda Jazz electric motor car introduce infrared collision alert?
List the vehicles that pass all safety attributes in all safety categories
How many new Mercedes Benz motor cars were added to the database in 2017?
Which bus model and make has the highest values for THC (Total Hydrocarbons)?
How many vehicles have three rows of seats?
What is the maximum number of owners for the one vehicle?
What is the cost of registration including the surcharge for a 2019 Ford Mustang GT?
If your model can’t answer these questions, think about reviewing your physical ER model.
The state of California would prefer the solution to be flexible to accommodate reasonable changes to the safety and emission laws as global emissions standards are reviewed and as vehicle safety standards are revised and improved. Please ensure your design is flexible.
INFO90002 A1 SUM-2020 5
Instructions:
Assignment 1 is a group assignment worth 20% of your final mark. Groups must be 3 members. Groups with four or more members will be penalised 10% of their total assignment mark. The assignment will be graded out of 200 marks as described in the table below:
Table 2. List of Items required for Assignment 1 assessment
You are to analyse the The Californian Motor Vehicle emissions and safety case study and design a Conceptual Model in Chen notation, a Data Dictionary, Physical ER Model for a MySQL Relational Database in Crow’s foot notation modelled with MySQL Workbench.
You may list any assumptions you have made about the model on a separate page. There is a 100 word limit for assumptions. Assumptions must not be used to simplify the assignment, but only to justify your decision about any ambiguity in the Case Study.
As part of your submission you must submit your final MySQL Workbench .mwb file of your Physical ER Model.
As part of your submission you must submit a work breakdown. This identifies the work and effort that each team member did on the assignment.
If you are uncertain about any aspect of the handout seek clarification via Canvas, in class, or at a Student Consultation
An Assignment Hurdle means you will must submit this information to pass the assignment. If you do not you will be awarded a maximum grade of 100/200 (i.e. 50%).
NOTE: You must submit items 1,2,3 & 4 as a single PDF document.
For help on how to create and submit a single PDF file please check the LMS
Please ensure all team member names and student ID are on the pdf and mwb files
Assessment
This assignment is worth 20% of your marks in INFO90002
Your work will be assessed according to its correctness and completeness. ‘Correctness’ refers to whether your model and data dictionary adhere to the required rules and syntax. ‘Completeness’ refers to whether the model includes all of the required entities and relationships and can handle the business needs, and data storage problems specified, and whether the data dictionary adequately explain the entities modelled.
Item 1
Conceptual Model in Chen notation
50 marks
Item 2
Data Dictionary
50 marks
Item 3
ER Physical Model
100 marks
Item 4
Team Member Work breakdown
Assignment Hurdle
Item 5
Soft copy of your .mwb (workbench) file
Assignment Hurdle
INFO90002 A1 SUM-2020 6
Assignment Submission:
You are to submit the assignment under the Assessment tab on LMS. Each section of the LMS Submission is clearly labelled and colour coded in LMS for your convenience. Submit the following two files:
ONE PDF document named as your Group number id (e.g. Group20.pdf), containing: Legible image of your Chen Conceptual Model (hand drawn is OK)
legible image of your Physical ER Model
your data dictionary,
your assumptions (if you stated any) work breakdown per team member
NOTE: If you fail to submit legible models you will be penalised 10% of your total grade for this assignment.
Your final .mwb MySQL Workbench file of the Physical ER model. Submit your .mwb file under the mwb submission link.
Assignments that are late will attract a penalty of 10% for each Academic Day as per the CIS school policy.
Subject Hurdle
To pass INFO90002 you must pass two hurdles. First you must obtain a mark of 15/30 or higher for both assignments (Assignment 1 20%, Assignment 2 10%) AND obtain a mark of 35/70 or higher for the exam.
Alice’s team obtains 23/30 for assignments 1 & 2, and Alice obtains 52/70 for the exam. Alice’s final grade is 85/100 H1.
Bob’s team obtained 29/30 for the assignments and Bob obtained 34/70 for the exam. Bob’s final grade is NH 49 (hurdle fail). Despite the fact that his grade is 63/100.
Carol did not submit any assignments 0/30 and obtained 63/70 for the exam. Carol fails the subject with a NH 49 (hurdle fail) despite a final grade of 69.
Be sure you submit all assignments and attempt every question on the final exam to optimise your chance of passing INFO90002
INFO90002 A1 SUM-2020 7
Frequently Asked Questions
1. Do I have to store the labels in the database? A: No. You have to store the information that can be printed on the labels.
2. Do all images and links need to be stored in the database? A: YES. As a database designer you are required to store every attribute you think is required inside the database.
3. How do I submit a High Resolution image of our conceptual and physical design? A: We recommend using an A3 page size in MySQL Workbench Modeller and exporting the image as a PDF
4. How do I join my four five different documents together? A: Save all your documents as PDFs and then merge each PDF into a master document. (nb: Uploading to a single Google doc is also an excellent way to merge multiple documents)
5. How do I make MySQL workbench show PFK’s (Primary Foreign Keys) A: You can’t. This was a bug introduced into 5.7 of Workbench on Mac which now affects all releases of 8.0.x of MySQL Workbench. Markers are well aware of the issue and know what to look for. We have logged a bug with the development team at MySQL
Group Work Advice
Industry expects our Master’s graduates to be able to work and communicate effectively in groups. This is why the University includes group work assessment in the majority of graduate classes.
When you form your team immediately decide the following:
1. How you will communicate to each other
2. How often you will communicate
3. How often you will meet as a group
4. We strongly recommend meeting face to face. It means there is less chance for miscommunication and is 30 times more effective than any online communication.
5. Agree on a communication escalation path
a. E.G. WhatsApp – then if no response, SMS then if no response, email then if no
response phone call, then if no response speak to the Subject Coordinator
6. Work out each team members strengths and weaknesses. Assign tasks based on
strengths.
7. Agree on a timetable or gant chart of tasks and deadline dates.
8. Pick someone to be the team leader/coordinator of your team. They will have
responsibility to do their own work and follow up with other team members to make
sure they are doing theirs.
9. Although unlikely in an odd numbered team, work out how you will break a deadlock
before you need to break a deadlock.
10. Teams from a variety of cultural, age, gender, socio-economic and educational
backgrounds do better than homogenous teams. Mix it up to avoid group think and the
same cognitive biases in team members.
11. Team Problems? Escalate to the Subject Coordinator EARLY so it doesn’t mean anger,
tears and regret later.
12. Failure to plan is a plan to fail. Don’t fail. PLAN.
GOOD LUCK!
INFO90002 A1 SUM-2020 8
The University of Melbourne
School of Computing and Information Systems Assignment Cover Sheet
* To protect privacy, student numbers should not be shared with other students. Please list names as shown on student cards.
PLAGIARISM
Plagiarism is the presentation by a student of an assignment which has in fact been copied in whole or in partfrom another student’s work, or from any other source (e.g. published books, periodicals, or the web) without due acknowledgement in the text.
COLLUSION
Collusion is the presentation by a student of an assignment as his or her own which is in fact the result in whole or in part of unauthorized collaboration with another person or persons.
Before submitting our assignment, we have:
1. Made a copy of the assignment and of any
material submitted with the assignment
2. Ensured that our assignment and any material
submitted are clearly identified.
3. Retained a copy of the email submission of this
assignment (if appropriate).
4. Attached all files and required material to the email (if appropriate).
Declaration
Please tick to indicate that you understand the following statements. We declare that:
̈ This assignment is our group’s own original work, except where we have appropriately cited the original source.
̈ This assignment has not previously been submitted for assessment in this or any other subject.
For the purposes of assessment, we give the assessor of this assignment the permission to:
̈ Reproduce this assignment and provide a copy to another member of staff; and
̈ Take steps to authenticate the assignment, including communicating a copy of this assignment to a checking service (which may retain a copy of the assignment on its database for future plagiarism checking).
Note: For electronic submissions the signatures may be typed. Also, unless otherwise indicated, it will be assumed that all group members made an equal contribution to the overall effort. If a dispute arises, thematter should be reported to the lecturer-in-charge for consideration.
Signatures
Date
Contribution
1
2
3
INFO90002 A1 SUM-2020 9