The sports database has including different tables that is discussed below one by one.
it can be applied at the sports club or at the any sports events. so there will many teams,players and refreries
etc. if there is a sports club then there will participate different teams. so now lets discusse the all tables
functionality so it will clear the point.
Create database Sports;
Use Sports;
create table coach(
coachid int not null primary key,
name varchar(20),
coachingexperience varchar(25),
hometown varchar(20));
the above we have the table of the coach, which is treating as a an entity. the coach entity has its all attributes
given. the coach has its own id and shouldn't be null because there will be other coaches. for example we have a
cricket team so there is a co-coach and headcoach. and experience is also the important attribute of the entity
coach becuase if he has the most experienced, then he should be the head coach. each coach can participate only in
one team and its home town tells about from which place he belongs he can also be foreign or may be domestic coach.
Create table Team(
Teamid int not null primary key,
name varchar(20),
Location varchar(20),
gender char,
sport varchar(20));
the second table is team. the team has the unique id. there may be different teams and it is also possible that
there may many teams of the same sports. for example if there is 3 or 4 cricket teams so each team has its own team
id and that shouldn't be null. the team has also has its name and its location. the event may be on the domestic
level or may be on the international level. if the the event is on the international level then there may be
international and domestic teams.
the gender is also should be defined the teams are male or female team or mixed team. for example squash mixed
(male,female) teams.
the soports means that which type of sports team is? the teams may has one player(table tenis,bedmenton single)
or may have more than one players(cricket,foot ball teams).
Create table worksfor(
coachid int not null,
Teamid int not null,
Primary key(coachid,Teamid),
Foreign key(coachid)references coach(coachid),
Foreign key(Teamid)references Team(Teamid));
the third table is using here is worksfor table. it means that who is playing or participating in which team?
we should have the coach id and team id it means that that coach is participating only in that team. it means that
only one coach will participate in the only one team so there is foriegn keys are used. we will also use the
composite key, the coach id and team id combiningly behave as a composite key.
some of the teams have more than one coaches but still they will participate in that team, and can't participate in
the other team.
create table players(
Playerid int not null primary key,
name varchar(20),
year varchar(20),
weight int,
height varchar(10),
hometown varchar(20),
position varchar(20));
here is the table of the palayers. each player has its own unique primary key that shouldn't be null.
the player has its name,position, height, weight,year and home town etc. the player position,year and id is the
important attributes. because every event has its own schedule for example it held every year so the player id
could be changed and its position may be changed. for example if at the last event,if he was a captin, and now at
the current event the captin can be changed and it performance and position can be changed at the every match. if
we talk about the cricket team,suppose at the first match,if a player was an openner, and in the second match he
can be at the one down. but if has position is as a bowler then he will be a bowler.
create table coaches(
coachid int not null,
Playerid int not null,
Primary key(coachid,Playerid),
Foreign key(coachid)references coach(coachid),
Foreign key(Playerid)references Players(Playerid));
the coaches is the another separate table. it means that the there may be more than one coaches in the team or
a player has more than one coaches. if we talk about the cricket team then there are more than one coaches or if
there is a boxer, he may has more than one coaches. there may be one, two or three coaches. that has their own duties
it means means that one player may has more than one coaches.
create table Playsfor(
Teamid int not null,
Playerid int not null,
Primary key(Teamid,Playerid),
Foreign key(Teamid)references Team(Teamid),
Foreign key(Playerid)references Players(Playerid));
the last table is playsfor table, it shows that which player is palying for which team. it is also possible that
there may be many players in the team, means that all the players are the part of that team but all the players
are not participating in the game. we must have the player id that are participating in the game and ids of the all
other players that are the part of the team.
so we must have the team id and the player id that we will derive from the parent tables and the team id and player
combiningly making the composite key. it means its a separate key that is got from the other keys combinations.
