COMP3311 26T2 Assignment 2
Scripts on Real Estate DB 		Database Systems
Draft
Last updated: Wednesday 15th July 12:12am
Most recent changes are shown in red ... older changes are shown in brown.

[Assignment Spec]  [Database Design]  [Examples]  [Testing]  [Submitting]

Aims

This assignment aims to give you practice in

The goal is to build some useful data access operations on the real estate database, which contains a wealth of information about the Sydney property scene. The data about local government areas (LGAs) and suburbs is largely accurate; the data about properties is synthetic.

Summary

Submission: Login to Course Web Site > Assignments > Assignment 1 > [Submit] > upload q1.py, q2.py, q3.py, q4.py, ass2.sql
or,
on a CSE server, give cs3311 ass2 q1.py q2.py q3.py q4.py ass2.sql
Deadline: 23:59 Monday 3 August
Marks: 15 marks toward your total mark for this course
Late Penalty: 0.1 marks off the ceiling mark for each hour late, for 5 days
any submission after 5 days scores 0 marks ... UNSW late penalty policy

How to do this assignment:

Details of the above steps are given below. Note that you can put the files wherever you like, but they should not be under your /localstorage directory. Also, you should edit your Python and SQL files on hosts other than vxdb02. The only time that you need to use vxdb02 is to manipulate your database, and run your Python/Psycopg2 scripts yo access the database. Since you can work at home machine, you don't have to use vxdb02 at all while developing your solution, but you should definitely test it there before submitting.

Introduction

In order to work with a database, it is useful to have some background in the domain of data being stored. Here is a very quick tour of Sydney real estate.

Sydney is a sprawling metropolitan area, from Bondi in the east to Penrith in the west and Hornsby in the north to Campbelltown in the south. There are hundreds of suburbs, arranged into dozens of local government areas (LGAs), in three broad regions (inner, outer, greater). The price of properties varies wildly between suburbs; harbourside mansions cost tens of millions of dollars, while modest bungalows way out west may be less than half a million.

The following diagram shows the LGAs in the greater Sydney region. Most of these LGAs are in the database but not all of the suburbs within the LGAs are in the database:


Based on information from Wikipedia and various real estate web sites, we have constructed a database with information about LGAs (their names, when they were founded, whether they have been merged), and suburbs (name, median house and unit prices, adjacent suburbs, LGA located within). We have also synthesised some properties within those suburbs based on median house and unit prices, typical numbers of bedrooms, bathrooms, car spaces in those regions, etc. The database schema is described in more detail both as an ER model and an SQL schema in the schema page.

Doing this Assignment

The following sections describe how to carry out this assignment. Some of the instructions must be followed exactly; others require you to exercise some discretion. The instructions are targetted at people doing the assignment on vxdb02. If you plan to work on this assignment at home on your own computer, you'll need to adapt the instructions to local conditions.

If you're doing your assignment on the CSE machines, some commands must be carried out on vxdb02, while others can (and probably should) be done on a CSE machine other than vxdb02. In the examples below, we'll use vxdb02 to indicate that the comand must be done on vxdb02 and cse$ to indicate that it can be done elsewhere.

Setting Up

The first step in setting up this assignment is to set up a directory to hold your files for this assignment. 

cse$ mkdir /my/dir/for/ass2
cse$ cd /my/dir/for/ass2
cse$ unzip /home/cs3311/web/26T2/assignments/ass2/ass2.zip

This gives you templates for the Python scripts and an SQL file into which you can optionally place any views and functions you use in the scripts. You edit these files, (re)load the SQL/PLpgSQL definitions into the database you created for the assignment, and test the Python scripts based on this.

As supplied, the ass2.sql template file consists entirely of comments; remove the comments from whatever question you're working on. Similarly, the Python scripts do little except run a main function and check command-line arguments; you need to provide the actual functionality.

The next step is to set up your database: 

...... login to vxdb02, source env, run your server as usual ...
...... if you already had such a database
vxdb02$ dropdb ass2
...... create a new empty atabase
vxdb02$ createdb ass2
...... load the database, saving the output in a file called log
vxdb02$ psql ass2 -f /home/cs3311/web/26T2/assignments/ass2/ass2.dump 
...... examine the database contents
vxdb02$ psql ass2

The database loading should take less than 2 seconds on vxdb02, and will produce messages like: 

CREATE DOMAIN
CREATE TYPE
SET
CREATE TABLE
COPY number
ALTER TABLE

The ass2.dump file contains the schema and data in a single file, along with a simple PLpgSQL function (dbpop()), which counts the number of tuples in each table.

If you're running PostgreSQL at home, you'll need to load both ass2.sql and ass2.dump.

Think of some questions you could ask on the database (e.g. like the ones in the lectures) and work out SQL queries to answer them.

One useful query is 

ass2=# select * from dbpop();

This will give you a list of tables and the number of tuples in each. The dbpop() function is written in PLpgSQL, and makes use of the PostgreSQL catalog.

Your Tasks

Answer each of the following questions by completing one of the Python/Psycopg2 scripts. Optionally, you may want to add some SQL or PLpgSQL code into the ass2.sql file and then load it into the database so that your scripts can use it. Note that you can add as many auxuliary views and functions to ass2.sql as you want. However, make sure that everything that's required to make all of your views and functions work is in the ass2.sql file before you submit.

There are examples of the output from each of the scripts in the Examples page.

Q0 (2 marks)

Style mark

Given that you've already taken multiple programming courses, we should be able to assume that you'll express your code with good style conventions. But, just in case ...

The same style guidelines from Assignment 1 apply to any SQL and PLpgSQL code that you might write for this assignment. Go back and review them if you can't remember.

Ugly, inconsistent layout of SQL queries and PLpgSQL functions will be penalised. Since Python almost forces you to lay code out properly, it is difficult to mess up Python code except by using ridiculously long or meaningless names.

Q1 (2 marks)

Complete the Python script q1.pythat gives interesting information about properties and suburbs (well, I thought it was interesting). It first gives a list of the ten most expensive houses in the database, and the suburbs they are located in. It then gives a list of the ten most expensive aprtments in the database, and the suburbs they are located in. If any of these properties has been sold, this should be indicated next to it. The format needs to be precise: suburb names should be in a field of width 25 characters and prices should be left-justified in a field of width 10. Each of these can be a separate item in a Python print() statement.

After this, the ten suburbs with the highest median house prices should be displayed, followed by the ten suburbs with the highest median apartment prices. The formatting should be as above. Of course, since we're talking median prices we don't have to worry about anything being sold.

Finally, list any suburbs where the median apartment prices is higher than the median house price. For each suburb, show both median prices, house then apartment.

You can see the precise format for the output in the Examples page.

Q2 (2 marks)

Complete the Python script q2.py that gives information about local government areas (LGAs).. It has two possible behaviours, depending on the command-line arguments.

  1. If there are no command-line arguments, it prints a sorted list of current (non-merged) LGAs, and give the year of founding of each. For each LGA, which is composed of other LGAs which were merged into it, print the names of each of the merged LGAs, along with the year of their original founding. All lists should be given in alphabetical order.
  2. If there is one command-line argument, assume that it is the full name of an LGA and print its name and year of founding (as in part (a)). Then print a alphbetically sorted list of the names of all suburbs contained within this LGA.

If the argument matches no LGA name, return an error message.

Q3 (3 marks)

Complete the Python script q3.py so that it gives information about all sold properties in a given region (inner, outer, greater). It should list all suburbs in the region which have sold properties in alphabetical order of suburb name. For each suburb, it should display all of the properties sold in that suburb in order of property type**, date sold, and price. For each property, it should show the proprty type, the numbers of bedrooms, bathrooms, car spaces, the price it was sold for and the date when it was sold.

You can see the precise format for the output in the Examples page.

If no region is specified on the command line, the script should print the following usage message: 

Usage: q3.py (inner|greater|outer)

Q4 (6 marks)

Complete the Python script q4.py so that it gives information about all available properties in a given suburb or in neighbouring suburbs that satisfy certain criteria:

Properties should be displayed in increasing order of price.

The script is used as follows: 

$ python3 q4.py PropType (in|near) Suburb [# bed[s]] [# bath[s]] [# car[s]]

PropType is one of house or apartment, which may be abbreviated to simply h or a. Suburb is case-insensitive but must be a complete suburb name. Any of the bedroom, bathroom and cars criteria may be omitted.

Some examples of usage: 

$ python3 q4.py apartment in randwick
# find all apartments for sale in randwick

$ python3 q4.py house near randwick 3 beds
# find all house in Randwick or adjacent suburbs with at least 3 bedrooms

$ python3 q4.py house in "Castle Hill" 2 beds 2 baths
# find all houses in Castle Hill with at least 2 bedrooms and at least 2 bathrooms

$ python3 q4.py apartment in "surry hills" 1 bed 1 bath 0 cars
# find all apartments in Surry Hills with at least 1 bedroom, 1 bathroom and no car space

Notes:

When using near, you should display the suburb given on the command-line and any relevant properties in that suburb first, then display all the adjacent suburbs and their relevant properties in order of suburb name. Before each list of properties, print a line gving the property type and the suburb name. If any suburb, including the original suburb, has no relevant properties simply print a message that there are no suitable properties in that suburb.

Command-line arguments should be processed from left-to-right. When the first error is detected, an appropriate error message should be printed and the script should be terminated. In some cases, the "appropriate error message" is simply the usage message.

Examples of all the various messages mentioned above are in the Examples page.


Submission and Testing

We will test your submission as follows:

Note that there is a time-limit on the execution of your scripts. If any script takes longer than 3 seconds to run your mark for that script will be reduced.

Your submitted code must be complete so that when we do the above, your ass2.sql will load without errors. If your code does not load because your definitions are in the wrong order, there will be a 1 mark penalty. Similarly, your Python scripts should start execution under the Python3 interpreter without error.

Before you submit, it would be useful to test whether the files you submit will at least load on vxdb02, by doing the following: 

vxdb02$ dropdb ass2
vxdb02$ createdb ass2
vxdb02$ psql ass2 -f ass2.dump
vxdb02$ psql ass2 -f ass2.sql

These commands may produce information messages, but they should not produce any errors. You should then, of course, test whether your scripts work on this setup.

Have fun, jas