Reference no: EM132680541

ICT581 Information Systems Principles and Practice - Murdoch University

Learning outcome 1: Demonstrate an understanding of relational database principles and theory
Learning outcome 2: Demonstrate practical skills in data modelling using entity-relationship modelling
Learning outcome 3: Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF (partly)

Problem 1: Normalisation
The following Problem is based upon the GRAND SLAM relation below that lists details of ‘Grand Slam' tennis tournaments and their winners over the last few years. You can assume that the data is representative.

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.

a. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.
b. What is the candidate key(s) of the relation? What normal form is the relation currently in? Explain your reasoning.
c. 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. Give each of your new relations an appropriate name. Show all primary keys and foreign keys.
d. Explain how your new design addresses the problems you identified in (a) and preserves all the information in the original design.

Problem 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. 1
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.

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

Attachment:- Information Systems Principles.rar