INFO90002 Assignment 1 Semester 1 2019
E-R Modelling
Instructions:
Assignment 1 is a group assignment worth 20% of your final mark. Groups must be 3 members. The
assignment will be graded out of 200 marks as described in the table below:
• You are to analyse the Bite-n-Write Case Study and design a Physical ER Model for a MySQL Relational Database in Crow’s foot notation modelled with MySQL Workbench.
• You are to provide a Data Dictionary for the five largest tables in your ER Model. Largest is defined as the most number of attributes.
• 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 LMS, in class, or at a Student Consultation (Tuesdays 2.30 – 4.00pm Doug McDonnell Building Floor 7 Room 7.02)
• An Assignment Hurdle means you will must submit this information to pass the assignment
NOTE: Item 1 (Data Dictionary & Physical ER model) Item 2 (Assumptions) and Item 3 (Team Member work breakdown) must be submitted as a single PDF. The title of the PDF document must be your Group Number (e.g Group 32). Item 4 – your mwb file is submitted as a separate file.
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
Item 1
ER Physical Model with assumptions
150 marks
Item 2
Data Dictionary
50 marks
Item 3
Team Member Work breakdown
Assignment Hurdle
Item 4
A soft copy of your .mwb MySQL Physical Workbench Model File
Assignment Hurdle
INFO90002 S1-2019 A1
1
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 examples adequately explain the entities modelled.
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:
1. ONE PDF document named as your Group number id (e.g. Group20.pdf), containing:
i. legible image of your Physical ER Model
ii. your data dictionary,
iii. your assumptions (if you stated any)
iv. work breakdown per team member.
Submit this single PDF document under the BLUE submission link.
NOTE: If you fail to submit legible models you will be penalised 10% of your total grade for this
assignment.
2. Your final .mwb MySQL Workbench file of the Physical ER model. Submit your .mwb file under the BLACK submission link.
3. Assignments that are late will attract a penalty of 10% for each Academic Day.
INFO90002 Hurdle requirements
To pass INFO90002 you must obtain a grade of 35/70 for the exam and more than 15/30 for the two assignments combined.
INFO90002 S1-2019 A1
2
BITE-n-Write Case Study
Bite-n-Write is a food diary and exercise app for people seeking to monitor their calorie intake and exercise. Bite-n- Write will be available from app stores and is designed to be a cross platform system (website & phone app) available anywhere and anytime.
Users must register in order to use Bite-n-Write. They must provide a first name, last name, unique username, email address, and optionally upload a photograph or avatar image. After they have registered, users must enter their current weight, goal weight, target daily calories intake and sex. Bite-n-Write records the date users became a member of Bite-n-Write.
An average male needs 2100 calories a day and a female 1800 calories a day. To lose weight users must have consumed fewer calories than they have expended either through metabolic activity (normal cost of keeping the human body alive) or exercise. To gain weight users must consume more calories than they have expended. As most users stop and start food diaries Bite-n-Write will need to store multiple goal body weights, and current body weights over periods of time.
The app system has three sections: the food diary & database; the exercise diary; and the user forums.
The food database contains nutritional information about all types of foods. It records the total in calories and the amount of protein, fat, carbohydrates and sodium levels. As well as storing information about unprocessed food, it also stores nutritional information about processed foods (e.g. Twix Bar) and prepared foods by the major food retailers (e.g. McDonalds, KFC, SumoSalad, Subway, Starbucks etc.).
Information available from the Food database for unprocessed foods (Banana) and processed foods (Twix Bar)
INFO90002 S1-2019 A1
3
On any day users open the app and enter information about the meals they eat. Users can enter food under meal categories ‘Breakfast’, ‘Lunch’, ‘Dinner’ and ‘Snacks’. The system allows users to create their own foods and meals. For example user JamieO has saved a food called ‘Nut and Fruit Granola’. User BamBamSam has saved a meal called ‘Favourite Brekky’ which is made up of the following foods: 2 poached eggs, 1 Slice Sourdough Toast, 1⁄2 cup steamed spinach. This is so users can quickly add commonly consumed meals and foods to the diary.
Adding foods to the database
Using their phone or tablet camera users can scan a barcode of a processed food item and see if it is already in Bite-n-Write’s food database and add it to their diary. If the barcode is not found, users can enter the food macro nutrient information (calories, fat, carbs, protein, sodium) from the packet and add this food item to the database. The Bite-n-Write system records which user added the food to the database.
Setting and Saving Targets
Users can set daily targets for macronutrients (Carbohydrate, Fat, Protein) and record the number of standard glasses (250ml) of water they drink in a day. The app records a running average for net and sum (total) energy for the last 3, 7 days and current calendar month. Users can enter their current weight at any time, and on the first day of each calendar month users are encouraged to update their current weight. This enables Bite-n-Write to chart their weight over time and show progress to their goal weight.
Reminders and Prompts
The app reminds users to enter data in their food diary if no data has been entered by 6pm on any given day. If by 6pm users have drunk less than 2 glasses of water it prompts users to stay hydrated. The app keeps a record of how many times it had to remind and prompt users and a count of the number of days where no food diary information was entered at all. Bite-n-Write need to collect this data to see how effective the reminders and prompts are and if they need to be revised in the app design.
Micro Blog and Vlogs
‘Public’ users can post short micro blog posts of up to 300 words and vlog posts (max 60 seconds in size) to their personal blog. Other users can “like” a user’s blog/vlog post. ‘Private’ profiles share no information with any other users of Bite-n-Write and are not able to create a blog/vlog or like another users blog/vlog.
Forums
All public users can publish to five forum areas ‘General’, ‘Motivation’, ‘Food Ideas’, ‘Inspiration’ and ‘Problem Solving’. Under each of these areas people can post a topic (e.g. “Using Old Bananas”) and other users can post replies to the topic. The post and replies should be ordered by time and show the username. There is a maximum post and reply size of 300 characters.
Mates
Public users can invite other public users to be a ‘Mate’. Users can either accept or reject the invitation. Users who are Mates can send messages of up to 140 characters to each other. Users can delete the messages and unfriend Mates. The database records the invite date, the response of the user
INFO90002 S1-2019 A1
4
(Accept or Reject) and the date a user unfriends a Mate. If a user changes their visibility from public to private, they lose all of their ‘Mates’.
Reporting and Blocking
Unfortunately, as there are trolls and unwanted users in any social media community, users are allowed to ‘Block’ users. Once a user is ‘Blocked’ they can no longer see posts or any information from the user who blocked them. As well as blocking unwanted users, users can also ‘Report’ trolls who violate the standards of Bite-n-Write membership to Bite-n-Write. Users who ‘Report’ other users must provide a contact email, contact phone number a statement of up to 100 words and the username of the member being reported.
Exercise Diary
Users can also record their daily exercise in an exercise diary. The database has a list of several hundred activities including the average calories burned per hour. Activities can be as diverse as walking, gardening, rugby, netball, and rogaining. When users record their exercise (e.g. Gym workout 45 minutes calories 273) the food calendar will display a net and gross calories total for the day. The net calories is the total number of calories consumed including credit calories for any exercise. As females and males burn calories at different rates per hour the exercise information must be stored for the male and female sex.
Business Tests
Use these business tests to help test your ER Model. Imagine how your model would solve these
needs. Please DO NOT SUBMIT SQL as it will not be assessed.
• Can you identify the net calorie intake for username JamieO on 15th March 2016?
• Can you identify the users that have saved favourite meals?
• Can you identify how many male users in 2017 were under their target weight when they first
joined Bite-n-Write?
• Can you identify the users who have been blocked by more than 5 other users?
• Can you identify the days Alice92 forgot to record any information in her diary?
• Can you identify if a user’s mate, unfriended them, and then become that user’s mate again?
• Can you identify users that have posted to all forum areas?
• Can you identify if BamBamSam ignored prompts in the month of December 2017?
• Can you identify members that have private profiles?
END OF CASE STUDY
INFO90002 S1-2019 A1
5
Appendix
A. Exporting your model from Workbench
To ensure that you submit a readable image of your E-R model please export your design as a PDF. Select i) FILE
How to export your ER Model from MySQL Workbench
B. Creating a single pdf file from multiple PDF files
To create a single PDF file, you will need to save each of your files i) ER Model, ii) Data Dictionary iii) Assumptions and iv) Work breakdown as individual PDF documents.
B1. Using Adobe Acrobat
Using any PDF tool (e.g. Adobe Acrobat DC) you can merge these files into one document.
Step 1. Open Adobe Acrobat and select “Create PDF” Step 2: Select Multiple Files
Step 3 Add your files
INFO90002 S1-2019 A1
6
4. Combine the files
5. Save as your team name
B2. Using Google Docs
Alternatively, you can put all of your documents into a single Google Doc and export as a PDF.
INFO90002 S1-2019 A1
7
INFO90002 S1-2019 A1
8