A database is to be developed to hold details about a range of team sports including:
· Association Football (Soccer)
· Australian Rules Football (AFL)
· Beach Volleyball
· Rugby League
· Rugby Union
For each sport we wish to record the sports name, the sports standard team size (the number of players on the ground/court at any time), a description of the sport to a maximum of (VARCHAR 100) and whether the sport is played in multiple countries of the world (ie. if the sport is international).
Sports are arranged into leagues. For each league we wish to record the league code, the league name, whether the league is an international league, the name of the President/CEO of the league, the title of the position held by the this league head, the leagues contact address and phone number.
Sports are played by teams, a given team is restricted to playing a single sport. Each team is assigned a unique team code across all sports. A team will have recorded the teams name, the home city of the team, the name of the head of the team (the President/CEO) and the name of the team's head coach.
A team consists of players. All players are required to be registered as a player and are assigned a unique player number across all sports. The players name is recorded. The system must also record the players height, weight, date of birth and a brief bio of the player
(which is optional) up to (VARCHAR 200). A given player may be a member of multiple teams eg. a local, a state and a national team, or even across different sports. The system must also record the official position of a player within a given team, where such a position has been
assigned. We wish to record, for each player the amount of time they spend on the ground for each game they play in.
Within a league, when two teams play one another they play at a venue, on a particular date with a particular game start time. The outcome from the game may be one team Wins, a Draw, the game is Cancelled or Forfeited. A sample game fixture/result is shown in Appendix
A (note not all rows are shown in each table, indicated by ....). The system must allow the full season fixture to be loaded at the start of the season, game results will be updated as they
For the venues in which games are played we wish to record the name of the venue, its full address (including city) and its seating capacity. For each city in which a venue is located we wish to record the city's name, elevation in metres and average temperature, average high temperature, average low temperature (all temperatures in C), average precipitation (rainfall
in mm) and the country in which the city is located. Typical weather data can be determined from several internet locations including
Countries, are identified by the ISO3166-1 2-character country, details of which can be obtained from several internet locations, for example the CommonDataHub http://www.commondatahub.com/live
1. Using LucidChart, make a conceptual model (Entity Relationship Diagram) for the Sports database.
○ For this conceptual model, include what you see as identifiers (keys) for each entity and the other attributes required for that entity to support the operational activities of the database. As a conceptual model remember you must not include foreign keys.
○ Surrogate keys must not be added to this model.
○ Participation and connectivity for all relationships must be shown on the diagram
Please see my answers below and help to complete the assignment task
Sport Case Study solution
Identified entities and attributes
1. SPORT entity
· Sport Name ----KEY for sport entity
· Team Size
· Sport Description (VARCHAR 100)
· Countries sport played -----is countries a separate entity?
2. LEAGUE entity
· League Code---------KEY for league entity
· League Name
· Countries league played------is countries a separate entity?
· League's CEO or PRESIDENT -----how do I deal with this to show league head tittle?
· League's head tittle
· League's contact Address
· League's Phone Number
3. TEAM entity
· Team Code -------KEY for team entity
· Team Name
· Team City
· Name of head of team (CEO or PRESIDENT)----same problem of separation as with league above?
· Name of head coach
4. PLAYER entity
· Player Number------KEY for player entity
· Player Name
· Player Height
· Player weight
· Player Date of birth
· Player BIO (VARCHAR 200)
· Player position
· Time played
5. VENUE entity
· Venue Name-----KEY for Venue entity
· Venue full address
· Venue sitting capacity
· Venue City -----How do I show city details? Ie city name, elevation, temperatures, precipitation and country in which the city is located? Make city another entity?
My questions are underline in italics above
Other questions are
· Are they entities and attributes I have identified correct and are they the only entities and or attributes in this case study?
· Not sure of the connectivity, participation,cardinalities shown below: HELP WITH BELOW IDENTIFYING RELATIONSHIP STRENGH, CARDINALITIES, CONNECTIVITY
PARTICIPATION and CONNECTIVITY
one to many arrow direction
Recently Asked Questions
- which type of immunity reacts to an invading pathogen first
- consider the following true process. However the regression relationship is estemited as. Will the estimate B1 be biased if the true value of Bo does not = 0?
- A stock sells for $40. The next dividend will be $4 per share. If the rate of return earned on reinvested funds is a constant 15% and the company reinvests 40%