COMP3311 Final Exam The University of New South Wales
COMP3311 Database Systems
22T3 Final Exam Instructions 		Database Systems

Before continuing on with reading this, please read and acknowledge the following:

I acknowledge that all of the work I submit for this exam
will be completed by me without assistance from anyone else.
I will not copy the questions to any site outside CSE
except onto my home computer.

Warning: We will be running plagiarism-checking on your submissions.
Plagiarism, especially in exams, will be prosecuted as Student Misconduct.
"Tutoring" sites like chegg.com will be monitored during the exam.
Use of such sites will also be prosecuted as serious Student Misconduct.

This document has seven sections:

Read All Seven Sections Now.

You might want to keep this page open in a browser tab in case you need to refer to it.

In order to take this exam, you will need access to a PostgreSQL server and a Python3 interpreter with the Psycopg2 module.

You can work on the exam either on your laptop/workstation at home or by logging in to your CSE account on one of the CSE servers. Logging in to CSE can be accomplished either via putty or ssh (low network bandwith), or vlab (high network bandwith). However you choose to do it, you should

Setting up your on-screen workspace

We suggest that you open a browser with the following tabs:

The Exam Paper

To read the questions and instructions.

Exam Updates Page

To keep track of any notices related to the exam; refresh this page every so often.

This Instruction Page

Just in case you need to check it again..

COMP3311 Home Page

To look at course contents (e.g. lecture slides, course notes)

The Webcms3 Submission page

If you plan to submit your answers through Webcms3.

Also, open a terminal window for testing your programs. If you use an editor like vim, you can also use this window for editing. Otherwise open a separate window where you edit your answers.

If your terminal window is on Grieg, then you'll need to open another terminal window not on grieg in order to submit your answers via the command-line.

You should also have a mail reader open to receive any major updates on the exam (which will also be posted on the Webcms3 Notices). All updates will be posted on the Exam Updates Page

To ask for clarification on any question, email cs3311@cse.unsw.edu.au. Do NOT post questions on the Webcms3 Forum.

Note on Downloading

If you click on the links to exam-*.zip below, your browser will most likely put them in your downloads folder, and may even unzip them for you. Check the downloads folder and move the relevant directory (work or paper) into your exam directory. If the downloads folder contains the zip file, then move that to your exam directory.

You can generally force browsers to downlod the exam-*.zip file without unzip'ing it by using a left mouse-click on the link. Once you have the zip file in your exam directory, you can run the unzip command manually.

Reading the exam paper

If you want to put the exam paper on your laptop at home ...

If you want to view the exam paper via the CSE web server ...

Setting up your working directory

If you want to work on your laptop/workstation at home ...

If you want to work on the CSE servers ...

After doing either of the above, you will have a new directory called work. In this directory you will find:

q1/, q2/, ... q5/

Directories containing all of the files for completing the programming questions.
Each directory includes a run_tests.sh script and testing data.

q6.txt, q7.txt, ... q9.txt

Templates for answering the written (aka theory) questions.
Edit these files using a text editor of your choosing, and submit the edited files.

Setting up your database

The first thing you need is a running PostgreSQL server. We assume that you can do this in whatever environment you are using. We also assume that you have cleaned up your environment sufficiently that you can create a relatively small database. And we finally assume that you are in your exam working directory and the file racing.dump is present.

The next thing to do is to create a database called racing and load the contents of racing.dump into it. If you're using db2 or your own machine via the command line, this is as easy as: 

$ createdb racing
$ psql racing -f racing.dump
... a bunch of SET, CREATE COPY and ALTER messages ...
$

If you mess things up enough that you need to re-create the database, then do: 

$ dropdb racing

and repeat the above steps.

Warning: Depending on how your server is configured, you may end up with different "collation" settings to those used for generating the expected outputs. This can affect some orderings (e.g. in Q4 and Q5). If the only error message you receive from running the Q4/Q5 tests is "Different order", your solution is correct, and you can submit it.

Testing your prac answers

Each prac question directory has a check script that will run your code (SQL, PLpgSQL, Python) through a sequence of tests. You must be in the appropriate prac question directory in order to run the tests. The tests are in the tests/ subdirectory, and you can look at them via, e.g. 

$ more tests/*.sh

Questions Q1 and Q2 only have a single test. The other questions at least 5 tests. To run the check script, use a command like the following: 

$ sh check
Test 01 PASSED

Here's what the script does when invoked as sh check in the qX directory: 

load the qX.sql file into the racing database
for each test in tests/*.sh:
   run the test and save the output
   compare your output to the expected output
   if it matches:
      print "Test NN PASSED"
   else:
      compare your output to expected, ignoring all spaces
      if it matches:
         print "Test NN Differences in spaces"
      compare your output to expected, sorting both files
      if it matches:
         print "Test NN Different order"
      if none of the above matched:
         print "Test NN Failed"

Note that only PASSED is an indication that you passed that test. All other messages mean that you failed.

Note also that the first thing that check does is to load the qX.sql file in the current directory. You must set up your qX.sql file so that it can be loded and reloaded. Essentially this means that all CREATE statements either need a DROP ... IF EXISTS statement preceding them, or be written as CREATE OR REPLACE

Submitting your answers

You must submit all files containing your answers via give. You can give files either via Webcms3 or via the command-line. If you use the command-line version of give the files will need to be loaded onto a CSE server (details below).

Warning: give does not work on Grieg. To run the give command you will need to login to one of the other CSE servers (e.g. williams), change to your exam directory, and run give from there.

If you are working on your laptop/workstation at home ...

If you are working on the CSE servers ...