data 编程代做、代写 SQL 程序语言

日期：2023-07-21 07:17

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.