Figure 1: Entity Relationship Diagram

Source: Created by author

The above relation is normalized up to third normal form as:

1. all of the data achieve atomicity.
2. there are no repeating groups of data.
3. There are no partial functional dependencies between the relations.
4. There are no transitive functional dependencies between the relations.

The transitive dependency existed between the swimmer-event table as well as Swimmer-Race table. It was resolved by an intermediate table for Swimmer-Event as EventSwimmer and for Swimmer-race as RaceSwimmer.

Part A: Analysis And Design 

Client Business Rules

• Each Events can have many races and each race belongs to one and only one event.
• Each Pool can have many races and each race can take place at one and only one pool location.
• A Member can be either Coach, Medical Officer, Swimmer, or Manager. A coach, medical officer, swimmer and manager are a member.
• A country can have one or many teams. Each team belongs to one and only one country.
• Each team has many swimmers. Each swimmer can belong to one and only one team.
• Each swimmer can be a Team Leader. All team leaders are only Swimmers.
• Each coach can train many swimmers. Each swimmer can be trained by one and only one coach.
• Each swimmer can participate on many events. Each event can have many swimmers registered.
• Each swimmer can swim many races. Each race can have many swimmers.
• Each event swimmer details have one and only one competition. Each competition can have many swimmer registrations.

Assumptions Made

• All timings of the race details are in seconds.
• All swimmer uses unique lanes in each race.
• Some lanes in a pool may not have swimmers assigned.
• Place for each swimmer for each race is unique.
• Certified Date for Coach should be less than current date.
• WWC Check Date for Coach should be less than current date.
• Registration Date for Team should be less than current date.
• Qualification Year for Medical Officer should be less than current date.