Description

Databases ICT 285 Assignment 2

this solutions contains codes, SQL queries for each task, modified ERD from Assignment 1 as well as data model

Question 5: SQL statements for all Transactions
Transaction 01: Inserting data for the route TN007 and the bus CH-20
Transaction 02: Inserting data for staff assignments for route TN007 on 26th of February 2024
Transaction 03: Inserting data for the booking made by customer Joe King on TN007 on 26th of February 2024
Transaction 04: Updating the departure and arrival times for route TN007 on 26th of February 2024
Transaction 05: Inserting maintenance record for bus CH-20 on 12th Dec 2023
View
All tours’ bookings made by Joe King including, for those tours that have been taken, the duration of the tour.
Total hours covered EVER, for staff of the TN007 on 26th of February 2024
Total hours spent on tours EVER, by the driver of the TN007 on 26th of February 2024, broken down by that person’s role
Maintenance history (service log) for bus CH-20 including the date and location of the maintenance episode, whether or not the maintenance
session was scheduled or not, and the full name and phone number of the supervising staff