DB HW/project

Soccer is the most famous sport in the world, as it is played by approximately 250 million players in over 200 countries, making it the world's most popular sport among all ages. Traditionally, soccer news is popularly followed through television or the soccer teams’ websites. Moreover, considering the vast majority of people using their personal smartphones to access all kind of data, the idea of a dedicated mobile app that keeps track of all the games that have been played in the tournament would arguably be perfect, timely and efficient. It is, however, such a complicated game consisting of several factors, that need to be broken down into entities and have them set and linked together accurately in order to build the app. The database includes the components of a soccer tournament.

The tournament is identified by a name and has an association description, such as a league where teams play home and away legs in a season, or a cup tournament where teams play only once and advance. The tournament should also consist of at least two teams to play against each other, and a team of referees.

Each team must have a coach, and at least eighteen players, where eleven must play in the line up, and the remaining seven are substitutes. Additionally, every team should have a name, origin, and a stadium name.

Team information includes,

1. Points - three points for each win, one point for each draw, and no points for each lost.

2. Number of games played.

3. Number of games won.

4. Number of games lost.

5. Number of tied games.

6. Number of goals scored - associate the player name, assistant name

7. Number of goals received.

8. Number of yellow cards - associate the player name

9. Number of red cards. - associate the player name

Player profile includes,

1. Name.

2. Nationality.

3. Age.

4. Height.

5. Field position.

6. Player number

7. Goals this season.

8. Assists this season.

9. Yellow cards.

10. Red cards.

Referee information includes,

1. Name.

2. Nationality

3. Age

4. Position - Main, right-line, left-line, and Fourth.

The App should be user-friendly with the user able to open the application and choose a tournament, then choose to view the list of teams, fixtures, results, and/or standings.

The Tournament page should have the name of the tournament and the description of whether it is a league or a cup.

The list of teams should show all the teams’ basic information on the list. Moreover, the user can click on each team to view all the detailed information.

The Fixtures page when clicked on should show all the games in the tournament, which should be detailed in terms of what teams are playing, date, location and time.

The Results page when clicked on should show all the games that have been played, and the details consist of the names of both teams and the referees, location of the game, indicate whether first leg or second leg, and the scores.

The Standings page should show a list of all the teams in the tournament, and it should show all the teams’ game information, 1 to 9 from list above.

The user should be able to click on any team listed on either:

1) Tournament page

2) Fixtures page

3) Results page

4) Standings page

to view its detailed information and the whole squad of 18 players, and a coach. Thereafter, the user can click on any player, or coach to view his full profile described above. The user can also view all the relating information about the team as described above, to which we might, later on, need to add a news feed link somewhere.

Project Title: Description of the Enterprise

Finally, the application is online, meaning real-time updates are automatically received when the game is being played It also keeps track of all the games that are being played simultaneously.

Draft 4 III-1 3 November 2014

Soccer Mobile Application -- Application Form

Tournament input data:

· Tournament name

· Tournament type

· Tournament description

· Tournament teams

Team input data:

· Name of the team

· Origin of the team

· Stadium of the team

· Names of all the players

· Name of the coach

Player input data:

· Name

· Nationality

· Date of Birth

· Height

· Field position

· Player number

Referee input data:

· Name

· Nationality

· Date of Birth

· Position

Match input data:

· Names of home and visitor teams

· Names of the referees

· Score of the match

· Names of players who scored

· Names of players who assisted

· Names of players with yellow cards

· Names of players with red cards

Some potential assumptions can be listed as follows,

Figure 1 Some assumptions for soccer tournaments.

1. Tournaments can only be in two types, a league in which all the teams are from the same country, or a cup with a list of teams from across the world.

2. In a league, teams cannot play with each other more than twice – once as home and once as visitor.

3. In a cup, teams can play up to three matches.

4. Teams earn points by either winning or tying. After a win three points are added to the team’s total points. After a tie, one point is added to the total points.

5. The winner team of a league is the team with the highest points.

6. If two teams have the same points by the end of the league, then the team scored more than the other during this competition is the winner.

7. A player can score or assist

8. A player can be punished with yellow or red cards.

9. During a match each team can make up to three substitutions.

10. A player receiving a red card is banned from playing the next match.

11. Two yellow cards equal to one red card in a single match. Therefore, if a player gets two yellow cards in a single match, then he will be eliminated from the next match.

12. If a player gets four accumulative yellow cards on different matches, then he will be banned from playing the next one.

13. Normal matches consist of ninety minutes divided into two halves with a 15-minute resting time and a maximum of 7-8 minutes of added time.

14. If two teams draw on the semi final or the final match, then the match is extended by thirty minutes divided into two halves.

Table 1. User-oriented data dictionary.

Table 2. Mapping of data to forms and transactions.

Project Title: Definition of the Environment

Draft 4 IV-10 3 November 2014

· Tournament

Attributes:

· name

· type

· description

· date

· Team

Attributes:

· name

· origin

· stadium

· coach_first_name

· coach_last_name

· Player

Attributes:

· first_name

· last_name

· nationality

· date_of_birth

· height

· number

· field_position

· Referee

Attributes:

· first_name

· last_name

· date_of_birth

· position

· nationality

· Match

Attributes:

· home_team

· visitor_team

· home_score

· visitor_score

· date

Tournament_teams (Tournament.name, Team.name)

-------------------------------------------------------------------------------------------------------------------

Goal: (Scorer.first_name, Scorer.last_name, Team_scored.name, Team_received.name, Assistant.first_name, Assistant.last_name)

Where Scorer and Assistant are roles of Player and Team_scored and Team_received are roles of Team

-------------------------------------------------------------------------------------------------------------------

Penalty: (Player.first_name, Player.last_name, Match, penalty_type)

Where penalty_type is attribute of Penalty relationship

------------------------------------------------------------------------------------------------------------------

Match_referees: (Match. name, Referee.first_name, Referee.last_name, position)

Where position is attribute of Match_referees relationship

------------------------------------------------------------------------------------------------------------------

· Tournament can have two or more Teams

· Tournament has one or more Matches

· Team can belong to zero or more Tournaments

· Team plays zero or many Matches (as visitor or home)

· Match is played by 2 Teams (home and visitor)

· Match is played by many Players

· Match belongs to one Tournament

· Player takes part in zero or more Matches (with zero or more goals and penalties)

· Match has many Referees

· Referee controls zero or more Matches (on one position)

· Team has many Players

· Player is member of one Team

Tournament: (name, type, description)

PK: name

Team: (name, origin, stadium, coach_first_name, coach_last_name)

PK: name

Player: (first_name, last_name, date_of_birth, field_position, height, number, nationality, team)

PK: first_name, last_name

FK: team references Team.name

Referee: (first_name, last_name, date_of_birth, nationality)

PK: first_name, last_name

Match_referee: (referee_first_name, referee.last_name, match_id, position)

PK: referee_first_name, referee.last_name, match_id

FK: match_id references Match.id

FK: referee_first_name, referee.last_name references Referee.first_name, Referee.last_name

Goal: (match_id, time, scorer_first_name, scorer_last_name, team_received, team_scored, assistant_first_name, assistant_last_name)

PK: match_id, time

CK: time, team_scored, team_received

FK: match_id references Match.id

FK: scorer_first_name, scorer.last_name references Player.first_name, Player.last_name

FK: assistant_first_name, assistant.last_name references Player.first_name, Player.last_name

FK: team_scored references Team.name

FK: team_received references Team.name

Match: (tournament, home_team, visitor_team, home_score, visitor_score, play_date)

PK: tournament, home_team, visitor_team

CK: home_team, visitor_team, play_date

FK: home_team references Team.name

FK: visitor_team, references Team. name

FK: tournament references Tournament.name

Penalty: (player_first_name, player_last_name, match_id, penalty_type, time)

PK: match_id, player_first_name, player_last_name, penalty_type

FK: match_id references Match.id

FK: player_first_name, player_last_name references Player.first_name, Player.last_name

Tournament_team: (tournament, team)

PK: tournament, team

FK: tournament references Tournament.name

FK: team references Team.name

Table 3. Table Dictionary.

Table 4. Attribute Dictionary

Project Title: Enterprise Database Design

Draft 4 V-10 3 November 2014

create table Nationality

( id int not null,

name varchar(50) not null,

abbreviation char(2)

);

alter table Nationality

add constraint pk_nationality primary key (id);

create table Team

( id int not null,

name varchar(100) not null,

origin int,

stadium varchar(100),

coach_first_name varchar(30) not null,

coach_last_name varchar(30) not null

);

alter table Team

add constraint pk_team primary key (id);

alter table Team

add constraint fk_team_nationality

foreign key (origin) references nationality (id)

on delete set null;

create table Tournament

( id int not null,

name varchar(100) not null,

type char(1),

description varchar(256)

);

alter table Tournament

add constraint pk_tournament primary key (id);

alter table Tournament

add constraint ck_tournament_type check (type in ('C', 'L'));

create table Referee

( id int not null,

first_name varchar(30) not null,

last_name varchar(30) not null,

date_of_birth date,

nationality int

);

alter table Referee

add constraint pk_referee primary key (id);

alter table Referee

add constraint fk_referee_nationality

foreign key (nationality) references nationality (id)

on delete set null;

create table Player

( id int not null,

first_name varchar(30) not null,

last_name varchar(30) not null,

date_of_birth date,

nationality int,

height int,

play_number int not null,

field_position char(1) not null,

team int not null

);

alter table Player

add constraint pk_player primary key (id);

alter table Player

add constraint fk_player_nationality

foreign key (nationality) references nationality (id)

on delete set null;

alter table Player

add constraint fk_player_team

foreign key (team) references team (id);

alter table Player

add constraint ck_player_position

check (field_position in ('G', 'D', 'M', 'S'));

create table Match

( id int not null,

tournament int not null,

home_team int not null,

visitor_team int not null,

play_date date not null,

home_score int,

visitor_score int

);

alter table Match

add constraint pk_match primary key (id);

alter table Match

add constraint fk_match_tournament

foreign key (tournament) references tournament (id)

on delete cascade;

alter table Match

add constraint fk_match_home

foreign key (home_team) references Team (id);

alter table Match

add constraint fk_match_visitor

foreign key (visitor_team) references Team (id);

create table Match_Referee

( id int not null,

referee int not null,

match int not null,

position char(1) not null

);

alter table Match_Referee

add constraint pk_match_referee primary key (id);

alter table Match_referee

add constraint fk_match_referee_referee

foreign key (referee) references Referee (id);

alter table Match_referee

add constraint fk_match_referee_match

foreign key (match) references match (id)

on delete cascade;

alter table Match_referee

add constraint ck_match_referee_position

check (position in ('M', 'R', 'L', 'F'));

create table Tournament_Team

( id int not null,

tournament int not null,

team int not null

);

alter table Tournament_Team

add constraint pk_tournament_team primary key (id);

alter table Tournament_Team

add constraint fk_tournament_team_tournament

foreign key (tournament) references tournament (id)

on delete cascade;

alter table Tournament_Team

add constraint fk_tournament_team_team

foreign key (team) references team (id)

on delete cascade;

create table Penalty

( id int not null,

player int not null,

match int not null,

type char(1) not null,

time int not null

);

alter table Penalty

add constraint pk_penalty primary key (id);

alter table Penalty

add constraint fk_penalty_player

foreign key (player) references Player (id);

alter table Penalty

add constraint fk_penalty_match

foreign key (match) references match (id)

on delete cascade;

alter table Penalty

add constraint ck_penalty_type check (type in ('R', 'Y'));

alter table Penalty

add constraint ck_penalty_time check (time between 1 and 128);

create table Goal

( id int not null,

match int not null,

scorer int not null,

assistant int,

team_received int not null,

team_scored int not null,

time int not null

);

alter table Goal

add constraint pk_goal primary key (id);

alter table Goal

add constraint fk_goal_match

foreign key (match) references match (id)

on delete cascade;

alter table Goal

add constraint fk_goal_scorer foreign key (scorer) references Player (id);

alter table Goal

add constraint fk_goal_assistant foreign key (assistant) references Player (id);

alter table Goal

add constraint fk_goal_team_received foreign key (team_received) references Team (id);

alter table Goal

add constraint fk_goal_team_scored foreign key (team_scored) references Team (id);

alter table Goal

add constraint ck_goal_time check (time between 1 and 128);

select distinct R.last_name, R.first_name

from Referee R, Match_Referee MR

where MR.referee = R.id and

MR.position = 'M'

order by R.last_name, R.first_name;

select count(*)

from Player;

select last_name, first_name, height

from Player

order by height desc;

select T.name, P.last_name, P.first_name, P.field_position, P.play_number

from Player P, team T

where P.team = T.id

order by T.name, P.field_position, P.play_number;

select T.name, H.name, V.name, M.home_score, M.visitor_score

from Match M, Team H, Team V, Tournament T

where M.home_team = H.id and

M.visitor_team = V.id and

M.tournament = T.id and

home_score + visitor_score = (select max(X.home_score+x.visitor_score)

from Match X);

select PL.id, PL.first_name, PL.last_name, count(*)

from Player PL, Penalty PE1

where pe1.player = pl.id

group by PL.id, PL.first_name, PL.last_name

having count(*) = (select max(T.cnt)

from (select PE2.player, count(*) as cnt

from penalty PE2

group by PE2.player) T);

select R.id, R.first_name, R.last_name, count(*)

from Referee R, Match_Referee MR, Match M, Penalty P

where R.id = MR.referee and

MR.match = M.id and

P.match = M.id

group by R.id, R.first_name, R.last_name

having count(*) = (select max(t.cnt)

from (select MR2.referee, count(*) as cnt

from Match M2, Match_Referee MR2

where MR2.match = M2.id

group by MR2.referee) T);

select T.name, count(*)

from Team T, Match M

where M.home_team = T.id and

M.home_score > M.visitor_score

group by t.name

order by count(*) desc;

select T.name, H.name, V.name, M.play_date, M.home_score, M.visitor_score

from Tournament T, Match M, Team H, Team V

where M.home_team = H.id and

M.visitor_team = V.id and

T.id = M.tournament

order by M.play_date desc;

select P.first_name, P.last_name

from Player P

where P.date_of_birth = (select min(date_of_birth)

from Player)

The documents are attached.

Limitations for this Enterprise Database SoccerApp would be the things that would not be easy to do at this level. For example, at what time a certain team scored? Or, at what time a player received a card based on the local time of the match, because this application is intended to be global. Therefore, a game in Australia could be played at the same time as a game in LA, but with time difference these could be in two different days. Also, what type of field they are playing in, in terms of size, grass type. One of the limitations can also be injuries and their types, which is hard at this moment. One last limitation is suspensions, which they can differ not only based on how many red cards or yellow cards. But, can be different when placed by the FIFA organization (International Federation of Association Football) due to unnecessary or, uncategorized actions done by teams or players.

For the SoccerApp, I think it is a very complex application that can go even further in complexity level if I didn’t intentionally put a limit on it. The only modifications I would propose are just trying to make this application more limited to only a single tournament with a lot less attributes. This way the queries SQL

Draft 4 VI-9 3 November 2014

application

penalty

goal

member

schedule

position

visitor_team

home_team

Match

date

home_score

visitor_score

Referee

fitrst_name

last_name

nationality

date_of_birth

Tournament

name

type

description

Team

name

origin

stadium

coach_first_name

coach_last_name

Player

first_name

last_name

height

field_position

date_of_birth

number

nationality