Database Supplementary Assessment 2024 S1SQL

Java Python Database Supplementary Assessment 2024 S1

Please note that this is a supplementary assessment. You must clearly show a satisfactory understanding of the key areas covered in the unit, namely database design (including normalisation) and SQL/NoSQL. This means you MUST attempt all five tasks.

Your work will be assessed as Pass or Not Satisfactory, 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.

Generative AI tools cannot be used in this assessment task

In this assessment, you must not use generative artificial intelligence (AI) to generate any materials or content in relation to the assessment task. This whole assessment task requires students to demonstrate human knowledge and skill acquisition without the assistance of AI.

GIT STORAGE

Your work for these tasks MUST be saved in your individual local working directory (repo) under the Assignments folder in a subfolder called Supp.

In your local repo, please create a new folder called Supp under Assignments. Place the supplied  task4-es.sql, task5-json.sql, and task5-mongo.mongodb.js files 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 establish a clear history of your approach's development.

● Tasks 1, 2, 3 and 5 require a minimum of three pushes for each task as you develop your solutions,

● Task 4 requires a minimum of five pushes (at least 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 a fail grade.

Before submission via Moodle, you must log into the Git Lab server's web interface and ensure your files are present in your individual repo and that their names are correct.

In solving these tasks, you can only use concepts and syntax covered as part of the unit content during the past semester. Use of anything outside the unit content (e.g. SQL syntax such as WITH or the use of PL/SQL declare..begin..end) will not be accepted nor marked.

If you have any queries or issues while working on these tasks, please email your unit's role account.

Task 1: Relational Algebra (10 marks)

The following relations represent part of the Endangered Species database. Please refer to the case study in Appendix A to observe the business rules.

SPECIES (spec_genus, spec_name, spec_popular_name, spec_family, spec_natural_range) ANIMAL (animal_id, animal_sex, animal_added, centre_id, spec_genus, spec_name)

EXCHANGE (exchange_no, exchange_date, exchange_from_centre_id, exchange_to_centre_id, animal_id, et_code)

CENTRE (centre_id, centre_name, centre_address, centre_director, centre_phone_no) EXCHANGE_TYPE (et_code, et_description)

Write the relational algebra operations for the following queries (your answer must show an understanding of query efficiency):

i.      Show the animal’s id, sex, species’ popular name for all animals kept in the centre named ‘Alice Springs Desert Park’ . Note that only one centre is named  ‘Alice Springs Desert Park’ . [4 marks]

ii.      For each breeding exchange that happened between 1 Jul 2018 and 31 Aug 2018

(inclusive), show the exchange number, exchange date, centre name in which the animal was transferred from, centre name in which the animal was transferred to, animal id, species genus and species name. [6 marks]

Submission Requirement: A single PDF file called task1-ra.pdf containing your answer for the above questions.

Task 2: Database Design (25 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 page PDF file called task2-play.pdf of your model exported from LucidChart.

Task 3: Normalisation (15 marks)

Below is an example of booking details for a particular Last Curtain Theatre Company’s show.

Last Curtain Theatre Company Show Booking Details Show Date/Time: 6 July 2024, 7:00 PM Play Number: MR101 Play Name: Mouline Rouge Play Writer: Luhrmann and Craig Pearce Total number of patrons: 265 Theatre ID: 901 Theatre Street: 42 Rich Street Theatre Town: Lakemba Booking:
Booking Number	Number of seats	Total amount due	Paid (Y/N)	Client Number	Client Name
1001	4	200	Y	1	Michael Corsina
1002	2	100	N	1	Michael Corsina
1003	10	800	Y	2	Michelle Kinako
	… (only some bookings shown)

Represent this form in UNF. In creating your representation you should consider the Last Curtain Theatre Company case study in Task 2. You must keep each person's name as a simple attribute.

Continue the normalisation to third normal form. (3NF). Clearly write the relations in each step from the unnormalised form. (UNF) to the third normal form. (3NF). Clearly indicate primary keys on all relations from 1NF onwards by underlining the primary key attribute/s, and show the dependencies (partial dependency at 1NF, transitive dependency at 2NF, and full dependency at 3NF) via dependency diagrams, e.g. a_id → a_name, a_desc. Also include all candidate keys at the 1NF stage.

Do not add new attributes during the normalisation.

Submission Requirement: A single PDF file called task3-show.pdf containing your full normalisation.

Task 4: SQL (30 marks)

Remember, in arriving at your solutions for Task 4 you are ONLY permitted to use the SQL structures, syntax and functions covered within this unit.  SQL syntax and commands outside the covered work will NOT be accepted or marked. Views and/or PLSQL must not be used.

You can only 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  i.e. the appearance and 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 i.e. 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 who were born in the centre as a result of a breeding event and have a species popular name which include the word RHINOCEROS or HIPPOPOTAMUS and who were added to the system before the year  2020.

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 their popular name, then by animal sex, and for animals of the same popular name and sex by animal id descending. [4 mks]

ii.      List the genus name, and the ratio of the animals born in the wild to the total animals for

that genus in the database. Show the ratio as a percentage. For example, if the system has 100 animals from the Equus genus and 75 were born in the wild, the ratio (percentage born in the wild) will be 75/ 100 ie. 75.00. Display the percentage to two decimal points. Order the list according to the genus name.

Your output should have the general form (sample rows only shown):

[5 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. Your output should have the general form. (sample rows only shown):

[5 mks]

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.

Your output should have the general form (sample rows only shown):

[6 mks]

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.

Your output should have the general form (sample rows only shown):

[10 mks]

Submission Requirement: The supplied SQL script. task4-es.sql completed with your SQL commands to provide the required reports.

Task 5: NoSQL (20 marks)

Below is a JSON-formatted data sample for the 'Endangered Species' list of centres and animals belonging to each centre (note that the sample only includes partial data). The _id is the centre_id.

The animal's ID, popular name, sex, date added to the system, whether it was bred in a centre (Centre Bred) or added from the wild (From Wild), and the total number of exchanges the animal has been involved in are recorded (an exchange to a centre and back at a later stage is regarded as two exchanges).

{

"_id": "AUS10",

"centre_details": {

"centre_name": "Australia Zoo",

"centre_address": "1638 Steve Irwin Way, Beerwah  QLD  4519, Australia",

"center_type": "Zoo"

},

"total_number_animals": 6,

"animals": [

{

"animal_id": 4,

"popular_name": "Black Rhinoceros",

"sex": "F",

"date_added": "12-Jun-2018",

"wild_or_bred": "From Wild",

"no_of_exchanges": 2

},

{

"animal_id": 3,

"popular_name": "Quokka",

"sex": "F",

"date_added": "09-Jun-2018",

"wild_or_bred": "Centre Bred",

"no_of_exchanges": 0

},

... (only some animals are shown)

]

},

{

"_id": "AUS20",

"centre_details": {

"centre_name": "Werribee Open Range Zoo",

"centre_address": "K Road, Werribee VIC 3030, Australia",

"center_type": "Zoo"

},

"total_number_animals": 4,

"animals": [

{

"animal_id": 29,

"popular_name": "Common Hippopotamus",

"sex": "F",

"date_added": "13-Sep-2021",

"wild_or_bred": "Centre Bred",

"no_of_exchanges": 1

},

... (only some animals are shown)

]

}

... (only some centres are shown)

Remember, in arriving at your solutions for Task 5 you are ONLY permitted to use the SQL and MongoDB structures, syntax and functions covered within this unit.  Syntax and commands outside the covered work will NOT be accepted or marked. Views and/or PLSQL must not be used.

i.      Write an SQL statement that generates the above JSON formatted data from the tables owned by the user ES in the Oracle database.    [8 marks].

ii.      Create a new collection and insert all documents generated in (i) above into MongoDB.   Provide a drop collection statement right above the create collection statement. You may pick any collection name.

After the documents have been inserted, use an appropriate db.find command to list all the documents you added  [ 2 marks].

iii.      Display the full centre details (name, address and type) and the number of animals held for all centres with at least six animals  [2 marks].

iv.      Display the centre name and address for all centres that have at least one animal with the popular name Cheetah [2 marks]

v.      It has been decided to move the Quokka with an animal_id of 3 from the Australia Zoo (id = AUS10) to the Werribee Open Range Zoo (id = AUS20)

a.   show the full details for the Australia Zoo (id = AUS10) and the Werribee Open Range Zoo (id = AUS20) before this move [ 1 mark].

b.   move the Quokka with an animal_id of 3 as listed above (this move should be treated as a permanent transfer for this animal) [4 marks].

c.   show the full details for the Australia Zoo (id = AUS10) and the Werribee Open Range Zoo (id = AUS20) after the move has been recorded [ 1 mark].

Submission Requirement:

●   The supplied SQL script. task5-json.sql completed with your SQL statement to generate the required JSON-formatted data for Task 5 (i).

● The supplied MongoDB script. task5-mongo.mongodb.js completed with your

MongoDB commands to provide the required commands for Task 5 (ii) - Task 5 (v)