The following relational schema contains the database model for this TV show.

A television channel has decided to create a simple database to register payment information about its most successful show TheVoiceLondon.

In this show, there are contenders that compete to represent the UK in Eurovision. These contenders are coached by famous artists (namely the coaches). Contenders can be formed by a group of participants or a single participant. Both coaches and participants are paid based on the number of shows they attend.

For each coach and participant, the database stores their id, name, surname, date of birth, phone, gender and daily salary.

For each contender, the database stores its id, type (group or individual), stage name, its coach and the participants forming that contender. Each contender should have at least one participant.

For each show, the database stores its date, start time, end time and a location if the show does not take place in the television studio.

Finally, the database also registers which coaches and contenders attended each show.

If a coach decides to leave the program, then their personal and attendance information must be deleted from the database and any contenders they coach need to be assigned a replacement coach.

Coach: PRIMARY KEY (idCoach)

Contender: PRIMARY KEY (idContender), FOREIGN KEY (idCoach) in Coach

Participant: PRIMARY KEY (idParticipant), FOREIGN KEY (idContender) in Contender

Show:

PRIMARY KEY (idShow)

CoachInShow: PRIMARY KEY (idCoach, idShow), FOREIGN KEY (idCoach) in Coach, FOREIGN KEY (idShow) in Show

ContenderInShow: PRIMARY KEY (idShow, idContender), FOREIGN KEY (idShow) in Show FOREIGN KEY (idContender) in Contender

3. Query the Data. Write the SELECT statements that to obtain the following queries:

• Average Female Salary. TheVoiceLondon would like to know the average daily salary for female participants. Write a SELECT query that gives the average daily salary for female participants. Have your result return a single scalar value (i.e. in total GBP).

• Coaching Report. For each coach, list the total number of contenders they are coaching. In the listing, include the information about the coaches without any contender.

• Coach Monthly Attendance Report. For each coach, list the total number of shows attended in each month.

• Most Expensive Contender. TheVoiceLondon would like to know which contender has the highest total daily salary (i.e., sum of the daily salaries of the participants forming that contender). Write a SELECT query that lists the stage name of the contender with the highest total daily salary.

• March Payment Report. Create an itemized payment report for March corresponding to the shows attended by each coach and participant in March. Write a SELECT statement(s) that retrieves:

  • For each coach, show their name, the number of shows attended in March, their daily salary and their total salary for March (calculated as the number of shows attended multiplied by their daily salary).

  • For each participant, show their name, the number of shows attended in March, their daily salary and their total salary for March.

  • The last line of the report should just contain the total amount to be paid in March.

Well Formed Groups! Note group contenders should be formed by more than one participant (otherwise they are individual contenders).

Since MySQL does not support an assertion to check this constraint, write a SELECT statement that returns only a scalar Boolean value (i.e. either True or False). It should return True if there are no violations in the database of this regulation. If there is a violation, then the SELECT statement should return False.

There is a violation if there is a group contender formed by less than 2 participants.

Show that your SELECT statement works by creating a group contender that violates this rule and then running your SELECT statement.

4. One more thing... To avoid that coaches and contenders arrive late to the shows, TheVoiceLondon has decided to change to hourly payments instead of daily payments:

1. Update the coach and participant information to only contain the hourly payment. Given that the shows have a duration of 2 hours and that coaches and participants were required to arrive one hour before the show and to leave one hour after the show, the hourly payment should be calculated as the daily payment divided by 4.

2. Add new fields to the attendance table to register when coaches and contenders arrive to and leave the shows.

3. UPDATE the attendance information to include the arrival and departure times for the past shows. Your query should set the arrival time to one hour before the show started and the departure time to one hour after the end time.

5. Fair payment! The contender with the lower total salary became upset and wants to leave TheVoiceLondon. The participants forming that contender have demanded to have all their contender and personal data removed from TheVoiceLondon database.

Using this contender stage name as its identifying attribute in the query, write the DELETE statement(s) that removes this contender and all their related data from the database. To avoid any future embarrassment in case of a data leak, make sure you also remove all trace of the participants forming that contender from the database.

Coach name surname DoB idCoach phone dailySalary gender Contender stageName type idContender coach Participant name surname DoB idParticipant phone dailySalary gender contender TVShow location date idShow startTime endTime CoachinShow coach show Contenderln Show contender show