Business I.T. and Logistics Semester 2 – 2019
Business Data Management and Analytics Page 1 of 3
clean
ISYS2421 – Business Data Management and Analytics
Assignment 1 – SQL Queries – Part B
Due Date: End of Week 8 (Friday 20th September 2019)
SPECIFICATIONS
Read the following questions carefully. You will be asked to specify SQL queries to answer these
questions.
CASE STUDY
Relational Model
ANIMAL KEEPER FEED ANIMAL_FOOD CAGE SPECIES
AnimalID KeeperID FeedID FoodID CageNo SpeciesID
Name Surname FoodID Name cageSize Name
Colour Given AnimalID Location
DOB DOB QtyPerDay cageCost
ScientificName Sex KeeperID
Sex Joined
SpeciesID Resigned
CageNo Address
KeeperID Suburb
Postcode
Phone
Commission
RatePerHour
Role
SupervisorID
Entity-Relationship Diagram
supervises
CAGE
ANIMAL KEEPER
ANIMAL_FOOD
has
vet
feed
SPECIES uses
School of Business I.T. and Logistics Semester 2 – 2019
Business Data Management and Analytics Page 2 of 3
QUESTIONS
You will be working with a set of tables for a Zoo. You can access these tables by using the
ZOO database on the mysql server (mo.its.rmit.edu.au). You are to prepare 10 SQL query
statements that will provide answers to the following 11 requests for information.
(1 mark per question)
1. The zoo management needs to keep an eye on banana consumption. Create a view that lists
all the animals that eat “bananas”. Show the animal no, animal name (with the scientific
name in brackets) and the animal’s total consumption per week.
2. There is a secret police investigation at the zoo and they need a list all keepers from a suburb
that has “HILL” somewhere in the suburb name, who look after snakes. Show the keeper’s
full name, phone contact details and the suburb.
3. The zoo is interested in publicizing the OLDEST and YOUNGEST animals at the Zoo in
order to promote visitors. Please provide management a list of animals who are the
youngest or oldest. Show the name and date of birth of the animal.
4. Who is the highest paid keeper in the zoo ?
5. A number of new animals have been sent to the zoo. A list of all the cages that do not have
any animals in them is needed. Show all the cage details to help allocate the new animals.
6. Show the detail for cage number 100. Include the name of the animals in this cage, along
with the keeper that looks after the cage, and the keeper that looks after each animal in the
cage. Include the species name for each animal in cage. The data should show each animal
from oldest to youngest.
(1.5 marks per question)
7. For each keeper, show the number of cages cleaned by the keeper and the total cost of the
cages.
8. List each of the keepers who are both vets and cleaners (‘A’). Show the keeper’s name, age,
how long they have worked at the zoo and their supervisor’s name.
9. Write a query that lists ALL foods with a total consumption for one week (including foods
that are not consumed at all). Show the food id and name, along with the weekly
consumption.
10. (a) Create a view that displays how many animals there are of each species in the zoo.
(b) Write a query using the view that displays which species has the most animals in the
zoo, displaying the species name.
(3 marks)
11. Produce a report of your own design and write a query to solve it. Marks will be awarded
for report design (ie. How useful is the report), complexity of the query and originality.
Please provide:
a) Business question
b) SQL query
School of Business I.T. and Logistics Semester 2 – 2019
Business Data Management and Analytics Page 3 of 3
REQUIREMENTS
11 SQL queries that answer the questions asked, based on the data model and
implemented database (on mo.its.rmit.edu.au) provided.
ASSESSMENT
Assessment of the data model will be based on the following areas (by the tutor):
• How well the query answers the questions, in relation to the case study provided.
• Understanding of data structure
• Efficiency and simplicity of resulting query
DEMONSTRATION
All activity on the mo server is logged. It is expected that you will work on your
assignment on the mo server.
Students who show server activity not consistent with the assignment they submit (e.g. not
running many queries and not logging in very often) may be required to attend a
demonstration session where they will be asked to demonstrate and explain the queries
they have written and to write several new queries for the same database.
Failure to explain the queries written or the inability to write new queries will result in a
FAIL mark being recorded for assignment 1 part b.
SUBMISSION
• SQL queries (output not required) only.
• Assignment will be submitted online using the learning hub.
• By submitting online you are adhering to all the requirements specified on a standard
assignment coversheet.

~~~For this or similar assignment papers~~~