Zoo Animals Database

Case study

The World of Animals Zoo Director is pleased with your work so far and asked you to go on to implement your design. They have an addition to the original specifications that you should note:

• The Zoo wants to provide a number of experiences to enable the visitors to interact with the animals. At present there are two different types of experience, guided tours and close encounters with the animals. They are modelling these somewhat on those at Perth Zoo: see https://perthzoo.wa.gov.au/walks-and-talks and https://perthzoo.wa.gov.au/close-encounters .
• The experiences happen on various days and times, but each one is scheduled regularly (for example, the Penguin tour is always Tuesdays and Thursdays at 10.00am). All the experiences are scheduled for a month ahead (i.e. your database will be able to show the schedule for the whole of July and August 2018).
• The following information is to be stored about each experience: Name, brief description, date, time, and duration. In addition, the close encounters experience also stores minimum age, maximum group size, cost, and any restrictions on who can participate.
• Each of the experiences has one or more particular animal types as its main attraction, and of course an animal type may participate in more than one experience.
• The Zoo wishes the list of experiences to be searchable so that potential visitors can see what is on offer in the coming weeks. However, they are not planning on using it to make bookings as yet.

Part 1: ERD and schema

a)Create the ERD for this database that you are going to use as the basis of your implementation.

b)Show the relational schema in 3NF that will be the basis of your implemented design. Show your relations using the following convention:
RELATION_NAME (PrimaryKey, Attribute, Attribute, ForeignKey)

Part 2: Data dictionary

Create a data dictionary for your database. This should include:

a)For each table: a definition of each column (attribute), consisting of the column name, brief description of what it represents, its data type and size, domain (allowable values), any default value, whether it is required, whether unique, and any constraints (primary key, foreign key). Use the data types available in Oracle.

b)For each of the columns that is a foreign key, give the appropriate referential integrity rules (i.e. the on delete. ; on update etc actions that should apply when the corresponding primary key is altered). The appropriate action should be included whether or not there is a statement in Oracle to implement it.

c)Any business rules (enterprise constraints) that should apply to the database, apart from those that have already been covered in your design by domain constraints or cardinality.

Note that your data dictionary must be consistent with your ERD and schema.

Part 3: Implementation

Implement the tables for the World of Animals Zoo in Oracle SQLPlus on arion.murdoch.edu.au. Note the following:

a)All tables should be created as per your ERD and data dictionary; the marker will check your ERD against your tables. You do NOT need to include the SQL CREATE TABLE statements that you used to create the tables.

b)All entity and referential integrity constraints should be created and appropriately named.

c)All columns (attributes) should be of an appropriate data type/size and be set as required, unique or not as appropriate.

d)All domain constraints should be implemented. Use CHECK constraints as required. You are not expected to create triggers.

e)All tables should be populated with sample data that will allow the marker to test that your database fulfils the application requirements as specified and supports the transactions and views listed below. Also provide the same sample data in your Word document. If you use a screen dump, it MUST be a size that is readable without zooming.

Note you do not need to include the SQL INSERT statements that you used to add the data. You can use the Perth Zoo website for inspiration on sample data.

f)SELECT, UPDATE, INSERT and DELETE permissions should be GRANTED on all database objects (particularly tables and views) to the user MARKERTL. This is most important. If you do not grant this permission, the marker will not be able to mark this part of your assignment and you will not get any marks for it.

g)Please state in your documentation whether you have used your T account or E account.

Part 4: Views

• Create VIEWS for the following in Oracle (views should be named as ViewA, ViewB etc). Note that some of these have changed from Assignment 1 in line with the Zoos new requirements.
• You should also provide the CREATE VIEW statements you used to create the views in your Word document.
• Include sufficient attributes in your SQL so that the result tables can be interpreted. Rename columns if you need to for understandability.

VIEW AAll the animal types represented at the zoo, the class they belong to, and their conservation status.

VIEW BThe number of individuals of each animal type, ranked from most individuals to least.

VIEW CAll Australian animal types that are endangered.

VIEW DAll the individual animals looked after by a particular keeper, and the enclosures they are in.

VIEW EThe names of all the head keepers, and the number of keepers supervised by each of them.

VIEW FAll the animal types represented in a particular zone.

VIEW GAll the keepers who work in a particular zone.

VIEW HThe history notes about Sophie the giraffe for 2017.

VIEW I All the experiences for a particular day (e.g. 4 August 2018), in time order, starting in the morning.

VIEW J All the guided tours that involve elephants for the week beginning 1 August 2018.

NOTES:

Note that where the requirement is for a particular keeper/zone/etc, you can assume for this assignment the view definition includes a specific value such as Bridget Jones or Australian Bushland (although, obviously, it should work for all relevant values).

Formatting:

You can use TO_CHAR to format your output: (see e.g. https://www.techonthenet.com/oracle/functions/to_char.php )

Dates and times in Oracle:

https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
http://www.akadia.com/services/ora_date_time.html
http://psoug.org/reference/date_func.html