Need help with an ERD [Archive]

ijenkins

09-12-02, 22:31

Not too good with the whole ERD thing. Perhaps somebody can come up with a diagram for me or explain it. Here is the problem...

There is a rowing regatta that is held once a year. At this regatta many schools compete in many events. One particular school cannot compete in the same event more than once (1 boat/event). Each boat that races in an event has 1 or more rowers. No rower can race in more than one event. Each school has one head coach. Each head coach can only coach one school.

If someone can make more sense of this than me I would be appreciative. Please email to ijenkins@yahoo.com. Thanks -Ian

r937

09-13-02, 06:21

ian, i hope this isn't homework, i hate doing people's homework for them :)

SCHOOL (schoolid, schoolname)

EVENT(eventid, eventname)

RACE (schoolid, eventid, datescheduled)

this is the classic many-to-many relationship structure

by making schoolid and eventid a composite primary key, each combination can occur only once, so a particular school cannot compete in the same event more than once

ROWER(rowerid, schoolid, rowername)

this is a one-to-many from school to rower

you didn't say so, but this limits the rower to only one school

RACEROWER (schoolid, eventid, rowerid, datescheduled)

with only this structure it would be possible for a rower to participate in more than one event, so you also need a unique constraint on (eventid,rowerid)

COACH (coachid, schoolid, coachname)

one-to-many from school to coach

rudy
http://rudy.ca/