7003ICT – DATABASE MANAGEMENT

2814ICT – DATA MANAGEMENT
7003ICT – DATABASE MANAGEMENT
School of Information & Communication Technology
Trimester 1, 2020
Assignment Part 2:
Designing a Database for
Commonwealth Transport Services

Aims






To analyse and comprehend a given ER diagram and Database schema
To implement a database based on the given ER diagram and Database schema
To write required SQL statements to query the database
To 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 use 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



Due date:	Week 11, 15 May Friday, 5:00 PM Submit the individual work file, named ‘a2-<Surname_ID>.zip’, by the above due date. Any student’s ID in the group is fine. Each group member will have a short presentation or viva.
Late submission:	Any submission after the due date will receive a deduction of 10% per day. Standard university policy will apply for all late submissions. See the course website/profile for detail.
Marks:	(a) Group submission: a total 100 marks and it is worth 10% out of the total assessment, plus (b) individual viva/presentation: 5% out of the total assessment.
Extensions:	An extension will only be considered with supporting documentation from a health professional and if the problem/illness occurred within the week prior to the due date. If an extension is granted the extension will then equal the number of days specified on the doctor’s certificate, with a maximum limit of five (5) working days.
Authorship:	This assignment is a Group assignment and it shall be completed by the students in each group only. The final submission must be identifiably the work of the individual group members. Breaches of this requirement will result in an assignment not being accepted for assessment and may result in the offending student or students being required to present before the Disciplinary Committee.

2814ICT & 7003ICT – Assignment Part 2 Page 2 of 4
Designing a Database for Commonwealth Transport Services
Assignment Specification
Commonwealth Transport Services (CTS) now require a partial implementation of the
design made in Assignment Part 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. Task 1: Create a text file named Create_<StudentID>.sql (for example,
Create_s3087654.sql) that will contain SQL statements to:

I.
II.

Create a database named CTSDB<StudentID>.
Create all of 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_<StudentID>.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. Task 3: Create a text file named Query_<StudentId>.sql that will contain all the
queries to display the following

I.	A list of available Vehicles sorted according to seating capacity. Display the Make, Model, Registration number, and the Seating capacity. Find the Locations whose Street names consist of two or more words. The last word is four character long ending with a ‘d’. Display the full street addresses. A list of Officials sorted according to their First name followed by Last
II.
III.

name. Display their full names, Country names, and the Languages they
speak.

IV.	The date on which the most recent maintenance was done. Show the date as well as the Registration number, Make, and Model of the vehicle. List the Drivers who have driven more than 1000 KM in total. Display their full names and total kilometres travelled. Show the list sorted by total kilometre travelled. Find the Vehicles whose repair cost was more than the average repair cost. Display vehicle registration number, make, model and total repair cost. A list of all Officials who have not made any booking request yet. Display the Officials’ full names and the description of their roles. Find the Vehicles that incurred the maximum cost in total for maintenance
V.
VI.
VII.
VIII.

and repair.
2814ICT & 7003ICT – Assignment Part 2 Page 3 of 4
Additional queries for 7003ICT students only:
IX. List the Trips whose actual travel time exceeded the intended travel time by
30 minutes or whose actual start time exceeded the intended start time by 15
minutes. Display the corresponding drivers’ and officials’ full names.
X. Find the Trips whose pick‐up locations’ street name and drop‐off locations’
street name are the same. Display the full names of the Drivers, full name of
the Officials, and the full Street addresses of the pick‐up and drop‐off
locations.
4. Task 4: [for all students] Create a text file named Booking_<StudentId>.sql that
will perform the followings. Insert additional data in the tables appropriately if
needed.
I. An Official named Daniel Ortega from Spain, having OfficialID SPN99710,
wants to make a booking. He speaks Spanish and he will play the role of a
‘Judge’ in the games. He wants to travel from 16 Nielsens Rd, Gold Coast
to 117 Pacific Avenue, Gold Coast on April 9, 2018. His expected start time
is 10:00 am and end time is 10:45 am. The above trip was performed using
the vehicle having VIN number SANFDAE11U1286116. The starting
odometer reading for the vehicle was 26982 KM.
II. The trip started at 10:15 and ended at 11:55 am. At the end of the trip the
odometer reading was 27119 KM. The driver for the trip was John Arnold
having Driving licence number 098674432. John Arnold has a security
clearance level of 3 and he speaks both English and Spanish.
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‐<Surname_ID>.zip’ before uploading.
Your report should include:

 	Use the supplied template for your Assignment Report. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment. A table of contents and page numbers. A report of the results from running the SQL queries (Task 3) by using copy/paste of their output. A bibliography containing a list of all resources used to complete the assignment. If no resources, apart from the course materials, have been used please indicate this.
 


Assessment Criteria
 How clear and well organised your presentation is. On the front page of your report
you should include a list of acknowledgements of all people who have assisted you
with this assignment including fellow students.
2814ICT & 7003ICT – Assignment Part 2 Page 4 of 4

	Adherence to our standards. How clear and well organised your presentation is. You should write all the queries in consistent style and use indent format. Data correctness and quality. Please use appropriate data for your examples (e.g., do not use inappropriate person names) Please refer to the provided marking guide (below) to see the distribution of marks.



Assignment Resources
Find the following information in attached files:
 The logical ER Diagram, and
 Relational Database Schema.
Assessment Criteria and Marking Overview
For 2814ICT students only:

ITEM	Marks
1. Presentation How clear and well‐presented your submission is.	8
2. Creation of database and tables (Task 1) Create the relevant tables: Database correctly named and created, includes all of the PKs and FKs in the database. No evidence that tables have been created using the GUI. They work properly.	20
3. Insertion (Task 2) Successfully inserts data into the tables. NO evidence exporting from GUI. They work properly.	30
4. Query (Task 3) Use of appropriate query statements. They work as intended. Each query should output at least one row of valid data.	32
5. Booking (Task 4) Use of appropriate data manipulation statements to perform the required actions. They work as intended.	10
Total	100

For 7003ICT students only:

ITEM	Marks
1. Presentation How clear and well‐presented your submission is.	5
2. Creation of database and tables (Task 1) Create the relevant tables: Database correctly named and created, includes all of the PKs and FKs in the database. No evidence that tables have been created using the GUI. They work properly.	20
3. Insertion (Task 2) Successfully inserts data into the tables. NO evidence exporting from GUI. They work properly.	30
4. Query (Task 3) Use of appropriate query statements. They work as intended. They work as intended. Each query should output at least one row of valid data.	35
5. Booking (Task 4) Use of appropriate data manipulation statements to perform the required actions. They work as intended.	10
Total	100

Note: Due to current situation, we will decide about viva/presentation in Week 11.
References:
[1] Country Codes, https://en.wikipedia.org/wiki/ISO_3166-1. Last accessed on 21 July 2017.
[2] Language Codes, https://en.wikipedia.org/wiki/List_of_ISO_639-1_codes. Last accessed on 21 July 2017.

We are the Best!

Quality Papers

We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.

Professional Academic Writers

Over the years, Gotmyhomework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.

On-Time delivery

We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.

100% Originality

At Gotmyhomework, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.