General Description
We want to design and implement a database in MySQL for storing the information of all athletes and their participation in the Olympic Games held over the past years. Summer Olympics have been held since 1896 (1928 for women). Of course, you are not expected to populate the database with all the athletes from all these years – just enough to answer the queries that we wish to present.
- An athlete has an Olympic ID, name, gender, country, and a birth date.
- Each Olympiad has the year, a season (summer or winter), the country where it took place, and the city. For example, the 2008 summer Olympic Games were held in Beijing, China.
- Every athlete in the database participates in at least one Olympiad. An athlete can participate in several events in one Olympiad and can, in fact, participate in more than one Olympiad. For example, Michael Armstrong participated in the swimming, diving, and water polo events. Ian Thorpe participated in the Sydney 2000 summer Olympics and won 3 gold and 2 silver medals. In Athens 2004, he won two gold, one silver and one bronze medal.
- An event has a sport name, event name, location, and scheduled time and date (there is no need to keep track of the time for Olympics held prior to 2012, only the date), which athletes were scheduled to compete in which events and how they placed, and who the winner(s) of the event was (e.g., what kind of medal they were awarded). You may assume that null values are used for winners and placings until the event has been held. For example, the women’s 100 m backstroke swimming held in summer 2008 Olympics resulted in a gold medal (finished in 1st place) for Natalie Coughlin from USA in a time of 58.96
- Events in the Olympics may be either individual or team events. If it is a team event, we wish to know who the members of each team were.
Note that this is a guide. You may need to add additional entities and attributes to meet the requirements for the queries or application programs.
Queries and Database modifications
In order to assess the potential of your MySQL database system for use, the database should be able to supply answers to the following queries:
- List the competitor countries in the event men’s Basketball in alphabetical order in the 2012 London Olympics.
- List the full names of all competitors in the 2012 London Olympics from Australia
- For each country, display the country name and the total number of competitors, including those countries that have no competitors, in descending order of the number of competitors.
- Count the number of competitors who were from the Philippines in every Olympics held since 1999.
- List all the competitors who have competed in more than 2 events in any Olympics since 1999.
- Display the results of the men’s 1500 freestyle event in the 2008 Beijing Olympics.
- Display all the events that were held in the 2010 winter Olympics.
- For each competitor, list their full names and the total number of races the competitor was in. (you can assume that all competitors are in at least one race.)
Project format and organization:
Treat this project as you would a report to be delivered to a customer. While content is obviously the most important part of the task, the format should be neat and well organized. All documentation and reports should be typed.
Project Deliverables: To make the project easier to do, divide it into several steps.
Step 1: Design the EER model and specify all constraints on it. Design a conceptual model (EER). Document by drawing the EER diagram and fully describing your design choices. Specify key attributes and all constraints on each relationship type, e.g., include cardinality and participation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. Fully document any assumptions that you make.
Step 2: Translate your EER model into a fully specified relational schema. Define all relations, keys, attributes, data types and integrity constraints. Document, by drawing your relational DB schema.
Step 3: Normalize the relational schema. Document how your design avoids update anomalies. Address:
- Normalization -- in what normal form are your tables? How do you know they are in 3NF? If they are not in 3NF, why not?
- The update behaviour you defined through SQL, e.g., cascade deletes, etc.
- Lossless joins -- are they possible? How do you know?
Step 4: Implement the relations in MySQL Implement your relational schema in MySQL. Most of the documentation for this task will be in the MySQL files that create the tables and define integrity constraints.
Step 5: Populate your database For this project you should populate your database with a minimal set of records, create the common queries shown above. Populate your database with sufficient sample data to demonstrate queries and applications. Document by submitting a listing that shows the contents of each table.
Step 6: Design the queries that must be issued on the DB to perform each action. Implement all queries and database modifications described in the section above as well as two additional queries of you own choice.
System functions should be made as robust as possible (error checking) and should preserve database integrity.
