Question

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

The following relational schema contains the database model for this TV show. In the
relational schema below, primary and foreign keys are not given to you.
Implementation
(2.1) Create and implement an ER model in appropriate 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 appropriate SELECT queries.
(2.4) Update data using the appropriate UPDATE query.
(2.5) Remove data using a DELETE query.
2.1 Schema Definition. Based on the both the requirements above, and the keys
you have identified, 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 the semantic domain constraints in
the CREATE TABLE statements and MySQL does not have Assertions in the
manner that we discussed in 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).

2.2 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 favourite celebrities to include as Coaches. Make
up their personal data. If you don’t really care for celebrity culture, then
pick 3 random people to include as Coaches.
• 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, making sure that
there is at least one coach without contenders.
• Create shows taking place all Saturdays and Sundays in March and
April 2019. 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 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 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 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).

2.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.

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).

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

Write all 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).

2.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.

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).

Answer 1

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

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

Here we are creating the table : coach and participant:

create table coach(id numeric(10), name varchar(10), surname varchar(10), dob numeric(10), phone numeric(10), gender varchar(5), daily salary numeric(10));

create table participant(id numeric(10), name varchar(10), surname varchar(10), dob numeric(10), phone numeric(10), gender varchar(5), daily salary numeric(10));

after creating the table then we have to insert the values into the respective tables ;

insert the values into coach(01, vikash,varma, 21-10-1982, 9876543210, male, 2000);

insert the values into coach(02, akash, sharma, 22-12-1922, 9887654321, male, 3000);

insert the values into coach(03, nagesh, mitra, 23-01-1882, 8976543210, male, 4000);

insert the values into coach(04,shivani,xyz, 02-11-1982, 7876543210, female, 5000);

insert the values into coach(05, sanju, vihar, 26-04-1985, 9876556210, male, 6000);

insert the values into coach(06, radha, kapur, 11-12-1989, 7876583210, female, 7000);

insert the values into coach(07, bindhu, roor, 20-10-1982, 8896543210, female, 8000);

insert the values into coach(08, raju, vahe, 20-05-1983, 8765432123, male, 2000);

insert the values into coach(09, jan, arma, 27-10-1982, 9876543345, male, 9000);

insert the values into coach(10, mahesh, mama, 23-11-1982, 8973543210, male, 3000);

inserting the values to the participant table:

insert the values into coach(01, ram, kam, 26-04-1985, 9876556210, male, 6000);

insert the values into coach(02, alen, t, 26-04-1985, 9876556210, male, 6000);

insert the values into coach(03, sham, m, 26-04-1985, 9876556210, male, 6000);

insert the values into coach(04, anup, yb, 26-04-1985, 9876556210, male, 5000);

insert the values into coach(05, arpitha, g, 26-04-1985, 9876556210, female, 6000);

insert the values into coach(06, anu, s, 26-04-1985, 9876556210, female, 6000);

insert the values into coach(07, adi, shah, 26-04-1985, 9876556210, male, 6000);

insert the values into coach(08, dig, am, 26-04-1985, 9876556210, male, 6000);

insert the values into coach(09, sam, lam, 26-04-1985, 9876556210, male, 6000);

insert the values into coach(10, muki, bh, 26-04-1985, 9876556210, male, 6000);

then the table is to be created and if you want to show the database table is ,

what we are created above that table,all values are to be same in this table so write qureey as

select * from coach;

id	name	surname	dob	phone	gender	daily salary
01	vikas	varma	26-04-1985	9876556210	male	1000
02	akash	sharma	26-04-1985	9876556211	male	2000
03	nagesh	mitra	26-04-1985	9876556233	male	3000
04	shivani	xyz	26-04-1985	9876556234	female	4000
05	sanju	vihar	26-04-1985	9876556434	male	5000
06	radha	kapur	26-04-1985	9876556244	female	6000
07	bindhu	roor	26-04-1985	9876556210	female	7000
08	raju	vahe	26-04-1985	9876556200	male	8000
09	jan	arma	26-04-1985	9876556222	male	9000
10	mahesh	mama	26-04-1985	9876556245	male	3000

selct * from participant;

Here also same able

01, ram, kam, 26-04-1985, 9876556210, male, 6000

02, alen, t, 26-04-1985, 9876556210, male, 6000

03, sham, m, 26-04-1985, 9876556210, male, 6000

04, anup, yb, 26-04-1985, 9876556210, male, 5000

05, arpitha, g, 26-04-1985, 9876556210, female, 6000

06, anu, s, 26-04-1985, 9876556210, female, 6000

07, adi, shah, 26-04-1985, 9876556210, male, 6000

08, dig, am, 26-04-1985, 9876556210, male, 6000

09, sam, lam, 26-04-1985, 9876556210, male, 6000

10, muki, bh, 26-04-1985, 9876556210, male, 6000

here some are shows the best explaination for the creating the table

• SELECT - extracts data from a database.
• UPDATE - updates data in a database.
• DELETE - deletes data from a database.
• INSERT INTO - inserts new data into a database.
• CREATE DATABASE - creates a new database.
• ALTER DATABASE - modifies a database.
• CREATE TABLE - creates a new table.

Then we are moving to these questions as

example we are taking as follows:

INSERT:
a) To insert a row into the table.
SYNTAX:
mysql > INSERT INTO TABLENAME VALUES („STRING‟, NUMBER….);
EXAMPLE:
mysql > INSERT INTO DEPARTMENT VALUES (1,‟CSE‟);
Query ok
b) To insert multiple rows into the table.
SYNTAX:
mysql > INSERT INTO TABLENAME VALUES („STRING1‟, NUMBER1….),(„STRING2‟, NUMBER2….),(„STRING3‟, NUMBER3….);
EXAMPLE:
mysql > INSERT INTO DEPARTMENT VALUES (1,‟CSE‟),(2,‟EEE‟),(3,‟ECE‟);
Query ok
SELECT:
a) To display table‟s data.
SYNTAX:
mysql > SELECT * FROM TABLENAME;


b) To display all the columns.
EXAMPLE:
mysql > SELECT * FROM DRPARTMENT

DEPT_ID DEPT_NAME
1 CSE
2 EEE
3 ECE
c) To display the specified columns
SYNTAX:
mysql > SELEXT FILENAME1, FILENAME2 ,…. FROM TABLENAME;

TO DELETE THE TABLE

SYNTAX:
mysql > DROP TABLE TABLENAME;
EXAMPLE:
mysql > DROP TABLE COACH;
Query OK,0 rows affected

TO DELETE A COLUMN

SYNTAX:
mysql > ALTER TABLE TABLENAME DROP COLUMNNAME;

EXAMPLE:
mysql > ALTER TABLE COACH DOB;
Query OK, 0 rows affected

here how ER diagram represents

for example as: student examination system:

then we are like that only solved for the coach details

coach name, id, phone, salary, gender, date of birth, surname;

as well as participant aslo same thing.