Overview
The purpose of this coursework is to create a database ER schema and relational schema
for a specific domain based on the provided requirements. This coursework also involves
implementing a relational schema in SQL and writing some queries in SQL Data
Manipulation Language.
This coursework is comprised of 2 Parts, each with separate deadlines. This is Part 2 of
the Coursework. Be aware any late submisions of Part 2 wil have the grade for Part 2
caped at 40%.
The entire coursework is formaly asesed and is worth 11% of your final grade. Part 2
of the Coursework is worth 6%. You wil receive some fedback as part of the marking of
the coursework.
Part 2: Implementation (6% of your final grade)
(2.1) Create and implement an ER model in apropriate SQL schema and table creation
queries, including entities, relationships and constraints.
(2.2) Insert appropriate sample data using INSERT queries.
(2.3) Create and output the apropriate SELECT queries.
(2.4) Update data using the apropriate UPDATE query.
(2.5) Remove data using a DELETE query.
Part 2 Deadline: Monday March 12th by 1:5 pm on KEATs.
(Part 2 Late Deadline: Tuesday March 13th by 11:5pm on KEATs)
Overal presentation including legibility and proper use of language (where aplicable).
Setup. In Part 2, based on a given ER diagram and a set of requirements, you wil
implement a relational model for a television talent show named
TheVoiceLondon. Then, you wil have to perform. different data manipulation and
retrieval operations on the database. On KEATs you wil find a .zip containing
template SQL files to edit for your Part 2 submision.
Requirements. A television chanel has decided to create a simple database to
register payment information about its most sucesful 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 atend.
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.
Finaly, 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
ned to be asigned a replacement coach.
The folowing 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.
2.1 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 coresponding tables.
Ensure that:
• table and atribute 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 apropriate key and entity constraints properties
• every table has a primary key specified as it coresponds 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 trigered 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 al of the semantic domain constraints
in the CREATE TABLE statements and MySQL does not have Assertions in the
maner that we discused in lecture (i.e. using CREATE ASSERTION). If you are
unable to enforce a semantic domain constraint include a coment 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 wil already be created for you (i.e. do not
include a CREATE SCHEMA statement in your file, it wil result in an error). Also
Coach
idCoach name surname DoB phone dailySalary gender
Contender
idContender stageName type idCoach
Participant
idParticipant name surname DoB phone dailySalary gender idContender
TVShow
idShow date startTimeendTimelocation
CoachInShow
idCoach idShow
ContenderInShow
idContender idShow
assume that your script. wil already be run within your database schema (i.e. do
not include a USE…; statement in your file, it wil result in an eror).
2.2 Populate Database with data. Time to get creative! Populate your database
with some data that you wil come up with on your own. Since you only require
a small test sample of data, you wil 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 diferent coaches, but making sure that
there is at least one coach without contenders.
• Create shows taking place al 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 al shows can have the same start and end times.
• For each show create at least 3 attendances of contenders and 2
atendances of coaches.
Write you INSERT statements in the provided template file: insert.sql
You may only use the DML (Data Manipulation Language) comands 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, PROCEDURESs or other
programatic MySQL-specific comands.
Asume that your script. wil already be run within your database schema (i.e.
do not include a USE…; statement in your file, it wil result in an eror).
2.3 Query the Data. Write the SELECT statements that to obtain the folowing
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).
• 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 Atendance Report. For each coach, list the total number of
shows atended in each month.
• Most Expensive Contender. TheVoiceLondon would like to know hich 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
coresponding to the shows atended by each coach and participant in
March. Write a SELECT statement(s) that retrieves:
• For each coach, show their name, the number of shows atended in
March, their daily salary and their total salary for March (calculated
as the number of shows atended 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./en/string-
functions.html#function_concat) and UNIONS to help to build the payment
report.
• Wel Formed Groups! Note group contenders should be formed by more
than one participant (otherwise they are individual contenders).
Since MySQL does not support an asertion to check this constraint, write
a SELECT statement that returns only a scalar Bolean 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 les than 2
participants.
Show that your SELECT statement works by creating a group contender
that violates this rule and then runing your SELECT statement.
Write al of these SELECT statements in the above order in the provided
template file: select.sql
Asume that your script. wil already be run within your database schema (i.e.
do not include a USE…; statement in your file, it wil result in an eror).
2.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 arive 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 arival
time to one hour before the show started and the departure time to one
hour after the end time.
Write al of these statements in the provided template file: update.sql
Asume that your script. wil already be run within your database schema (i.e.
do not include a USE…; statement in your file, it wil result in an error).
2.5 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 al of their contender and personal data
removed from TheVoiceLondon database.
Using this contender stage name as its identifying atribute in the query, write
the DELETE statement(s) that removes this contender and all their related
data from the database. To avoid any future embarasment in case of a data
leak, ake sure you also remove al trace of the participants forming that
contender from the database.
Write al of these DELETE statements in the provided template file:
delete.sql
Asume that your script. wil already be run within your database schema (i.e.
do not include a USE…; statement in your file, it wil result in an eror).
What to turn in
For each SQL file that you turn in:
1. Include your NAME and STUDENT NUMBER at the top of every SQL file in a
SQL line coment.
2. Edit these files as text files, not Word files or propriety SQL software.
3. Do NOT rename the files.
4. ONLY use the SQL line coment character (i.e. lines begining with --) for
coments.
Coment your SQL.
Just like program code, coments help outline, structure, and make clear what is
writen. You wil be evaluated on your ability to provide coments to help
provide structure, organization, and clarity to your SQL code. Make your
coments useful, concise, and clear.
Submision
Put all of the SQL files (and your optional updated database design PDF) in a ZIP
file (i.e. with a .zip file extension) and submit it on KEATs before the deadline.
Do not put the files in a RAR (i.e. rar file).
Do not put the files in a tar-gziped file (i.e. tar.gz.).
Submit your files in a ZIP file (i.e. with a .zip file extension).
Any SQL file that is mising or renamed wil result in 0 marks for that sub-part.
Evaluation
The SQL files you create wil be valuated using the NMS database server that you
utilized in lab. Test al of your database SQL files on the NMS’s database server in
your own personal database to be absolutely sure that they work and do not have
any erors.
Your files wil be executed in folowing order:
1. schema.sql
2. insert.sql
3. select.sql
4. update.sql
5. delete.sql
Each file wil be tested from the NMS UNIX comand line, with this comand:
mysql -u k123456 –p -h mysql2.nms.kcl.ac.uk -P 33306 yr_db < file.sql
Where k123456 is the database user name (i.e. your k-number), yr_db is the
database name, and file.sql is the sql file to be executed. Using your NMS
databases, test your files, in this order, to make sure that they run, before you
submit them. Test your files, even if you are “just ading coments”.
If any of your submited files do not run on the NMS database server, your Part 2
coursework wil be caped to 60% of the Part 2 total marks.