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~~~