Contents
Question 1...............................................................................................................................................1
Question 2...............................................................................................................................................2
Question 1
a.Modification Anomaly
1.If the venue is changed, it needs to be modified in all the concerned rows. If not updated,
inconsistency arises.
2.If the sponsor name is changed, it needs to be updated in all places for the country. If not
updated, inconsistency arises.
3.If the Dates of the Tournament are changed, it needs to be modified, else inconsistency arises.
b.Candidate Keys ------------ Tournament, Year, Event, Winner
The relation is in 1NF as all the attributes are atomic. But there exists partial dependencies, so it
is not in 2NF.
c.3NF
Primary key ------ Tournament, Year, Event
Functional Dependencies:
Tournament, Year, Event -> Winner ------------- full dependency
Winner -> Country, Sponsor ---------- transitive dependency
Tournament -> Venue, Location, Surface, Dates ----------- partial dependency
Tables in 3NF
Tournament (TournamentName, Year,Event, WinnerName)
Winner (WinnerName, Country, Sponsor)
Location (TournamentName, Venue, LocationName, Surface, Dates)
Bold and red are primary keys and green are foreign keys.
d.Now if the Venue needs to be modified, it will be updated in Location Table.
Question 2
Step 1: List the entities:
1. Animal: This entity is used to store the animal details
2. AnimalType: This entity is used to store the animal types and related details
3. ConservationStatus: This entity is used to store the details about Conservation
Status of an animal type
4. Enclosure: This entity is used to store the details of enclosure.
5. EnclosureAnimal: This entity is used to store that what animal type is displayed at
which enclosure with number of animals of particular type.
6. AnimalClass: This entity is used to store the class details for animal type.
7. Region: This entity is used to store the region details for an animal type.
8. LifeEventType: This entity is used to store the different types of life events that can
occur in animal’s life.
9. AnimalEventHistor: To store the events occurred in animal life
10. Zookeeper: To store the zoo keeper details
Step 2: Assign attributes to the entities:
1. Animal(Animal_id, Name, Gender, Date_of_birth, Place_of_birth, isChildFriendly,
Type_id(fk))
2. AnimalType(AnimalType_id, Common_name, Scientific_name, Description, Diet,
Conservation_status(fk), Distribution, Habitat_type, isDisplayed, Class_id(fk),
Region_id(fk), Keeper_id(fk))
3. ConservationStatus(Status_id, Name, Description)
4. Enclosure(Enclosure_id, Group)
5. EnclosureAnimal(Enclosure_id(fk), AnimalType_id(fk), totalAnimals)
6. AnimalClass(Class_id, Name, Description, Head_keeper_id(fk))
7. Region(Region_id, Name, Other_details)
8. LifeEventType(EventType_id, Type, Description)
9. AnimalEventHistory(Event_id, Animal_id(fk), EventType_id(fk), Date, Notes)
10. Zookeeper(Keeper_id, Name, Date_of_birth, Address, Contact_phone, Next_of_kin ,
Next_of_kin _contact, Supervisor_id(fk))
Step 3: List the relationship between the entities:
1. Animal belongs to one Animal Type; where as an animal type can have many Animals.
2. Several Animals will have serval histories of events. It is an M: N relation displayed in
AnimalEventHistory.
3. Zookeeper supervises zero or many other zookeepers.
4. Head Keeper supervises many other zookeepers.
5. An Animal Type will belong to one Class, Region and Conversation Status.
Step 4: Assumptions in the creation:
1. Enclosure will contain many AnimalTypes and Animal Types will be displayed at
various enclosures. Thus created separate entity to solve M:M relation in
EnclosureAnimal.
2. One ZooKeeper can head at most one animal class.
3. One Zookeeper will be assigned to one or more Animal Types, but an animal Type will
have one zoo keeper only.
Step 5: Final ERD in Crow’s foot notation:
Diagram showing primary key denoted as PK and foreign key as FK
Step 6: Answers to the questions from ERD:
1. All the animal types represented at the zoo, the class they belong to, and their
conservation status.
Solution: The information will be easily obtained from AnimalType, respective Class
and Conservation status as the entity has a foreign reference to the respective entities.
From the ERD, entity “animaltype_id” identifies each animal identically. AnimalClass
tables has columns like name description and information about head keeper.
Conservation status contains the name and its description.
2. The number of individuals of each animal type, ranked from most individuals to least.
Solution: The data can be obtained by group by on animalType in Animal Entity
3. All the animal types currently off-display.
Solution: The data can be obtained by group by on animalType in Animal Entity
4. All Australian animal types that are endangered.
Solution: Data of Animals types are endandered can be obtained by knowing their
statusID, conservation table contains statusID and all the relevant information about the
animal type and their conservation status.The data can be obtained by joining
conservation_status and animal Type.
5. All the individual animals looked after by a particular keeper, and the enclosures they
are in.
Solution: The data regarding the information about keeper is contained in the table
ZooKeeper. There’s a entity Headkeeper_id conayned the details about the keeper.
Enclosures tables contains the information of enclosure and group.
6. The number of keepers supervised by each head keeper.
Solution: The data can be obtained from ZooKeeper entity. Each zookeeper has an
unique id. And supervisorID.
7. All the animal types represented in a particular zone.
Solution: The data can be obtained from Animal Type, EnclosureAnimal and enclosure
entities. There’s a entity available region_id in animal type table. And region table
contains all the information aboutparticular zone.
8. Enclosures with more than the average number of animals.
Solution: Data can be obtained from Enclosure and EnclosureAnimal entities, uniquely
defined with id.
9. All the keepers who work in a particular zone.
Solution: The data can be obtained from ZooKeeper, uniquely AnimalClass identified by
keeper id and class_id.
11. The history notes about Sophie the giraffe for 2019.
Solution: The AnimalLifeHistory, Animal and LifeEventType can be used to obtain the
data. From class animallifehistory, date , notes , etc can be retrieved.
