Objective: The research question to answer is to show that your database schema can answer directly or indirectly (defining views) the queries users want to ask. The following questions are examples that you can consider for this activity.

• Athletes: 
  • Their competition(s) schedules
  • The number of medals and ranking of the participants in their discipline organised by country 
  • Their scores during the competition
  • The scores of the participants during the games
• Trainers: 
  • Performance per athlete of their team  
  • Number of medals and ranking of the participants of their discipline, 
  • The global score of the team (they couch)
  • The contest(s) schedules.
• Organisers: 
  • The general schedule of the contests (EVENTS) of all disciplines, including the number of athletes participating in each contest, the day and location
  • The general results of all disciplines (gold, silver, bronze) organised by country 
  • The general classification of all athletes participating in each contest organised by discipline and country
  • The participants of the delegations organised by discipline and country
• Press/Public: 
  • The composition of the delegations organised by country and discipline
  • The disciplines of the delegation of a country
  • The profile of top-k athletes organised by discipline and country
  • The profile of an athlete
  • The schedule of the contests organised by the discipline
  • The participants of a contest of a discipline organised by the country
  • The general medal results are organised by country and discipline

Material

• Notion of query: from relational algebra to SQL expressions [PDF]
• MariaDB installed locally or https://www.db-fiddle.com/ (MySQL v8)
• DDL, DML SQL scripts for creating an Olympics Database (here)
• SQL Basics Cheat Sheet (here)
• Algebraic expressions online (here)

Theory background [PDF]

• The notion of query: relational operators (algebra)
• Query language: from algebra to SQL
  Session January 2024
• The query space for a relational database
• The notion of view

To Do

Session 20/12/2023

1. Write the algebraic expressions for the queries you have identified for each user type. Organise your script by user type. Are there queries that cannot be expressed with the algebra?
2. Write a script with the SQL queries implementing the queries you identified for each user type. Organise your script by user type. 
   Session January 2024
3. Design and write a program that implements the query space associated with your schema so that you can parametrise and generate SQL expressions to query your database.
4. Choose queries that can be answered by defining a view, defining the view, and reformulating your query space.

Deliverables: 

• Algebra expression of a selection of queries explaining the criteria used for choosing some queries (document) – January 2024
• SQL queries for every type of user (script and document) January 2024
• Defining views (script)  January 2024
• Query space specification and implementation  (document and pseudocode) January 2024