讲解 FIT9132 Supplementary Assessment 2024 S2辅导 SQL语言

FIT9132 Supplementary Assessment 2024 S2

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), SQL and NoSQL.

To demonstrate this required understanding, you MUST attempt all five tasks. ENSURE your ID and name are added to EVERY 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.

TASKS:

●   Task 1: Relational Algebra (10 marks)

●   Task 2: Database Design (25 marks)

●   Task 3: Normalisation (15 marks)

●   Task 4: SQL (30 marks)

● Task 5: NoSQL (20 marks)

Total Available Marks = 100 mks

Your work will be assessed as either Pass or Not Satisfactory, detailed grade/feedback will not be provided for these tasks.

If you are assessed as having reached a Pass level (at least 50 marks out of the available 100) and you have attempted all questions, your unit grade will be upgraded to 50% P, otherwise your mark will remain as it stands with a grade of N.

DUE: MONDAY 9th DECEMBER 2024 at 4:30 PM via MOODLE SUBMISSION.

It is your responsibility to ENSURE that the files you submit to Moodle are the correct files, no work will be marked from GitLab

● We strongly recommend after uploading a submission, and prior to actually submitting,that you download the submission into a NEW EMPTY folder and double check its contents

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.

If you are having issues with the Monash CISCO VPN please install and use the appropriate Global VPN:

● Monash International VPN for China

● Students not in China - Global Protect (you may ignore the "only available to the VP Services portfolio until further notice" message)

Any queries or concerns while working on these tasks, must be emailed to your unit's role account. Please note that the role account will not respond to such enquiries over the weekend. For   this reason it is important that you get started as soon as possible, please do not leave the tasks to the weekend when assistance with password resetting will not be available.

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. In a given show, an artist may perform several roles and a given role may be played by several artists in the same 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.

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)

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 PL/SQL 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).