Reference no: EM133781869

Information Systems Principles and Practice

The assignment addresses the following learning outcomes for the unit:
Demonstrate an understanding of relational database principles and theory
Demonstrate practical skills in data modelling using entity-relationship modelling
Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF (partly)

Question 1: Normalisation
The following question is based upon the BIRD relation below, which lists information about the birds of the USA1. You can assume the data is representative. Note that scientific names are generally used worldwide for plants and animals to eliminate ambiguity, as common names can vary between countries.
You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively.

Answer the following questions. Make sure you answer all parts of each question.

What is the candidate key (or keys) of the relation, as it currently exists? What normal form is the relation in? Explain your reasoning.

Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.

Convert the relation to a set of relations in at least Third Normal Form (3NF). You only need to show the schema, not the data. Do not create any new attributes: work with the ones in the table. Give each of your new relations an appropriate name. Show all primary keys and foreign keys.

Explain how your new design addresses the problems you identified in (b). Also demonstrate that your set of relations has the dependency-preserving and lossless join properties.

Question 2: Conceptual Design
Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the Western Highlands Zoo database. Your ERD should be able to be implemented in a relational DBMS.

The Western Highlands Zoo, located somewhere in Australia, contains animals from all over the world and from many different habitat types. It is currently moving from a paper- and spreadsheet- based system to a database. The database needs to record information about the individual animals, types of animals, enclosures, and the zoo keepers. As well as basic record-keeping and reporting, it is anticipated that the database will also be able to be queried to make up customised ‘tours' for visitors on particular themes, such as Australian animals, reptiles, etc.

Each animal has a unique identification code, and the larger animals also have names (such as Sophie the giraffe). There are usually several different individuals of each animal type. Information held about individuals includes gender, date and place of birth (all the animals have been born in captivity, at this zoo or others). To record the history of each animal, notes, including the date, are made about each animal if a significant life event occurs (such as a health check, visit by the zoo dentist, birth of young, etc). Whether the animal is child-friendly is also noted, as sometimes the smaller animals are taken to schools for demonstrations.

There are many different animal types represented in the zoo. Each animal type has a common name, a scientific name, a description, diet, conservation status, distribution, and habitat type. Each animal type belongs to a broad class: these are mammals, reptiles, fish, amphibians, birds and invertebrates. Each animal type comes from one of six regions: these are Europe and Asia, North America, Central and South America, Africa, Southeast Asia, and Australia. It has a conservation status: Critically Endangered, Endangered, Vulnerable, Near Threatened, Least Concern and Unknown.
For example: the animal type with the common name Koala has scientific name Phascolarctos cinereus , is a herbivore, and is found in Eastern Australia in Eucalypt forest and woodland habitat. It is a mammal and comes from the region Australia, and its conservation status is Near Threatened. 2
At any given time, some of the animal types may be on display to the public, while others are off display (e.g. if their enclosure is being renovated).
The enclosures at the Zoo are uniquely identified and each contains a number of animals of a particular type. The enclosures are grouped into broad zones such as tropical rainforest, savannah grassland, Australian bush, etc, that mimic the animal's natural habitat. Some different animal types can be housed together, so an enclosure may contain a mixture of animals. Other animals can only be housed with others of the same type.
The Zoo employees many zookeepers. Information recorded about keepers is their name, date of birth, address, contact phone number, next of kin and next of kin's contact number. Each keeper is assigned to one or more animal types. Each major class of animals (mammals, etc) has a head keeper who supervises the relevant keepers.

Below are several queries and reports that the Zoo database must be able to support. There may well be many others as the Zoo analyses their business and plans for the future; therefore, you should design for flexibility as well as ensuring your ERD could answer these questions.

All the animal types represented at the zoo, the class they belong to, and their conservation status.
The number of individuals of each animal type, ranked from most individuals to least.
All the animal types currently off-display.
All Australian animal types that are endangered.
All the individual animals looked after by a particular keeper, and the enclosures they are in.
The number of keepers supervised by each head keeper.
All the animal types represented in a particular zone.
Enclosures with more than the average number of animals.
All the keepers who work in a particular zone.
The history notes about Sophie the giraffe.

What you have to do:

Use the case study description and querying requirements to create an entity-relationship diagram (ERD) for the Western Highlands Zoo database. Your ERD should be able to be implemented in a relational DBMS.

List and explain any assumptions you have made in creating the data model. You should make any assumptions that are required, but must state them clearly. Obviously, your assumptions should not contradict any of the information already provided.

You should use the crow's feet ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD, and indicate primary and foreign keys. The use of a drawing tool such as Visio will make this task easier.

Whichever tool you use, you must copy and paste the ERD into a word-processed document. Please make sure the labels and symbols in your ERD are readable. Enlarge them from the default if necessary Please note that hand-drawn ERDs are NOT acceptable.