View the step-by-step solution to:

Case Study A database is to be developed to hold details about a range of team sports including:

Case Study

A database is to be developed to hold details about a range of team sports including:

·      Association Football (Soccer)


·      Australian Rules Football (AFL)


·      Baseball


·      Basketball


·      Beach Volleyball


·      Cricket


·      Netball


·      Rugby League


·      Rugby Union


·      Volleyball



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

are played.


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

http://www.weatherbase.com/

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


Tasks

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

Attributes

·       Sport Name ----KEY for sport entity

·       Team Size

·       Sport Description (VARCHAR 100)

·       Countries sport played -----is countries a separate entity?

2.      LEAGUE entity

Attributes

·      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


      









       


                                                       arranged into




one to many arrow direction

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question