Create an entity

Vision G · July 21st, 2011, 04:55 PM

Hi all,

I am new database prgogrammer to sql and i would like your help.

I am creating a database for soccer and i have problem with an entity.

I have the entities TEAM, PLAYER and i want to add the entity MATCH/GAME.

In the ER Diagramm how should i show the the game betwen two teams.

MATCH should be an entity or a relationship between TEAM and TEAM?

I want to know for a match the match id the two teams, the score and the date of the match.

I am little confused...

thanx in advanced.

melvik · July 23rd, 2011, 10:02 AM

hello there:
sure MATCH is an entity so u can show matches list & ...

Code:

CREATE TABLE Match
(
 MatchID int NOT NULL IDENTITY(1,1) PRIMARY KEY
,Team1 int NOT NULL REFERENCES TEAM(TeamID)
,Team2 int NOT NULL REFERENCES TEAM(TeamID)
,MatchDate datetime
,Team1Score tinyint NOT NULL DEFAULT 0
,Team2Score tinyint NOT NULL DEFAULT 0
)

but u can add a Goal table to have full list of goals & ... then u can have some reports on (e.x: how many goals did X player scored[SUM/IN season/...]?

Code:

CREATE TABLE Goal
(
 GoalID int NOT NULL IDENTITY(1,1) PRIMARY KEY
,PlayerID int NOT NULL REFERENCES PLAYER(PlayerID)
,GoalDate datetime
)

HTH

Vision G · July 26th, 2011, 04:32 PM

thank yo very much, yes i agree with you, but please help me with this.

Now i have 3 entities: TEAM,PLAYERS,MATCH

the relationship between TEAM and MATCH is many to many so i have to create a new table e.g. MATCHOUTCOME

PLAYER
player-id
age
position

TEAM
team-name (key)
colours
formed

MATCH
match-id(key)
date

MATCHOUTCOME
match-id (key)
team-name (key)
score

I think I should create 4 new attributes HomeTeam, AwayTeam, HomeTeamScore, AwayTeamScore... Am I right?

If yes, should I have these attributes to table MATCHOUTCOME or MATCH ?

I think the new table MATCHOUTCOME will be

MATCHOUTCOME
match-id(key)
HomeTeam(key)
AwayTeam(key)
HomeTeamScore
AwayTeamScore

thanx in advance....

melvik · July 28th, 2011, 03:31 AM

Hi

I didnt get ur whole business (like what is formed in Team for?)
can u tell me what is MATCHOUTCOME for?
it seams ur design has some problems but since I couldnt figure it out i cant say any

u have entities below

Team
team-name (key) I dont suggest u to use it as key!, better to have an ID Numeric(not string)
colours
formed (what is it?)
Player
player-id
age
position
(u have no name! isnt it important?)
Match
match-id(key)
date
which teams is the match between?!?
so u r going to add new entities here
MATCHOUTCOME
i have no clue what it could be

if u answer me, i think I can help u more

Vision G · July 28th, 2011, 11:38 AM

ok, i ll try to explain you from the begging.

I am trying to create a database about football with 3 entities and join them.
The entities are TEAM,PLAYER,MATCH

TEAM[entity]
team-id [attribute](primary key)
team-name [attribute]
formed [attribute] (the day established e.g. 1968)
color [attribute] (the colors of the team)

PLAYER[entity]
player-id [attribute](primary key)
player-name [attribute]
position [attribute]
height [attribute]
....

MATCH[entity]
match-id [attribute] (primary key)
date [attribute]
result [attribute]
leg [attribute] (e.g. 1st leg between the two teams)

Script:
"A team has many player, a player belongs to one team" relationship = 1:n
"A team plays many games, the games have many teams" relationship = m:n (The problem it is here, the relationshp between TEAM and MATCH is 1:n or m:n)
I want to show all the games between all teams of the last year.

Now every entity in MySQL must be a table.

When we have relationship m:n isn't it a new table?

So now i ll create 4 tables, like: (the fourth table wil be MATCHOUTCOME and will have the keys of the table MATCH and TEAM)

TEAM[table]
team-id [attribute](primary key)
team-name [attribute]
formed [attribute] (the day established e.g. 1968)
color [attribute] (the colors of the team)

PLAYER[table]
player-id [attribute](primary key)
team-id [attribute] (foreign key)
player-name [attribute]
position [attribute]
height [attribute]
....

MATCH[table]
match-id [attribute] (primary key)
home-team-id [attribute] is it foreign key?
away-team-id [attribute] is it foreign key?
home-team-name (do i have to add this?)
away-team-name (do i have to add this?)
date [attribute]

MATCHOUTCOME[table]
match id [attribute] (foreign key)
team-id [attribute] (foreign key)
leg [attribute]
result [attribute]

I think I helped you in order to help me, with your opinion.

Thanx one more time

rstelma · July 28th, 2011, 02:22 PM

Looks like you need a TeamPlayers table with:

TeamID
PlayerID
StartDate
EndDate

That way you can keep a history of the players, when they started with the Team and when they retired, fired, traded, etc. You can know who was on what team at any given time, for any given match.

Looks like the Match table needs:

TeamHomeID
TeamAwayID
MatchDate
TeamHomeScore
TeamAwayScore
TeamWinnerID (May not really need this cause you can see who actually won from the score. But having it will make some queries easier to write.)

Vision G · July 29th, 2011, 04:04 PM

Looks like the Match table needs:

TeamHomeID is foreign key?
TeamAwayID is foreign key?
MatchDate
TeamHomeScore
TeamAwayScore
TeamWinnerID

melvik · July 31st, 2011, 09:40 AM

Dear friend
Match is nothing by it self & MATCHOUTCOME is useless
u just need to have fields of teams in it, its not M:N relation
so in my opinion ur last version of tables must be something like below

TEAM[table]
team-id [attribute](primary key)
team-name [attribute]
formed [attribute] (the day established e.g. 1968)
color [attribute] (the colors of the team)

PLAYER[table]
player-id [attribute](primary key)
team-id [attribute] (foreign key)
player-name [attribute]
position [attribute]
height [attribute]
...
its good to mention start & end date in Player Table as a friend mentioned above. players who has NULL endDate r current players or...

MATCH[table]
match-id [attribute] (primary key)
home-team-id [attribute] YES it is foreign key!
away-team-id [attribute] YES it is foreign key!
home-team-score
away-team-score
date [attribute]
u dont need to add home-team-name & away-team-name bc u violate normalization.

Vision G · July 31st, 2011, 10:34 AM

Melvik, first of all thank you for your time.

I am posting my logical diagramm as i had created in MS Access for a trial

http://img600.imageshack.us/img600/8053/teammatch.jpg

here is the query I ve made:
SELECT match.[match-id], match.[home-team-id], match.[away-team-id], team.[team-name], match.score, match.leg, match.date
FROM team INNER JOIN [match] ON team.[team-id] = match.[home-team-id];

as you can see the query returns only the name of home-team and not the name of the away team, what I am doing wrong?

also,
How can it be the relatioship 1:N , and not M:N?

Many matches have many teams, many teams are in many matches.

melvik · August 1st, 2011, 10:37 AM

ok first u should join it twice to team as

Code:

SELECT match.[match-id], T1.[team-name] AS HomeTeam, T2.[team-name] AS AwayTeam, match.score, match.leg, match.date
FROM [match] INNER JOIN team T1 ON T1.[team-id] = match.[home-team-id]
		INNER JOIN team T2 ON T2.[team-id] = match.[away-team-id];

then its not a many to many relation bc there is only 1 match at a time but evry match is between 2 teams. i hope its clear.

it would be many to many if every match was between many(more than 2) teams

(imagine that )