Question 1.
i.
a)

If we delete a row from table Staff_Branch for example row 4, StaffID S333, BranchID B33 the other information about that branch are also deleted from the database as well e.g. Br_Address 21 Faker Road.

Y /a
select P_code, V_name
from PRODUCT p inner join VENDOR v
on p.V_CODE = v.V_CODE;

Y / c
select Order_code, COUNT (P_PRICE) AS 'Total cost'
from ORDER_DETAIL od inner join PRODUCT P
on od.Product_CODE = p.P_CODE
group by Order_code;



ii.


iii.
b)
select DISTINCT firstName, lastName
from [Owner] o inner join PetAndOwner pao
on o.ownerId = pao.ownerId inner join Pet p
on p.petId = pao.petId inner join PetType pt
on p.petTypeId = pt.petTypeId
where animalType ='dog';


iv.
a)
ACID Stands for
A	-	Atomicity
C	-	Consistency
I	-	Isolation
D	-	Durability

Isolation
The isolation property ensures that the concurrent execution of transaction results in a system state that would be obtained if transaction were executed sequentially, one after the other.
Example of ACID (I)
A teller looking up a balance must be isolated from a concurrent transaction involving a withdrawal from the same account. Only when the withdrawal transaction commits successfully and the teller looks at the balance again will the new balance be reported.
You go to the bank, your current balance is $100, you withdraw $20 from the same account, only when the withdrawal transaction how been completed successfully and the banker looks at your balance again, will the new balance be reported.

b)
select  firstName, lastName, petName
from [Owner] o LEFT OUTER join PetAndOwner pao
on o.ownerId = pao.ownerId
LEFT OUTER join Pet p
on p.petId = pao.petId;

Question 2.

i.
ER Diagram



ii.
GRD




Question 3.
Create Statements
i.

CREATE TABLE Training (
trainingID numeric(8) NOT NULL,
trainingType varchar(20) NOT NULL,
CONSTRAINT Training_PK PRIMARY KEY (trainingID)
);

CREATE TABLE Coach (
coachID numeric(8) NOT NULL,
coachName varchar(20) NOT NULL,
coachEmail varchar(20) NOT NULL,
coachTele numeric(8) NOT NULL,
coachAddress varchar(20) NOT NULL,
coachPostcode numeric(4) NOT NULL,
CONSTRAINT Coach_PK PRIMARY KEY (coachID)
);

CREATE TABLE Achievements (
qualifications varchar(20) NOT NULL,
[certificates] varchar(20) NOT NULL,
awards varchar(20) NOT NULL,
yearOfConferment numeric(4) NOT NULL,
coachID numeric(8) NOT NULL,
CONSTRAINT Achievements_qualifications_PK PRIMARY KEY (qualifications),
CONSTRAINT Achievements_coachID_FK FOREIGN KEY (coachID) REFERENCES Coach
);

CREATE TABLE Venue (
venueID numeric(8) NOT NULL,
venueName varchar(20) NOT NULL,
venueTele numeric(8) NOT NULL,
venueAddress varchar(20) NOT NULL,
venuePostcode numeric(4) NOT NULL,
bookingID numeric(8) NOT NULL,
CONSTRAINT Venue_PK PRIMARY KEY (venueID)
);

CREATE TABLE Client (
clientID numeric(8) NOT NULL,
clientName varchar(20) NOT NULL,
clientEmail varchar(20) NOT NULL,
clientTele numeric(8) NOT NULL,
clientAddress varchar(20) NOT NULL,
clientPostcode numeric(4) NOT NULL,
CONSTRAINT Client_clientID_PK PRIMARY KEY (clientID)
);













CREATE TABLE Booking (
bookingID numeric(8) NOT NULL,
dateOfBooking date NOT NULL,
timeofBooking datetime NOT NULL,
sessionDuration varchar(20) NOT NULL,
amountDue varchar(20) NOT NULL,
amountPaid varchar(20) NOT NULL,
paymentStatus varchar(20) NOT NULL,
completionStatus varchar(20) NOT NULL,
coachID numeric(8) NOT NULL,
clientID numeric(8) NOT NULL,
trainingID numeric(8) NOT NULL,
venueID numeric(8) NOT NULL,
CONSTRAINT Booking_bookingID_PK PRIMARY KEY (bookingID),
CONSTRAINT Booking_coachID_FK FOREIGN KEY (coachID) REFERENCES Coach,
CONSTRAINT Booking_clientID_FK FOREIGN KEY (clientID) REFERENCES Client,
CONSTRAINT Booking_trainingID_FK FOREIGN KEY (trainingID) REFERENCES Training,
CONSTRAINT Booking_venueID_FK FOREIGN KEY (venueID) REFERENCES Venue
);

Insert Statements
INSERT INTO Training VALUES ('1','Mathematics');
INSERT INTO Training VALUES ('2','Physics');
INSERT INTO Training VALUES ('3','Swimming');
INSERT INTO Training VALUES ('4','Tennis');
INSERT INTO Training VALUES ('5','Latin Dancing');
INSERT INTO Training VALUES ('6','Ballet');

INSERT INTO Coach VALUES ('111','Ash','ashthecoach@mail.com','95552775','21 Star Road','2170');
INSERT INTO Coach VALUES ('222','Vatche','vatche@mail.com','95556576','98 Berry Road','2770');
INSERT INTO Coach VALUES ('333','Moe','moe@mail.com','95551221','4 Crickle Road','2210');

INSERT INTO Achievements VALUES ('Leadership Skills','PHD Science','Science Award','1998','111');
INSERT INTO Achievements VALUES ('Team Manager','PHD Sport','Sports Award','1989','222');
INSERT INTO Achievements VALUES ('Great Communication','PHD Drama','Drama Award','2011','333');

INSERT INTO Venue VALUES ('1','Science Centre','98772341','23 Cluckin Road','2345','1');
INSERT INTO Venue VALUES ('2','Sports Centre','98779310','4 Shayle Road','2781','2');
INSERT INTO Venue VALUES ('3','Dance Academy','98774832','69 Beandere Street','2211','3');

INSERT INTO Client VALUES ('1','Baron','nashor@mail.com','92221010','27 Yemoite Avenue','2333');
INSERT INTO Client VALUES ('2','Herald','rift@mail.com','92229889','58 Holla Cresent','2211');
INSERT INTO Client VALUES ('3','Fem','Inist@mail.com','92221234','76 Genders Road','2999');

INSERT INTO Booking VALUES ('1','2015-02-10','2015-02-10 12:30','2 Hours','$80','$80','Paid','Yes','111','1','1','1');
INSERT INTO Booking VALUES ('2','2015-03-10','2015-03-10 14:30','2 Hours','$80','$80','Paid','Yes','222','2','3','2');
INSERT INTO Booking VALUES ('3','2015-04-10','2015-04-10 16:30','2 Hours','$80','$80','Paid','Yes','333','3','5','3');



ii.
-- Question 3
-- ii.
-- a)
select coachName, dateOfBooking
from Coach c inner join Booking b
on c.coachID = b.coachID
where coachName LIKE 'Ash'
 AND dateOfBooking = '2015-02-10';

 --b)
 select coachName, COUNT (sessionDuration) AS 'Total number of Hours'
from Coach c inner join Booking b
on c.coachID = b.coachID
group by coachName;

 --c)
 select coachName, timeOfBooking, venueName, clientName
 from Coach c inner join Booking b
 on c.coachID = b.coachID
 inner join venue v on b.venueID = v.venueID
 inner join Client on b.clientID = Client.clientID
 where dateOfBooking = '2015-02-10'
 group by coachName, timeOfBooking, venueName, clientName;


--d)
select coachName
from Coach c full Outer join Booking b
on c.coachID = b.coachID
inner join Training t on t.trainingID = b.bookingID
WHERE dateofBooking = '2015-02-10 12:30'
AND trainingType = 'Mathematics';




iii.)