•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Database Design section within the Web Development category of DaniWeb, a massive community of 361,631 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,168 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Database Design advertiser:
Views: 578 | Replies: 3
 |
•
•
Join Date: Mar 2008
Posts: 7
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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.
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.
•
•
Join Date: May 2006
Location: homeworkhelp.co.in
Posts: 790
Reputation:
Rep Power: 4
Solved Threads: 59
Master Poster
Yes you are right track , now identify the relationships among the entities.
cout << Homework Help << Freelance Services << Top 10 Web Host;
cout << Build A Website << Interview Question And Answer << Tax Filing;
cout << Build A Website << Interview Question And Answer << Tax Filing;
•
•
Join Date: Mar 2008
Posts: 7
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
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
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
•
•
Join Date: Mar 2008
Posts: 25
Reputation:
Rep Power: 1
Solved Threads: 7
Light Poster
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
[/code]
to list all events:
to list all pupils in a given team:
to list winners for all events
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
[/code]
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
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Database Design Marketplace
Linear Mode
•
•
•
•
age amd avatar backup blue gene breach business chips daniweb data data protection database design development dos economy energy enterprise europe government hardware hp ibm ibm. news it linux medicine memory microsoft news open source openoffice pc photoshop ps3 recession red hat school security server sun supercomputer supercomputing survey technology trends ubuntu web working x86
- Previous Thread: Which is better in terms of optimization?
- Next Thread: Simple Job Logging Database
