The ERD Diagram showing the conceptual model is as shown below;

The ERD Diagram showing the conceptual model is as shown below;

The relational schema is as shown below;

Event(EventID,name, date, VenueID, time, No_ofathletes)

Primary key (pk): {EventID}, candidate keys (ck): {name}

Foreign key (fk): VenueID which uniquely identifies the  venue in which the event will be held

Team (TeamID, name, location, no_ofathletes)

Primary Key (pk): {TeamID}, candidate keys (ck):{name}

Athleteperfomance/medal(PerfomanceID,EventID, pointsearned, medalawarded,TeamID, AthleteID )

Primary key (pk): {PerfomanceID}, candidate keys (ck): {eventide, medalawarded, athleteID}

Foreign key (fk): VenueID which uniquely identifies the  venue in which the event will be held,  TeamID which shows which team has worn the  medal, and AthleteID which shows the athlete who has won the medal

Athlete(AthleteID,names, nationID, EventID, passport, age, dateregistered)

Primary key (pk): {AthleteID}, candidate keys (ck): {name,nationID}

Foreign key (fk): NationID which uniquely identifies the nation the athlete is from, and EventID which uniquely identifies the event in which the athlete participates.

Volunteers(VolunteerID,name, age,nationality, Passport, EventID)

Primary key (pk): {VolunteerID}, candidate keys (ck): {name}

Foreign key (fk): EventID which uniquely identifies the type of event being volunteered

Venue(VenueID,name, capacity, events, states)

Primary key (pk): {VenueID}, candidate keys (ck): {name}

Assumptions made when designing the above database are;

  • The awarding of medals will be based on the points gained regardless on the event type
  • When assigning points to a team of athletes participating on a particular game, the team will be regarded as an athlete.
  • One can only volunteer on one event while there are many volunteers that can volunteer on one event.
  • The word team and nation represent the same thing
  • One team or nation can have unlimited number of athletes who can participate on unlimited number of events.
  • A volunteer can volunteer in events that his or her nation or team is participating on without favoring either of the side.

References

Coronel,C.,  Morris, S.,& Rob,P.  (2011). Database Systems Design, Implementation and           Management. United States of America: Cengage Learning.

1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: