Databases代写代做、代做Databases程序

日期：2018-05-12 09:15

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 5 Km Run

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 2 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 3 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.

TASK 2: [10 + 1 + 1 = 12 mks]

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 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.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 4 of 5

TASK 3: [10 + 10 + 10 + 10 = 40 mks]

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.

TASK 4: [8 + 20 = 28 mks]

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 5 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

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.