Using HSQLDB

These notes are for dealing with the HSQL database used in a legacy web application.

HSQL database commands

Note: These commands are required to by run where the hsqldb jar file is stored

• Starting the server:

  
  java -cp /var/lib/hsqldb/lib/hsqldb.jar org.hsqldb.Server -database.0 file:mydb -dbname.0 phonebook

  
  
• Start the GUI application for the HSQL database:
  
  

  java -cp /var/lib/hsqldb/lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

SQLTOOL

An Example ~/sqltool.rc file:

urlid localhost-sa
url jdbc:hsqldb:hsql://localhost/
username SA
password

To use SQL Tool:

1. Setup a ~/sqltool.rc to store the connection settings for the database (and run the server).
2. Once you have started the database (using the commands in the previous section), run the SQLTool command line utility:
   
   

   java -cp /var/lib/hsqldb/lib/hsqldb.jar:sqltool.jar org.hsqldb.cmdline.SqlTool

   
   
3. Connect to the database as indication by the urlid in your sqltool.rc file:
   
   

   \j localhost-sa

   
   
4. You can stop sqltool at any using
   
   

   \xq (table OR SELECT QUERY)

DATABASE INFO

• Grab list of all tables in the database:
  
  

  SELECT TABLE_SCHEM,TABLE_NAME FROM INFORMATION_SCHEMA.SYSTEM_TABLES WHERE TABLE_TYPE = 'TABLE';

  
  
• Grabbing a single employee record:
  
  

  SELECT LIMIT 0 1 - FROM EMPLOYEES;

  
  
• Outputting user info as a CSV file:
  
  

  SELECT A.USERNAME, A.PASSWORD, A.FIRSTNAME, A.LASTNAME, A.MIDDLENAME as OTHERNAME, B.EMAIL INTO TEXT "users_file" FROM USERS AS A INNER JOIN USER_DETAILS AS B ON A.USERNAME = B.USERNAME;

  
  
• Removing the temporary users table used to create the CSV file:
  
  

  DROP TABLE "users";

  
  
• Date based search query where date is a string (DD-MM-YYYY):
  
  

  SELECT A.USERNAME, B.FIRSTNAME FROM USERS AS A INNER JOIN EMPLOYEES AS B ON A.USERNAME = B.USERNAME WHERE SUBSTRING(A.EXPIRY,7,4) > 2016;

  
  
• Searching for string partials:
  
  

  SELECT TIMES, DATES, USERS FROM LOGS WHERE USERS LIKE 'admin%' AND DATES LIKE '%/03/2015';

  
  
• Grabbing the exact number of matches that match parameters:
  
  

  code>SELECT BUILDING, INCTYPE, COUNT(*) AS NUM FROM REPORTS WHERE BUILDING LIKE 'PLACE %' AND DATE LIKE '%/2015' GROUP BY BUILDING, INCTYPE;

Notes from a Google Entreprenuer Day

These are notes I had lying around from a Google Entrepreneur Day.

• Small teams, Flat structure. Adaptibility. Agile, flexible, high sharing of data.
• Meritocracy and Transparency. Helps make passionate people.
• Execution. Experiment often, fail quickly. Testing and Auditing. Reward risk, do not punish failure.
• Collect and aggregate usage data. Data should not lie, and it will keep you honest.
• 3 A's of entrepreneurship:
  
  1. Audit Data and yourself.
  2. Admit when things are not working.
  3. Adapt in order to survive.

 Understand your user

• Australia has the highest internet penetration. Best place to actually test. South Korea, UK and US are other penetration markets.
• Trends: Mobility, Video, Social, Cloud.
• Consumerism of IT. Take Consumer technology (phones, tablets, etc) into the business.

BigQuery

• Big Data is "When the cost of throwing away data becomes higher than technology used to store it"
• Batch-based solutions were not fast enough (for data-driven decision making)
• Accessed via RESTful API. Make it into a service.
• Suitable for aggregation and predictions.

Go

• Small language
• Simple type system
• Fast runtime properties
• Native concurrency support
• Fast development workflow
• Comprehensive standard library
• Standard library includes JSON, XML, net, SQL database and command line flags

Lean platforms for lean start-ups

• You are not the only one working on it. You do not always have a great idea.
• Steps:
1. Start small, start fast
2. Learn like a scientist. Rigorous, precise, learn from failure.
3. Minimise friction to ship
• Advatange is that a start-up can focus on markets no-one cares about.
• Move fast and break things

PostgreSQL Full Text Search

PostgreSQL has a decent Full Text Search capability. And it is quite simple to implement too; just a few SQL statements and you are done.

You want to implement your search function using Full Text Search because this method is comparatively faster than pattern matching with '%'. The 'tsvector' and 'gin' processes indexes your values and makes it easier for your DBMS to retrieve them.

To the SQL code (assuming you have a system schema with a clients table):

 1 - Add a search field to the table:

ALTER TABLE system.clients ADD COLUMN search TSVECTOR;

 2 - Convert the existing 'name' and 'email' fields into a TSVECTOR stored in the new search field:

UPDATE system.clients SET search = to_tsvector(name || email);

 3 - Index the search field:

CREATE INDEX search_client_index ON system.clients USING gin(search);

 4 - Create a trigger so that whenever the fields are updated then the search field is updated too:

CREATE TRIGGER update_ticket_search BEFORE INSERT OR UPDATE ON system.clients FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(search,"pg_catalog.english",title,email);

 5 - To search for entries you run the following code:

SELECT * FROM system.tickets WHERE search @@ to_tsquery('scrubber');

Resources:

•  Linux Gazette article on full text search features in PostgreSQL

PERL script to connect and test PostgreSQL database

I decided to follow up my last getopts script about PERL scripting (which greatly improved upon my original PERL efforts) with a script that will attempt to connect to a PostgreSQL database and test if some tables exist or not.

I won't go into too much details and just paste the code here...

#!/usr/bin/perl -w
#
# This is a perl script that uses warnings (hence the -w flag)
#
# This perl scripts aims to test the database connections
# and ensure the schema conforms to the standard.

# Use strict perl
use strict;

# Use the Getopt library
use Getopt::Std;

# Creates a hash variable where we can store our command line options
my %options=();

# Grab the list of command line options (some with optional arguments,
#   denoted by a ':')
#   -u {username}   : Sets the username
#   -p {password}   : Sets the password
#   -H {hostname}   : Sets the hostname
#   -D {database}   : Sets the database
#   -h              : Displays the help
#   -d              : Debugging
getopts("hdu:p:H:D:", \%options);

# Sets some variables based on command line options, otherwise use defaults
my $username = (defined $options{u}) ? $options{u} : 'test';
my $password = (defined $options{p}) ? $options{p} : 'test';
my $hostname = (defined $options{H}) ? $options{H} : 'localhost';
my $database = (defined $options{D}) ? $options{D} : 'testdb';

# Set some environment variables
$ENV{PGPASSWORD}=$password;

# Output the display menu if asked for
if (defined $options{h}) {
    print "Test the database connections and ensure the schema conforms to the standard\n\n";
    print "usage: test.pl [-u <username] [-p <password>] [-H <hostname>]\n\n";
}

# Display the passed variables for debugging purposes
print "USERNAME: " .$username . "\nPASSWORD: " . $password . "\nHOSTNAME: " . $hostname . "\nDATABASE: " . $database . "\n\n" if defined $options{d};

# The auth tables
my @tables = qw(system.session system.logs system.users);

# Loop through all our declared tables
foreach (@tables)
{
  psql_command($_);
}

# Clear some environmental variables
delete $ENV{PGPASSWORD};

# Exit from the system
exit 0;

# This subroutine runs a psql command
sub psql_command
{
  # Grab some parameters
  my ($table) = @_;
  if(!( defined($table)))
  {
    die "psql_command() was passed some bad arguments!\n";
  }

  my $output = `/usr/bin/psql -U $username -h $hostname -w -d $database -c 'SELECT * FROM $table' 2>&1`;
  if ( $? == -1 )
  {
    print "!!ERROR!! : $!\n";
  } else {
    if (($? >> 8) > 0)
    {
      print "!!ERROR!! : $table does not exist\n";
    } else {
      print "$table exists\n";
    }
  }

  return 0;
}

References:

• Perl Primer by Pat Gunn
• The Perl Tutorial by tiztag.com
•  The getopts howto by Alex Batko
• http://www.perldoc.com is a great documentation source
• O'Reilly's Programming Perl is a good book on the subject

PostgreSQL: Basic Database User Schema

The following is a basic schema for creating a user authentication system in PostgreSQL. Adjust to your particular use case:

CREATE FUNCTION update_modified() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.modified = now();
RETURN NEW;
END;
$$;

CREATE TABLE users (
user_id integer NOT NULL,
username character varying(64) NOT NULL,
password character varying(255) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL,
activated smallint DEFAULT 0 NOT NULL,
banned smallint DEFAULT 1 NOT NULL
);

CREATE SEQUENCE users_user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE users_user_id_seq OWNED BY users.user_id;

SELECT pg_catalog.setval('users_user_id_seq', 1, false);

CREATE TABLE person (
person_id integer NOT NULL,
firstname character varying(64) NOT NULL,
lastname character varying(64) NOT NULL,
othername character varying(64) NOT NULL,
date_of_birth date NOT NULL,
gender character(1) NOT NULL,
created timestamp with time zone DEFAULT now() NOT NULL,
modified timestamp with time zone DEFAULT now() NOT NULL
);

CREATE TABLE person_address (
address_id integer NOT NULL,
tag character varying(24) NOT NULL,
address character varying(128) NOT NULL,
locality character varying(64) NOT NULL,
region character varying(32) NOT NULL,
country character varying(32) NOT NULL,
code character varying(8),
person_id integer NOT NULL
);

CREATE SEQUENCE person_address_address_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_address_address_id_seq OWNED BY person_address.address_id;

SELECT pg_catalog.setval('person_address_address_id_seq', 1, false);

CREATE TABLE person_email (
email_id integer NOT NULL,
tag character varying(24) NOT NULL,
email character varying(128),
person_id integer NOT NULL,
CONSTRAINT proper_email CHECK (((email)::text ~* '^[A-Za-z0-9._%-]+@(?:[A-Za-z0-9.-]+\.)+[A-Za-z]+::text'))
);

CREATE SEQUENCE person_email_email_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_email_email_id_seq OWNED BY person_email.email_id;

SELECT pg_catalog.setval('person_email_email_id_seq', 1, false);

CREATE SEQUENCE person_person_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_person_id_seq OWNED BY person.person_id;

SELECT pg_catalog.setval('person_person_id_seq', 1, false);

CREATE TABLE person_phone (
phone_id integer NOT NULL,
tag character varying(24) NOT NULL,
phone character varying(32),
person_id integer NOT NULL
);

CREATE SEQUENCE person_phone_phone_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER SEQUENCE person_phone_phone_id_seq OWNED BY person_phone.phone_id;

SELECT pg_catalog.setval('person_phone_phone_id_seq', 1, false);

ALTER TABLE ONLY users ALTER COLUMN user_id SET DEFAULT nextval('users_user_id_seq'::regclass);

ALTER TABLE ONLY person ALTER COLUMN person_id SET DEFAULT nextval('person_person_id_seq'::regclass);

ALTER TABLE ONLY person_address ALTER COLUMN address_id SET DEFAULT nextval('person_address_address_id_seq'::regclass);

ALTER TABLE ONLY person_email ALTER COLUMN email_id SET DEFAULT nextval('person_email_email_id_seq'::regclass);

ALTER TABLE ONLY person_phone ALTER COLUMN phone_id SET DEFAULT nextval('person_phone_phone_id_seq'::regclass);

ALTER TABLE ONLY users
ADD CONSTRAINT user_pkey PRIMARY KEY (user_id);

ALTER TABLE ONLY users
ADD CONSTRAINT username_key UNIQUE (username);

ALTER TABLE ONLY person_email
ADD CONSTRAINT email_pkey PRIMARY KEY (email_id);

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_pkey PRIMARY KEY (address_id);

ALTER TABLE ONLY person
ADD CONSTRAINT person_pkey PRIMARY KEY (person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT phone_pkey PRIMARY KEY (phone_id);

CREATE TRIGGER update_users_modified BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_modified();

CREATE TRIGGER update_person_modified BEFORE UPDATE ON person FOR EACH ROW EXECUTE PROCEDURE update_modified();

ALTER TABLE ONLY person_address
ADD CONSTRAINT person_address_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_email
ADD CONSTRAINT person_email_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

ALTER TABLE ONLY person_phone
ADD CONSTRAINT person_phone_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(person_id);

You can check out the other parts of my PostgreSQL series including an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases.

References:

• Library of free database models from databaseanswers.org
• Moodle's user schema
• Schema for an invoice system by stackoverflow.com

CodeIgniter web app authentication with PostgreSQL and TankAuth

Some of you may note that I had earlier created my own simple authentication system in CodeIgniter. However there comes a time when you just need something fully featured by you don't have the time create it yourself; step into TankAuth (it is also a hosted project on GitHub).

This post will focus on how to get TankAuth working with PostgreSQL .

Some problems....

TankAuth was designed around a MySQL database, and hence some SQL commands won't work. This means that you will get some odd behavior; unfortunately fixing all of these issues is outside the scope of this post.

Some Prerequisite knowledge...

I am going to dive right into the deep end here so if you don't know anything about PostgreSQL, PHP, CodeIgniter or Relational Database theory I strongly suggest you go and do some quick research.

I have a series on PostgreSQL that include an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases. As for PHP, you can check out my blog posts such as my AJAX with JQuery and PHP tricks. You can also check out this list of useful CodeIgniter Tutorials.

Setting up PostgreSQL

We are going to assume that you have already installed PostgreSQL and have correctly download and setup CodeIgniter (and it is correctly serving the default web page). Make sure you have installed the PHP PostgreSQL plug-ins (php5-pgsql and php5-odbc).

Use this script to check if you have correctly set-up PostgreSQL and PHP.

Open up a terminal and create a user and database for CodeIgniter and TankAuth like we did in this earlier post:

createuser -h localhost -U postgres tankauth -W -S -D -R -P

createdb -h localhost -U postgres -O tankauth -W  tankauthdb

NOTE: These commands assume that you have password protected the postgres user; if you have a default install you can ditch the -W trigger.


Once we have created a user and a database, connect to the database server via the terminal command:

psql -h localhost -d tankauthdb -U tankauth -W

Now create a schema for our project to use. In the psql terminal run the following:

tankauthdb=> CREATE SCHEMA codeigniter;

PostgreSQL is now prepped and ready for CodeIgniter...

Setting up CodeIgniter

To get CodeIgniter working with PostgreSQL you will simply need to edit two configuration files. First, edit ./application/config/database.php with the following:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'tankauth';
$db['default']['password'] = 'tankauth';
$db['default']['database'] = 'tankauthdb';
$db['default']['dbdriver'] = 'postgre';
$db['default']['dbprefix'] = 'codeigniter.';

Now we will autoload the database (just because it is easier than calling it for every controller); edit ./application/config/autoload.php with:

$autoload['libraries'] = array('view', 'database');

And that's it! CodeIgniter will now connect to PostgreSQL.

Setting up TankAuth

1. Download and extract TankAuth.
2. Copy the application folder content to your CI application folder.
3. Copy the captcha folder to your CI folder. Make sure this folder is writable by web server.
4. Open the application/config/config.php file in your CI installation and change $config['sess_use_database'] value to TRUE.
5. Create an encryption key in application/config/config.php and editing $config['encryption_key']
6. Finally turn off captcha's in by editing application/config/tank_auth.php with $config['captcha_registration'] = FALSE;

Now we will need to create the databases that TankAuth requires. First we will create a table for storing CodeIgniter sessions:

CREATE TABLE codeigniter.ci_sessions
(
  session_id character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL DEFAULT '0',
  ip_address character varying(16) COLLATE pg_catalog."en_AU.utf8" NOT NULL DEFAULT '0',
  user_agent character varying(150) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_activity integer NOT NULL DEFAULT 0,
  user_data text COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  CONSTRAINT ci_sessions_pkey PRIMARY KEY (session_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.ci_sessions
  OWNER TO tankauth; 

This will create a table for recording login attempts AND a function and trigger for automatically updating the timestamp:

CREATE TABLE codeigniter.login_attempts
(
  id serial NOT NULL,
  ip_address character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  login character varying(50) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  "time" timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT login_attempts_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.login_attempts
  OWNER TO tankauth;

CREATE OR REPLACE FUNCTION codeigniter.update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.time = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_login_attempts_modtime BEFORE UPDATE ON codeigniter.login_attempts FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_column();

This will create a table for tracking users who choose to use the autologin feature (and the requisite triggers):

CREATE TABLE codeigniter.user_autologin
(
  key_id character(32) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  user_id integer NOT NULL DEFAULT 0,
  user_agent character varying(150) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_ip character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_login timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT user_autologin_pkey PRIMARY KEY (key_id, user_id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.user_autologin
  OWNER TO tankauth;

CREATE OR REPLACE FUNCTION codeigniter.update_modified_login()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_login = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_autologin_modtime BEFORE UPDATE ON codeigniter.user_autologin FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_login();

This will create our user profiles:

CREATE TABLE codeigniter.user_profiles
(
  id serial NOT NULL,
  user_id integer NOT NULL,
  country character varying(20) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  website character varying(255) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  CONSTRAINT user_profiles_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.user_profiles
  OWNER TO tankauth;

And finally this is our users login table:

CREATE TABLE codeigniter.users
(
  id serial NOT NULL,
  username character varying(50) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  password character varying(255) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  email character varying(100) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  activated smallint NOT NULL DEFAULT 1,
  banned smallint NOT NULL DEFAULT 1,
  ban_reason character varying(255) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_password_key character varying(50) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_password_requested timestamp without time zone DEFAULT NULL,
  new_email character varying(100) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  new_email_key character varying(50) COLLATE pg_catalog."en_AU.utf8" DEFAULT NULL,
  last_ip character varying(40) COLLATE pg_catalog."en_AU.utf8" NOT NULL,
  last_login timestamp without time zone NOT NULL DEFAULT now(),
  created timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT user_pkey PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);
ALTER TABLE codeigniter.users
  OWNER TO tankauth;

CREATE TRIGGER update_users_login BEFORE UPDATE ON codeigniter.users FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified_login();

CREATE OR REPLACE FUNCTION codeigniter.update_modified()
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_modtime BEFORE UPDATE ON codeigniter.users FOR EACH ROW EXECUTE PROCEDURE codeigniter.update_modified();

And that is TankAuth set up!!!

One more thing...

For those keeping track, they may have noticed my warning about TankAuth using MySQL specific functions. To get the basic stuff working, you will need to do edit application/models/tank_auth/login_attempts.php and replace:

$this->db->or_where('UNIX_TIMESTAMP(time) <', time() - $expire_period);

with:

$this->db->or_where('extract(epoch FROM time) <', time() - $expire_period);

And you are good to go!

PostgreSQL: Creating Users and Databases

This is a very, very quick blog post regarding how to create a user and database in PostgreSQL through the command line prompt.

To do this you just need to run two commands:

createuser -h localhost -U postgres testuser -W -S -D -R -P

createdb -h localhost -U postgres -O testuser -W  test_db

These commands assume you have password protected the local postgres user (if not, you can drop the -W trigger).

A quick overview of the createuser command:

• -U tells the command which user to perform the command with (NOT the user to create)
• -S tells us that the new user is NOT have superuser privileges (as opposed to the lower case -s, which do have superuser privileges)
• -D tells us that the new user is NOT have create database privileges (as opposed to the lower case -d, which do have superuser privileges)
• -R tells us that the new user is NOT have create roles privileges (as opposed to the lower case -r, which do have superuser privileges)
• -P prompts the command to immediately prompt for a password for the new user.
• -W will prompt the password for the user that will run the command (i.e. the username passed with the -U trigger)
• -h tells the commands which host to connect to.

The createdb command only differs with the -O trigger, which tells the command which user will own the new database.

And that's it!

Other parts of PostgreSQL series include an installation guide for Ubuntu, useful functions and operators, a guide to improving Postgre performance, how to check for installed procedural languages, and an overview of some basic concepts and some more advanced ones.

You can also check out my notes on designing relational databases.

PostgreSQL: Improving performance

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, useful functions and operators, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases.

• Although PostgreSQL does a good job of maintenance of tuning. However, it is useful to analyses the systems real-time query use of indexes. There are plenty of tools available to profile your server, but the usefulness depends on the requirements of your particular system.
  
  NOTE: Always performing tuning on real data. Test data will just tell you what will be optimal for the test data only.


• EXPLAIN is a tool that shows the execution plan of a statement and the associated costs (measured in disk page fetches). You can add the options ANALYZE (actually executes the statement; no longer uses estimates), and VERBOSE (display additional information).
  
  A good explanation on how to use EXPLAIN is provided by the documentation.
  

  EXPLAIN [ANALYZE] [VERBOSE] statement



• ANALYZE collects statistics about the contents of tables and stores the results in a system catalog. These statistics are then used by the planner in selecting an appropriate query plan.
  
  It is useless to conduct performance tuning without first running the ANALYZE command. Otherwise, any results gathered will be generating using default values that are sure to be wrong.
  

  ANALYZE [VERBOSE] [table]



• There are a number of useful views to display statistical information about the server:
  

  View	Description
  pg_stat_activity	One row per server process
  pg_stat_database	One row per database
  pg_stat_database_conflicts	Show standby server database conflicts due to dropped tablespaces, lock timeouts, old snapshots, pinned buffers and deadlocks.
  pg_stat_all_tables	Show all tables in the current database
  pg_stat_all_indexes	Show all indexes in the current database
  pg_statio_all_tables	Show all the tables' disk io statistics
  pg_statio_all_indexes	Show all the indexes' disk io statistics



• The default configuration of PostgreSQL is designed to work on a wide variety of hardware and software installations and is most definitely not optimal for your system. You should edit the postgresql.conf file to increase the values of shared_buffers, effective_cache_size, sort_mem, max_fsm_pages and max_fsm_relations.


• The physical memory and disks will most probably be the slowest thing in your system. You should always tweak your hardware to generate the optimal performance in your system.

References:

• Examining indexes to improve performance from the PostgreSQL documentation
• Performance tips chapter from the PostgreSQL documentation
• 5-minute introduction to PostgreSQL performance tuning by Gregory Smith
• PostgreSQL performance tuning by Christopher Browne
• Performance tuning PostgreSQL by Frank Wiles
• Performance checklist by Selena Deckelman

PostgreSQL: Check if Procedural Language is installed

To check if a procedural language is installed on PostgreSQL, just run the following:

SELECT COUNT(*) FROM pg_language WHERE lanname = 'plpgsql';

Values for lanname can be one of the following:

• plsql
• pltcl
• plperl
• plpython

References:

• PostgreSQL documentation on procedural languages
• This SQL code snippet from polak.ro

PostgreSQL: Functions & Operators

This a continuation of my PostgreSQL series (an installation guide for Ubuntu, an overview of some basic concepts and some more advanced ones). Here I will briefly outline some functions available on the PostgreSQL database.

You can also check out my notes on designing relational databases

• The usual logical operators of AND, OR, and NOT are available. However, SQL uses a three-valued logic system of TRUE, FALSE and NULL (which represents "unknown"). You should therefore design your system to handle a NULL result.


• The usual comparison operators of >, <, >=, <=, = and <> (or !=, but the parser resolves this to <> anyway) are available.


• The BETWEEN construct allows you to test a value between a range (NOT BETWEEN tests if a value is not within a range)
  

  a BETWEEN x AND y

  is equivalent to
  

  a >= x AND a <= y



• The following extra comparison operators will always resolve to a TRUE or FALSE value:
  

  a IS NULL
  a IS NOT NULL
  a IS TRUE
  a IS NOT TRUE
  a IS FALSE
  a IS NOT FALSE
  a IS UNKNOWN
  a IS NOT UNKNOWN



• Pattern matching can be achieved through LIKE, SIMILAR TO, or POSIX style regular expressions.
  
  LIKE is the simplest pattern matching technique. The _ represents any single character, while a % represents a sequence of characters.
  
  SIMILAR TO is a hybrid approach between LIKE and regular expressions. It still uses the _ and %, but additionally uses symbols borrowed from regular expressions like *, ?, {} and [] (see the documentation for an in-depth explanation).
  
  Regular expression support is also provided, but is an extensive topic that would be too lengthy to include here. A great reference can be found at regular-expressions.info


• PostgreSQL provides a wide array of date & time functions. These include current_date, current_time, and current_timestamp.
  
  Another useful function is age(timestamp), which will subtract the timestamp from the current_date and output a result in years, months and days.


• A CASE expression exists for conditional statements. expression is the SQL expression, value is the matching result of the expression, and result is the value to return
  

  CASE expression
  

  WHEN value THEN result

  [WHEN....]

  [ELSE result]

  END



• The general purpose window functions will only work if invoked via the window function syntax (that is, the OVER clause is required). In addition to in-built aggregate functions, the following general purpose functions will work:
• row_number(): number of the row within the partition
• first_value(field): returns the value of the field from the first row of the window frame
• last_value(field): returns the value of the field from the last row of the window frame
• nth_value(field): returns the value of the field from the nth row of the window frame


• There are many functions to get the current system state, but here are a few of the useful ones:
• version(): Version info
• current_user: Username of the current execution context
• current_database(): Name of the database in use
• current_schema[()]: Name of the current schema
• inet_client_addr(): The address of the remote connection
• inet_client_port(): The port of the remote connection
• pg_conf_load_time(): Configuration loading time


• There also exists many administration functions that allow to configure PostgreSQL at runtime. You can also get other running information from the system such as the physical size of databases, initiate back-ups and recoveries, and signal actions to the server.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• Regular Expressions reference

PostgreSQL: Advanced Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu and an overview of some basic concepts). Here I will briefly outline some more concepts about the database.

You can also check out my notes on designing relational databases

• The purpose of an index is to allow the RDBMS to locate the row efficiently. However maintaining an index requires some overhead, so it is not worthwhile to index all fields in a table. In addition, since the index is synchronized when the table is updated it is recommended to remove indexes that are seldom or never used.


• Although a multi-column index can be specified (up to 32 columns), it is generally not recommended to do so unless in certain circumstances. For instance, a multi-column index is useful if you are constantly making queries that references the two or more fields. This is because multi-column indexes are larger and slower than querying a single column index.


• Unique indexes with NULL values are not considered equal in PostgreSQL (you will need to specify the NOT NULL clause to restrict NULL values for this field)


• Partial indexes allow you to index only the field values that you search frequently, and not the whole table. It offers some benefits as your index will not be as big as if you indexed the whole table, but lost out if you every need to query something outside of the index parameters.


•  Data consistency is maintained by following the MVCC approach (MultiVersion Concurrency Control). Each transaction will see a snapshot of the data as it was, regardless of the current state of the underlying data. This isolates the transaction, preventing it from seeing inconsistent data that is produced from concurrent transactions.
  
  MVCC's major advantage is that locks for read transactions do not conflict with locks for write transactions, and vice versa.


• You can explicitly lock tables or rows when the MVCC approach does not produce desirable behavior. However by explicitly locking resources you increase the risk of producing deadlocks in the system.


• Messages generated by the server are assigned a five-character error code that follows the SQL standard for SQLSTATE codes. This standard states that the first two characters denote the class of the message, while the last three characters denote the specific condition.


• The followin class codes have been defined:
  
  

  CODE	DESCRIPTOR
  00	Successful completion
  01	Warning
  02	No Data (also serves as a warning)
  03	SQL Statement not yet complete
  08	Connection exception
  09	Triggered action exception
  0A	Feature not supported
  0B	Invalid transaction initiation
  0F	Locator Exception
  0L	Invalid Grantor
  0P	Invalid Role specification
  20	Case not found
  21	Cardinality Exception
  22	Data Exception
  23	Integrity Constraint Violation
  24	Invalid Cursor state
  25	Invalid Transaction State
  26	Invalid SQL Statement Name
  27	Triggered Data Change Violation
  28	Invalid Authorization Specification
  2B	Dependant Privileges Descriptors still exist
  2D	Invalid Transaction Termination
  2F	SQL Routine Exception
  34	Invalid Cursor Name
  38	External Routine Exception
  39	External Routine Invocation Exception
  3B	Savepoint Exception
  3D	Invalid Catalog name
  40	Transaction rollback
  42	Syntax Error or Access Role Violation
  44	WITH CHECK OPTION Violation
  53	Insufficient Resources
  54	Program Limit Exceeded
  55	Object not in prerequisite state
  57	Operator Intervention
  58	System Error (external to PostgreSQL)
  F0	Configuration File Error
  HV	Foreign Data Wrapper Error
  P0	PL/pgSQL Error
  XX	Internal Error

  
  


• Unfortunately you cannot raise exceptions directly within the PostgreSQL server. You could use a procedural language within the server (such as PL/pgSQL), but this will limit the portability and compatibility of your code with other RDBMS systems.


• In PL/pgSQL, you would use the RAISE statement to report exceptions and messages.
  

  RAISE [level] 'message' [, value] USING ERRCODE = 'SQLSTATE';

  where:
  level = DEBUG, LOG, INFO, NOTICE, WARNING, and EXCEPTION
  message = String (% denotes a placeholder for an optional value
  value = field value
  SQLSTATE = Error code conforming to the SQLSTATE convention


• A SQL query will undergo the following process in order to generate a result:
  1. A connection from the application to the server is established. The application then sends a query to the server and waits for a result
  2. The parser checks the query for correct syntax and creates a query tree.
  3. The rewrite system takes the query tree and looks for any rules (stored in the system catalog) to apply to the tree. It performs the transformations given in the rule bodies.
  4. The planner/optimizer takes the rewritten query tree and creates a query plan. It does this by creating all possible paths leading to the same result, and then calculates the estimated cost of each plan. It will then select the cheapest path and prepare a query plan tree for the executor.
  5. The executor recursively steps through the plan tree and retrieves rows in the way represented by the plan. When it is complete it hands the result back to the application.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• ISO/IEC 9075-1:2011, or the SQL standard (it costs money, though) 
• PL/pgSQL exception handling by stackoverflow.com

PostgreSQL: Basic Concepts

This a continuation of my PostgreSQL series (an installation guide for Ubuntu can be found here). Here I will briefly outline some basic concepts about the database.

You can also check out my notes on designing relational databases.

• Views are a concept that is common to almost all relational database (RDBMS) systems. The best way to think of a view is akin to an object-orientated interface; an abstraction that takes away all the underlying structural details and provides a generic way to access data and functions.


• Foreign keys help maintain the referential integrity of your data. It links a record to another record stored in a different relation.You can additionally define the constraints and associations for the link.


• Transactions are atomic operations where all the steps happen or none of the steps happen. A transaction also guarantees the the result of the operation has been logged to physical storage so that no data is lost in the case of a system crash.


• In PostgreSQL, a transaction block starts at the BEGIN command and completes when it reaches the COMMIT command. You can place as many SQL commands as you wish between these two block commands. If you want to cancel the update and reset the database, you just issue a ROLLBACK command.
  
  An example of a transaction block is as follows:
  

  BEGIN;
  UPDATE accounts SET balance = balance - 100.00 WHERE name = 'Alice';
  UPDATE branches SET balance = balance - 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
  UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Bob';
  UPDATE branches SET balance = balance + 100.00 WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
  COMMIT;

• Window functions perform a calculation across a relational table. It is similar to aggregate functions like count() and min(), however where these functions return a single output row a window function retains the identities of all rows in the relation. PostgreSQL defines some in-built aggregate functions that can be used within a window function.


• The OVER clause in a window function specifies how the rows are split up and processed. For instance, you can specify a PARTITION BY list that groups records by a field value. The aggregate function then processes these records by the group rather than all records in the relation. This is a relatively complex topic, so it is strongly suggested that you read the PostgreSQL documentation on this topic.


• Inheritance is a concept that has carried over from object-orientated databases. However, it is not feature-complete and has some serious caveats that severely limits the functionality of this feature. You should read the documentation to see if the inheritance feature is suitable for your needs.


• You can alternatively use views, foreign key constraints and other database concepts to mimic inheritance in your database.


• PostgreSQL supports the idea of constraints for table fields. This allows us to fine-tune the allowable data that can be stored beyond just defining data types. The following constraints have been defined:
  
  

  Type	Description
  CHECK	The value for a column must satisfy a Boolean condition. You can also specify a name by using the optional CONSTRAINT clause. CREATE TABLE products( price numeric CONSTRAINT positive_price CHECK (price >0) );
  NOT NULL	Simply states that a value cannot be NULL
  UNIQUE	Simply states that the value is unique across all record rows
  PRIMARY KEY	Similar to a combination of UNIQUE and NOT NULL constraints, however it specifies that the field can be used as a unique identifier for the row. There can only be one PRIMARY KEY entry for each table.
  REFERENCES (Foreign Key)	The REFERENCES clause is used to specify a foreign key relationship between two tables, which will maintain referential integrity of the data. When this clause is used, it will become impossible to create a row when the foreign key does not exist in the referenced table. You can also specify what happens to the row when the referenced field is updated or deleted by using the ON UPDATE and ON DELETE clauses, respectively. The actions you can define are RESTRICT, CASCADE, NO ACTION, SET NULL and SET DEFAULT. See the documentation for more information on how this works.

  
  


• Each table is created with several system columns. These are reserved and cannot be used as names for user-defined columns. System columns store information such as the unique id of the table, the unique id of the record in the table, and the physical location of the row.
• The ALTER TABLE command allows you to modify an existing table. This is useful if the table already contains data or is being referenced by other tables.
• PostgreSQL Schemas can be thought of as table namespaces. You can logically group tables in a database by schema, and you can have identically named tables existing in two different schemas.
  By Default, a new database will automatically have a 'public' schema that is accessible by all users with privileges on the database.
• Partitioning a table is done via table inheritance (so read up on how PostgreSQL does inheritance before using this feature!). By physically partitioning a table you can increase query times (since the server only needs to search the partition and not the whole table), and you can move less-used data to cheaper disks.

References:

• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• An extensive (but still a work in progress) comparison of the SQL implementations of various RDBMS' from troels.arvin.dk
• A decent explanation as to why there is a public schema created by default from stackoverflow.com

PostgreSQL 9.1 and Ubuntu 12.04

This is just a quick guide on how to get started using PostgreSQL on Ubuntu.

Installing

1. Install the PostgreSQL server using apt:
   

   sudo apt-get install postgresql

2. Install the contrib add-on package. This provides additional tools and features, such as improved logging and administration functions.
   

   sudo apt-get install postgresql-contrib

3. Install the GUI admin interface pgadmin3:

   sudo apt-get install pgadmin3

4.  Edit the file /etc/postgresql/9.1/main/postgresql.conf to allow TCP/IP connections to the server. Just uncomment the following line in the file:
   

   listen_addresses = 'localhost'

5. Start the database
   

   sudo service postgresql start

6.  To automatically start the server run the following command:
   

   sudo update-rc.d postgresql defaults

7. Put a password on the default postgresql user by opening up a connection to the database:
   

   sudo -u postgres psql template1

   And then running the following SQL command:

   ALTER USER postgres WITH PASSWORD 'password';

8. Set up the same password for the system postgres user:
   

   # Delete the existing postgres user password
   sudo passwd -d postgres
   
   # Set the user password
   sudo su postgres -c passwd

    

Interfacing with PostgreSQL

• You can create a database directly from the command line (this assumes that your linux user has a corresponding account to use PostgreSQL):
  

  createdb newdb

• To create a new database with a user that has full rights on that database:

  # Create the user
  sudo -u postgres createuser -D -A -P newuser# Create the database
  sudo -u postgres createdb -O newuser newdb

• The corresponding command to delete a database is:
  

  dropdb olddb

• Accessing the database through the commandline:
  

  psql newdb
  #OR you could use
  sudo -u postgres psql newdb#OR you could use
  psql -h localhost -U postgres -W newdb

  
  NOTE: If the command prompt shows "=#", then you are using an admin account which bypasses normal access controls. This is potentially dangerous. Your average account should be displaying "=>" as part of the prompt.
• In-built psql commands are prefaced with a '\':
  
  • \h displays the help for SQL commands
  • \? displays the help for psql-specific commands
  • \q exits the terminal interface for psql
• Some useful in-built SQL statements:
• SELECT version(); will display the current PostgreSQL version
• SELECT current_date; will display the current date of the system
• SELECT now(); will display the date and time
• SELECT now()::date; will only display the date portion
• SELECT now()::time; will only display the time portion
• count(), sum(), avg(), max(), and min() are special aggregate functions you can perform on fields in your SQL statement.

References:

• How to set up PostgreSQL in Ubuntu by ubuntugeek.com
• The PGAdmin3 main web-page
• The PostgreSQL 9.1 documentation
• Automatically starting services in Debian

Email validation through MySQL triggers and signals

Did some research and found out that MySQL provides triggers and signals. Triggers allow you to run some code when an certain event occurs, and Signals allow you to raise exceptions in your code.

Here is a SQL script to show you how it is done:

CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

USE `test`;

CREATE TABLE IF NOT EXISTS `test`.`entity_email` (

    `emailID` INT NOT NULL,

    `email` VARCHAR(64) NOT NULL,

    PRIMARY KEY (`emailID`) )

ENGINE = InnoDB

COMMENT = "Generic Email table";

DELIMITER $$

USE `test`$$

CREATE TRIGGER `trg_entity_email_insert` BEFORE INSERT ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

CREATE TRIGGER `trg_entity_email_update` BEFORE UPDATE ON `test`.`entity_email` FOR EACH ROW

BEGIN

    IF NOT (SELECT NEW.email REGEXP '$[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}$') THEN

        -- bad data

        SIGNAL SQLSTATE '40000';

    END IF;

END$$

DELIMITER ;

Now MySQL will perform e-mail validation EVERY time you insert or edit the record. If it doesn't match, it will fail (MySQL outputs an "Unhandled user-defined exception condition" as the error message).

Hope this helps!

Ubuntu 12.04, LAMP and CodeIgniter 2

This guide aims to install and use CodeIgniter on an Ubuntu machine. We will assume you have the default Ubuntu installation.

1. Install Apache 2:

   sudo apt-get install apache2

2. Install MySQL:
   

   sudo apt-get install mysql-server mysql-client

3. Install PHP5 and the necessary libraries:
   

   sudo apt-get install php5-cli php5-mysql libapache2-mod-php5

4. Download the CodeIgniter framework:
   

   wget http://codeigniter.com/download.php -O ~/CodeIgniter.zip

5. Extract the framework to the default Apache2 web directory:
   

   sudo unzip ~/CodeIgniter.zip /var/www/

6. You should now be able to navigate to your extracted folder from your web-browser. Hint: you will need to open firefox to http://localhost/<name-of-folder> which you can get by executing the following command:
   

   ls /var/www

References

• 'Installing the LAMP stack for Apache2' from www.howtoforge.com
•  Another LAMP stack for Ubuntu tutorial from smashingweb.info
• Settin up LAMP and CodeIgniter from caravaggisto.wordpress.com

Setting up a CentOS 6.2 web server: Installation

CentOS is the free release version of Red Hat Linux with all the branding removed. It also does not have the support options and some of the fancy trimmings the Enterprise version offers, but it is still a solid server OS. This guide is a brief step-by-step guide in how to install CentOS 6.2 and configure it as a web-server.

Install from DVD:

1. Boot up from your DVD (you will need to enter into the Boot menu of your computer OR edit your BIOS to do so)


2. Select Install or upgrade an existing system from the menu


3. If you are worried about your DVD you can choose to to test it, but this is not a necessary step so you can skip it.


4. On the Welcome screen select 'Next'
   


5. Select your language (in our case we are going for the default of 'English (English)')


6. Select your keyboard type (in Australia we use 'U.S. English')


7. If you are just going for a standard local hard-drive set-up then just choose the 'Basic Storage Devices' option. If you are going for something fancy (such as network storage or special drives), or you just want to disable some devices for that extra level of paranoid security then choose 'Specialized Storage Devices'.
   
   If you have no idea which one you should choose then just select the Basic option.


8. Enter in the host-name of your new server (for best results you should append your domain name to the end so it works seamlessly with SSL certificates) i.e. testserver.example.com
   
   If you want to configure a static IP address click on the 'Configure Network' button, select the your network card (probably eth0) and enter away.
   
   If you are going to use DHCP, or just don't know, just hit 'Next'


9. Select the correct timezone for you (just click a location on the map and it should select the closest one to you).


10. Enter in an appropriate root password. Make as long and complex as possible (long sentences with mixed character types are easier to remember than jibberish strings; for instance 'My office is situated in 1234 fake street, Fakeville!')


11. In this example we are going to go for a custom partition layout, so select 'Create Custom Layout'. If you are fine with defaults, just skip to part .


12. Delete all existing partitions and do the following:
• A /boot partition of about 100MB. Use the ext4 format


• Create a LVM Physical Volume that fills up the rest of the hard-drive


• Create a LVM Volume Group with a Physical Extent of 4MB.


• Create LVM Logical Volumes on the Volume group as follows:


• Swap space that is at least equal to how much RAM is in your server
• /tmp/ should be as big as the largest file you will be manipulating (for instance, if you are copying a DVD you will need at least 4GB)
•  /var/log and /var/log/audit are separated so that if your log system goes haywire it does not kill the space for other applications. Dedicate a couple of gigabytes to each.
• /home/ and /usr/ should be a few gigabytes each. /usr/ just holds your applications and should remain pretty static, while /home/ is where you will store your personal files.
• /var/ and /var/www/ will contain the majority of space on your system. MySQL stores your database files in /var/lib/mysql/, while Apache runs from /var/www/. Dedicate adequate space to each folder.
• Your root folder (/) will only need a few GB of space. It will mainly hold configuration files.


13. The system will take some time to format your hard-drive. Once it is complete it will ask you to install the boot-loader. While the defaults are suitable, for extra security you should consider password protecting your boot-loader.


14. We can now select our packages. You can customize the system to suit your needs, but for the basics just select 'Basic Server' from the menu and the 'Customize now' from the radio buttons. Hit 'Next'.


15. Do the following edits:
• Base system - Remove 'Java Platform' and 'Directory Client'
• Web Server - Add 'Web Server' and 'PHP support'


16. Reboot your system!

References

• Howto Forge's guide: 'The Perfect Server - CentOS 6.2 x86_64 with Apache2'
• Howto Forge's guide: 'Installing Apache2 with PHP5 and MySQL support on CentOS 6.2'
• Red Hat Linux 5 Hardening Tips - National Security Agency
• Guide to the secure configuration of Red Hat Linux 5 - National Security Agency
• CentOS wiki on hardening the OS

Designing relational databases

While I was introduced to database theory in University, we never really did much in the way of practically implementing that theory. As such, when I am tasked to design a database for a web application I had no idea where to start. Well, not really; I had a vague idea of where to begin.

Thus I scrambled through my notes and various on-line articles to relearn the topic. Here are my notes; use them wisely...

• The Relational Database Model describes a database that has a series of unordered relations (or a related set of information) that can be manipulated using operations that return tables.
• Some interchangeable terms:
  Relations = tables (set of related information)
  Attributes = columns/fields (Descriptor of the information)
  Tuples = rows/record (Actual data)
• Each table should only represent one (and only one) thing or event.
• A primary key is a column/attribute of a relation/table that guarantees the uniqueness of a record/row/tuple. You can only have one primary key per relation.
• A foreign key is an attribute used to reference a primary key in another relation/table.
•  Normalisation is the process of simplifying the design of a database so that it achieves optimum structure.
  
  
  1. First normal form: All attributes must be atomic. Attributes should not be able to be broken down or repeated in a relation.
     
     In other words, an attribute cannot be an aggregate or list of data.
  2. Second normal form: Every non-key attribute must be dependant on the primary key.
     
     In other words, the postcode attribute will be dependant on the address and not the person. It stands that the address should be moved to it's own table.
  3. Third normal form: All non-key attributes are mutually independent. And attribute should not be reproducible or rely on information already existing in the tuple.
     
     For instance, the total cost for a sale is the sum of all costs, and does not deserve it's own table or attribute.
• A join is used to collate relations into one relation with relevant data. This can be used to perform other operations without going back and forth between the tables.
• A Natural join relies on an intersection of attributes (i.e. both tables contain attributes with the same name). This can be either a primary key or a foreign key.
• An Inner Join specifies which matching attributes to use (this is done through the USING keyword). In other words, you get to to specify which attributes are going to be used to match records together.
• An Outer Join is used where you want to display all data, whether they have a matching record or not. This is because the inner join would simply discard any record that did not have a matching attribute in the other table.
• A view (also called a named query or stored query) is simply a SELECT statement that is given a name and is stored in the database.
• A good practice in database design is to avoid having NULL values stored in your relation. If the attribute is not required to make the relation complete, do we need the attribute? Or can we move it to it's own relation?
• Some common mistakes in database design are:
  
  1. Poor design/Planning
  2. Ignoring normalisation
  3. Poor naming standards
  4. Lack of documentation
  5. One table to hold them all (a sort of repeat of the 2nd normalisation rule)
  6. Using identity/guid columns as your only key
  7. Not using SQL facilities to protect data integrity
  8. Not using stored procedures
  9. Trying to build generic objects
  10. Lack of testing

References

• Article about the fundamentals of database design by Paul Litwin
• Common database design mistakes by Louis Davidson
• Database Design by Tom Jewett