[lug] Databases

[George Sexton]

I would be happy to present on SQL Anywhere.

Wayde: Just let me know the proposed date and I will coordinate with Sean.

Here is my take on why the features I listed are important.

1)	Declarative Referential Integrity

This means that you define the relationships in the data and the database
enforces them. If you have a coding error that would violate the
relationship, then the update or insert fails. This just makes programs
faster to develop and results in fewer coding bugs. Postgresql supports this
in version 7.0. MySQL does not.

2)	True Transaction Log

The transaction log stores a history of updates. If the drive containing the
database fails, and you have the transaction log on another drive (which I
do), then you can restore from your last backup and apply the transaction
log. IOW, you lose almost 0 data.

3)	Stored Procedure Language

On any sufficiently complex database you will have reports that cannot be
created without resorting to procedural code. People will want some complex
set of queries that cannot be coded in a single statement. The simplest way
to handle this is to use a stored procedure and call that stored procedure
from your reporting tool. It can also be useful for performing data
intensive operations because only the result set is returned by the server.
If you have some very complex operation that requires a table scan of a
400MB table, this can really boost performance. The down side to using
stored procedures is that if you want to change databases, this is code that
must be re-written.

4)	Outer Joins

Again, given any sufficiently complex database you will need these. A normal
or equijoin, says something like:

select *
from Customer
	Inner Join Invoice
	ON Invoice.Cust_ID=Customer.Cust_ID

or in older style

Select * from Customer, invoice where Invoice.Cust_ID=Customer.Cust_ID

This join fails in the instance for customers where there is no invoice. A
typical report might be: Prepare a summary report of customers that shows
all customers, the number of invoices, and the sum of sales for a given date
range. With an outer join you would write this as:

select
	Customer.Cust_ID,
	Customer_Name,
	count(Invoice.Invoice_No) as InvCount,
	sum(Invoice.Total) as InvSum