What is a "RDBMS" (Relational Database Management System)?

It is a way of storing information in a manner that enforces consistency, facilitates access, ensures reasonable performance, without constraining how the data is correlated or presented.

• Most RDBMS systems (including MS-Sequel Server, Oracle, Informix, DB2, MySQL, Adabase, Sybase) speak a language refered to as SQL (pronounced sequel).
• A RDMBS breaks data into tables, which contain rows, which are broken into fields. Rows can be selected from tables based upon the value of a row's field(s). Most operations are performed on rows.
• A "database" is a collection of tables. A RDBMS may contain multiple databases.
• A database has a "schema" which is a description of all the tables in the database.

What is SQL?

A universal RDBMS access language, defined by commitiee. The current standard is reffered to as SQL92, as that is the year it was ratitifed. Many RDMBSs (including PostgreSQL) implement proprietary extensions to SQL. SQL has the following basic command and structure.

• Create - Used to create objects such as tables, indexes, and views.
• Drop - Used to destroy objects such as tables, indexes, and views.
• Select - Used to pull fields and rows from the database tables.
• Insert - Used to populate a table.
• Delete - Used to remove records from a table

.
For example:

select first_name, last_name

from phone_book

where phone_number matches "616*"

order by last_name, first_name

Will "select" the fields named first_name and last_name from the rows of the table called phone_book where the phone number begins with "616".

delete from phone_book

where phone_number = "6165551212"

Will delete all rows from the phone book table where the phone number field contains "6165551212"

PostgreSQL Features

Functions - Allows user defined functions to be directly executed by the back end via the use of shared libraries. Supported languages are C and SQL. Additional languages can be integrated using the create language command.

CREATE FUNCTION name ( [ ftype [, ...] ] )

RETURNS rtype

AS path

LANGUAGE 'langname'

CREATE FUNCTION ean_checkdigit(bpchar, bpchar) RETURNS bool

AS '/usr1/proj/bray/sql/funcs.so' LANGUAGE 'c';

CREATE TABLE product

(

id char(8) PRIMARY KEY,

eanprefix char(8) CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}')

REFERENCES brandname(ean_prefix),

eancode char(6) CHECK (eancode ~ '[0-9]{6}'),

CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode))

);

Constraints - A limit, type conversion, or validity check on table records. This functionality is defined in the SQL 92 standard.

CREATE [ TEMP ] TABLE table (

column type

[ NULL | NOT NULL ] [ UNIQUE ] [ DEFAULT value ]

[column_constraint_clause | PRIMARY KEY } [ ... ] ]

[, ... ]

[, PRIMARY KEY ( column [, ...] ) ]

[, CHECK ( condition ) ]

[, table_constraint_clause ]

Constraint Types:

NOT NULL - A field must contain a value.

UNIQUE - A field's value cannot be the same as in any other row in the table.

CHECK - A logical condition must be satisfied for the operation to succeed.

Example: CREATE TABLE distributors (

did DECIMAL(3),

name VARCHAR(40)

CONSTRAINT con1 CHECK (did > 100 AND name > ''));

PRIMARY KEY - Define a group of fields that must consitute a unique value.

Example: CREATE TABLE films (

code CHAR(05),

title VARCHAR(40),

did DECIMAL(03),

date_prod DATE,

kind CHAR(10),

len INTERVAL HOUR TO MINUTE,

CONSTRAINT code_title PRIMARY KEY(code,title));

In version 6.4 the usefulness of constraints is limited by the lack of support for foreign keys.

Triggers - Ties the execution of a user supplied procedure (function) to the execute of an operation on a relation (insert, update, or delete). The create trigger command is specific to PostgreSQL and may not apply or may act differently in other RDBMSs.

Rules - Define actions to occur when a particular SQL statement is executed by and application or user (current_user return the name of the user name associated with the application making the request.) Access to system functions or catalogs (other than the "oid" field) are not possible from within a rule. If a rule exceeds a system page (usually 8kb) it's creation may fail.

In a rule the "current.*" expression corresponds to the record being affected by a select, update or delete.

create rule example_1 as

on update EMP.salary where current.name = "Joe"

do update EMP (salary = new.salary)

where EMP.name = "Sam"

Typically a rule action will be performed in addition to the user/application requested action. With the use of the "instead" keyword the rule will be performed in place of the user action.

create rule example_2 as

on select to EMP.salary

where current.name = "Bill"

do instead

select (EMP.salary) from EMP

where EMP.name = "Joe"

The "nothing" action in a "instead" rule causes no action to be perfumed when the application or user submits a particular action.

create rule example_3 as

on select to EMP.salary

where current.dept = "shoe" and current_user = "Joe"

do instead nothing

The "new.*" expression refers to fields in a records being inserted into a table:

create rule example_5 as

on insert to EMP where new.salary > 5000

do update new.salary = 5000

Rules are a PostgreSQL extension to the SQL standard and may not be supported or function in the same manner on other DBMS systems.

Home| Log-in | Contact | About Us | Support
terms & conditions | privacy plolicy

Copyright © 2008 CMG Network Systems