FIT2094 Databases
2018 Semester 1
Assignment 2 - Database Implementation - Run Monash (RM)
Run Monash (RM) is a running carnival which is held on separate dates at both Monash Caulfield
and Clayton campuses during different seasons (Summer, Autumn, Winter and Spring) of a year.
The carnival naming convention that Run Monash uses is RM Series . So, for example, a carnival to be held in Summer season at Caulfield campus in
2019 will be named as RM Summer Series Caulfield 2019.
Anyone can attend an RM Carnival. A carnival is run on a particular date, in a particular location
and only lasts for one day, only one carnival is run on any date. During a carnival a range of events
are offered from the following list:
● Maratho 42.2 Km
● Half Marathon 21.1 Km
● 10 Km Run
● 5 Km Run
● 3 Km Community Run/Walk
When a competitor initially registers for Run Monash, they are assigned a unique competitor
number. Competitor's numbers consist of four digits e.g., 1122. A competitor is required to provide
details of an emergency contact information at the time of registration along with parent/guardian
information if the competitor being registered is under 16 years of age. The relationship to
competitor can be Parent (P), Guardian (G), Partner (T) or Friend (F).
In an upcoming carnival, there are a range of events that are offered. Competitors can enter for a
particular event within a carnival. Every entry is assigned a unique entry number (e.g., 3021) which
is generated by adding 1 to the last entry number used in a carnival. Using official timing devices at
the carnival, Run Monash records the entrants finishing time and their place within the event.
A major focus of the Run Monash Carnivals is to raise funds for various charities. When a
competitor enters an event, they may nominate a charity for which they will raise funds (not all
competitors will select a charity for each event they enter). Competitors who have entered an event
can also form. teams with other competitors, who they know and have entered the carnival, to
support their training and run as a group. The first competitor to register a team for a given carnival
is assigned as the Team Manager. Teams are identified by a unique team name which the team
manager must select when they first create the team. This team manager can then add other
competitors of the carnival to the team. Team names are unique within a carnival, however, a given
team name may be reused by different competitors in a different carnival as teams are recreated
for each carnival depending on which competitors have entered an event for the carnival.
Page 1 of 6
Run Monash wishes to record, as part of the stored data, how many members are on each team.
Teams may also nominate a charity for which they will raise funds, although not all teams will do
so. All charities for which funds can be raised must first be approved by Run Monash.
A data model has been developed for this situation. The relational model is shown below:
You have been supplied with a schema file RunMonash_A2_Schema_Start.sql (which must not
be altered in any way) which partially implements the Run Monash model.
You have also been supplied with a document RunMonash_A2_Solutions.sql - you should rename
this script. by prepending your authcate username to the start of the filename, e.g.,
abc123_RunMonash_A2_Solutions.sql. This script. file will be referred to as your solutions script.
Within this script. there are marked points where each of your solutions must be added.
All of the work for assignment 2 will take place in this document so please take great care to keep
regular backups, including off your computer, e.g., on Google Drive, so you do not lose
work. It is suggested that you place regular backups of this file on to your database Google shared
folder supplied to you for assignment 1.
Before starting work on the task complete the header by adding your name etc., in the solutions
script.
In completing this assignment you are not permitted to manually look up a value in the
database, obtain its primary key or highest or lowest value in a column (for example) and
use that in your answer. As an example you should not look in the database and see that
'42.2 Km Marathon' is event type 42K and use this in your work. You MUST USE ONLY the
values listed for the particular task you are working on in this document.
TASK 1: Data Definition [15 + 5 = 20 mks]
For this task you are required to complete the following:
1.1Add to your solutions script, the CREATE TABLE and CONSTRAINT definitions which
are missing from the RunMonash_A2_Schema_Start.sql script. You MUST use the
relation and attribute names shown in the data model above to name tables and
attributes which you add.
1.2Add the full set of DROP TABLE statements to your solutions script. In completing this
section you must not use the CASCADE CONSTRAINTS clause as part of your DROP
TABLE statements.
Before proceeding with Task 2, you must run the file RunMonash_A2_Schema_Start.sql (which
must not be altered in any way) followed by the extra definitions that you added in 1.1 above.
In a script. you can run a section of the script. by highlighting the lines you wish to run and selecting
the run button. If at any stage your tables are corrupted during working on this assignment you
simply need to run your drop commands from 1.2 above and then rerun
RunMonash_A2_Schema_Start.sql and your extra definitions that you added in 1.1 above.
Run the script. RunMonash_A2_Insert_Start.sql to add some initial data into the tables you created
in task 1.
For this task you are required to complete the following sub-tasks in the same order they have
been mentioned:
2.1Add entries for the Rose family into the races for the carnival to be held at Caulfield
campus in Autumn season of 2018. Some of the data for the Rose family is provided to
you in the table shown below.
Name DOB Contact Information Race(s)
21.1 Km Half Marathon
21.1 Km Half Marathon
Daughter Annamaria Rose 12/12/2004 3 Km Community Run/Walk
At this stage, the Rose family is not supporting any charity and also not forming a team.
For competitor numbers, you may wish to assign primary keys that you choose,
provided the numbers are between 1000 and 1009. For entry numbers you may
assume these are the first entries in this carnival and use the entry numbers from 1 to
4. Also, for these entries, emergency contacts and guardians should be selected from
within this family.
2.2An Oracle sequence is to be implemented in the database for the subsequent insertion
of records into the database for the COMPETITOR table. Provide the CREATE
SEQUENCE statement for the COMPETITOR table. The sequence will be used to
generate new primary key values when adding new tuples/rows to the database. The
sequence should start at 1010 and increment by 1.
2.3Provide the DROP SEQUENCE statement for the sequence objects you have created
in question 2.2 above.
Sequence created in task 2 must be used to insert data into the database for the task 3 questions.
For these questions you may only use the data supplied in this task. You may assume that a phone
number and name identifies a particular competitor.
For this task you are required to complete the following sub-tasks in the same order they have
mentioned:
3.1Add an entry for the following competitors, who are friends and studying at Monash
University, into the races to be held at Caulfield campus in Autumn season of 2018. Some
of the data for these competitors is provided to you in the table shown below. Both of them
have nominated their friend Forrest Gump with the phone number 6142800800 to be their
emergency contact person.
Name DOB Contact Information Races(s)
Wendy Wang 14/09/1985 6112349876
42.2 Km Marathon
Sam O'Hare 08/08/1986 6198761234
42.2 Km Marathon
3.2Sometime after the registration, Wendy has decided to form. a team for the 42.2 Km
marathon event and call the team Gentle Earth. She will be the leader of this newly created
team. Wendy would also like her team to support the Cancer Council Of Victoria charity.
Add this information into the database.
3.3Sometime after Wendy setup the Gentle Earth team and the team is registered with Run
Monash, Sam O'Hare decides to join the Gentle Earth team for the 42.2 Km marathon
event.
3.4Suppose today is 6th of May 2018 and Wendy and Sam have already completed their race
and they were the only courageous ones to run the marathon for cancer research on a wet
day. Update the database to record these completions. You can use your imagination for
the attribute values of the rows you need to update. However, you need to ensure that the
data is meaningful to the case study.
For this task you are required to complete the following sub-tasks.
After using the system for some time, Run Monash has realised that it is necessary to
4.1record whether all competitors have any medical issues. They do not want to keep the
details of the medical condition. They only want to flag whether a competitor has a medical
issue or not (the value cannot be left empty). Change the "live" database and add this
required information for all competitors currently in the database. You may assume that all
existing competitors will be recorded as NOT having a medical condition. The information
will be updated later when the competitors reply to their request for this additional
information.
Sometime after sending the request to all the existing competitors for this additional
information, Wendy Wang of phone number 6112349876 has contacted Run Monash and
indicated that she has a medical condition. Update the database to reflect this new
information. You may assume that a phone number and name identifies a particular
competitor.
4.2record the type of track (Grass or Synthetic), total number of parking spaces and the type of
toilets available at each location (Portable, Fixed or Mixed) since they are receiving a lot of
calls for this information from participants. Change the "live" database and add this
information into the database in a manner that the changes made are most appropriate and
consistent and the data is reasonable and correct to help Run Monash retrieve this
information effectively from the database.
This extra data for the currently recorded addresses in the CARNIVAL table is:
Current Address Track Type Toilets Parking Space
900 Dandenong Rd,
Caulfield, VIC, 3145
Grass Portable 200
Scenic Blvd, Clayton,
VIC, 3800
Synthetic Mixed 400
SUBMISSION REQUIREMENTS
Due Date: Wed 16-MAY-2018 5 PM (Week 11)
For this assignment there is only one file to submit. You are required to submit your solutions script.
file to Moodle before the assignment due date/time. If you need to make any comments your
marker/tutor should be aware of please place them at the head of your solutions script. in the
"Comments for your marker:" section.
Late submission will incur penalties as outlined in the unit guide.