FIT3171 Databases
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:
- Apply the theories of the relational database model.
- Implement a relational database based on a sound database design.
- Manage data that meets user requirements, including queries and
transactions.
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
Due Date Thursday, 26 May 2022, 4:30 PM (AEST) / 2:30 (MYT)
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
● Submissions more than 7 calendar days after the due date will receive a
mark of zero (0) and no assessment feedback will be provided.
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
Page 1 of 14
FACULTY OF
INFORMATION
TECHNOLOGY
INSTRUCTIONS
Run Monash (RM) is a running carnival which is held on separate dates at various Monash
campuses during different seasons (Summer, Autumn, Winter and Spring) of the year. The
carnival naming convention that Run Monash uses is RM Series name> . So, for example, a carnival to be held during the Autumn season at the
Clayton campus in 2022 will be named RM Autumn Series Clayton 2022.
Anyone can attend an RM Carnival, the carnivals are open to the public as well as Monash
staff and students. A carnival is run on a particular date, in a particular location and only
lasts for one day. RM only runs one carnival on any particular date. During a carnival a range
of events are offered from the following list (only some may be offered):
● 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.
When a competitor initially registers for Run Monash, they are assigned a unique competitor
number. A competitor is required to provide details of an emergency contact at the time of
registration. The relationship to the competitor can be Parent (P), Guardian (G), Partner (T)
or Friend (F).
When a carnival is being offered, Run Monash contacts all registered competitors and
provides details of the carnival date and what events are on offer. A competitor can only
attend one event at a particular carnival. Every entry is assigned an entry number. The entry
number is reused in each event. Run Monash also, on the carnival day, using official timing
devices, Run Monash records the entrants starting and finishing times.
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/invite other competitors from the
carnival to join their team. Team names are unique only within a given carnival. 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. Note that an individual competitor may be supporting a charity as an individual and
also the same or a different charity as a team member.
Page 2 of 14
FACULTY OF
INFORMATION
TECHNOLOGY
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 - Download the Assignment 2 Required Files (ass2-student.zip) archive from Moodle
- 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. - Run rm-schema-insert.sql
- 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). - Save, add, commit and push the file/s regularly while you are working on the
assignment - 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.
Page 3 of 14
FACULTY OF
INFORMATION
TECHNOLOGY
Note that the final SQL scripts you submit MUST NOT contain SPOOL or ECHO commands
(you may include them as you work but must remove them before submission). Please
carefully read the Marking Guide document.
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_dob Competitor’s date of birth
comp_email Competitor’s email
Page 4 of 14
FACULTY OF
INFORMATION
TECHNOLOGY
comp_unistatus Competitor’s university student/staff status (‘Y’ for Yes or
‘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
Page 5 of 14
FACULTY OF
INFORMATION
TECHNOLOGY
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:
(i) You may treat all of the data that you add as a single transaction since you are
setting up the initial test state for the database.
(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.
For this task ONLY, Task 2, you may look up and include values for the loaded
tables/data directly where required. However, if you wish, you can still use SQL to get
any non-key values.
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.
Your SQL must correctly manage transactions and use sequences to generate new
primary keys for numeric primary key values (under no circumstances may a new
primary key value be hardcoded as a number or value).
PL/SQL may ONLY be used for Task 5.