Draw ER Diagram for following:
Project Specifications - Australian Premier League (APL)
The following are the requirements for a design of a database for a new premier soccer league in
Australia. Representatives of current Australian state soccer clubs have come together to form the
new Australian Premier League (APL). They have set up a Management Board to oversee the
creation of this new league, and have commissioned you to design the database.
For this first stage, APL are only interested in maintaining data at the club level, to ensure that the
clubs are tracking well for future success. Therefore, the APL needs information regarding the
running and maintenance of the clubs, including their players and coaches, stadiums, sponsors
and sponsor contributions, members, and all saleable merchandise. Any other club financial
requirements, including club assets and running inventory are kept on separate financial
databases, and are not part of this database project.
All attributes containing metrics will be sourced from club databases and are accumulated values
in this database. i.e. player attributes like “number of tackles” etc., will be read from the club
database and then added to the current value in this database.
Using the following business rules, design a database that will allow the new Australian Premier
League to track their soccer clubs:
? The Australian Premier League needs to store the name, city, state and email of all
clubs. Each club also needs a unique ID to identify them.
? Each club may have one or more sponsors to help finance them during the course
of the year. It is also possible that a sponsor may sponsor more than one club.
? The league needs to keep a record of a sponsor’s name, email address, and the
type of sponsorship and funding amount for sponsorships given to clubs. Each
sponsor also needs a unique ID.
? A club may also have many members who belong to it. However, a member can
only belong to one club. A member would need to have a separate member ID to
belong to another club. The league has set this rule to better gauge how many
members a club actually has.
? The league needs to store the member's ID, first and last name, address, city and
post code, and their email address.
? Each club stocks a variety of merchandise that they can sell. All clubs have the
same types of items, i.e. “Scarf”, “Beanie”, “Jacket”, “T shirt” etc., the only
difference between them is the club logo and club colours/patterns.
? The club can only sell the merchandise with their branding on it.
? The Merchandise needs an ID that shows that it distinctly belongs to the respective
club, and what type it is. The selling price and amount sold also need to be stored.
? Each club has only one stadium, and stadiums are not shared amongst clubs. If a
stadium is unable to be used by the home club team(s), then the game will be
played at the other club’s stadium.
? (Assumption): At least one of the clubs who has team(s) playing a game at the
stadium will have their own stadium available.
? The league needs the stadium name, seating capacity, cumulative percent
attendance and number of executive suites. A unique ID is also needed.
? Clubs have many coaches, at least one per the three divisions, but also clubs can
have multiple coaches who assist. All coaches, however, can only belong to one
club.
The league keeps a record of the coach's first and last name and number of games
they have coached. A coach will also need a unique ID. Head coaches may
supervise other coaches, but are not supervised themselves. A coach can only be
supervised by one head coach.
? A club also has many players, but players can only play for one club.
? The league keeps a record of a player’s first and last name, date of birth, career
number of games for all games played and their current salary (in dollars). Also
each player needs a unique ID.
? A player can be either a field player or a goal keeper, but are rarely both. The
league needs to keep player’s performance tallys, in order to get an idea of how the
players have been performing over time. (For example, they may do a query for a
player's skill count divided by the player's number of games).
? The league would like to separate the two types of players, to save on the number
of NULLS for which the player is not.
? For a field player the league wants to have a running total on: Number of shots on Target,
Number of assists, Number of passes, Number of tackles and Number of penalties. Also a field
type attribute stating if the player is an “Attacker”, “Midfield” or a “defender”.
? For a Goal keeper the league wants to have a running total on: Number of free
kicks saved, Number of goal kicks, Number of normal saves and Number of goals
conceded.
? There are three possible divisions that a coach can coach in, namely: “Senior”,
“Youth” and “U18s”. A division can have many coaches, but a coach can only coach
in one division.
? Players can play in more than one division, depending on their form and any
injuries. For example a “Youth” may move up into the “Senior” division, if a “Senior”
player has an injury. Also divisions contain many players.
? The league would also like to keep a tally of the number of games that a player has
played in each division.
? For a division, the league would like to have the division name used as the unique
ID, also an attribute giving a long description of the division would be useful.
Project Specifications - Australian Premier League (APL)
The following are the requirements for a design of a database for a new premier soccer league in
Australia. Representatives of current Australian state soccer clubs have come together to form the
new Australian Premier League (APL). They have set up a Management Board to oversee the
creation of this new league, and have commissioned you to design the database.
For this first stage, APL are only interested in maintaining data at the club level, to ensure that the
clubs are tracking well for future success. Therefore, the APL needs information regarding the
running and maintenance of the clubs, including their players and coaches, stadiums, sponsors
and sponsor contributions, members, and all saleable merchandise. Any other club financial
requirements, including club assets and running inventory are kept on separate financial
databases, and are not part of this database project.
All attributes containing metrics will be sourced from club databases and are accumulated values
in this database. i.e. player attributes like “number of tackles” etc., will be read from the club
database and then added to the current value in this database.
Using the following business rules, design a database that will allow the new Australian Premier
League to track their soccer clubs:
? The Australian Premier League needs to store the name, city, state and email of all
clubs. Each club also needs a unique ID to identify them.
? Each club may have one or more sponsors to help finance them during the course
of the year. It is also possible that a sponsor may sponsor more than one club.
? The league needs to keep a record of a sponsor’s name, email address, and the
type of sponsorship and funding amount for sponsorships given to clubs. Each
sponsor also needs a unique ID.
? A club may also have many members who belong to it. However, a member can
only belong to one club. A member would need to have a separate member ID to
belong to another club. The league has set this rule to better gauge how many
members a club actually has.
? The league needs to store the member's ID, first and last name, address, city and
post code, and their email address.
? Each club stocks a variety of merchandise that they can sell. All clubs have the
same types of items, i.e. “Scarf”, “Beanie”, “Jacket”, “T shirt” etc., the only
difference between them is the club logo and club colours/patterns.
? The club can only sell the merchandise with their branding on it.
? The Merchandise needs an ID that shows that it distinctly belongs to the respective
club, and what type it is. The selling price and amount sold also need to be stored.
? Each club has only one stadium, and stadiums are not shared amongst clubs. If a
stadium is unable to be used by the home club team(s), then the game will be
played at the other club’s stadium.
? (Assumption): At least one of the clubs who has team(s) playing a game at the
stadium will have their own stadium available.
? The league needs the stadium name, seating capacity, cumulative percent
attendance and number of executive suites. A unique ID is also needed.
? Clubs have many coaches, at least one per the three divisions, but also clubs can
have multiple coaches who assist. All coaches, however, can only belong to one
club.
The league keeps a record of the coach's first and last name and number of games
they have coached. A coach will also need a unique ID. Head coaches may
supervise other coaches, but are not supervised themselves. A coach can only be
supervised by one head coach.
? A club also has many players, but players can only play for one club.
? The league keeps a record of a player’s first and last name, date of birth, career
number of games for all games played and their current salary (in dollars). Also
each player needs a unique ID.
? A player can be either a field player or a goal keeper, but are rarely both. The
league needs to keep player’s performance tallys, in order to get an idea of how the
players have been performing over time. (For example, they may do a query for a
player's skill count divided by the player's number of games).
? The league would like to separate the two types of players, to save on the number
of NULLS for which the player is not.
? For a field player the league wants to have a running total on: Number of shots on Target,
Number of assists, Number of passes, Number of tackles and Number of penalties. Also a field
type attribute stating if the player is an “Attacker”, “Midfield” or a “defender”.
? For a Goal keeper the league wants to have a running total on: Number of free
kicks saved, Number of goal kicks, Number of normal saves and Number of goals
conceded.
? There are three possible divisions that a coach can coach in, namely: “Senior”,
“Youth” and “U18s”. A division can have many coaches, but a coach can only coach
in one division.
? Players can play in more than one division, depending on their form and any
injuries. For example a “Youth” may move up into the “Senior” division, if a “Senior”
player has an injury. Also divisions contain many players.
? The league would also like to keep a tally of the number of games that a player has
played in each division.
? For a division, the league would like to have the division name used as the unique
ID, also an attribute giving a long description of the division would be useful.
Comments
Post a Comment