ITSE 2309 Project Database Programming Oracle Olympics Database
Project Objective: Design and implement a database from requirements and execute queries using SQL.
1. Design a database for tracking Olympic events and results.
2. Write scripts to create the database and populate the tables.
3. Write SQL queries to produce reports.
1. Create an ER Diagram for your Olympics database.
2. Write a short design document (1 or 2 pages) describing your choices and reasons for designing your database as you did.
3. Provide scripts to create, populate, and teardown the Olympics database.
A. makeOlympicsDb.sql – Script to create tables, constraints, & any DB objects.
B. populateOlympicsDb.sql – Script to add records to the database tables.
C. dropOlympicsDb.sql – Script to delete all tables and database objects.
4. Provide one or more scripts (with .sql file extension) to execute the queries defined for the project. Each script should include comments with your name and query/question number.
The International Olympic Committee is creating a database for their upcoming summer Olympic games. Each sport has competitions for several different events. The competitions for each sport are assigned to a specific venue. A competitor is an individual athlete representing a particular country. Each event is scheduled for a single day and time, and we save a single result for each athlete competing in the event.
Note: For this project, there are no qualifying rounds.
The goal of the database is to identify when and where competitions occur, the athletes competing in events, and the medalists for each competition. Medals are awarded:
1. Gold for 1st place
2. Silver for 2nd place
3. Bronze for 3rd place
For each competition, results are stored for each athlete. The results are recorded as an elapsed time, a score, or a measurement.
To test the database, you need to populate with data to demonstrate that the database will meet requirements and produce desired reports. For demonstration purposes, the data should be populated with events and competitors for Gymnastics, Track and Field, and Swimming. Use your own creativity to name athletes and choose countries for the competitions. Provide at least 5 competitors for each event. Some athletes should compete in multiple events and some compete in only a single event.
1. List all the Olympic events in which women compete, sorted alphabetically. No duplicates.
2. List all the Olympic sports with the earliest event date/time and latest event date/time for the events contested in the sport. Order results by the name of the sport. Each sport should only be listed once.
3. List all events scheduled for August 3rd, with the time and venue of the event. Sort events by the time, with the earliest event listed first. If more than one event starts at the same time, sort by the name of the event.
4. For Gymnastics, list each event and the names of all the athletes competing in those events. Sort results by the name of the event, and secondarily by the name of each athlete.
5. List all the countries in your database and the number of individual athletes from each country. Sort by country name.
6. List all the names of athletes who compete in more than one event along with the name of his/her event and the competition date/time. Sort results by the athlete’s name.
7. List each sport, the names of each event (including gender), and the number of competitors entered into each event.
8. List results for Track and Field’s 100 Meter race with times for each athlete, with fastest time first. List the athlete’s name, country, and race time.
9. List all medal results for events that have already occurred and been entered into the Olympics database. List the sport, event, athlete’s name, country and medal (Gold/Silver/Bronze). Order results by the sport, event, and place. (Gold Medal = 1st Place, Silver = 2nd Place, Bronze = 3rd Place)
10. List the countries that won the most cumulative medals in descending order, listing the number of gold, silver, and bronze medals, along with the total. Report should be sorted with the most medals listed first in the report down to the country with the fewest medals. If countries have the same number of medals, the most Gold/Silver/Bronze next followed by alphabetic listing by country name.