FIT9132 Value in the Database
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 Solutions.sql - you should rename this script by prepending your authcate username to the start of the filename, e.g., 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 21.1 Km Half Marathon Mother Adrianna Rose 11/11/1971 6187654321 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 42.2 Km Marathon Sam O'Hare 08/08/1986 6198761234 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.
Answer:
STEP 1
RUN ALL COMMANDS IN TH
IS FILE >> FIT9132_2018S1_A2_Schema_Start.sql
STEP 2
RUN ALL COMMANDS IN THIS FILE >> FIT9132_2018S1_A2_Insert_Start_v1.1.sql
STEP 3
RUN ALL COMMANDS IN THIS FILE >> START FROM:
TASK Task 2.1 >> Task 2.2 >> Task 3.1 >> Task 3.2 >> Task 3.3 >> Task 4.1 >> Task 4.2 >> YOUR DONE
NB: I SKIP TASK 1.1 BECAUSE THOSE COMMANDS HAVE ADDED IN THE FILE(Toward the end of file) >> FIT9132_2018S1_A2_Schema_Start.sql
NB: I SKIP TASK 1.2 TO AVOID DROPPING ALL THE CREATED TABLES
NB: I SKIP TASK 2.3 TO AVOID SEQUENCE MANAGER WHICH SHALL BE NEEDED.
STEP 4 [NOTE]
IF YOU MISS ANY STEP DROP ALL THE TABLES AND START AGAIN
USE THIS TO DROP ALL THE TABLES
DROP TABLE ENTRY;
DROP TABLE COMPETITOR;
DROP TABLE EMERCONTACT;
DROP TABLE GUARDIAN;
DROP TABLE TEAM;
DROP TABLE TEAMMEMBER;
DROP TABLE CHARITY;
DROP TABLE EVENT;
DROP TABLE EVENTTYPE;
DROP TABLE CARNIVAL;
DROP SEQUENCE COMPETITOR_SEQUENCE;
COMMIT;
Buy FIT9132 Value in the Database Answers Online
Talk to our expert to get the help with FIT9132 Value in the Database Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
