Assignment $2$: ICT$581$

Question $1$: Normalisation

a$.$ Identify Problems with Existing Design:

The existing design might exhibit modification anomalies, including insertion, update, and deletion anomalies. For instance:

Insertion Anomaly: If a Grand Slam tournament occurs without a winner, we cannot insert the tournament details without violating the primary key constraint.

Update Anomaly: If a player's name changes, we have to update multiple rows, leading to inconsistency.

Deletion Anomaly: If a player's record is deleted, information about the tournament might also be lost.

b$.$ Candidate Key$($s$)$ and Normal Form:

The candidate key$($s$)$ could be a combination of attributes like Year, Event, and Winner. The relation is likely in at least the First Normal Form $(1$NF$)$ as it contains atomic values. However, it may not satisfy higher normal forms due to potential transitive dependencies.

c$.$ Convert to Third Normal Form $(3$NF$)$:

To achieve $3$NF$,$ we should eliminate transitive dependencies. We split the relation into smaller relations to address this. For instance:

Grand Slam$\_$Tournament$($Year$,$ Event, Venue, Dates, Location, Surface$)$

Player$($Player$\_$Name, Country$)$

Winner$($Year$,$ Event, Player$\_$Name$)$

Here, the Winner relation serves as a bridge between tournaments and players, eliminating transitive dependencies.

d$.$ Explanation of New Design:

The new design preserves all original information while addressing the identified problems. Each relation now represents a distinct entity, reducing redundancy and dependency issues. By separating data into smaller relations, we ensure data integrity and facilitate efficient querying and updates.

Question $2$: Conceptual Design

For the conceptual design, we need to create an Entity$-$Relationship Diagram $($ERD$)$ for the Western Highlands Zoo database. We'll follow these steps:

Identify Entities: Animals, Animal Types, Enclosures, Zookeepers.

Determine Relationships: Animals belong to Animal Types, Animal Types belong to Classes and Regions, Animals are housed in Enclosures, Zookeepers care for Animals.

Define Attributes: For each entity, list attributes based on the provided information.

Design ERD: Using Crow's Feet notation, depict entities, relationships, and attributes. Ensure to indicate primary and foreign keys.

Make Assumptions: Clearly state any assumptions made during the design process, ensuring they don't contradict provided information.

Address Queries: Ensure the ERD can support the specified queries by structuring entities and relationships accordingly.

Once the ERD is created, it should be pasted into a word$-$processed document along with a legend explaining the notation and any assumptions made.

Let's start by creating the ERD for the Western Highlands Zoo database. We'll begin by identifying entities, relationships, and attributes based on the given case study.