DB HW/project
BryanAdams
doc_2.pdf
PROJECT TITLE: Description of the Enterprise
7 O c t o b e r 2 0 1 4
SoccerApp Abdul Alzaid This project designs and implements a database for a mobile application to track the full information of soccer tournaments
Fall 2014 | CMSI 486 Enterprise Project
08 Fall
PROJECT TITLE: Description of the Enterprise
Draft 4 III-‐1 3 November 2014
Table of Contents
. III. ................................................................................. Description of the Enterprise 3
III.1. Enterprise Model Project .................................................................................................................... 3 III.1.1 Soccer Tournament Database ....................................................................................................................... 3
. IV. Definition of the Environment ................................................................... 7
. V. Enterprise Database Design .................................................................... 1 V.1. List of all entities and their associated attributes. ...................................................................... 1 V.2. List of relationships and attributes. ................................................................................................. 2 V.3. Entity-‐relationship diagram of the enterprise .............................................................................. 4 V.4. Conceptual model of the enterprise. ................................................................................................ 6 V.4.1. Table dictionary .................................................................................................................................................. 8 V.4.2 Attribute dictionary ........................................................................................................................................... 9
. VI. .................................................................................................. Database and queries 1
VI.1. Database definition ............................................................................................................................... 1 VI.2. Database queries ................................................................................................................................... 6 VI.2.1 List of referees that controlled at least one match as Main Referee ............................................ 6 VI.2.2 Number of registered players ....................................................................................................................... 6 VI.2.3 List of players from highest one .................................................................................................................. 6 VI.2.4 List of players and their teams with positions ...................................................................................... 6 VI.2.5 Match with the greatest number of goals scored ................................................................................. 6 VI.2.6 Who is the most punished player? .............................................................................................................. 7 VI.2.7 Who is the most sharp referee? ................................................................................................................... 7 VI.2.8 Best of teams at home (the greatest number of wins) ....................................................................... 7 VI.2.9 List of most recent matches ........................................................................................................................... 7 VI.2.10 Who is the oldest player? ............................................................................................................................. 8
VI.3. Review sign-‐off sheet ........................................................................................................................... 8 VI.4. Design Limitations: Discussion of the limitations of your design. ........................................ 9
Left-justify all entries in the Table of Contents Preface page numbers within each section with section number
PROJECT TITLE: Description of the Enterprise
Draft 4 III-‐2 3 November 2014
PROJECT TITLE: Description of the Enterprise
Draft 4 III-‐3 3 November 2014
III. Description of the Enterprise
III.1. Enterprise Model Project
III.1.1 Soccer Tournament Database 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,
associated
This does not belong in the enterprise description
Note - only "association" should be struck out here - annotation tool inadvertently struck out the entire line and has no "undo".
PROJECT TITLE: Description of the Enterprise
Draft 4 III-‐4 3 November 2014
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.
PROJECT TITLE: Description of the Enterprise
Draft 4 III-‐5 3 November 2014
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
display
PROJECT TITLE: Description of the Enterprise
Draft 4 III-‐6 3 November 2014
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.
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.
This line should not be struck out -- tool gone awry again
, the squad of eighteen players....
tentative... try "to which a news feed link can be added at a future date."
. Real-time updates are...
period after "played". To what does "It" refer? Avoid using "it" in technical writing.
PROJECT TITLE: Definition of the Environment
Draft 4 IV-‐7 3 November 2014
IV. Definition of the Environment 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
Separate fields needed for home, away
PROJECT TITLE: Definition of the Environment
Draft 4 IV-‐8 3 November 2014
. 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.
that
period
PROJECT TITLE: Definition of the Environment
Draft 4 IV-‐9 3 November 2014
Table 1. User-‐oriented data dictionary.
Datum Information Definition coach_first_name The coach’s first name. coach_last_name The coach’s surname. date_of_birth DD/MM/YYYY. Calculated field Age comes in the form of a
two digit numbers. field_position Field position can be: Goalkeeper, Defender, Midfielder, or
Striker. height Height of a player comes in the form of centimeters
<number> and <”cm”>: name_of_assistant/scorer red_card/yellow_card
Refer to player_first_name and player_last_name
nationality Nationality in the form of <country name>. origin_of_team Refer to nationality player_first_name The player’s first name. player_last_name The player’s surname. player_number The player number can be from 0 to 99. referee_first_name The referee’s first name. referee_last_name The referee’s surname. score Score is in this form:
Home: Real Madrid Home Score: 4 Visitor: Barcelona Visitor Score: 2
stadium_name The Stadium name is in the form of a string. team_name The name of the team in the form of a string, which can
contain spaces or dots. tournament_description
The description can be in one sentence, as follows: “this tournament is Spanish league” or “this tournament is a friendly cup.”
tournament_name The name can be any string. tournament_teams Refer to name_of_team. Follow each name by either home,
or visitor. Then, separate teams with commas. tournament_type The type can be either a “cup” or a “league”
This is a "super" data item containing four distinct parts - see related midterm problem for example of how to break this down.
Use of this attribute is unclear.
Must be broken into separate entries for HOME, VISITOR Explain difference between these two
terms in the project description. See related notes on deliverable #3.
PROJECT TITLE: Definition of the Environment
Draft 4 IV-‐10 3 November 2014
Table 2. Mapping of data to forms and transactions.
Datum Form or screen
coach_first_name X coach_last_name X date_of_birth X field_position X height X home_team X X X name_of_assistant /scorer red_card/yellow_card
X nationality X X origin_of_team X player_first_name X X player_last_name X X player_number X X referee_first_name X X referee_last_name X X score X stadium_name X tournament_name X tournament_teams X tournament_type X visitor_team X X X
To ur na m en t i np ut d at a
Te am
in pu t d at a
Pl ay er in pu t d at a
R ef er ee in pu t d at a
M at ch in pu t d at a
There should be a direct correspondence between entries in this table and entries in the previous table.
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐1 3 November 2014
V. Enterprise Database Design
V.1. List of all entities and their associated attributes.
• 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
Are all games played at home team stadium? If so, be sure to mention this in assumptions.
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐2 3 November 2014
§ field_position
• Referee
Attributes:
§ first_name
§ last_name
§ date_of_birth
§ position
§ nationality
• Match
Attributes:
§ home_team
§ visitor_team
§ home_score
§ visitor_score
§ date
V.2. List of relationships and attributes. 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
A match actually represents a relationship between teams (and a league or cup). The clue to this is that it refers to multiple other entities.
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐3 3 November 2014
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
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
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐4 3 November 2014
V.3. Entity-‐relationship diagram of the enterprise
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
Application contains the DB - it is not part of the DB.
Does the DB capture the history of a team, or only its current members? Clarify this in assumptions or narrative.
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐5 3 November 2014
§ 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
Label this section. Is it a intended to represent set of assumptions or just repeat the cardinality/optionality notation in the ERD? If the latter, and all information is recorded in the ERD, this list is not needed and can be eliminated.
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐6 3 November 2014
V.4. Conceptual model of the enterprise.
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
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐7 3 November 2014
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
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐8 3 November 2014
V.4.1. Table dictionary Table 3. Table Dictionary.
Table Attributes Informal definition Goal match_id
time scorer_first_name scorer_last_name team_scored team_received assistant_first_name assistant_last_name
Goal statistics
Match tournament home_team visitor_team home_score visitor_score play_date
Match details
Match_referee referee_first_name referee.last_name match_id position
Referees assigned to matches
Penalty player_first_name player_last_name match_id penalty_type time
Red and yellow cards for matches and its players
Player first_name last_name date_of_birth field_position height play_number nationality team
Player detail
Referee first_name last_name date_of_birth nationality
Referee detail
Team name, name origin stadium coach_first_name coach_last_name
Team detail
Tournament name Tournament detail
Reformat to fit on a single page. Consider using a different font or a slightly different font size, or make the TABLE column narrower and use two columns within the ATTRIBUTES column. Another option is to reduce the space between lines, say to .95 lines. The table is a figure and thus can be formatted differently, provided it is readable.
Add the match_id to the attribute dictionary; you do not need to add it to the previous User-oriented Data Dictionary.
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐9 3 November 2014
type description
Tournament_Team tournament team
Terms taking part in tournaments
V.4.2 Attribute dictionary Table 4. Attribute Dictionary
Datum Information Definition table in which each attribute is used
assistant_first_name The assistent’s first name. Goal assistant_last_name The assistent’s surname. Goal coach_first_name The coach’s first name. Team coach_last_name The coach’s surname. Team date_of_birth DD/MM/YYYY. Age is calculated in the
form of a two digit numbers. Player, Referee
description The description can be in one sentence. Tournament field_position Field position can be: G-‐Goalkeeper, D-‐
Defender, M-‐Midfielder, or S-‐Striker. Player
height Height of a player in centimeters Player home_score Home team match goals. Goal home_team Refer to name_of_team Match name The name can be any string. Tournament,
Team nationality Nationality in the form of <country name>. Player, Referee origin_of_team Refer to nationality Team play_date Date of the match Match play_number The player number can be from 0 to 99. Player player_first_name The player’s first name. Player player_last_name The player’s surname. Player referee_first_name The referee’s first name. Referee referee_last_name The referee’s surname. Referee scorer_first_name The scorer’s first name. Goal scorer_last_name The scorer’s surname. Goal stadium The Stadium name is in the form of a string. Team team_name The name of the team in the form of a
string, which can contain spaces or dots. Team
team_received Refers to team received goal. Goal team_scored Refers to team scored goal. Goal type The tournament type can be C-‐cup or L-‐
league, the penalty type can be R-‐red or Y-‐ yellow
Tournament, Penalty
Put this on its own page so that all of the rows of the table appear on a single page.
PROJECT TITLE: Enterprise Database Design
Draft 4 V-‐10 3 November 2014
visitor_score Visitor team match goals. Goal visitor_team Refer to name_of_team Match time Minute of penalty or goal Goal, Penalty
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐1 3 November 2014
VI. Database and queries
VI.1. Database definition 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'));
Use fixed point font, indentation and alignment to facilitate reading these table definitions.
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐2 3 November 2014
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'));
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐3 3 November 2014
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;
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐4 3 November 2014
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;
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐5 3 November 2014
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);
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐6 3 November 2014
VI.2. Database queries
VI.2.1 List of referees that controlled at least one match as Main Referee 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;
VI.2.2 Number of registered players select count(*) from Player;
VI.2.3 List of players from highest one select last_name, first_name, height from Player order by height desc;
VI.2.4 List of players and their teams with positions 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;
VI.2.5 Match with the greatest number of goals scored 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);
tallest
Add: Which referee was responsible for the greatest number of penalties in a single match against a home team?
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐7 3 November 2014
VI.2.6 Who is the most punished player? 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);
VI.2.7 Who is the most sharp referee? 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);
VI.2.8 Best of teams at home (the greatest number of wins) 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;
VI.2.9 List of most recent matches
penalized?
How is "sharp" defined? Be more specific in the English version of the query.
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐8 3 November 2014
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;
VI.2.10 Who is the oldest player? select P.first_name, P.last_name from Player P where P.date_of_birth = (select min(date_of_birth) from Player)
VI.3. Review sign-‐off sheet The documents are attached.
PROJECT TITLE: Enterprise Database Design
Draft 4 VI-‐9 3 November 2014
VI.4. Design Limitations: Discussion of the limitations of your design.
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
Proofread, or have someone else proofread to eliminate awkward wording. All questions should end with a question mark.
not a complete sentence
wouldn't you just record the date on which the team was played in the country where it was played?
fewer
something missing here.
Note: Although the project is challenging, the complexity of this SoccerApp database is average for this assignment. It is intended to challenge the student, not to be a straightforward, easy to complete project. It incorporates submodels included in lectures. Keep that in mind when considering the complexity.
Time zone needs to be recorded, or record all times in Greenwich Mean Time.
Most of the limitations mentioned here are beyond the scope of your project. What limitations exist within the scope of the project? Which queries did you intend for your DB to answer that cannot be easily answered?
