INFO90002 2021 Semester 1 – ASSIGNMENT 1
Date Due:
Assignment Due: Tuesday 13th April 2021 0900H (9 a.m.) AEST
Weighting: 20% of your total assessment (75% E.R Model 25% Conceptual Model) Group Assessment: Groups of four (4) students
CASE STUDY: The Australian Telecommunications Ombudsman
The Australian Telecommunications Ombudsman keeps a record of all handheld devices used on all telecommunications networks in Australia. Telecommunications networks are provided by a range of network carriers who share the responsibility of providing Australia wide handheld device coverage. Some network carriers (eg. Optus, Telstra, Vodaphone) provide wholesale services to other network carriers (e.g. ALDI, Kogan, Dodo). About each network carrier we store their legal name, their trading name, their Australian Business Number (ABN), the postal address of their head office, and the name, phone number and email of both the chief data officer, and the nominated technical data officer.
Sim and e-sims have a unique 22 digit numerical ICCID (integrated card circuit identifier). All Australian sim or e-sim enabled handheld devices have a unique 10 digit phone number (e.g. 0412 345 678) associated with the ICCID. At any time a network carrier’s 10 digit number may be active, archived, suspended, unavailable or available. Phone numbers attached to a network carrier are attached to the sim / e-sim that is part of the handheld device (tablet, smartphone, phone). Please note some devices may have the capability to store dual sims and therefore dual numbers. Every handheld device has a 15 digit numeric IMEI (International Mobile Equipment Identity), name (e.g. “Galaxy S20” “iPhone 12 Pro Max”), make (e.g. “Samsung”, “Apple”, “Huawei”, ”OnePlus”) and model (“SM-G981”, “A2411”). Not all make and models are certified for use in Australia but maybe in use. We need to distinguish between certified and uncertified handheld devices.
Each handheld device runs an operating system (e.g. “Android”, “iOS”, “Ubuntu Touch” and others) that is supported by a vendor (e.g. “Google”, “Apple”, “Open Source”). There are many versions of an operating system , and each operating system version has many patch releases to address bug fixes and security vulnerabilities. We need to keep a record of all bugs and security vulnerabilities, the operating system versions affected, and the operating system version that fixes the bug or security vulnerability. This way we can identify at risk handheld devices and those devices that are safely patched.
We need to store the date the vulnerability/bug was identified, and the date the vulnerability/bug was fixed by the vendor, the version of the OS the patch was released in, and the date the patched OS version was released to customers. Each time a patch is released the operating system version increments (e.g. Android patch level for February 2021 was 4.9.118-19869059 was released on 22- Jan-2021 at 14:50:50 UTC). For example, for the iOS version 13 , version 13.0 was released on 3-June- 2019 and moved in increments (e.g. 13.1, 13.2 … 13.7). The terminal release of version iOS 13 (13.7) was released on 1-September-2020.
Most handheld devices support the installations of Apps. Apps can be for a variety of uses. For example Apple’s AppStore currently has fourteen App categories. Some apps may be in multiple categories (e.g. ‘Business’ and ‘Productivity’). For each app we need to record the app name ‘Facebook’, the app version (e.g. 305.1.0.40.120), the supported operating systems (e.g. Android 10), the registered developer, the developer contact name, website, email, app version release date (e.g. 9-Feb-2021), whether the app has in app purchases (e.g. $1.39 – $549.99), and whether it is suitable for adults, children or all ages. The app must provide a release note of no more than 500 words listing the enhancements and fixes in this app release.
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 1
All numbers in use by carriers and attached to a handheld device are either on a prepaid or post paid plan. We need to know the carrier’s plan name (Optus Prepaid Epic Data 45G), the plan’s terms for voice (“unlimited”) and data (20G per 30 days). We need to know the contract start date and time, the contract duration, and the contract end date and time.
We need to track the metadata for all phone calls, short message service messages (SMS) and app data use. For example, for a phone call, we need to know the number called (local, or international, fixed line or mobile), the date and time of the call, and the call duration. For SMS we need to know the numbers of the SMS recipients (again, local and international), sms character length, and the date and time the SMS was sent. Lastly, we need to know how much data each app on the device uses per calendar month for the length of any prepaid or post paid contract. Assume that out of contract active accounts have a month by month extension of the last active plan.
Due to the Australian government introducing mobile phone number portability, 10 digit mobile phone numbers can move from one network carrier to another carrier. In some cases the 10 digit mobile phone number may return to the original network carrier. We need to store the history of phone numbers attached to each network carrier.
TASK: Your group has been asked to provide:
I) A Conceptual model of the ATIO case study in Chen notation
II) A physical Entity Relationship model using Crows Foot notation suitable for a MySQL relational database version 8.0 or higher. The physical E.R model should be based on your Chen conceptual model.
III) A copy of your final workbench file ( format .mwb).
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 2
Assignment Submission:
ONE GROUP MEMBER should submit the assignment via the CANVAS LMS https://lms.unimelb.edu.au
ONE PDF document named as your Group number id (e.g. Group20.pdf) on or before Tuesday 13th April 2021 0900H (9 a.m.) AEST, containing:
• Legible image of your Conceptual Model in Chen notation
• Legible image of your Physical ER Model in Crows foot notation
• Assumptions (maximum 100 words) – your models should speak for themselves.
• Work break down per team member (measured 1-100% per team member)
• Student name and Student Number of all the students in your group
N.B. If you fail to submit legible models you will be penalised 10% of your total grade for this assignment.
ONE COPY of your team’s final MySQL Workbench modelling file (with an .mwb extension) of the Physical ER model on or before Tuesday 13th April 2021 0900H (9 a.m.) AEST. Submit your .mwb file under the mwb Assignment 1 submission link.
Late Submissions
Assignments that are late without a formal granted deadline extension from the subject coordinator will attract a penalty of 10% for each Academic Day as per the School of Computing and Information Systems policy.
Subject Hurdle
To pass INFO90002 you must pass two hurdles. First you must obtain a mark of 25/50 or higher for the assignment and three quizzes (Assignment 1 20%, and the three Quizzes – each 10% = 30%) AND obtain a mark of 25/50 or higher for the end of semester assessment (exam).
Examples:
Alice’s team obtains 18/20 for assignments 1, and Alice obtained 15/30 for the quizzes. Alice obtained a grade of 30/50 for the End of Semester assessment Alice obtained 33/50 for the assignment and quizzes and 30/50 for the End of Semester assessment and passes the subject with a grade of 63 (Pass).
Bob’s team obtained 19/20 for the assignment 1. Bob obtained 28/30 for the quizzes. Bob obtained a grade of 24/50 for the End of Semester assessment. Bob obtained a total mark of 71/100 however Bob failed the Exam hurdle. Bob’s final grade is NH 49. Bob has not passed the subject.
Carol did not join a team for assignment 1 and obtained (0/20) and obtained a grade of 20/30 for the quiz. Carol’s exam grade is 47/50. Carol fails the subject with a NH 49 (hurdle fail) despite a final grade of 67. Carol did not pass the Assignment hurdle (20/50).
Be sure you submit all assignments and attempt every question on the end of semester assessment to optimise your chance of passing INFO90002
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 3
Frequently Asked Questions
1. 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.
2. 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 and exporting the image as a PDF
3. How do I join my multiple different documents together? A: Save all your documents as PDFs and then merge each PDF into a master document. Another method is to upload all documents to a single Google doc and then download as a single PDF
4. How do I make MySQL workbench show PFK’s (Primary Foreign Keys) A: Please refer to the modules section in the LMS. Scroll down to Resources. You will find the file db.Column.pkfk.11×11.png and a pdf of instructions for both Mac and Windows.
Group Work Advice
Industry expects our Master graduates to be able to work and communicate effectively in teams. 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. Agree on a communication escalation path
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
5. Work out each team member’s strengths and weaknesses. Assign tasks based on strengths.
6. Agree on a timetable or gant chart of tasks and deadline dates.
7. 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.
8. Although unlikely, work out how you will break a deadlock before you need to break a deadlock.
9. 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.
10. TeamProblems?EscalatetotheSubjectCoordinatorEARLYsoitdoesn’tmeananger,tearsand regret later.
11. Failuretoplanisaplantofail.Don’tfail.PLAN.
GOOD LUCK!
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 4
Example Rubrics – Physical E.R. Model
Looking at the model make an assessment about the attempt. This includes attribute & entity names. Over reliance of default values. Relationships and Relationship labels. Appropriate suitable PK choices.
Clarity of thought and layout. Most importantly suitability to the case study itself.
Check for signs of plagiarism (conscious and unconscious) and similarity to other student attempts and previous assignment solutions.
COMPLEXITY TEST
5. No unnecessary or superfluous relationships. Efficient design that is normalised with focus on efficient and effective implementation of the case study. Very good relationship labels. Nothing beyond 3NF 4. Not overly complex. Entity count is reasonable. Relationship Labels make sense.
3. Somewhat complex; Some excessive use of attributes; Modelled beyond the case study; A few superfluous entities, relationships, attributes
2. General impression is of over engineered solution going well beyond the reasonable scope of the case study. Every attribute that could be a lookup table (e.g. Suburb, Country) is a lookup table
1. Excessively overengineered, more representative of a model about to become an instance than the first cut of the clear text case study. Too many entities, too many attributes and relationships.
0. Excessively complex; excessive entities excessive attributes and relationships; Modelled the real world not to the case study; Plagiarism (COMMENT)
SIMPLICITY TEST
5. Not overly simplified. Very good normalisation. Efficient design that is normalised with a focus on effective and efficient performance
4. A good model. Some minor issues with normalisation. Mostly efficient and practical design, with perhaps a focus on the model’s eventual implementation rather than the clear text case study. Better than below, Not as good as above.
3. Some denormalisation. Some reliance of default data types (e.g. varchar 45). A few missing key entities & attributes. Some use of assumptions to solve issues.
2. Over reliance on weak entities and surrogate keys. Focus is on query performance not modelling design
1. Overly denormalised; Too simplified; Missing relationships; Missing key entities & attributes. Over reliance on assumptions. 0. Plagiarism
DATA TYPES
4. The choice of data type has been considered for every attribute in the model and suitable for the context of the case study
3. Most data types have been considered but there are some errors and inconsistencies
2. ALL data types are default (INT for PK, VARCHAR(45) for strings – or other default (e.g varchar(40))
1. Mismatch in PK-FK data type; Poor data type choices in more than half of the entities
0. Complete misunderstanding of datatype use. Plagiarism (COMMENT)
ENTITIES
8.An excellent map of entities that models the case study. Entity names are sensible (not too long, abbreviated names are understood)
7. A very good map of entities that models the case study; Minor issues with entity choices, naming, relationship normalisation logic. 1-3 minor issues with missing OR/AND superfluous entities
6 A good map of entities that models the case study. Some issues with entity choice, naming, relationship, minor denormalisation; More than 3 A few superfluous entities OR/AND few missing entities;
5. Entity count is too few; Entity count is too many; Model is workable; Some denormalisation; Poor entity naming choices across the majority of entities; Many superfluous entities OR/AND Many Missing entities
4. An acceptable attempt at modelling the entities; Denomarlised; Poor name choices; Lack of clarity; Difficulty in understanding the choices made; Entity abstraction from the case study is complex, difficult and lacking clarity of thought; Writing the SQL for these entities would be cumbersome.
3. Significant issues with entity modelling. Mostly denormalised; Poor name choices; Lack of coherency; Inconsistent application in choices; Entity abstraction is obtuse; Writing SQL would be cumbersome.
2. Unsatisfactory entity handling across the majority of the entities. Far too many; Far too few; Poor names (imagine writing the SQL for the entities: would the SQL be error prone and cumbersome?). Foreign Keys missing or wrong data types. (COMMENT)
1. Very Unsatisfactory. No demonstration of application of theory. Very poor choices for entities; Little evidence of normalisation; Cardinality is confused; Participation is misunderstood; No Primary Keys; Plagiarism (COMMENT)
ATTRIBUTES
4. Sensible attribute names. Easily understood by a manager. No ambiguity. Not too long in length.
3. Appropriate attribute names. Mostly easily understood by a manager. Minor issues with interpretation, clarity of purpose in name choice.
2. Satisfactory attribute names. Some issues with name choices. (e.g length), some issues with clarity of purpose in the attribute name choice Some very long attribute names. Some missing attributes
1. An unsuccess attempt. Attribute name choices are long; Attribute name choices are vague; Attribute names are ambiguous. Attribute names have spaces. Attribute names would be cumbersome to type in adhoc SQL. (COMMENT)
0. An unsatisfactory attempt. Plagiarism. (COMMENT)
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 5
Physical E.R. Model (continued)
CARDINALITY
8. All Business rules were met without any cardinality issues; All relationships between entities have the correct cardinality;
7. All Business rules were met without any cardinality issues; There are minor errors with relationships between cardinality (1-4);
6. The majority of business rules were met without cardinality issues. There are a few errors with relationships between cardinality (4-8);
5. Some business rules had cardinality issues. There are errors with relationships between cardinality (8 or more).
4. Satisfactory but not without issues with cardinality affecting the business rules and overall ER Model. Consistent but incorrect application of cardinality;
3. Significant cardinality errors; Inconsistent application of cardinality rules; Requirements of the case study would struggle to be met.
2. Unsatisfactory cardinality errors; No clear application of the requirements of cardinality in an ER Model (COMMENT)
1. Very unsatisfactory comprehension of the function of cardinality. Completely unworkable model as a result (COMMENT)
0. Plagiarism
Participation
5. Clear consistent demonstration of correct participation choices
4. Mostly correct participation choices; Some minor errors;
3. Consistent but incorrect participation choices; Inconsistent participation choices; Inappropriate participation choices ; Satisfactory is OK; Workable is OK;
2. Most participations are incorrect. EG forcing both sides to be mandatory when one must exist before the other. (COMMENT)
1. No clear demonstration of what is the appropriate participation choice (COMMENT)
0 Plagiarism
Weak Strong Entities
5. Excellent understanding and application of Weak/Strong Entities
4. Mostly correct understanding of when an entity should be weak.
3. Significant application of Weak Strong entity. Some entities have composite Primary keys which really don’t need to have a composite primary key because of the Weak – Strong defined relationship
2. Majority of entities have been forced into Strong / Weak relationships which do not need to be so. Primary keys which really don’t need to have a composite primary key because of the Weak – Strong defined relationship (COMMENT)
1. Unsatisfactory understanding of strong / weak entities. Plagiarism (COMMENT)
Primary Key
5. Excellent. Correct choice of Primary key. Correct choice of surrogate primary key; Correct choice of composite primary key;
4. Very Good. Some minor errors
3. Good. Some issues. Some unworkable choices, but the majority of PK choices is acceptable. This grade if VARCHAR is used as a PK in isolation
2. Significant issues with the choices of Primary Key. This grade if over zealous application of Weak Strong entities has not produced the key from the superkey candidates. COMMENT
1. Unsuccessful attempt. Most PK choices are incorrect. Most PK choices are inappropriate COMMENT
PFK/FK
5. Foreign Keys match their parent primary keys; No issues; PFK choices are correct; Approach has made decisions to avoid ambiguity
4. Foreign Keys match their parent primary keys; No issues; PFK choices are correct; Approach has some ambiguity in FK names when linked to PKs
3. Foreign Keys match their parent primary keys; Minor issues; PFK choices are mostly correct. Minor instances a surrogate key would be better.
2. Unsuccessful Attempt. Data type mismatch between FK and PK; PFK choices mostly incorrect. (COMMENT)
1. Unsatisfactory (COMMENT)
Normalisation
4. Normalised ER Model. Text Book.
3. Mostly normalised. Some denormalisation but case study requirements are met.
2. A good first attempt
1. Overtly denormalised. Most relations do not meet 3NF
0. Significant errors; Significant denormalisation; COMMENT
Readability, Format
1. I can read it.
0. I had to open the mwb file. Unreadable; crowded; poor image; Incomplete;
Originality /
Similarity /
Plagiarism
1- 20 marks
COMMENT AND REPORT
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 6
Chen Conceptual models
Entities
10: All Entities are present and clearly identified as weak or normal
8: All Entities are present and most are correct few extra / missing
6: Some missing / additional entities /
5: incorrect weak or normal – consistently incorrect identification of weak/normal entities; Satisfactory use of entities
4: Many missing / Many additional entities / incorrect weak or normal / inconsistencies in use of Chen notation
2: Unsatisfactory attempt (PLEASE EXPLAIN WHY)
0: No submission No attempt; Plagiarism
Attributes
10: Attributes are accurately diagrammed and use the correct Chen notation
8 : Attributes are accurately diagrammed and use the correct Chen notation Minor issues
6: Attributed attributes in case study are diagrammed AND Most attributes use the correct Chen attribute type
4: Several missing or additional attributes, incorrect Chen notation; Consistency in incorrect Chen notation
3: Significant missing / Significant additional attributes, M:M resolved; Inconsistency in use of Chen notation
1: Unsatisfactory. No demonstrated comprehension of Chen notation use and application. Plagiarism (COMMENT)
Multivalued/Composite/ Deived Attributes
2: Multivalued, composite and derived attributes used correctly
1 : Some errors in the use of multivalued, composite and derived attributes
0: Unsatisfactory attempt. Inconsistent use of Chen notation (demonstrated by incorrect use making no sense when interpreted) Plagiarism
Relationships
5: ALL Relationships are correctly used and labelled
4: Most relationships are correctly used, minor errors
3: Some errors in relationships.
2: Incorrect use of relationships; Consistent incorrect use of relationships 1: Significant errors; 0: Major misunderstanding of the idea of relationships, or no attempt. Plagiarism
Key Constraints
10: Key constraints for all relationships and are correctly documented in Chen Notation
8: Mostly correct Key constraints
6: Good attempt at Key constraints and/or consistently incorrect use of Chen notation
5: Satisfactory.
3: Significant errors in Key Constraint choices; Inconsistent application of Key constraints
1: Hybrid notation / no clear demonstration of understanding of key constraints using Chen notation. Plagiarism
Participation Constraints
8: Participation constraints for all relationships and are correctly documented in Chen Notation.
7: Mostly correct Participation constraints and/or consistently incorrect use of Chen notation
5: Good attempt at Participation constraints. A few minor or moderate errors in application and comprehension
4: Satisfactory: Some mismatch in total/partial participation; Model becoming unworkable; 3: Significant errors in Participation Constraint choices; Consistent incorrect choice of participation constraints 2: Hybrid notation / Inconsistent notation demonstrating lack of comprehension.
1: Unsatisfactory. Plagiarism
Clarity / Readability
5: Excellent. Very legible easy to read
4. Very Good. Legible easy to read. Minor issues 3. Good. Legible, some layout / readability issues 2. Crowded. Messy. Some difficulty in reading and interpreting the Chen model
1. Unsatisfactory. Plagiarism
N.B.:Each Assessment rubric is designed specifically to the needs of the case study. Use this as guidance, not writ law.
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 7
Revisions – revisions to the document
In this section is the document history. After GA (General Availability) all changes in the document will be colour coded. Both the revised document and the original document will be preserved and available to students.
Date
Version
14-Jan
α
Initial Draft
10-Feb
β
First revision. Added details to all sections.
5-Feb
Γ
Second revision. Fixed typos and updated examples.
15-Mar
1.0 (GA)
Third revision. Peer reviewed (FM). Added the workbench file component. Changed the instructions for PKFK red key. General Availability and released to students.
INFO90002 S1 2021 A1 © The University of Melbourne v 1.0 (GA) 8