Archive

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

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.

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

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.

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.

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

The ER Diagram

Cardinalities

Each manager manages exactly one branch, and each branch is managed by exactly one manager.

Each branch holds zero or more accounts, but each account is held be at most one branch.

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.