Scenario

Consider a database system for the organisation of fixtures and results in a soccer league. The data requirements are summarized as follows:

The people involved in the league include players, coaches, managers, and referees. Each person is identified by a unique personnel id.

They are also described by their first and last names along with the date and place of birth. Players are further described by other attributes such as their position on the field (goal-keeper, defender, midfielder, or forward) and have a squad number associated with them.

Teams are uniquely identified by their names. Teams are also described by the city in which they are located and the division and league in which they play (such as premier division or first division). Teams have one manager, a number of coaches, and a number of players. One of these players is the captain. At the beginning of each season all fixtures should be available to view in the database.

Each team plays every other team twice (once athomeand onceaway). Games are played between two teams with one assigned referee. For any particular game on a particular date, there is one team designated as thehometeam and the other team designated as theawayteam. Once the game has taken place, the final result of the game is recorded. The score and the scorers are recorded for each team.

Requirements

1. Using Chen's notation, design an Enhanced Entity Relationship (EER) model for the soccer database in a modelling tool of your choice. All entity types, their attributes and relationships must be clearly shown. You will also be required to show all cardinality and participation constraints. Any enhanced features used (e.g. generalisation/specialisation) must be indicated.

2. Map the EER model devised in (1) into a set of relations in the relational data model. Specifically state any enhanced features used and indicate how you mapped these to the relational model. Clearly state any de-normalization, if any, in all your relations and clearly state the reasons for de-normalizing relations in your implemented solution.

3. Using appropriate SQL commands create a set of database tables using any relational database. Your tables should also show all constraints applied at either column or table level.

4. Populate the database with a small amount of data. The data should be meaningful but does not need to be extensive. The following sites may be useful for quickly generating data:

5. You should produce, demonstrate, and document the following SQL queries for your system:

? List the managers of each team.

? Output the full name of the top scorer in the league with the number of goals scored.

? Output the average number of goals per game of all games played.

? List all of the games that were played in a particular ‘city'.

? List all players who have played a game that was refereed by a particular referee (choose any referee).

6. Create a databaseviewfor your system as follows:

? Produce a list of fixtures yet to be played along with the date of each game.
Notes

[1] You do not need to include an extensive selection of data; the emphasis is on demonstrating your technical and design skills rather than including a wide range of data. [2] Any assumptions above the basic specifications stated must be clearly stated and justified.

Deliverables

1. Interim Submission (Optional)

You should submit your conceptual design of the database for feedback. The idea is to get feedback on conceptual design and its subsequent logical database design mapping.

2. Implementation (Mandatory)

You will be required to demonstrate your system in the laboratory.You will be assigned a time to present your work. There are marks available for being prepared and demonstrating your work in a clear and logically coherent manner. Treat the demonstration as an examination..

Failure to demonstrate your work to a tutor will result in a failing grade for the coursework.

3. Report (Mandatory)

Ashort reportcovering requirements 1 - 6. Show screen shots as outputs to queries. Write a short paragraph on what you would do differently, if you were to do this again. The report should mostly consist of what you will be demonstrating so it will not be a major task to compile.

Grading Criteria

To pass this coursework you must demonstrate:

? competence in conceptual database design of database models

? competence in mapping your conceptual data model to a logical database

? competence in database implementation

? competence in querying the database implementation

? an appreciation of the underlying design and implementation principles

Your grade will depend on how well you meet these criteria. A portion of the available marks will be given for above minimal specification features of your implementation.