Aims

• To analyse and comprehend a given ER diagram and Database schema

• To implement a database based on the given ER diagram and Database scheme

• To write required SQL statements query the database

• Write SQL statements to manipulate the data in the database

Learning Objectives

In the process of this assessment task you will:

• plan, schedule and execute project tasks with a view to improving your personal productivity;

• gain awareness of the typical challenges related to the practical implementation of databases;

• learn how to used Data Definition statements to implement a database from a given ER diagram and the corresponding Database schema

• learn how to use Data Manipulation statements to query a database, and insert and update data in the tables

Assignment Specification

Commonwealth Transport Services (CTS) now require a partial implementation of the design made in Assignment 1. In order 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. Create a text file named Create_.sql (for example, Create_3087654.sql) that will contain SQL statements to:

I. Create a database named CTSDB

II. Create all of the tables for the database according to the Database schema given at the end of this document

2. Create a text file named Insert_.sql that will contain SQL statements to:

I. Insert at least five 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. Create a text file named Query_.sql that will contain all the queries to display the following

I. A list of available Vehicles sorted according to seating capacity. Display the Model, Registration number, and the Seating capacity.

II. A list of Official sorted according to their First name followed by Last name. Display their country name as well as the languages they speak.

III. List the drivers who speak French. Display their First name and Last name.

IV. List the drivers having first aid training. Display their First name, Last name, First aid level, and the Date the training was completed

V. List the drivers having special security training. Display their First name, Last name, First aid level, the Date the training was completed, and the Name of the certifying body.

VI. Find the vehicles which went for repair and/or maintenance in March 2015. Show their Registration number, Model, and the date of maintenance group by maintenance type.

VII. Find the total cost incurred for maintenance and repair. Display type of maintenance and the total cost.

Assignment 2

School of Engineering and Information Technology

ITECH1006/5006: Database Management Systems

VIII. Find the vehicles whose repair cost was more than the average repair cost.

IX. Find the locations whose street name starts with ‘B'.

X. List the names of the drivers who has Security clearance level above T and who speak English. Display their First name, Last name, and the Security clearance level.

4. Create a text file named Transaction_.sql that will perform the followings. Make appropriate assumption if needed.

I. An Official named Daniel Ortega from UK, having OfficialID AUS997, wants to make a
booking. He speaks English and he will play role of a ‘Judge' in the games. He wants to travel from 10 Elizabeth St, Brisbane to 117 Kings Road, Gold Coast on April 9, 2015. His expected start time is 10:00 am and end time is 1:15 pm.

II. The above trip was performed using the vehicle having VIN number SANFDAE11U1286116. The starting odometer reading for the vehicle was 26982 KM. The trip started at 10:15 and ended at 1:30 pm. At the end of the trip the odometer reading was 27190 KM. The driver for the trip was John Arnold having Driver licence number 098674432. John Arnold, although has a First Aid training (level D), completed on August 17, 2013, he does not have a special security clearance. John Arnold also speaks English.

Attachmen:- 1283054_1_dbms2.pdf