EXAM Part 1: Assignment
School of Computing and Information Systems
INFO90002
Database Systems and Information Modelling
END OF SEMESTER EXAM
Part 1: Modelling, SQL, Relational Algebra & Normalisation
Semester 2 2020
Exam Release Time: 9.00 am Tuesday November 24th 220 AEDT
Submission Due Date: 9.00am Thursday November 26th 2020 AEDT
SUBMISSION via the LMS https://lms.unimelb.edu.au before the due date and time
This Part (Part 1) has 9 pages including this page
Authorised Materials:
While you are undertaking this assessment, you are permitted to
• MySQL Workbench is supported for E.R. modelling questions
• Any lecture notes, books, laptop, PC
• You are free to use the course materials and your laptop/PC in this assessment.
While you are undertaking this assessment, you MUST NOT
• make use of any messaging or communication technology
• make use of any world wide web or internet based resources such as wikipedia, stackoverflow, google or any other search engine services
• act in a manner that could be regarded as providing assistance to a student who is undertaking this assessment or in the future will be undertaking this assessment
• seek assistance from any other student who is undertaking this assessment, has undertaken this assessment, or in the future will be undertaking this assessment
• plagiarise
• All work must be a synthesis of material studied in INFO90002
• You must not quote any work verbatim, this includes slide notes, websites, other student’s notes and materials – including students from previous semesters of INFO90002
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020 1
EXAM Part 1: Assignment
Instructions to Students
• There are two parts to this exam paper Part 1 and Part 2
• The total for this exam is 140 marks
• Part 1 must be submitted via the LMS before 9.00am Thursday November 26th 2020 AEST
• Part 1 (This section) is worth 70 marks
• Attempt all questions in all parts, which are of unequal marks value
• PLEASE DO NOT USE RED font colour or pens.
• You should not communicate with other students whilst taking this exam, e.g. using messaging, social media, chat rooms or email
The work you submit must be based on your own knowledge and skills and without the assistance of any other person. You must not directly copy and paste material that is not your own work.
Submission Instructions:
Exam A Part 1 must be submitted as a single PDF file in the Assessment Tab of the Learning Management System. Be sure to use the EXAM A Part 1 link.
Unless Academic Adjustment Plans are in place any assignment submitted after the due date and time will receive 0 marks.
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020 2
Figure 1: A survey consisting of one short-text question, one numerical question, and one multiple-choice question.
EXAM Part 1: Assignment
Question 1: Modelling from a case study (30 marks)
A system to support “Experience Sampling” research
We are building a software system to support Experience Sampling experiments. In these experiments, a group of human participants fill in the same survey (set of questions), several times per day over multiple days. (Questions typically ask about what the person is doing or feeling.) Participants have an app on their phones that retrieves the questions from the database, displays the questions, accepts answers from the user, and sends the answers to the database to be stored. All relevant data, including details about researchers and participants, experiments and surveys, questions and answers are stored in the database.
To set up a new experiment, a researcher specifies a start and end date, the set of questions and the order to display them in, and the number of times per day they should be sent to participants. The researcher then recruits participants and stores their details in the system.
When the start day arrives, the system will begin to send surveys to participants. Each survey is the full set of questions specified by the researcher for that experiment. We need to keep track of each survey that is sent out, including when it was sent and to whom. When a participant responds to a survey, we need to store the answers, remembering when the answers arrived, and which answers came from which participant. (Note that participants don’t necessarily respond to each survey.) Figure 1 shows a survey consisting of 3 questions, displayed on a participant’s phone.
Questions can ask for three possible types of answer: short-text, numerical (integers zero or above), and multiple-choice. For short-text questions, the researcher specifies the maximum
number of characters allowed, while for numerical questions, they specify the maximum number. For multiple-choice questions, they specify the list of possible answers. (The longest short-text our system allows is 1,000 characters, while the biggest maximum number we allow is 10,000.)
Multiple researchers will use our system, each of whom can set up as many experiments as they want. Over time we will build
up a bank of questions (and for the multiple-choice questions, answers), and we want to allow these to be re-used in multiple experiments. It’s possible that a given participant might take part in more than one experiment. Researchers and participants need to store a username and email address in the system. We allow researchers to give a name to each experiment, such as “Bob’s Masters project”. During the life of our system we anticipate having around 1,000 researchers who will run an average of 10 experiments each, with around 100 participants per experiment. Experiments contain on average 5 questions and run for 30 days.
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020 3
EXAM Part 1: Assignment
Q1. You are asked to model a physical Model of the Experience Sampling app in Crows Foot Notation for a MySQL v8.0 Relational Database using MySQL Workbench. State any assumptions you have made.
(30 marks)
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020 4
EXAM Part 1: Assignment
Question 2. SQL – DDL (10 marks)
Darling Murry is an online only superstore. It offers, technology, computers, phones, furniture, grocery items, household goods, whitegoods, phones, computers, baby goods and sporting goods. The following Chen conceptual model represents part of the Darling Murry customer product review system.
Figure 1 Conceptual Model of the Murray Darling Product Rating system
Q2. Write the SQL DDL for the above Chen conceptual model. Do not specify null/not null. You can use whichever data types you think are appropriate for a MySQL v8.0 database
(10 marks)
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020 5
EXAM Part 1: Assignment
Question 3. SQL & Relational Algebra (15 Marks)
Melbourne Touch Rugby Summer Competition
“Touch” rugby is a five-person game that is played in the off season (usually late Spring to late early Autumn). It is a fast and lively game where instead of tackling other players to the ground – they only have to touch or ‘tag’ the player for there to be a restart in play.
There are 8 touch rugby clubs that compete in the Melbourne Touch Rugby Summer comptition. There are currently four competitions: mens, womens, seniors – for men over the age of 35 years, and a mixed division for teams made up of men and women. A mixed team must have a minimum of three female players in every game. A team must have a minimum of four players available to play the game. If one team is unable to field a side (minimum of four players) at the game start time they forfeit the game and suffer a walkover, and do not record any value for their score. The other team scores 28 points.
The competitions have been running for several seasons, with the details of every game and participating teams and players carefully recorded.
Figure 2. The Melbourne Touch Rugby schema
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020 6
EXAM Part 1: Assignment
Questions 3A– 3D require you to write one single SQL statement per question. Do not use views, temporary tables or inline views or any schema on read solution. Format code for ease of reading. Ensure user-friendly output by renaming columns where appropriate. Display the first ten rows and the total number of rows returned.
For Example:
Q. List the first name, last name and salary of Alice Munro
SELECT firstname, lastname, salary
FROM employee
WHERE firstname = ‘Alice’ AND lastname = ‘Munro’;
1 row returned
Q3A. List the game date, team name and all players who were selected for a team that eventually had to forfeit (walkover) in 2018?
( 3 marks)
Q3B. How many points did the Melbourne University Rugby Club team score in each year of the Dewar Shield competition? List the team name, and cumulative score for each year. Order the result from the highest cumulative score to lowest.
( 3 marks)
Q3C. Elizabeth Blackburn has played for three different clubs. How many games did she play for each club? List the club name and number of games.
( 3 marks)
Q3D. List the games, teams and scores played on 4th October 2015 at 10.00 a.m.
Your result should be in tabular form :
Team A
32
Team B
27
Team C
15
Team D
23
Team E
23
Team F
6
Team G
0
Team H
10
(3 marks)
Q3E. Write the SQL and two (2) versions of Relational Algebra for the following query:
List all team names of the Geelong Rugby Club
(1+ 1 + 1 = 3 marks)
© The University of Melbourne INFO90002 EXAM PART 1 S2 2020 7
EXAM A : PART 1 Modelling
Question 4. Normalisation (15 Marks)
The following is an example of the class timetable for a Pilates studio with a number of venues.
date
venue
class
28-Oct-20
Pilates Studio
Pilates Advanced
29-Oct-20
Flemming Park
Strength
29-Oct-20
Pilates Studio
Pilates Mat
30-Oct-20
Reformer Studio
Pilates Advanced
31-Oct-20
Strength Studio
Strength Circuit
31-Oct-20
Pilates Studio
Pilates Conditioning
02-Nov-20
Flemming Park
Reformer
03-Nov-20
Strength Studio
Strength Circuit
04-Nov-20
Pilates Studio
Pilates Conditioning
04-Nov-20
Flemming Park
Strength
04-Nov-20
Reformer Studio
Reformer Mat
09-Nov-20
Pilates Studio
Pilates Conditioning
09-Nov-20
Reformer Studio
Pilates Conditioning
09-Nov-20
Pilates Studio
Reformer Mat
09-Nov-20
Strength Studio
Strength Circuit
10-Nov-20
Pilates Studio
Mobility & Release
10-Nov-20
Flemming Park
Strength
10-Nov-20
Strength Studio
Core Power
10-Nov-20
Pilates Studio
Pilates Conditioning
10-Nov-20
Pilates Studio
Pilates Mat
11-Nov-20
Flemming Park
Reformer
11-Nov-20
Reformer Studio
Pilates Conditioning
day
Wednesday
Thursday
Thursday
Friday
Saturday
Saturday
Monday
Tuesday
Wednesday
Wednesday
Wednesday
Monday
Monday
Monday
Monday
Tuesday
Tuesday
Tuesday
Tuesday
Tuesday
Wednesday
Wednesday
time
address
weeks
trainer
certification
capacity
6:15
343 A Lygon
8 weeks
Georgia
CPAA
12
18:00
Flemming
8 weeks
Jason
PPOA
24
18:00
343 A Lygon
6 weeks
Melissa
NZAP
12
6:15
354 Lygon
8 weeks
Georgia
CPAA
18
7:00
132 Victoria
8 weeks
Emily
PIA
15
12:00
343 A Lygon
6 weeks
Stephanie
PIA
12
12:00
Flemming
6 weeks
Eleanor
NZAP
16
13:00
132 Victoria
8 weeks
Jason
PPOA
15
7:00
343 A Lygon
6 weeks
Melissa
NZAP
12
7:30
Flemming
8 weeks
Eleanor
NZAP
24
18:00
354 Lygon
6 weeks
Brigitte
AFP
15
7:00
343 A Lygon
6 weeks
Filomena
PIA
12
7:00
354 Lygon
6 weeks
Georgia
CPAA
18
13:15
343 A Lygon
6 weeks
Melissa
NZAP
12
13:15
132 Victoria
8 weeks
Jason
PPOA
15
7:00
343 A Lygon
6 weeks
Georgia
CPAA
12
7:30
Flemming
8 weeks
Brigitte
AFP
24
9:00
132 Victoria
4 weeks
Jason
PPOA
15
12:00
343 A Lygon
6 weeks
Filomena
PIA
12
15:00
343 A Lygon
6 weeks
Melissa
NZAP
12
8:00
Flemming
6 weeks
Brigitte
AFP
16
17:00
354 Lygon
6 weeks
Stephanie
PIA
18
© The University of Melbourne INFO90002 Exam PART 1 S2 2020 8
EXAM A : PART 1 Modelling
DATE, VENUE, CLASS is the candidate key for this relation
The following functional dependencies hold:
Date, Venue, Class –> Trainer
Date, Venue, Class –> Time
Venue, Class –> Capacity
Date –> Day
Trainer –> Certification
Venue –> Address
Q5. Please normalise the data to third normal form (3NF) and show the final table data structures as a logical ER model. Be sure to show each stage of normalisation (1NF, 2NF, 3NF).
Key: BOLD primary key; ITALIC foreign key; BOLD + ITALIC primary foreign key.
(15 marks)
GOOD LUCK!
END OF EXAM: PART 1
© The University of Melbourne INFO90002 Exam PART 1 S2 2020 9