Database design problem?

Question

I have been given a senario on a swimming meet. The scenarion is:

You'll have one or more meets, each of which will have a given place and time associated with it.

A meet will have one or more events (e.g., 50 m freestyle, 4x100 freestyle relay, etc.).

An event will have one or more heats.

A heat will have zero or more individuals entered.

Each individual will have a time associated with their heat.

You'll have rules to design how to seed people and determine winners (usually "fastest is best").

Somehow i have to turn this into a database but i have no idea where to start. This is for a java program and i have some sql knowledge but i really am no good at designing the database so that everything links with each other through the appropiate primary keys etc.

Any help would be great

Answer 1

You can always do this one sentence at a time:

"…one or more meets… place and time…"

Meet(meetID, place, time)

"a meet will have one or more events…"

Event(eventID, meetID, description)

"an event will have one or more heats"

Heat(heatID, eventID, description)

"a heat will have zero or more individuals entered"

Individual(individualID, name)

HeatIndividual(heatID, individualID)

"each individual will have a time associated with their heat"  This could be one of two things, and my lack of sports knowledge will show here.  I'm guessing that time time is associated with the heat, and not with the individual:

Heat(+when)

"…rules… to determine winners… fastest is best"

HeatIndividual(+score)

So, put this together, and you have this:

Meet(meetID, place, time)

Event(eventID, meetID, description)

Heat(heatID, eventID, description, when)

Individual(individualID, name)

HeatIndividual(heatID, individualID, score)

In DB2 (dialects will vary), this is:

CREATE SCHEMA sports;

CREATE TABLE Meet(

...meetID INTEGER PRIMARY KEY,

...place VARCHAR(50) NOT NULL,

...when DATE);

CREATE TABLE Event(

...eventID INTEGER PRIMARY KEY,

...meetID INTEGER NOT NULL,

...description VARCHAR(100) NOT NULL,

...CONSTRAINT efk1 FOREIGN KEY(meetID) REFERENCES Meet(meetID));

CREATE TABLE Heat(

...heatID INTEGER PRIMARY KEY,

...eventID INTEGER NOT NULL,

...description VARCHAR(50) NOT NULL,

...when TIMESTAMP NOT NULL,

...CONSTRAINT hfk1 FOREIGN KEY(eventID) REFERENCES Event(eventID));

CREATE TABLE Individual(

...individualID INTEGER PRIMARY KEY,

...name VARCHAR(50));

CREATE TABLE HeatIndividual(

...heatIndividualID INTEGER PRIMARY KEY,

...heatID INTEGER NOT NULL,

...individualID INTEGER NOT NULL,

...score DECIMAL(7,3),

...CONSTRAINT hifk1 FOREIGN KEY(heatID) REFERENCES Heat(heatID),

...CONSTRAINT hifk2 FOREIGN KEY(individualID) REFERENCES Individual(individualID),

...CONSTRAINT hiu1 UNIQUE(heatID,individualID));