Figure 1: Entity Relationship Diagram
Source: Created by Author
Person (id_Person [PK], FirstName, LastName, Type, ContactPhone, Address, City, State, Postcode)
Medicalofficer (id_MedicalOfficer[PK][FK], Qualification, QualificationYear, Specialization)
Coach (id_Coach [PK] [FK], CertificationLevel, CertificationDate, WWCCheck)
Swimmers (id_Swimmer [PK] [FK], DOB, Gender, id_Coach [FK], id_Team [FK])
Team (id_Team [PK], DateRegistration, id_Manager [FK], id_Leader [FK], id_Country [FK])
Country (id_Country [PK], Name)
Competition (id_Competition [PK], CompetitionName, Location)
Aquaticcenter (id_AquaticCenter [PK], Capacity, Location)
Pool (id_Pool [PK], PName, LaneCount, Length, id_AquaticCenter [FK])
Event (id_Event [PK], EName, TotalDistance, QualifyingTime, id_AquaticCenter [FK])
Race (id_Race [PK], RaceName, Day, StartTime, id_Event [FK], id_Pool [FK])
Eventswimmer (id_Event [PK] [FK], id_Swimmer [PK] [FK], QualifyingDate, QualifyingTime, id_QualifyingCompetition [FK])
Raceswimmer (id_Race [PK] [FK], id_Swimmer [PK] [FK], Lane, RecordTime, Place)
- Each member can be either a swimmer, coach or a medical officer.
- Each Swimmer is a member.
- Each coach is a member.
- Each medical officer is a member.
- Each swimmer can have one and only one coach.
- Each coach can coach many swimmers.
- Each competition has many qualifiers.
- Each eventswimmer is associated with one and only one competition.
- Each event has many swimmers registered for it.
- Each swimmer can register for many events.
- Each swimmer can have many registrations for events.
- Each event has many races.
- Each race is associated to one and only one event.
- Each Aquatic centre has many pools.
- Each pool is in one and only one aquatic centre.
- Each race takes place one and only one pool location.
- Each pool location can have many races.
- Each swimmer has many races.
- Each race can have many swimmers participated to it.
- Each team has many swimmers in it.
- Each swimmer belongs to one and only one team.
- Each team manager is a person and a person may be a manager of a team.
- Each swimmer may become leader of a team. Each team has one and only one swimmer as leader.
- Each country has many teams and each team represents one and only one country.
- Timings for races are stored in seconds.
- For some race, a lane can be empty due to a smaller number of swimmers.
- Lane is unique for each swimmer in a race.
- Location for Aquatic centre and competition is different.
- Person is a supertype entity. Coach, Swimmers and Medical Officer are subtype entities of a person.
To keep consistency, the database will use strict naming conventions that will allow easy implementation and maintenance of the database when completed. The following conventions apply:
- TABLENAME (all in capital letters)
- AttributeName (Capital letter for first letter of new word)
- id_AttributeName (Primary and Foreign keys)
As all the relations above has atomic values and it does not have repeating groups of data. It can be said the relations are in first normal form.
For a relation, to be in a second normal form, it should be in first normal form first. The first normal has been defined already for the ERD. For second normal form, the relation does not have any partial functional dependency.
For third normal form, a relation must not have any transitive functional dependency which existed between the race and swimmer table as well as swimmer and event table. It created the many to many relationships between the entities. This was resolved by including an intermediate relation between both the relationships. Hence the final set of relations and the database design is normalized up to third normal form.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
