Problem Description
You have been commissioned by a non-profit organization to design and implement the Olympic History Database (OHDB) and support a specified set of business processing requirements. The following ER Model shows entities of interest to the organization.
Each Olympic game holds many sport events. Each sport event belongs to one sport category. An athlete can participate in one or more events. Each event must have at least two athletes. Every time an athlete participates in an event, he or she will be assigned a new ContestantNum. This ContestantNum can be used to keep track of team membership.
For example, when John Smith participates in the long jump event, he gets a ContestantNum. When he and his team mates participate in 4 x 100 m relay, he gets another ContestantNum but this time, the ContestantNum is shared with the other three members of his team.
An athlete can represent one and only one country in each Olympic game. However, an athlete can represent different countries in different games (because of nationality change). For example, Jing Chen participated in three summer Olympic Games. She represented China in the 1988 and 1996 and Taiwan in 2000.
Finally, each time an athlete participates in an event, he or she can get a chance to win a medal. See ER digram.
A description of the entities and relationships in Figure 1 are provided below. For attribute type, I = Identifier, C = Composite, M = Multivalued, D = Derived.
Business Processing Requirements
As part of application design, write SQL scripts to provide the functionality required to fulfil the following business processing requirements. Use the SELECT statement for the reporting scripts and DML statements for the data maintenance scripts.
Reporting Scripts
- List all the game has been cancelled. The query results should look like: See image.
- List all gold medal winners of a particular Olympic sport event (e.g., 100m Freestyle). The query results should look like: See image.
- List the last name, first name, date of birth, gender and country represented by each athlete. If an athlete has represented only one country, display only one record. If an athlete has represented many countries, display one record for each country represented. The query results should look like: See image.
- List all the athletes who have represented a particular country in Olympic games. List the results in ascending order of DOB. The query results should look like: See image.
- List in descending order of the number of times each country has participated in an Olympic event. NOTE: If a country has two athletes competing in the same individual event, it is counted as one event participation for the country. If a country has one or more teams competing in a team event, it is also counted as one event participation for the country. The query results should look like: See image.
- Compare the average participating age of the athletes in each Olympic game. (Don’t worry about small rounding errors.) The query results should look like: See image.
- Create a query to list personal details and the number of gold medals won by all gold medal winners. Sort the results in descending order of the number of gold medals won. The query results should look like: See image.
- List the total number of gold medals won by each country in the database in descending order. NOTE: In a team-based event, even if each winning team member is awarded a gold medal, it is only counted as one gold medal for the country. The query results should look like: See image.
- List the “medal score” for each country in descending order. The “medal score” is calculated as follows:
- Each gold medal earns 3 points
- Each silver medal earns 2 points
- Each bronze medal earns 1 point
- NOTE: In a team-based event, even if each winning team member is awarded a gold medal, it is only counted as one gold medal for the country. The query results should look like: See image.
Data Maintenance Scripts
You are required to write the following SQL scripts for the user. The scripts should only prompt the user for the relevant fields.
- A new athlete is going to represent his/her own country in a certain event. Write a script that will update the database accordingly. Assume that the event results are not yet known.
- The International Olympic Committee has decided to add a new event not belonging to any existing sport category in future Olympic games. Write a script to add this event in the database.
- An athlete who was supposed to participate in several events in an Olympic game got seriously ill just before s/he had a chance to participate in any of the events. Write a script to remove his/her records in that game.
Assignment Requirements (Read this section carefully)
Provide the following deliverables in your assignment document in the following order:
- A set of relations derived from the ER model provided. Use standard relational notation and if a relation contains a FK, you should clearly state which PK it references. You should also check that the relations that you come up with are at least in 3NF by applying the rules of normalisation, i.e. there are no partial and transitive dependencies present in the relations.
- A set of Table Instance Charts (TICs) to document the design of the tables. Use a naming convention that is descriptive when naming your tables and columns. For a TIC, you need to specify, if applicable, the most appropriate delete rule for the table’s foreign key(s). When selecting the delete rule for a FK, ensure that you consider the Set Null and Cascade options instead of simply selecting the default restrict rule. Immediately after every TIC, show your table creation and any table alteration statement. NOTE: Create the tables in the schema of one team member who can then grant the other team members access to the tables. Immediately after every TIC and table creation statement show: a) A description of the table created. b) A description of the table’s constraints.
- A physical data model of the database (using Crow's foot notation). You must indicate which columns are the Primary and Foreign Keys for each table in your model.
- The SQL scripts (part of Application Design) to fulfil the processing requirements specified in the assignment.
- The data you inserted into the tables to test your scripts. You can do a SELECT * from each table and include the output in the appendix.
