COMP3311 Final Exam The University of New South Wales
COMP3311 Database Systems
20T3 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.

[NOT RELEVANT] COMP3311 Home Page

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

[NOT RELEVANT] 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 give on 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

[NOT RELEVANT] The exam paper, working directories and database will be set up when you log in to the exam environment.

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

[NOT RELEVANT]

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

[NOT RELEVANT]

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/, ... q7/

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

q8.txt, q9.txt, ... q12.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

[NOT RELEVANT] The exam paper, working directories and database will be set up when you log in to the exam environment.

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 music.dump is present.

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

$ createdb music
$ psql music -f music.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 music

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, Q2, Q3 only have a single test. The other questions have up to 5 tests. To run the check script, use a command like the following: 

$ sh check q3
Test 01 PASSED

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

load the qX.sql file into the music 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

[NOT RELEVANT]

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 ...