Database Supplementary Assessment 2023 S1
Please note as a supplementary assessment you are required to clearly show a
satisfactory understanding of the key areas covered in the unit, namely database
design (including normalisation) and SQL as listed in these tasks. This means you
MUST attempt all three tasks.
Your work will be assessed as Pass or Not Satisfactory, a detailed grade/feedback will
not be provided for these tasks. If you are assessed as having reached a Pass level your
unit grade will be upgraded to 50% P, otherwise your mark will remain as it stands with a
grade of N.
ENSURE your id and name are shown on each file you submit.
GIT STORAGE
Your work for these tasks MUST be saved in your individual local working directory (repo) under
the Assignments folder in a sub folder called Supp.
In your local repo please create a new folder, under Assignments called Supp. Place the supplied
Task3_es.sql file in this folder and add/commit/push to your remote repo before starting any
work.
Your work must be regularly pushed to the FIT GitLab server to build a clear history of
development of your approach.
● Task 1 and 2 require a minimum of three pushes for each task as you develop your
solutions,
● Task 3 requires a minimum of five pushes (as a minimum one for each completed part of
the question).
Failure to satisfy this requirement will mean that your work will not be accepted and as a
result your grade will remain as a fail grade.
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 and that their names are correct.
If you have any queries about these tasks or have issues while working on them please email your
unit's role account.
Task 1: Database Design (20 marks)
The Last Curtain Theatre Company is an amateur theatre group that holds plays at various
theatres in and around your local city. At present, all information concerning the plays they run, the
artists involved, and ticket sales are kept manually using a textbook and a diary. As demand is
growing for their plays, the Last Curtain Theatre Company has decided to embrace modern
technology and implement a database to keep up with their growing information needs.
For each play, the company records a play number to identify the play, the play name and the
name of the writer of the play. For each artist, a record is kept of their given name, family name,
address, contact telephone number and whether they are a member of the company or not. An
artist number, to identify an artist, should be assigned automatically by the system
A show is the on stage presentation to an audience of a particular play in a particular theatre on a
particular date and time (a given play is never offered in two theatres at the same date and time).
Some plays are popular and may be shown multiple times, even within one year. The artists and
the theatre involved with the production of a play may change for each show. The number of
people attending a given show is recorded. Each theatre is identified by a theatre number, In
addition the details of its location (street and town), the theatre manager’s name, contact phone
number and the number of seats the theatre holds are recorded.
In order to produce the company yearbook, it is important to keep track of the role of each artist in
each show. An artist may perform several roles in the one show.
Currently, bookings for tickets are taken in person or over the phone. Each booking is assigned a
unique booking number. Clients may pay for their tickets when they book or when they arrive at the
theatre. Only the details of the client (client name and contact number) who booked the seats are
kept, not each individual theatregoer. Each client is assigned a unique client number. For a booking
the number of seats booked and the total amount due is recorded as well as the paid status (if the
tickets have been paid for).
Create a logical level diagram using Crow’s foot notations to represent the Last Curtain
Theatre Company's data requirements described above using LucidChart. Clearly state any
assumptions you make when creating the model.
Please note the following points:
● Be sure to include all relations, attributes and relationships (unnecessary
relationships must not be included)
● Identify clearly the Primary Keys (P) and Foreign Keys (F), as part of your
design
● Surrogate keys must not be added
● In building your model you must conform to this units modelling requirements
● The following are NOT required on your diagram
● verbs/names on relationship lines
● indicators (*) to show if an attribute is required or not
● data types for the attributes
Submission Requirement: A single PDF file called task1_play.pdf of your model exported from
LucidChart.
Task 2: Normalisation (10 marks)
A local netball little league organises a competition for under-16 teams. Each team is
graded according to their age bracket. Grade A is for the 15-16 years old and grade B for
the 13-14 years old. Matches are organised for both grades, a match can have teams from
different grades competing. At the end of the competition, the winner is determined for each
grade. The competition runs for seven weeks and consists of eight teams. Within each
round, there are four matches scheduled on Saturday during the competition weeks. The
following schedule shows some samples of the information about the matches.
COMPETITION SCHEDULE
Round 1, 10-June-2023
Start Time: 9 AM Court no: 1
Teams: Firebirds (grade A) vs Phoenix (grade B)
Umpire: Natalie Wood
Umpire accreditation level: 2
Start Time: 9 AM Court no: 2
Teams: Ladybugs (grade B) vs Pegasus (grade B)
Umpire: Karen Wan
Umpire accreditation level: 1
Start Time: 10 AM Court no: 1
Teams: Lyrebirds (grade A) vs Butterflies (grade B)
Umpire: Natalie Wood
Umpire accreditation level: 2
Start Time: 10 AM Court no: 2
Teams: Swifts (grade A) vs Dragonflies (grade A)
Umpire: Karen Wan
Umpire accreditation level: 1
Round 2, 17-June-2023
Start Time: 9 AM Court no: 1
Teams: Firebirds (grade A) vs Pegasus (grade B)
Umpire: Amy Tan
Umpire accreditation level: 3
Start Time: 9 AM Court no: 2
Teams: Ladybugs (grade B) vs Butterflies (grade B)
Umpire: Karen Wan
Umpire accreditation level: 1
Start Time: 10 AM Court no: 1
Teams: Lyrebirds (grade A) vs Dragonflies (grade A)
Umpire: Natalie Wood
Umpire accreditation level: 2
Start Time: 10 AM Court no: 2
Teams: Swifts (grade A) vs Phoenix (grade B)
Umpire: Amy Tan
Umpire accreditation level: 3 etc
The database designer has included team_id and umpire_id attributes in the database to
uniquely identify teams and umpires in the competition.
The unormalised form (UNF) for this data has been selected as:
ROUND (round_no, match_date, (start_time, court_no, team1_id,
team1_name, team1_grade, team2_id, team2_name, team2_grade,
umpire_id, umpire_firstname, umpire_lastname, umpire_acc_level))
Convert this UNF to first normal form (1NF) and show all partial dependencies via
dependency diagrams. Continue the normalisation to third normal form (3NF) showing
dependencies at each stage.
Do not add new attributes during the normalisation. Clearly write the relations in each step
from the unormalised form (UNF) to the third normal form (3NF). Ensure all stages are
shown and that the primary keys are clearly indicated via underlining.
You may consolidate the relations after arriving at 3NF (if necessary).
Submission Requirement: A single PDF file called task2_competition.pdf containing
your full normalisation.
Task 3: SQL (30 marks)
In arriving at your solutions for Task 2 you are ONLY permitted to use the SQL structures, syntax
and functions which have been covered within this unit. SQL syntax and commands outside of
the covered work will NOT be accepted or marked. Views and/or PLSQL must not be used.
You are only permitted to code a single select statement for each question below.
For each question sample output showing the form of what you are required to produce is
provided. Note this is the form of the output ONLY ie. the appearance, the data you return will be
different.
Using the case study and data model listed in Appendix A to write SQL to answer the
following queries.
Note the required tables are available in the oracle database under the account es ie you
need to use, for example:
select * from es.animal;
i. Code the SQL SELECT statement to list the animal id, animal sex (displayed as
Male or Female), date added to the system, the genus and species and the popular
name for all animals whose popular name includes BLACK, who are in the redlist
categories CRITICALLY ENDANGERED or LEAST CONCERN and who were
added to the system after the year 2018.
The genus and species name should be output in a single column called
scientific_name, for example for the animal with the popular name Mountain Zebra
this column's contents would be Equus zebra.
Order the output by animal sex, and for animals of the same sex by animal id
descending (4 mks).
ii. Code the SQL SELECT statement to list how many animals, which have
been born in the wild (ie. were not the result of a breeding event), belong to
the EQUIDAE family? Name the output column "Number of animals" (4
mks).
iii. Code the SQL SELECT statement to list all animals indicating if the animal has been
exchanged or not - the list should show animal id, centre name, popular name, and an
exchange status message, indicating if the animal has been exchanged or not.
The list should be in animal id order within popular name order (6 mks).
Your output should have the general form (sample rows only shown):
iv. Code the SQL SELECT statement to list which is the most popular centre/s for
exchange to or from? Your output should list the centre name and the number of times
the centre has been used for an exchange_from or an exchange_to. The
exchange_from and the exchange_to will be calculated as a single figure.
For example, if a centre is involved in an exchange as a recipient (exchange_to) and
in another exchange as a provider (exchange_from) then this centre will be counted to
have 2 exchange events. The list should be displayed in the order of the centre name
(6 mks).
Your output should have the general form (sample rows only shown):
v. Code the SQL SELECT statement to list, for all centres, the centre id, centre name, number
of animals currently held at the centre, total value of grants made to the centre and the
percentage of the total grant amount made paid to the centre.
The number of animals must be in a column labelled "NUMBER OF ANIMALS", the total
grants made to the centre must be in a column labelled "TOTAL GRANTS" and the
percentage of the value of all grants made to the centre must be in a column labelled
"GRANTS %".
The total grants must be shown in the form $1,234,567.00 (see below).
Order the output with the centre with the highest number of animals first. Where two
centres have the same number of animals, order the output by centre id (10 mks).
Your output should have the general form (sample rows only shown):
Submission Requirement: The supplied SQL script task3_es.sql completed with your SQL
commands to provide the required reports.
Appendix A
International Programme for the Preservation of Endangered Species
As natural habitats disappear, it is increasingly difficult to ensure that all species can survive in the
wild. One solution is to use zoos, reserves and other types of organisations that keep wild animals
in captivity as conservation centres for threatened species.
To turn the keeping of animals into a preservation programme, it is necessary for centres to
collaborate with each other and to coordinate their activities. This requires that they share records
about animals, as well as carefully documenting exchanges and breeding. A database model has
been prepared to assist this process. The following material provides background information
describing the various components of the model.
Biological classification is a hierarchical structure, which moves from the highest (most general)
level of domain down to species (the most specific level). The lowest three levels are family, genus
and species.
Species
Organisations dealing with threatened species often need to check essential information about the
animals. The most fundamental information is the name of the species, as well as its popular name
and the genus and family to which the species belongs. The relevant details for the Tasmanian
Devil, for instance, are as follows ...
Popular name: Tasmanian Devil
Family: DASYURIDAE
Genus: Sarcophilus
Species: harrisii
It is also important to know the species’ natural range, which is usually given as a description of the
geographic regions and natural habitats where a species is found in the wild.
The breeding and exchange programmes are chiefly concerned with animals whose existence in
the wild is threatened, usually because they are rare or endangered in some way. This information
is captured by a species’ conservation status. The chief international source for this information
is the Redlist, which is maintained by the International Union for Conservation of Nature (IUCN).
The Redlist categorises species using the codes listed in the table below.
The Tasmanian Devil, for instance, is classified as Endangered, coded "EN". For a species the
model needs to record this Redlist status. In addition to this Redlist status the URL for the full
details for a species must also be recorded. For example, the full details for the Tasmanian Devil
are recorded at http://www.iucnredlist.org/apps/redlist/details/40540/0.
Animals
For particular animals it is important to know when they were added to the system, which centre
they are currently located at, their species name and the sex of the animal.
Centres
In most countries there is at least one zoo, reserve or other centre that hosts threatened species.
There are several different types of centre where endangered animals are kept, including zoos,
wildlife parks, sanctuaries and nature reserves. Centres need to contact each other, as do
agencies, governments and the general public. Essential data includes the centre's name, the
director’s name, as well as the phone number and address.
Conservation Agencies
Conservation agencies in this context are bodies that coordinate, promote or manage conservation
activities. Two main types of agency need to be considered:
National agencies are based within a particular country and usually report directly to the
government ministry responsible for conservation policy. They include authorities (e.g. Environment
Australia) and usually manage various conservation programmes, as well as overseeing important
conservation activities (e.g. national parks).
International agencies are bodies that are active in many countries. Formal association with
particular countries is normally via a treaty, signed by the agency director and the relevant Minister
in countries that are signatories to the agreement.
For a conservation agency the data which needs to be recorded is the agency's name, its address
and phone number and the type of agency (National or International).
Funding sources (Grants)
Conservation programmes depend on funds. Conservation agencies provide grants to centres.
There are several types of grants. One-off grants help centres build new facilities, purchase new
equipment, cover the costs of exchanges, or to capture new animals from the wild. Annual grants
help with the routine costs of running preservation programmes, such as salaries, and routine
maintenance costs.
Exchange programmes
Animals are regularly exchanged between centres. Zoos, for example, often send offspring of
successful breeding to other zoos or reserves. The practice allows other centres to raise public
awareness by displaying the animals. It also reduces the risk of losing entire groups of animals
should disease or other misfortune hit a centre. Most centres participate in exchanges at some
time or other.
Exchanges occur by transfer of an animal from one centre to another. There are four main reasons
for such exchanges: loans, medical treatment, breeding and permanent transfers. Sometimes
animals are exchanged several times, e.g. for breeding purposes. Details of exchanges need to be
recorded as part of an animal’s life history.
Breeding programmes
Some species of animals are now so rare in the wild that their continued existence depends on
cooperative breeding programmes involving zoos and wildlife reserves around the world. Centres
need to keep careful track of breeding events. For each breeding event it is essential to know when
and where (i.e. the centre) it took place, as well as the female and male involved.
Any offspring that result are normally kept at the zoo where breeding takes place, but may later be
transferred permanently elsewhere. The centre needs to be able to identify which breeding event
produced which offspring.
Animals captured from the wild will have no breeding event details available.
Data Model
A data model has been developed to meet these requirements - the model is shown on the next
page.