Database DDL, and SQL queries. SIMPLE job
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
Square brackets not needed around brief project description.
Congrats on choosing a name! This makes it easier to refer to it in your document.
Pages within the document are numbered correctly. Including the draft # in the footer is an excellent idea.
Good progress. Rethink how to represent game statistics. Be sure that every attribute in the conceptual model is included in the attribute dictionary. These sections are currently inconsistent.
Separate entities from relationships. If the distinction is unclear, see me.
PROJECT TITLE: Description of the Enterprise
Draft 3 II-‐1 7 October 2014
II Table of Contents
III. Description of the Enterprise ..................................................................... III-‐1
IV. Definition of the Environment ................................................................... IV-‐1
V. Enterprise Database Design…………………………………………………………V-‐1
The table of contents should include at least the top three levels of headings and relevant page numbers for each section of your project report.
PROJECT TITLE: Description of the Enterprise
Draft 3 III-‐1 7 October 2014
III. Description of the Enterprise
Enterprise Model Project
Soccer Tournament Database
Soccer is the most famous sport in the world, as it is played by 250 million players in over
200 countries, making it the world's most popular sport among all ages. However, the
traditional way of following soccer news is through television or through the soccer teams
websites. Moreover, when looking at the high numbers of people using their personal
smartphones to access all kind of data, the idea of a mobile app that keeps track of all the
games that have been played in the tournament would be perfect and efficient. However, it
is a complicated game that consists of several factors, in which we need to break into
entities and have them set and linked together accurately in order to build the app.
Therefore, we need to describe all the factors that make up a soccer tournament.
First, we need to have a tournament or a title to win. The tournament should have a name,
and a description, such as a league where teams play two legs (one at home and one away)
in a season, or a cup tournament where teams play only once and advance. Also, the
tournament should consist of at least two teams to play against each other, and a team of
referees (main referee, two assistant line referees, and a fourth official).
Your writing is clearer in this revision. The ARC will be able to help you put the final polish on it.
It is best to avoid parenthetical remarks in technical papers. If a remark is important enough to include, include it directly in the text. Otherwise, eliminate it.
Also, in technical writing, avoid personal pronouns, such as "we need to..." Focus on enterprise, rather than the process of completing the task. For example, in place of the highlighted text, try "The database includes the components of a soccer tournament. The tournament is identified by a name and has an association description..."
PROJECT TITLE: Description of the Enterprise
Draft 3 III-‐2 7 October 2014
Each team must have a coach, and at least 18 players, where 11 must play in the line up,
and the remaining 7 players should be substitutes. Also, every team should have a name,
origin, and a stadium name.
Teams’ game information should have,
1. Points (3 points for each win, 1 point for each draw, and 0 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)
A player’s information should have,
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.
spell out small numbers
includesTighten up the wording
PROJECT TITLE: Description of the Enterprise
Draft 3 III-‐3 7 October 2014
10. Red cards.
A referee should have,
1. Name.
2. Nationality
3. Age
4. Position (Main, right-‐line, left-‐line, and Fourth.)
The user should be able to open the application and choose a tournament, then choose to
view the list of teams, fixtures, results, 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 (described above).
Moreover, the user can click on each team to view all the detailed information (described
above).
The fixture 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 both teams’ names, referees’ names, location of the game, 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 (listed above from 1 to 9).
The user should be able to click on any team listed on either, (1) Tournament page, (2)
Fixtures page, (3) Results page, and (4) Standings page to view its detailed information and
Be consistent in the use of capitalization.
PROJECT TITLE: Description of the Enterprise
Draft 3 III-‐4 7 October 2014
the whole squad (18 players, and a coach). From there, the user can click on any player, or
coach to view his full information (described above). Also, from there the user can view all
the relating information about the team as described above, and we might need to add a
news feed somewhere in there late on.
Finally, the application is live, meaning it updates automatically when the game is being
played, and it also keeps track of all the games that are being played simultaneously.
PROJECT TITLE: Definition of the Environment
Draft 3 IV-‐1 7 October 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
. Age
. Height
. Field position
. Player number
Referee input data:
. Name
. Nationality
If this is a permanent record of player information, store DoB rather than age. The former never changes, while the latter does. Age can always be derived from DoB.
PROJECT TITLE: Definition of the Environment
Draft 3 IV-‐2 7 October 2014
. Age
. Position
Match input data:
. Names of the two 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. 3. In a cup, teams can play up to 3 matches. 4. Teams earn points by either winning or tying. After a win 3 points get added to the
team’s total points. After a tie, one point gets 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 we look at which
team scored more than the other during this competition and make it the winner. 7. A player can score or assist 8. A player can be booked with yellow or red cards. 9. During a match each team can substitute up to 3 substitutions. 10. If a player gets a red card, then he will be 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 4 accumulative yellow cards on different matches, then he will be
banned from playing the next one. 13.Normal matches consist of 90 minutes divided into two halves. 14. If two teams draw on the semi final or the final match is extended to 120 minutes.
Need to specify home, visitor
Can they play each other more than twice?
Once as home, once as visitor?
wordy
Explain this use of "booked" ... can make up to three substitutions
10. A player receiving a red card is banned from playing the next match.
Try to write more succinctly.
This sentence is worded awkwardly. Clarify.
"get" is colloquial. Avoid it in formal writing.
PROJECT TITLE: Definition of the Environment
Draft 3 IV-‐3 7 October 2014
Table 1. User-‐oriented data dictionary.
Datum Information Definition
age Age comes in the form of a two digit numbers. i.e. 22
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”>: Example: 176 cm.
name_of_coach The coach’s name in the form of <first> and <last>.
Example, “Carlo Ancelotti”
name_of_team The name of the team comes in the form of a string, which
can contain spaces or dots. Example: ‘Real Madrid”, or
“F.C Barcelona.”
name_of_referee The name of a referee in the form of: <first>, <last>.
Example, John Osaka
name_of_player The player’s name comes in the form of: <first alphabet of
the first name followed by a dot>, and <last>. Example:
Cristiano Ronaldo would be C.Ronaldo
Name_of_assistant/scorer
redCard/yellowCard
Refer to name_of_player
nationality Nationality come in the form of <country name>.
List should be ordered alphabetically by Datum
Use consistent capitalization
Tables well labeled.
Sample data is invaluable to users.
Break the name of the referee into two separate fields
Use the same name format throughout the database. That will increase the likelihood that the data will be input in the correct format everywhere. Make it easy for the user to do the right thing.
Tables don't need to be double-spaced. Single spacing makes it easier to read the table.
This data item needs to be broken down further. Each assist, goal, redCard, yellowCard will need to be associated with a particular player at a particular game. Consider "statistic" which will have a type, e.g., assist, goal. When designing the DB, create a relationship between the statistic and a player at a game
PROJECT TITLE: Definition of the Environment
Draft 3 IV-‐4 7 October 2014
Example, “Brazil”
origin_of_team Refer to nationality
Player_number The player number can be from 0 to 99.
stadium_name The Stadium name is in the form of a string. Example, “Old
Trafford”
score Score is in this form:
Home: Real Madrid
Home Score: 4
Visitor: Barcelona
Visitor Score: 2
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: Example, “La Liga”, or “ Uefa
Champions League”
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 will need to be separated into two data items, home team and visitor team.
Do cup and league have differences in terms of the attributes stored? Does one have more, fewer, or different attributes than the other?
PROJECT TITLE: Definition of the Environment
Draft 3 IV-‐5 7 October 2014
Table 2. Mapping of data to forms and transactions.
Datum Form or screen
age X
field_position X
height X
name_of_coach X
name_of_team X X X
name_of_referee X X
name_of_player X X
Name_of_assistant /scorer
redCard/yellowCard
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
This is a multi-valued attribute. Each attribute should have a single value associated with it. Use home_team, visitor_team instead.
This will need to be revised based upon notes above.
PROJECT TITLE: Definition of the Environment
Draft 3 IV-‐6 7 October 2014
nationality X X
origin_of_team X
Player_number X X
stadium_name X
score X
tournament_name X
tournament_teams X
tournament_type X
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐1 7 October 2014
V. Enterprise Database Design
V.1.1. List of all entities and their associated attributes.
• Tournament
Attributes:
§ Tournament_Name
§ Tournament_type
§ Tournament_Discreption
• Team
Attributes:
§ Name_of_team
§ Origin_of_team
§ Stadium_name
• Player
Attributes:
§ Name_of_Player
§ Nationality
§ Age
§ Height
§ Player_number
§ Field_position
• Referee
Attributes:
§ Name_of_referee
DoB, unless the players will be re-recorded for each tournament and no permanent records are kept.
Needs a date
This section is nicely laid out and easily understood. Tighten up attribute names. For example, rather than "name_of_player" or "number_of_matches" use "player" and "matches".
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐2 7 October 2014
§ Age
§ Position
§ Nationality
• Goal
Attributes:
§ Name_of_player
§ Team_scored
§ Team_received
§ Name_of_assistant
• Match
Attributes:
§ Name_of_home_team
§ Name_of_visitor_team
§ Score_of_home_team
§ Score_of_visitor_team
• Yellow_Card
Attributes:
§ Name_of_player
§ Player_team
• Red_Card
Attributes:
§ Name_of_player
§ Player_team
This makes sense. Be sure it is consistent with the information in the tables of the previous section.
needs a date
Penalty is a relationship between a player and a game with an attribute "penalty type" whose value is either "yellow" or "red"
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐3 7 October 2014
V.1.2. List of relationships and attributes.
Torunamen: (T_name, T_type, T_description)
Team: (name_of_team, origin_of_team, stadium_name)
Relation Set:
Tournament_teams (T_name, name_of_teams)
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Player: (name_of_player, age, field_position, height, player_number, nationality)
Team: (name_of_team, origin_of_team, stadium_name)
Goal: (Name_of_player, Team_scored, Team_received, Name_of_assistant)
Relation Set:
Player_goals (player_number, name_of_team, goal_scored)
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Team: (name_of_team, origin_of_team, stadium_name)
Goal: (Name_of_player, Team_scored, Team_received, Name_of_assistant)
Match: (Name_of_home_team Name_of_visitor_team Score_of_home_team
Score_of_visitor_team)
Yellow_cards: (Name_of_player, player_team)
Red_cards: (Name_of_player, player_team)
Relation Set:
teams_stats (name_of_team, num_of_matches, num_of_wins, num_of_losts, num_of_points,
num_of_goals, num_of_yellows, num_of_reds)
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Multivalued attribute. Each attribute must be single valued.
Goal appears to be a relationship between a player on a team playing a game, and an assistant. The team
These need to be defined in user-oriented data dictionary.
Consider using "player" in place of "name_of_player"
This is an entity.
This is also an entity, unless there is a master list of stadiums, in which this includes both an entity (name and origin) and a relationship (team and stadium)
losses points matchesteam wins
tighten up the wording
goals yellow_cards red_cards
this part needs to be reworked
Rethink stats.
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐4 7 October 2014
Goal: (Name_of_player, Team_scored, Team_received, Name_of_assistant)
Yellow_cards: (Name_of_player, player_team)
Red_cards: (Name_of_player, player_team)
Relation Set:
Player_stats: ( name_of_player, num_of_goals, num_of_assists, num_of_yellows,
num_of_reds)
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐5 7 October 2014
V.1.3. Entity-‐relationship diagram of the enterprise
PK: thick underline
CK: dotted underline
Torunamet T_name T_type T_Discription
Team name_of_team origin_of_team stadium_name
Player name_of_player age field_position height player_number nationality
Referee name_of_referee age position nationality
Goal Name_of_player Team_scored Team_received
Name_of_assistant
Match Name_of_home_team Name_of_visitor_team Score_of_home_team Score_of_visitor_team
Yellow_cards Name_of_player Player_team
Red_cards Name_of_player Player_team
MATCH is a m:m relationship between teams that has attributes DATE (needed). SCORE is an attribute of the relationship between HOME_TEAM and MATCH and between VISITOR_TEAM and MATCH.
attribute of player; GOAL is a relationship between PLAYER and MATCH.
Inferred from PLAYER
Attribute of PLAYER. This represents a relationship between PLAYER and GOAL.
There are actually two relationships here- player scoring goal and player assisting goal
These need to be associated with specific matches.
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐6 7 October 2014
§ Tournament can have two or more teams
§ Tournament has * matches
§ Team can belong to 0 or more tournament
§ Team has 0 or many match
§ Match has * teams
§ Match has * players
§ Match has 1 tournament
§ Player has * matches
§ Match has * referees
§ Referee has * matches
§ Team has * players
§ Player has 1 team
§ Player has 0 or * yellow_cards
§ Player has 0 or * Red_cards
§ Yellow/red have 0 or * players
§ Player has 0 or * goals
§ Goals has 0 or * players
§ Match has 0 or * goals
Annotate diagram with cardinality, optionality. Label all relationships.
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐7 7 October 2014
V.2. Conceptual model of the enterprise.
Torunamen: (T_name, T_type, T_description)
PK: T_name
CK: T_type
Team: (name_of_team, origin_of_team, stadium_name)
PK: name_of_team
CK: origin_of_team
Player: (name_of_player, age, field_position, height, player_number, nationality)
PK: name_of_player
CK: player_number, nationality
Referee: (name_of_referee, age, position, nationality) PK: name_of_referee CK: position Goal: (Name_of_player, Team_scored, Team_received, Name_of_assistant)
PK: Name_of_player, Team_scored
CK: Name_of_assistant
Match: (Name_of_home_team Name_of_visitor_team Score_of_home_team
Score_of_visitor_team)
FK: Name_of_home_team Name_of_visitor_team (Source: Team)
Yellow_cards: (Name_of_player, player_team) & Red_cards: (Name_of_player,
player_team)
PK: Name_of_player, player_team
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐8 7 October 2014
V.3. Table dictionary
Table 3. Table Dictionary.
Table Attributes Informal definition
Torunamen Tournament
Team
Referee
A tournament consists of
tournament name, type and
description. Also, teams, and
referees.
Match_stats Team
Referee
player
Goals
Red/yellow cards
match
A match consists of two
teams, a team of refrees,
goals, and cards.
Player_stats Player
Team
Red/yellow cards
Goal
A player has a team, number
of cards, and number of
goals scored and assisted.
Team_stats Match
Tournament
Red/yellow cards
goal
A team has number of
matches played in a
tournament, number of
goals scored and received,
number of received
red/yellow cards.
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐9 7 October 2014
V.4. Attribute dictionary
Table 4. Attribute Dictionary
Datum Information Definition table in which
each attribute is
used
age Age comes in the form of a two digit
numbers. i.e. 22
Player_stats
field_position Field position can be: Goalkeeper,
Defender, Midfielder, or Striker.
Player_stats
height Height of a player comes in the form of
centimeters <number> and <”cm”>:
Example: 176 cm.
Player_stats
name_of_coach The coach’s name in the form of <first> and
<last>. Example, “Carlo Ancelotti”
Team_stats
name_of_team The name of the team comes in the form of
a string, which can contain spaces or dots.
Example: ‘Real Madrid”, or
“F.C Barcelona.”
Team_stats
name_of_referee The name of a referee in the form of:
<first>, <last>. Example, John Osaka
Match_stats
name_of_player The player’s name comes in the form of:
<first alphabet of the first name followed
Player_stats
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐10 7 October 2014
by a dot>, and <last>. Example: Cristiano
Ronaldo would be C.Ronaldo
Name_of_assistant/scorer
redCard/yellowCard
Refer to name_of_player Player_stats
nationality Nationality come in the form of <country
name>.
Example, “Brazil”
Player_stats
origin_of_team Refer to nationality Team_stats
Player_number The player number can be from 0 to 99. Player_stats
stadium_name The Stadium name is in the form of a string.
Example, “Old Trafford”
Team_stats
score Score is in this form:
Home: Real Madrid
Home Score: 4
Visitor: Barcelona
Visitor Score: 2
Match_stats
tournament_description
The description can be in one sentence, as
follows: “this tournament is Spanish
league” or “this tournament is a friendly
cup.”
Torunamen
tournament_name The name can be any string: Example, “La
Liga”, or “ Uefa Champions League”
Torunamen
PROJECT TITLE: Enterprise Database Design
Draft 3 V-‐11 7 October 2014
tournament_teams Refer to name_of_team. Follow each name
by either home, or visitor. Then, separate
teams with commas.
Torunamen
tournament_type The type can be either a “cup” or a “league” Torunamen
