Description
Question Narrative
(repeated from Assignment 3)
The directors of an annual music competition have decided to organize their participants using a
database.
The competitors must be at least 5 years old and may be no older than 18. They are registered by their
teacher, and all teachers belong to a studio. Guardian information is stored for each participant.
Competitors must perform an approved piece of music from the composition catalog. Each composition
has a genre and may only be played for a competition category of the same genre.
Competitors are entered into a competition category. Each competition has an assigned date, time,
genre, and minimum/maximum participation age. Performers earn a score out of 100 for each
composition that they perform in a particular category.
A file called music_competition.sql has been provided for your use in this assignment. Execute this
file in MySQL Workbench to build and populate the schema.
Relational Algebra Queries (18)
Code the following queries using a single relational algebra expression. You do not need to list the data
results of the query.
1. Write a query to retrieve the first and last name of each competitor (1 mark).
2. Write a query to retrieve the first and last name of all competitors under the age of twelve who
play the oboe (1 mark).
3. Write a query to retrieve all competitor ID numbers and the name of the music studio that they
belong to (1 mark).
4. Write a query to retrieve the first name and score earned by each competitor (1 mark).
Code the following queries using a sequence of relational algebra expressions. You do not need to list
the data results of the query.
5. Write a query to retrieve the titles of all compositions that will be performed during categories
scheduled for the 1:00 PM timeslot (3 marks).
6. Write a query to retrieve the titles of all compositions that have not been selected by any
performers (3 marks).
7. Code the following query as either a sequence or single expression (4 marks), then draw the
corresponding query tree (4 marks). You do not need to list the data results of the query.
Retrieve a list of all score values that were earned by competitors belonging to the “Music Mastery”
studio.