Database design for a school' sports day

[Neke]

Hello all,
I'm new in this group and with designing databases. I would like to design a databse for managing data about the sports day for a school.

I have been able to create the following tables: Pupil(differnt class and age group), Event(hockey, rugby, swimming,relay race), Staff, Equipment(used for the event).
I don't know if i have the ritght classes for the tables and could you kindly tell me how my ER/EER model would be used to answer some of their needs like drawing up with a list of events and start time, count all events participated by each group.
Thanks.

[ithelp]

Yes you are right track , now identify the relationships among the entities.

[Neke]

ok here are my enttities and their attributes
Pupil
P ID PK
Class ID
PfName
PlName
PAge

Class
ClassName
ClassType

Events
EventName PK
EventTime
StaffID

Equipment
Eqipment Name PK

Staff
Staff ID
SfName
SlName

Team
Team number

i'm afraid that from the tables , i may not be able to write query to list all events and start time, all pupils in a given team, and produce a list of winners for all events

[blater]

You need a table to record the results of each event (If you are only interested in the winner, not second place, third place etc then you could just put a pupilId in the event table to record just the winning pupil).

You need the eventId against the equipment table to associate that item of equipment with a particular event.

You also need a table to record which pupils are in each team.

Good luck with the project, Here's a suggested schema...

/*
** The pupils..
*/
Pupil
PupilID int not null primary key,
ClassID int not null,  -- foreign key to Class.classID
PfName  varchar(32),
PlName  varchar(32),
PAge    int

/*
** The classes
*/
Class
classID    int not null primary key,
ClassName  varchar(32),
ClassType  varchar(16)

/*
** The list of events 
*/
Events
EventID    int not null primary key,
EventName  varchar(64),
EventTime  datetime,
StaffID    int          -- foreign key to Staff.StaffID


/*
** The results of each event
** The id and position of each pupil is inserted into this table
** after each event to record the results.
*/
EventResults
EventID  int not null   -- foreign key to Events.EventID
PupilID  int not null   -- foreign key to Pupils.pupilID
position int            -- the position the pupil came in the event


/*
** The equipment used for an event
** There will be zero to many entries in here for each event.
*/
Equipment
EventID int not null,      -- foreign key to Event.EventID
EquipmentName varchar(32)

/*
** Master list of staff members
*/
Staff
StaffID  int not null primary key,
SfName   varchar(32),
SlName   varchar(32)

/*
** Master list of teams
*/
Team
TeamID int not null primary key,
teamName varchar(64)

/*
** List of team members for each team
*/
TeamMembers
teamID   int not null,  -- foreign key to Team.TeamID
pupilID  int not null   -- foreign key to pupils.PupilID

to list all events:

select * from Events order by eventTime

to list all pupils in a given team:

select t.team_name, pfName + ' ' + plName as pupil
from TeamMembers m, 
           Team t, 
           Pupil p
where t.teamID = m.teamId
and   p.pupilID = m.pupilID
and   teamName = 'red team'

to list winners for all events

select e.eventName, pfName + ' ' + plName as pupil
from  EventResults r, Events e, Pupils p
where e.eventID = r.eventID
and    p.pupilID = r.pupilID
and    r.position = 1
order by eventName