The Voice London television talent show Database Design & Complete Implementation with Queries

The Voice London Show

Setup. In Part 2, based on a given ER diagram and a set of requirements, you will implement a relational model for a television talent show named TheVoiceLondon. Then, you will have to perform different data manipulation and retrieval operations on the database. On KEATs you will find a .zip containing template SQL files to edit for your Part 2 submission.

Requirements. 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 sores 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.

The following relational schema contains the database model for this TV show. In the relational schema below, primary keys are in bold text, and foreign keys are underlined.

Schema Definition

Based on the requirements above, write the required SQL DDL (Data Definition Language) statements (i.e. CREATE TABLE…) to create the schema and corresponding tables.
Ensure that:

  • table and attribute names do not conflict with SQL reserved words
  • attribute data types are core primitive SQL data types as described in the lectures (i.e. do not use the ENUM type for example)
  • table columns have appropriate key and entity constraints properties
  • every table has a primary key specified as it corresponds to your relational model
  • all foreign keys are properly declared, and explicitly describe how they handle potential referential integrity constraint violations (i.e. it is up to you to decide the triggered action to the foreign key constraints)
  • your schema enforces the domain and semantic constraints stated in the requirements.

Note that you may not be able to enforce all of the semantic domain constraints in the CREATE TABLE statements and MySQL does not have Assertions in the manner that we discussed in the lecture (i.e. using CREATE ASSERTION). If you are unable to enforce a semantic domain constraint include a comment in your schema explaining your constraint and the reason it is not implemented. Write your schema in the provided template file: schema.sql Assume that the database schema will already be created for you (i.e. do not include a CREATE SCHEMA statement in your file, it will result in an error). Also assume that your script will already be run within your database schema (i.e. do not include a USE…; statement in your file, it will result in an error).

Populate Database with data. 

Time to get creative! Populate your database with some data that you will come up with on your own. Since you only require a small test sample of data, you will use SQL INSERT statements to populate your database.

More precisely:

  • Pick at least 3 of your favorite celebrities to include as Coaches. Make up their personal data.
  • Create at least 10 participants.
  • Create at least 5 contenders and assign them, participants, so that there is one group contender.
  • Assign these contenders to the different coaches, but making sure that there is at least one coach without contenders.
  • Create shows taking place all Saturdays and Sundays in March and April 2017. The shows can start at any time you want but must have a duration of 2 hours. Note that not all shows can have the same start and end times.
  • For each show create at least 3 attendances of contenders and 2 attendances of coaches

Write you INSERT statements in the provided template file: insert.sql You may only use the DML (Data Manipulation Language) commands covered in lecture to help you populate your database.

All of your data must be contained within the insert.sql file, do not load the data from separate data files (i.e. using a CSV file). Do not use other SQL statements, such as FUNCTIONs, procedures or other programmatic MySQL-specific commands. Assume that your script will already be run within your database schema (i.e. do not include a USE…; statement in your file, it will result in an error).

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 the gives the average daily salary for female participants. Have your result return a single scalar value (i.e. in total GBP).00
  • 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.
  • Most Expensive Contender. TheVoiceLondon would like to know which is the contender with 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. Hint: You may use the string concatenation function CONCAT (https://dev.mysql.com/doc/refman/5.5/en/stringfunctions. html#function_concat) and UNIONS to help to build the payment report.
  • 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. Write all of these SELECT statements in the above order in the provided template file: select.sql

Assume that your script will already be run within your database schema (i.e. do not include a USE…; statement in your file, it will result in an error).

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. In particular, 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.

Write all of these statements in the provided template file: update.sql Assume that your script will already be run within your database schema (i.e. do not include a USE…; statement in your file, it will result in an error).

Fair payment! 

The contender with the lower total salary became upset and wants to leave TheVoiceLondon. In particular, the participants forming that contender have demanded to have all of 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.

Write all of these DELETE statements in the provided template file:
delete.sql
Assume that your script will already be run within your database schema (i.e. do not include a USE…; statement in your file, it will result in an error).

Get Project Solution Now

Comments

Post a Comment