End of Semester Exam PDF

Preview

Summary

Download End of Semester Exam PDF

Description

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

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.) Figure 1: A survey consisting of one short-text Multiple researchers will use our system, each of whom can set question, one numerical question, and one multiple-choice question. 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 teams and scores for all games played on 4th October 2015 at 10.00 a.m. Your result should be in tabular form : Team A Team C Team E Team G

32 15 23 0

Team B Team D Team F Team H

27 23 6 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

time

address

weeks

certification

capacity

28-Oct-20

Pilates Studio

Pilates Advanced

Wednesday

day

6:15

343 A Lygon

8 weeks

Georgia

CPAA

12

29-Oct-20

Flemming Park

Strength

Thursday

18:00

Flemming

8 weeks

Jason

PPOA

24

29-Oct-20

Pilates Studio

Pilates Mat

Thursday

18:00

343 A Lygon

6 weeks

Melissa

NZAP

12

30-Oct-20

Reformer Studio

Pilates Advanced

Friday

6:15

354 Lygon

8 weeks

Georgia

CPAA

18

31-Oct-20

Strength Studio

Strength Circuit

Saturday

7:00

132 Victoria

8 weeks

Emily

PIA

15

31-Oct-20

Pilates Studio

Pilates Conditioning

Saturday

12:00

343 A Lygon

6 weeks

Stephanie

PIA

12

02-Nov-20

Flemming Park

Reformer

Monday

12:00

Flemming

6 weeks

Eleanor

NZAP

16

03-Nov-20

Strength Studio

Strength Circuit

Tuesday

13:00

132 Victoria

8 weeks

Jason

PPOA

15

04-Nov-20

Pilates Studio

Pilates Conditioning

Wednesday

7:00

343 A Lygon

6 weeks

Melissa

NZAP

12

04-Nov-20

Flemming Park

Strength

Wednesday

7:30

Flemming

8 weeks

Eleanor

NZAP

24

04-Nov-20

Reformer Studio

Reformer Mat

Wednesday

18:00

354 Lygon

6 weeks

Brigitte

AFP

15

09-Nov-20

Pilates Studio

Pilates Conditioning

Monday

7:00

343 A Lygon

6 weeks

Filomena

PIA

12

09-Nov-20

Reformer Studio

Pilates Conditioning

Monday

7:00

354 Lygon

6 weeks

Georgia

CPAA

18

09-Nov-20

Pilates Studio

Reformer Mat

Monday

13:15

343 A Lygon

6 weeks

Melissa

NZAP

12

09-Nov-20

Strength Studio

Strength Circuit

Monday

13:15

132 Victoria

8 weeks

Jason

PPOA

15

10-Nov-20

Pilates Studio

Mobility & Release

Tuesday

7:00

343 A Lygon

6 weeks

Georgia

CPAA

12

10-Nov-20

Flemming Park

Strength

Tuesday

7:30

Flemming

8 weeks

Brigitte

AFP

24

10-Nov-20

Strength Studio

Core Power

Tuesday

9:00

132 Victoria

4 weeks

Jason

PPOA

15

10-Nov-20

Pilates Studio

Pilates Conditioning

Tuesday

12:00

343 A Lygon

6 weeks

Filomena

PIA

12

10-Nov-20

Pilates Studio

Pilates Mat

Tuesday

15:00

343 A Lygon

6 weeks

Melissa

NZAP

12

11-Nov-20

Flemming Park

Reformer

Wednesday

8:00

Flemming

6 weeks

Brigitte

AFP

16

11-Nov-20

Reformer Studio

Pilates Conditioning

Wednesday

17:00

354 Lygon

6 weeks

Stephanie

PIA

18

© The University of Melbourne INFO90002 Exam PART 1 S2 2020

trainer

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 Class --> Weeks 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...