COSC2406/2407 Aims:
Database Systems
Week 4/5 Lab Tasks
The aim of this laboratory is for you to familiarize yourself with using Apache Derby and to time queries.
Task 1: Unfinished work
Make sure you have completed the work from the previous lab sheets and have both mongodb and Apache Derby installed in an AWS instance.
Task 2: Connect to Apache Derby on your AWS instance
Our aim by the end of this task is to have inserted the following data into Apache Derby (set up in week 2 lab) using the interactive SQL tool https://db.apache.org/derby/papers/DerbyTut/ij_intro.html which can be invoked with the following command:
java org.apache.derby.tools.ij
Given_name
Family_name
Age
Student_number
Fred
Zhang
20
s1234
Kim
Jones
21
s4567
Ann
Ng
24
s6789
Student
Course
Semester
s1234
COSC2406
1
s1234
COSC1127
1
s1234
COSC2110
2
s4567
COSC2406
1
s6789
COSC2406
1
s6789
COSC2110
2
(1) Use the interactive SQL tool to create two tables and insert the above data. Remember you need to first connect to an existing database or create a new database before creating any tables.
(2) Write a query to find the names of the students enrolled in COSC2110 in semester 2.
If you are struggling with the commands in apache derby, the following tutorial may be helpful: http://db.apache.org/derby//papers/DerbyTut/ij_intro.html
Task 3: Timing queries using Apache Derby
(1) The next task is to test the performance of Apache Derby in terms of the number of queries it can perform per second on our dataset. Our dataset is rather small and so a more realistic test might require a much larger dataset than this.
(2) Briefly read “Analysing statement execution” and Working with RunTimeStatistics” (starting from page 22 and up to about page 27) in Tuning Derby https://db.apache.org/derby/docs/10.13/tuning/tuningderby.pdf
(3) Use runtimestatistics to time your query from Task 2.