tianjara.net | Andrew Harvey's Blog

(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

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.

Aggregates

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'

PHP

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

strpos

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.

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

• You should add as many constraints to the database as needed for the data to make sense and is valid. Its probably bad practice to push this off to the application programming level in say PHP. There is probably a lot more to this though.

Queries

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);

• The important point here is as much as possibly you should try to grab as much data as you need from one SQL query with one call to the DB. Rather than just grabbing the whole database and getting the parts you need in the rest of your program (eg. PHP). I can see how this method would be tempting, but I can also see that its a bad approach.

Views

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

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.

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

Mapping N:M Relations

Mapping 1:N Relations

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

• Domains can have constraints (such as not null) specified on them, as well as default values defined on the domain type. You can't do this with user defined types.
• Domains are not strongly typed. Hence you can assign values of one domain type to values of another domain type so long as the underlying types are compatible.

Pattern Matching

Patterns in SQL can be desribed using % and _.

• Percent (%): The % character matches any substring.
• Underscore (_): The _ character matches any character.

eg.

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.

References

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.

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

The ER Diagram

Cardinalities

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.

Participation

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

References

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