FIT3171 Assignment 2 Solution

Creating, Populating and Manipulating Database - Run Monash (RM)

Purpose Students will be asked to implement, via SQL, a small database in the Oracle RDBMS from a provided logical model case study, followed by the insert of appropriate data to the created tables. Once populated the database will be used to: carry out specified DML commands and make specified changes to the database structure via SQL. This task covers learning outcomes:
1. Apply the theories of the relational database model.
3. Implement a relational database based on a sound database design.
4. Manage data that meets user requirements, including queries andtransactions.
Your task This is an open book, individual task. The final output for this task will be a set of tables and data implemented in the Oracle RDBMS
Value 25% of your total marks for the unit
Submission ● Via Moodle Assignment Submission.
● FIT GitLab check ins will be used to assess history of development
Assessment
Criteria ● Application of relational database principles.
● Handling of transactions and the setting of appropriate transaction boundaries.
● Application of SQL statements and constructs to create and alter tables including the required constraints and column comments, populate tables, modify existing data in tables, and modify the "live" database structure to meet the expressed requirements (including appropriate use of constraints).
Late Penalties ● 10% deduction per calendar day or part thereof for up to one week
Support Resources See Moodle Assessment page
Feedback Feedback will be provided on student work via:
● general cohort performance
● specific student feedback ten working days post submission ● a sample solution
INSTRUCTIONS
● Marathon 42.2 Km
● Half Marathon 21.1 Km
● 10 Km Run
● 5 Km Run
● 3 Km Community Run/Walk
Run Monash expects to offer around 10 - 20 such events across all carnivals in a given year.
A model to represent this system has been developed:

The schema/insert file for creating this model (rm-schema-insert.sql) is available in the archive ass2-student.zip - this file partially creates the Run Monash tables and populates several of the tables (those shown in purple on the supplied model) - you should read this schema carefully and be sure you understand the various data requirements. You must not alter the schema file in any manner, it must be used as supplied.
Steps for working on Assignment 2
1. Download the Assignment 2 Required Files (ass2-student.zip) archive from Moodle
2. Extract the zip archive and place the contained files in your local (MoVE or local HDD) repository in the folder /Assignments/Ass2. Do not add the zip archive to your local repo. Then add, commit and push them to the FITGitLab server.
3. Run rm-schema-insert.sql
4. Write your answer for each task in its respective file (e.g. write your answer for Task 1 in T1-rm-schema.sql and so on).
5. Save, add, commit and push the file/s regularly while you are working on the assignment
6. Finally, when you have completed all tasks, upload all required files from your local repository to Moodle (if you are using MoVE you will need to download them to your local HDD first - do not attempt to upload from MoVE). Check that the files you have uploaded are the correct files (download them from Moodle into a temporary folder and check they are correct). After you are sure they are correct, submit your assignment.
TASKS
ENSURE your id and name are shown at the top of any file you submit.
GIT STORAGE
Your work for these tasks MUST be saved in your individual local working directory (repo) in the Assignment 2 folder and regularly pushed to the FIT GitLab server to build a clear history of development of your approach. Any submission with less than five pushes to the FITGitLab server will incur a grade penalty of 10 marks. Please note five pushes is a minimum, in practice we would expect significantly more.
Before submission via Moodle you must log into the web interface of the GitLab server and ensure your files are present in your individual repo.
TASK 1: DDL (20 marks)
For this task you are required to add to T1-rm-schema.sql, the CREATE TABLE and CONSTRAINT definitions which are missing from the supplied partial schema script in the positions indicated by the comments in the script.
The table below provides details of the meaning of the attributes in the missing four tables. You MUST use exactly the same relation and attribute names as shown in the data model above to name the tables and attributes which you add. The attributes must be in the same order as shown in the model. These new DDL commands must be hand-coded, not generated in any manner (generated code will not be marked).
Table name Attribute name Meaning
EMERCONTACT
ec_phone Emergency contact’s phone number (unique identifier)
ec_fname Emergency contact’s first name
ec_lname Emergency contact’s last name
COMPETITOR
comp_no Unique identifier for a competitor
comp_fname Competitor’s first name
comp_lname Competitor’s last name
comp_gender Competitor’s gender (‘M’ for male, ‘F’ for female, or ‘U’ for
‘Undisclosed’)
comp_email Competitor’s email
'N' for No)
comp_phone Competitor’s phone number
comp_ec_relationship Emergency contact relationship to competitor (‘P’ for
Parent, ‘G’ for Guardian, ‘T’ for Partner, or ‘F’ for Friend)
ENTRY
entry_no Entry number (unique for each event)
entry_starttime The entrant start time
entry_finishtime The entrant finish time
TEAM
team_id Team identifier (unique)
team_name Team name
team_no_members Number of team members
To test your code you will need to first run the provided script rm-schema-insert.sql to create the other required tables. rm-schema-insert.sql, at the head of the file, contains the drop commands for all tables in this model. If you have problems with Task 1 and/or Task 2 simply rerun rm-schema-insert.sql which will cause all tables to be dropped and correct the issues in your script.
TASK 2: Populate Sample Data (20 marks)
Before proceeding with Task 2, you must ensure you have run the file rm-schema-insert.sql (which must not be edited in any way) followed by the extra definitions that you added in Task 1 above (T1-rm-schema.sql).
Load the EMERCONTACT, COMPETITOR, ENTRY, and TEAM tables with your own test data using the supplied T2-rm-insert.sql script file, and SQL commands which will insert as a minimum, the following sample data:
(i) 5 EMERCONTACT entries
● of the 5 contacts added 3 must be contacts for more than one competitor
(ii) 15 COMPETITOR entries
● Have at least 10 competitors who are Monash student/staff
● Have at least 2 competitors who are not Monash student/staff
● Included at least 2 competitors who are under 18 years of age (iii) 30 ENTRY entries
● Included at least 10 competitors
● Included at least 6 events from 3 different carnivals
● Have at least 5 competitors who join more than 2 events
● Have at least 2 uncompleted entries (registration only)
(iv) 5 TEAM entries
● Have at least 2 teams with more than 2 members
● At least one team with the same name in different carnivals
In adding this data you must ensure that the test data thoroughly tests the model as supplied, so as to ensure your schema is correct.
Your inserted data must conform to the following rules:
(ii) The numeric primary key values for this data should be hardcoded values (i.e. NOT make use of sequences) and must consist of values below 100.
(iii) The data added must be sensible eg. entry finish times should be after entry start times with a sensible running duration.
In carrying out this task you must not modify any data or add any further data to the tables which were populated by the rm-schema-insert.sql script.
____________________________________________________________ For all subsequent questions (Task 3 onwards) you are NOT permitted to:
● manually lookup a value in the database to obtain its primary key or the highest/lowest value in a column,
● manually calculate values (including dates/times) external to the database, e.g. on a calculator and then use such values in your answers. Any necessary calculations must be carried out as part of your SQL code, or
● assume any particular contents in the database - rows in a table are potentially in a constant state of change
Your answers must recognise the fact that you have been given, with the supplied insert file, only a very small sample snapshot of a multiuser database, as such you must operate on the basis that there will be more data in all of the tables of the database than you have been given. Your answers must work regardless of the extra quantity of this extra "real" data and the fact that multiple users will be operating in the tables at the same time. You must take this aspect into consideration when writing SQL statements.
You must ONLY use the data as provided in the text of the questions. Failure to adhere to this requirement will result in a mark of 0 for the relevant question.
TASK 3: DML ( 25 marks)
Your answers for this task (Task 3) must be placed in the supplied SQL Script T3-rm-dm.sql
(a) Oracle sequence is going to be implemented in the database for the subsequent insertion of records into the database for the COMPETITOR and TEAM tables.
Provide the CREATE SEQUENCE statements to create two sequences which could be used to provide primary key values for the COMPETITOR and TEAM tables. Both sequences should start at 100 and increment by 1. Immediately prior to the create sequence commands, place appropriate DROP SEQUENCE commands so they will cause the sequences to be dropped before being created if they exist. Please note that there can only be these two sequences introduced and used in Task 3.
[1 mark]
(b) Record two competitors named ‘Daniel Kai’ and ‘Annabelle Kai’. Both of them are Monash students. They nominated ‘Jack Kai’ (phone number: ‘0476541234’), their father, as their emergency contact.
[7 marks]
These changes must be treated as a single transaction.
[7 marks]
These changes must be treated as a single transaction.
[5 marks]
On the other hand, ‘Annabelle Kai’ has asked a few friends to join her team for this carnival but she is not sure if any have actually taken up the offer, so she directs Run Monash that her team, ‘Kai Speedstars’, should be disbanded. She will still participate in the carnival as an individual runner and she will support the ‘Beyond Blue’ charity
[5 marks]
TASK 4: DATABASE MODIFICATIONS ( 15 marks)
Your answers for these tasks (Task 4) must be placed in the supplied SQL script T4-rm-alter.sql

The required changes must be made to the "live" database (the database after you have completed tasks 1, 2 and 3) not by editing and executing your schema file again. Before carrying out the work below, please ensure that you have completed tasks 1, 2 and 3 above.
If in answering these questions you need to create a table, please place a drop table statement prior to your create table statement.
(a) Run Monash has decided that they would like to have the elapsed time (finish time start time) for a runner in a particular event stored as part of the system, rather than having to calculate it every time it is required.
Add a new attribute which will record the runner’s elapsed time in an event. The time should be stored as the number of minutes elapsed to two decimal places eg. 26.82
[2 marks]
(b) Some teams indicated that they would like to support more than one charity in future carnivals. They also want to store the percentage (0 to 100) of total raised funds that goes to each charity.
Modify the database structure to meet this new requirement.
[6 marks]
Modify the database structure to meet this new requirement
[7 marks]
TASK 5: PL/SQL ( 20 marks)
Your answers for these tasks (Task 5) must be placed in the supplied SQL script T5-rm-alter.sql
The required PL/SQL must be made to the "live" database (the database after you have completed tasks 1, 2, 3, and 4) not by editing and executing your schema file again. Before carrying out the work below, please ensure that you have completed tasks 1, 2, 3 and 4 above.
(a) Run Monash have noted that several competitors have enrolled in multiple events in the same carnival which they do not wish to occur. Write a trigger to prevent this issue in future carnivals.
[3 marks]
After making these changes, write a single trigger which will automatically:
● calculate the elapsed time attribute added in 4(a) when the finish time is updated (ie. the competitor finished the run), and
● update the ‘eventype_record’ and ‘eventtype_recordholder’ when the new elapsed time is faster than the current eventype_record time. Note that, initially, the value of these two attributes is null.
[8 marks]

● check if the competitor wants to create/join a team (ie. they provide a team name) and, if provided, whether the team name exists for the input carnival:
○ If the team does not exist then the procedure should add a new team and assign this competitor as the team leader, or
○ if the team exists then the procedure should add the entrant into the existing team
The output from this procedure, returned by an OUT parameter, must be a status string indicating successful registration in the event or the issue that prevented registration on this attempt. For a failed registration you are not required to report all possible errors which would occur, only the first found by your procedure.
[9 marks]
Ensure your trigger/stored procedure definition finishes with a slash(/) followed by a blank line as detailed in the week 10 workshop and week 11 applied class. In addition, when coding your triggers/procedure, you must provide output messages where appropriate.
Submission Requirements
For this assignment there are five files you are required to submit:
● T1-rm-schema.sql
● T2-rm-insert.sql
● T3-rm-dm.sql
● T4-rm-alter.sql
● T5-rm-plsql.sql
Do not zip these files into one zip archive, submit five independent SQL scripts. The individual files must also have been pushed to the FIT GitLab server with an appropriate history as you developed your solutions (a minimum of five pushes - 1 per file, however we would strongly recommend more than this). Please ensure your commit comments are meaningful.
Late submission will incur penalties at the rate of -10 marks for every 24 hours the submission is late.
Please note we cannot mark any work on the GitLab Server, you need to ensure that you submit correctly via Moodle since it is only in this process that you complete the required student declaration without which work cannot be assessed.
It is your responsibility to ENSURE that the files you submit are the correct files - we strongly recommend after uploading a submission, and prior to actually submitting, that you download the submission and double-check its contents.
Your assignment MUST show a status of "Submitted for grading" before it will be marked.
Please carefully read the documentation under the "Assignment Submission" on the Moodle Assessments page which covers things such as extensions and resubmission.
Resubmission
Marking Guide
Submitted code will be assessed against an optimal solution for this task - this optimal solution will be available as a sample solution after Assignment 2 has been graded. Given that this is SQL there are often several alternative approaches possible, such alternatives will be graded based on the code successfully meeting the requirements, If it does the answer will be accepted and graded appropriately.
Marking Criteria Items Assessed
TASK 1 DDL 20 marks
DDL Creation of tables Maximum 9.5 marks - Create table:
● Marks awarded for correct table DDL
● Marks awarded for correct attributes/data types
● Marks awarded for correct PK definition
● Marks awarded for correct use of column comments
● Mark penalty applied if generated schema used
DDL implementation of non PK database constraints Maximum 10.5 marks - Non PK Constraints:
● Marks awarded for correct implementation of non PK constraints:
○ CHECK
○ UNIQUE
○ FK
TASK 2 Populate Sample Data 20 marks
Insert of required items test data Maximum 10 marks - Insert of data:
● Marks awarded for correct insert of required data
○ 5 EMERCONTACT entries
○ 15 COMPETITOR entries
○ 30 ENTRY entries
○ 5 TEAM entries
● Marks awarded for correct management of transactions
Insert of valid test data Maximum 10 marks - Valid data inserted:
● Marks awarded for validity of data inserted
○ meets the requirements expressed in the assignment brief
● Marks awarded for correct management of dates when inserting

Task 3 DML 25 marks
Maximum 25 marks - Satisfy brief requirements:
● Marks awarded (a) - (e) for SQL code which meets the expressed requirement
● Mark penalty applied if commit not used appropriately
Task 4 Database Modifications 15 marks
Maximum 15 marks - Satisfy brief requirements:
● Marks awarded (a) - (c) for SQL code which meets the expressed requirement (including appropriate use of constraints). In making these modifications there must be no loss of existing data or data integrity within the database.
● Mark penalty applied if commit not used appropriately
● Mark penalty applied if column comments not used where required
Task 5 PL/SQL 20 marks
Maximum 20 marks - Satisfy brief requirements:
● Marks awarded (a) - (c) for PL/SQL code which meets the expressed requirement.
● Marks awarded for writing a test harness for each question (a) - (c) which includes both successful and failed tests (all errors your code raises must be tested) .
Penalty Criteria Penalty Applied
Limited/No push of model to FITGitLab server resulting in lack of development history. If less than five pushes showing a clear development history a grade deduction of 10 marks applied. Note that the expectation is that you would push significantly more times than this.
Use of
● PL/SQL in Task 1 - Task 4,
● VIEWs, and/or
● SET ECHO or SPOOL commands Use of PL/SQL in Task 1 - Task 4, VIEWS, inclusion of SET ECHO/SPOOL commands in submitted scripts will result in a grade deduction of 10 marks being applied.
Incorrect application of relational database principles Marks will be deducted, based on the particular question, where basic relational model principles have been violated. For example, creation of a table which is not in 3NF