Assignment Specification

Commonwealth Transport Services (CTS) now require a partial implementation of the design made in  Assignment Part 1. To keep consistency between the assignments, database specification containing the ER diagram and the corresponding schema are provided in this document. You should create your database according to this documentation. Make sure that your implementation is consistent with this design, i.e., your table names, field names, and data types are according to the specifications provided   in this document. The implementation phase includes writing SQL statements to create a database and  its tables, populating the tables with appropriate test data, and writing a number of queries to create reports that can be used by the management team. You need to insert at least five records in each of the tables and ensure that each of the query returns at least one record.

Implementation of the Database and Manipulation of the Data

You are required to perform the followings tasks:

一 1. Task 1: Create a text file named Create_< Group515>.txt (for example, Create_Group5.txt) that will contain SQL statements to:

一 I. Create a database named CTSDB<515> (e.g., CTSDBGroup5).

一 II. Create all the tables for the database according to the Database schema given with this document (separately attached).

一

一 2. Task 2: Create a text file named Insert_< Group515>.txt (for example, Insert_Group5.txt) that will contain SQL statements to: I. Insert at leastfive (5) records in each of the tables. The test data      inserted into the table must ensure that each of the queries, specified in Task 3, outputs at least one     record.

一

一 3. Task 3: Create a text file named Query_< Group515>.txt (for example, Query_Group5.txt) that will contain all the queries to display the following

一 I. A list of available Drivers sorted according to their clearance level. Display the driver’s licence number, first name, last name, and the clearance level.

一 II. Find the Locations whose city names consist of one or more words, and the last word is four character long ending with a ‘k’ . For each such location, display its street number, street name, and city. 一 III. A list of Officials and their highest preferred languages. Sort according to their First name followed by Last name. Display their full names and the highest preferred languages (names and preferences). Note that there is only one highest preferred language (1 is highest preference) for each     official.

一 IV. The date on which the most recent Trip(s) was(were) completed. Show the date as well as the vehicle model(s) and type(s) (description) involved in the trip(s).

一 V. List the Vehicles which have been driven more than 1000 KM in total for CTS trips. Display the vehicle registration numbers and total kilometres travelled. Show the list sorted by total kilometre travelled.

一 VI. Find the Passenger Vehicles whose one or more (single) repair costs were more than the            average repair cost of all vehicles. For each vehicle, display the vehicle registration number, make, model, seat capacity, and total repair cost. Note that if a vehicle went for multiple repairs, its total repair cost is the sum of all these (single) repair costs.

一 VII. A list of all Drivers who have not been involved in any trip yet (i.e., haven’t actually driven for any trips yet). Display the drivers’ full names, security clearance levels, and the languages they speak with at their highest proficiency levels. Note that a driver may speak in more than one language at his/her highest level of proficiency, which can be 5 or below.

I      VIII. For each vehicle type (e.g., Sedan), list the number of future bookings of vehicles if number

of future bookings in each type is more than 2. For each vehicle type, display the number of future bookings. Sort the output in descending order of the number of future bookings.

Additional queries :

I      IX. List the completed Trips whose actual travel time were at least 15 minutes less than the

intended travel time. Display the corresponding drivers’ and officials’ full names. Sort the output in ascending order of the trip completion date.

II    X. Find the Trips whose pick ‐up locations’ street name and drop ‐off locations’ street name are the  same, but these streets are located at different cities. Display the full names of the Drivers, full names of the Officials, and the street names (only) and the cities of the pick‐up and drop‐off locations.

Task 4: Create a text file named Booking_< Group515>.txt (for example, Booking_Group5.txt) that     will perform the followings. Insert additional data in the tables appropriately if needed. I. An Official    named Daniel Ortega from Spain, having OfficialID SPN99745, wants to make a booking. He speaks in both Spanish and English, Spanish being his highest preferred language. Spanish is currently spoken by 94% of the total population in Spain. He will play the role of a ‘Judge’ in the games. He wants to travel from 16 Brendan Thorne Place, Gold Coast (Rydges Gold Coast) to 117 Rubicon Crescent, Surfers Paradise (Swimming Pool) on April 9, 2023. His expected start time is 10:00 am and end time is 10:45  am.

II. The above trip is then planned to complete using the Sedan vehicle having VIN number SANFDAE33U1286116. The starting odometer reading for the vehicle is 126,982 KM. A driver with the highest preferred language of the official is not available, so the driver Md Polash with language  proficiency level 4 in English and security clearance level 2 is booked. His licence number is             098675532. The trip actually starts at 10:15 am and ends at 11:55 am on the same date. At the end of the trip the odometer reading is recorded as 127,119 KM.

You are required to adhere to the following output formatting conventions:

• All monetary values should be printed with a dollar symbol ($).

• You must use consistent and legible formatting in laying out your SQL queries. Include (brief) comments for any query or procedure that uses an "unusual" approach.

What to submit?

An electronic copy of your assignment should be submitted online and should include a copy of your report and the four files described in Task 1 to Task 4 above. Zip all the files into a single file named

'a2‐.zip' (for example, a2-Group5.zip) before uploading.

Your report should include:

• Use the supplied template for your Assignment Report.