What would be the perfect ERD1 & ERD2 for the following case study? And what are the attributes of the entities?
Five Aside Football Independent League (FAFIL)
The Five Aside Football Independent League (FAFIL) is an amateur organisation which was set-up to organise and record the results of a number of five aside football clubs who during the football season play relatively friendly matches with each other. FAFIL has recently decided to record the details of each team and which other team they play against by using a relational database system and is calling on you as renowned experts in this field to solve their problems.
The organisation consists of a number of clubs which regularly take part in the prestigious challenge cup tournament, which consists of each club putting forward a team to compete for the honour of winning the challenge cup by scoring the most points when competing with the other clubs. Each club may have more than one team, but can only enter one team into this prestigious tournament.
Throughout the season FAFIL has to arrange and record the order in which each team will play against each other and the venue and date that the match will take place on, as well as arranging for a designated FAFIL referee to oversee the match. The venue will be held at one of the team’s home ground and consequently be an away match for the other team. Each team must play all other teams twice, once at home and once away.
Therefore the first task which the database system must provide is to automatically create a fixtures list which will take as input the existing teams and produce a listing having each team playing all the others both home and away. The organisers can then arrange the matches required and record the referee and venue accordingly. Difficulties have arisen in the past when during the season it was discovered that either the referee or the venues were double booked on the same day.
Once the matches are arranged the results must be recorded as and when they come in. Points are awarded to the teams for each match they play according to the end result, two points for a win and one point for a draw and zero points if they lost or failed to show.
Once all the matches have been played it should be any easy job to calculate which team has the most number of points. Teams with equal points scored are ordered by the difference between goals scored and goals conceded. It is hoped that the database system will be able to record the points and allocate them to the correct team as the results are put into the system.
Due to some very aggressive players and the occasional hospital bill during the last season it has been decided to record and allocate penalty-points against players if they misbehave.
Accordingly the FAFIL referees where sent instructions to allocate penalty-points, such as: 1-bad language, 5-rude gestures, 10-stabings, and these will be allocated against each player as the season progresses. It was further decided that any player who exceeds 10 penalty-points e.g. stabs another player and then swears at them, must not be allowed to take part in any further matches.
It has also been decided to reward the best player with a highly prized book voucher. Accordingly each time a match is played, one player is chosen as the ‘player of the match’. At the end of the season the player who was ‘player of the match’ the most is awarded the prize, if there are a number of players who have been nominated the same number of times, they must share the book voucher as FAFIL is not made of money.
In an aid to assist your design plans, FAFIL has supplied a typical list of questions which it believes should be easy for the new database to answer and hence save its organisers a lot of time and effort.
Typical Set of Questions.
List the different types of penalty-points which can be awarded give the highest scoring ones first.
Provide an alphabetical list of all the teams and their players.
List all the players names and how many time they have been awarded ‘player of the match’, showing who is in the lead for being awarded the prized book voucher.
(Note this has to be calculated live by counting how often each player has been chosen as player of the match)
- Produce a list of all Teams playing all other Teams indicating who is at home and who is away.
