SQL Implementation for a Coach Booking Database with ERD Analysis

This assignment solution focuses on the design and implementation of a coach booking database. It includes an entity-relationship diagram (ERD) to visualize the database structure, followed by SQL queries to create tables (coach, trainee, booking) with appropriate attributes and data types. The solution demonstrates how to populate these tables with sample data using insert statements. Furthermore, it showcases various select queries involving joins and where clauses to retrieve specific information from the database. Finally, it implements a stored procedure to verify data integrity, ensuring that records exist before attempting to insert new data. Desklib offers this and many other solved assignments for students.

Coach Booking database
Name
Institution
Professor
Course
Date

Question Two: Entity relation diagram (ERD)

Question Three: More analysis and SQL
i. Creating database and tables
create database coaching;
create table coaching.coach(Coach_Name varchar(20), Email_address varchar(50),
Telephone_No int,
C_address varchar(30),C_post_code int,Achievement varchar(20), confirment_year
year,Crtificate varchar(20));
create table coaching.Trainee(Trainee_No varchar(20), Venue_Name
varchar(20),Trainee_Name varchar(20),
T_address Varchar(20),T_post_code int, Telephone_No int);
Create table coaching.booking(Telephone_No int, booking_date date,session_length
double(4,2),start_time time,
travelling_time double(4,2),Coach_Name varchar(20),Trainee_No
varchar(20),Payable_amount double(4,2),
Venue_Name varchar(20),
payable_status text(200),completion_status text(200));
ii. Populating and querying from tables
insert into
coaching.booking(Telephone_No,booking_date,session_length,start_time,Travelling_
time,
Coach_Name,Trainee_No,payable_amount,Venue_Name,Payable_status,completion
_status,Booked_time)values
('[phone]','2016-12-12','3.50','7:30','1.30','mike j','001','20.0','Easy coach','ok','Not
yet','1'),
('[phone]','2014-10-23','1.20','8.00','2.12','John
m','002','40.11','Jeriie','good','complte','2'),
('+1236547','2012-03-12','1.45','12.00','1.15','Mary','003','50.50','Gyme1','Not
paid','bad','3');

insert into
coaching.coach(Coach_Name,Email_address,Telephone_No,C_post_code,Achievem
ent, confirment_year,crtificate)
values('smith j','[e-mail]','[phone]','65','Tennis','2016','Tcert01'),
('Mike','[e-mail]','[phone]','566','swimming','2015','swim003'),
('Jose','[e-mail]','[phone]','7894','Entertainment','2015','Enter03');
Insert into
coaching.trainee(Trainee_No,venue_Name,Trainee_Name,T_address,T_post_code,Te
lephone_No)
values('1','kituro','John','Jumo-582','5879','[phone]'),
('2','Jemii','Lucy','125 jumuia','1236','[phone]'),
('3','guru','ngenga','569-mwere','784','[phone]');

Selecting from multiple table
a. select Coach_Name,booking_date from coaching.booking
left join coaching.trainee on Trainee.Trainee_No =booking.Coach_Name;
b. select coach_Name, Booked_time from coaching.booking;
c. select coach_Name, Booked_time, Venue_Name, session_length from
coaching.booking where booking_date='2016-12-12' order by Coach_Name;

d. select Coach_Name from coaching.booking where booking.session_length=''&&
start_time='1.15-2.00';
iii. Verify data integrity without through procedures
create procedure checkvalue
(
Telephone_No int,
start_time double(4,2)
)
as
If exists(select 'true' from coaching.booking where Telephone_No=@ Telephone_No)
Begin
select 'Records exist'
End
else
Begin
insert into
coaching.booking(Telephone_No,satrt_time)values(@Telephone_No,@start_time)
select 'Record added successully!'
End