Each year in Southern Cross, the over 45's footy clubs take part in a spirited local competition lasting 10 weeks of games (identified in the database by a gameID) with a final 11th week of one game played as a grand-final for the 2 teams finishing in the top 2 positions after the 10 week home-and-away season. The winner of the GF wins the bragging rights to be crowned "Southern Cross Golden Legends for that season/year.

The competition has six (6) teams (identified in the database as teamID).

The teams are:

team 1: Quokkas; home ground = Quokka Reserve

team 2: Eagles; home ground = Darlington Sports Club

team 3: Falcons; home ground = Ghooli Estate

team 4: Miners; home ground = Goldfields Centre

team 5: Lions; home ground = Corinthia Reserve

team 6: Giants; home ground = Moorine Rock Sports Club

Each team has one coach (coachID). Each team has between 22 and 28 registered players*1 (playerID) with eighteen (18) players required to play in a team for each game.

*1 Note: a minimum of 22 registered players is required for a team to be considered viable to take part in the competition.

Each team has its own home ground (for e.g., The Quokkas home ground is Quokka Reserve) where the team:

(1) trains during the week; and

(2) hosts a game every second weekend of the 10 week competition/season.

Home grounds have an address and spectator capacity, with attendance at games being kept.

There are 30 games of footy across the season, with each team playing each other twice, once at their home-ground and once at their opponent's home ground.

Teams win games by scoring a combination of goals and points (6 points per goal, 1 point per behind) for a total points-score higher than the team they are playing against.

A "win" results in a team gaining 4 competition points, for e.g., if a team went undefeated for the whole season, they would score 40 competition points. At the end of 10 rounds of games, the 2 teams at the top of the competition points ladder take part in the grand-final (with the team at the top hosting the game at their home-ground). For the record, there is no such thing as a "draw". If scores are level at the end of a game, the teams will play until one of the team's scores (a goal or a point counts).

Each game produces a main result (e.g., a win or a loss for each of the teams) as well as other results/information such as gaols and points scored by each team. The types of information/data associated with each game are:

A win or loss result for each team in a game

Three (3) best-on-ground (BoG) performances by players involved in the game as voted for by the two field umpires running each game. The player voted as best on ground gains 3 x BoG-points, 2nd best player gains 2 x BoG-points, and the third best player gains 1 x BoG-point. The player with the most BoG-points at the end of the season is crowned Best Player of the season.

Number of goals (worth 6 points) and behinds (worth 1 point) for each team

Total number of points for the game (which results in a win or loss)

Develop a data model to conceptualise how the entities within the case/scenario relate to one another and build a database to manage the data/information for one full season of games.

SOME NOTES TO HELP

6 teams playing each other each week means there are 3 games per week

3 games per week, over a period of 10 weeks (i.e., each team plays every other team twice) means there are 30 home-and-away games in the competition/season.

1 grand-final played in week 11 means there is a total of 31 games in the database

home and away games need not be separate entities per se. It's just one team is the home-team and one team is the away team

teams require at least 22 registered players so there should be between 132 and 168 players in the database

there are 18 players in each team when they take to the ground for a game (which makes a total of at least 44 players taking part in a game).

Best-on-Ground (BoG) points (to 3 players) are awarded at every game by the umpires using a 3-points, 2-points, 1-point system

each game has four (4) umpires: 2 x field umpires running the game, and 2 x goal umpires (1 at each end of the ground) who judge and call when a goal or point is scored. Thus there are a minimum of 12 umpires required in the database.

1. Design / Conceptual DB

1.1 Entity Relationship Diagram

Use VISO software to create your ERD.

1.2 Business Rules

Instruction: Write all Business Rules according to the ERD you have designed for the case.

HELP: Each Business Rule describes a relationship between 2 entities in real everyday language, first describing the relationship from entity 1 to entity 2, and then describing the reverse (how entity 2 relates to entity 1).

HELP: There should be twice as many business rule statements as there are relationships in your ERD (with each relationship requiring two (2) business rule statements

HELP: Since there should be no M:N (many to many) relationships in your final ERD, do not describe M:N relationships in the final business rules REMOVE HELP INSTRUCTIONS