avatar tianjara.net | blog icon Andrew Harvey's Blog

Entries tagged "comp3311".

PostgreSQL Very Basic Cheatsheet
5th May 2009

(Wrote this a few weeks ago when I knew nothing. Indented into my brain now. Should have published earlier or just trashed the post as it seems too simple now. So instead I'll update it when I find out some new neat tricks.)

List of databases: $ psql -l

To open one of them, $ psql MyDatabase

To see what is in the database (list of relations), mydb=# \d

To examine a specific table, mydb=# \d TableName

Can execute SQL, mydb=# select * from Table;

Can edit SQL in an editor from within PSQL, mydb=# \e

To quit, mydb=# \q

To load a schema from a file $ psql mydb -f /home/foo/bar

Also from the shell, $ pg_dump -O myDB > file (-O means no ownership information is outputed)

On my server configuration (default for ubuntu) you can restart the PostgreSQL service using, $ sudo /etc/init.d/postgresql-8.3 restart

Tags: comp3311, computing.
COMP3311 - Week 7 Notes
5th May 2009

Its pointless repeating what John put in the lecture slides, so this is just my additions that he mentioned but are not in the slides.


Initial condition is defaulted to NULL. So sometimes you will need to define,

  initcond = '';

This is different to NULL, because,

  null || 'abc' --> null

(where || is append) but

  '' || 'abc' --> 'abc'


$x = 2;
myFunc() {
  global $x;

If we omit the global $x, then any references to $x in myFunc will refer to a new local x, not the first x that is set to 2. To avoid this and force any references to x inside myFunc to refer to the first x that is equal to 2, we need this global $x line.


1. $i = strpos('abc', 'a') --> 0
2. $i = strpos('abc', 'b') --> 1
3. $i = strpos('abc', 'z') --> false

if($i) will only be true in case 2 (false in case 1 and 3). So if we want to test if the second string was at all in the first string we must use,

if($i !== false)

This one will be true in case 1 and 2, but not 3.

Tags: comp3311, computing.
COMP3311 - Wk3-4 General Notes
10th April 2009

Constraint Checking

If you use the keyword CONSTRAINT, you also need to provide a name for the constraint. However, it is permitted to omit both the keyword CONSTRAINT and the constraint name. In other words constraint definitions can be either

CONSTRAINT constraint_name CHECK ( expression )

or just

CHECK ( expression )

With respect to the expression,

x = NULL --this is always false
x IS NULL --returns true if x is null, and false otherwise


Standard paradigm for accessing DB from app.code:

-- establish connection to DBMS
db = dbConnect("dbname=X user=Y passwd=Z");
query = "select a,b from R,S where ... ";
-- invoke query and get handle to result set
results = dbQuery(db, query);
-- for each tuple in result set
while (tuple = dbNext(results)) {
-- process next tuple
process(val(tuple,'a'), val(tuple,'b'));
} dbClose(results);


create view name as select ...

This makes a "virtual table" called name that you can use in your subsequent SQL queries, but the table will dissapear when the connection is closed (or at least this is when I think it dissapears).


pg_dump dbname > file

This will dump the whole database (in SQL format) to a file. Use -o to ommit the ownership data.

Enforcing Case

SQL is case insensitive, to enforce case use double quotes. eg. select name as "Foo" from bar;

Foreign Keys

Just because an attribute in a foreign key does not automatically imply that it is not null. It may be NULL. If  you want the attribute to never be NULL you must add NOT NULL.

Tags: comp3311.
COMP3311 Wk02
26th March 2009

Various things about mapping ER Designs to Relational Schemas.

Mapping Strong Entities


The relational model supports only atomic attributes. To map composite attributes you can try,

  1. Concatenate the attributes eg. Struct name {"John", "Smith"} --> "John Smith"
  2. Map atomic components of the composite attribute to a set of atomic components. eg. mapcompatt1 mapcompatt_table
  3. ??

Mapping N:M Relations


Mapping 1:N Relations

map1nrel2Mapping 1:1 Relations


Notes from the Text Book (The Lecture Notes are a Little Different)

Domain Types & User Types

In the sample code for the first assignment to define "custom types" create domain is used. eg.

create domain PersonGender as char(1) check (value in ('M','F'));

However the text also shows create type. eg.

create type Dollars as numeric(12,2) final

It goes on to explain the difference.

Pattern Matching

Patterns in SQL can be desribed using % and _.


select foo from bar where lar like '_to%'

This will match to any of these strings, "Lto" "Ato" "ltoo" "rtoto" ... (any character at the start, then the "to" string, then any (even null) trailing string)

You can define the escape character for a like comparison as follows,

like 'he\%%'  escape '\'' --matches all strings begining with 'he%'

You can also use not like.

SQL:1999 allows for similar too which is similar to Unix regular expressions.

Drop vs. Delete

drop table r will remove all the tuples from r, and removes the schema of r, whereas

delete from r will just remove all the tuples from r, but leaving the schema so you can still add values to the table.


Shepherd, John. COMP3311 09s1 Lecture Slides. http://www.cse.unsw.edu.au/~cs3311/09s1/lectures/. (Diagrams have also been sourced from here).

Silberschatz. Database System Concepts. 5th Ed.

Tags: comp3311, computing.
COMP3311 - Wk01
23rd March 2009

Just a couple random notes, to reiterate some things I need to become acquainted with. Definitely not comprehensive.

The ER Diagram



  1. Each manager manages exactly one branch, and each branch is managed by exactly one manager.
  2. Each branch holds zero or more accounts, but each account is held be at most one branch.
  3. Each customer owns zero or more accounts and each account is owned by zero or more customers.


participNot all customers must take out a loan (or it is not the case that every customer takes out a loan), but every loan is taken out by at least one customer. i.e. Every loan is associated with at least one person, but every person is not necessarily associated with at least one loan.

Attributes Linked to Relationships


In (a) you know how much time a particular person spends on a project. In (b) you only know how much time has been spend on a particular project. You don't know the distribution of that time among the researches that have worked on it.


Shepherd, John. COMP3311 09s1 Lecture Slides. http://www.cse.unsw.edu.au/~cs3311/09s1/lectures/. (Diagrams have also been sourced from here).

Tags: comp3311, computing.

RSS Feed