Get Cheapest Assignment in Australia, UK, US, UAE, Canada and NZ Order Now

ICT581 Information Systems Principles and Practice

0 Comments

ICT581 Information Systems Principles and Practice

Worth: 20% of your final grade.

Due date: Saturday 5 November 2022, 11:55 PM

Submit to:      LMS, via the Assignments tool. Submit as a SINGLE Word (or PDF) document

including all parts of the assignment. You should include your name and student number as part of your document filename. Your name and student number (or Each group member’s) should also be included within the assignment document. The missing names in a group will not be marked.

Late assignments It won’t have an extension unless otherwise noticed. The late submission will be penalised at the rate of 5% per day or part of a day.

* This is a Group or Individual assignment. Only one file needs to be submitted per group.

ICT581 Information Systems Principles and Practice assignment requires you to answer a number of questions on relational database principles, and to design a database based on a case study.

The assignment addresses the following learning outcomes for the unit:

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

Marks are distributed as follows:

Question 1: Normalisation40
Question 2: Conceptual design50
Report writing (including layout, formatting, table of contents, and proofreading)10
Total100

Question 1: Normalisation (40 marks)

The following question is based upon the GRAND SLAM relation below which 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.

Answer the following questions. Each question is worth ten (10) marks

  1. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.
    1. What is the candidate key(s) of the relation? What normal form is the relation currently in? Explain your reasoning.
    1. Convert the relation to a set of relations in at least the 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.
    1. Explain how your new design addresses the problems you identified in (a) and preserves all the information in the original design.

GRAND SLAM

TournamentYearEventWinnerCountryVenueDatesLocationSurface
Australian Open2021Men’s singlesNovak DjokovicSerbiaMelbourne Park8 – 21 FebMelbourneHard
French Open2021Men’s singlesNovak DjokovicSerbiaRoland Garros30 May-13 JuneParisClay
Wimbledon2021Men’s singlesNovak DjokovicSerbiaThe All England Club28 Jun-11 JulLondonGrass
U.S. Open2021Men’s singlesDanill MedvedevRussiaUSTA National Tennis Center30 Aug – 12 SepNew YorkHard
U.S. Open2021Women’s singlesEmma RaducanuUKUSTA National Tennis Center30 Aug – 12 SepNew YorkHard
Australian Open2021Women’s singlesNaomi OsakaJapanMelbourne Park8 – 21 FebMelbourneHard
French Open2021Women’s singlesBarbora KrejcikovaCzech RepublicRoland Garros30 May-13 JuneParisClay
Wimbledon2021Women’s singlesAshleigh BartyAustraliaThe All England Club28 Jun-11 JulLondonGrass
Australian Open2022Men’s singlesRafael NadalSpainMelbourne Park9 – 30 JanMelbourneHard
French Open2022Men’s singlesRafael NadalSpainRoland Garros22 May-5 JunParisClay
Wimbledon2022Men’s singlesNovak DjokovicSerbiaThe All England Club27 Jun-10 JulLondonGrass
U.S. Open2022Men’s singlesCarlos AlcarazSpainUSTA National Tennis Center29 Aug – 11 SepNew YorkHard
U.S. Open2022Women’s singlesIga SwiatekPolandUSTA National Tennis Center29 Aug – 11 SepNew YorkHard
Australian Open2022Women’s singlesAshleigh BartyAustraliaMelbourne Park9 – 30 JanMelbourneHard
French Open2022Women’s singlesIga SwiatekPolandRoland Garros22 May-5 JunParisClay
Wimbledon2022Women’s singlesElena RybakinaKazakstanThe All England Club27 Jun-10 JulLondonGrass

Question 2: Conceptual Design (50 marks)

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.

1 Data from https://perthzoo.wa.gov.au/animal/koala

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. (Each question is worth 5 marks)

  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.

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.