FIT9132 Introduction to 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 <season name> Series <campus name> <year>. 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. During a carnival a range of events are offered from the following list:
l Marathon 42.2 Km
l Half Marathon 21.1 Km
l 10 Km Run
l 5KmRun
l 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. 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.
Page 1 of 5
A data model has been developed for this situation. The relational model is shown below:
You have been supplied with a schema file FIT9132_2018S1_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 FIT9132_2018S1_A2_Solutions.sql – you should rename this script by prepending your authcate username to the start of the filename, e.g., abc123_FIT9132_2018S1_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 FIT9132 Google shared folder.
Before starting work on the task complete the header by adding your name etc., in the solutions script.
Page 2 of 5
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.1 Add to your solutions script, the CREATE TABLE and CONSTRAINT definitions which are missing from the FIT9132_2018S1_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.2 Add 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 statement.
Before proceeding with Task 2, you must run the file FIT9132_2018S1_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 FIT9132_2018S1_A2_Schema_Start.sql and your extra definitions that you added in 1.1 above.
TASK2: [10+1+1=12mks]
Run the script FIT9132_2018S1_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 mentioned:
2.1 Add 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)
Father
Fernando Rose
10/10/1970
6112345678 fernando@rm.org
21.1 Km Half Marathon
Mother
Adrianna Rose
11/11/1971
6187654321 adrianna@rm.org
21.1 Km Half Marathon
Daughter
Annamaria Rose
12/12/2004
3 Km Community Run/Walk
Son
Juan Rose
01/01/2006
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.
3 Km Community Run/Walk
2.2 An Oracle sequence is to be implemented in the database for the subsequent insertion of records into the database for COMPETITOR table. Provide the CREATE SEQUENCE statement for 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.3 Provide the DROP SEQUENCE statement for the sequence objects you have created in question 2.2 above.
Page 3 of 5
TASK3:[10+10+10+10 =40mks]
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.1 Add 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 wendy@rm.org |
42.2 Km Marathon |
|
Sam O’Hare |
08/08/1986 |
6198761234 sam@rm.org |
42.2 Km Marathon |
- 3.2 Sometime after the registration, Wendy has decided to form a team for 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 Cancer Council Of Victoria charity. Add this information into the database.
- 3.3 Sometime 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 42.2 Km marathon event.
- 3.4 Suppose 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.
TASK4:[8+20 =28mks]
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.1 record 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.2 record 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 changes made are most appropriate and consistent and data is reasonable and correct to help Run Monash retrieve this information effectively from the database.
Page 4 of 5
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
Due Date: Wed 16-MAY-2018 5 PM (Week 11)
SUBMISSION REQUIREMENTS
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.
Page 5 of 5