Internetworkers: http://www.ibiblio.org/internetworkers/

["Michael D. Thomas" <mdthomas AT mindspring.com>]

> From: internetworkers-bounces AT lists.ibiblio.org
[mailto:internetworkers-
> bounces AT lists.ibiblio.org] On Behalf Of Steven Champeon
> Sent: Thursday, October 09, 2003 11:48 AM

> Most people I know can't even do HTML with any sort of sense, so I'd
> rather use guitar as an example.

Wow. /Most/ of the people you know? More than 50%? Interesting....

Anyway.... I found some technical inaccuracies in this post. I'd hate
for the newbie databasers out there to be mislead, so I've cleaned them
up. I've also added some of my perspective from years of designing,
developing and teaching relational databases.

To start, let's talk about the central structure of a relational
database -- the table. That's table as in 'chart,' not table as in
'dining room table.' It has columns and rows, just like a ledger. 

You define the columns when you create the table. By defining the
columns, you describe the attributes of your data. Though you shouldn't
be adding columns after you create the table, you are constantly adding
and deleting rows.

To play along with this example, each row in our table would represent a
guitar.

So, let's define some columns for our guitar table. Here are some
attributes of Guitar:

* name -- the name of the guitar. 
* maker_id -- the id of who made it
* type -- electric or acoustic?
* tuned -- Is it tuned?
* guitar_id -- a random number to uniquely identify the guitar. It's the
'primary key' for the guitar.

> If you can take your guitar and fetch it from its case, that's a
SELECT.

Okay.... I have a slight problem with this analogy. A case can only have
one guitar, right? But a SQL table can have many rows. Instead of using
case, let's imagine that we have a rack that has many guitars. The rack
can have as many guitars as you would like -- the only limit is the size
of your database.

So, here's the corrected analogy:

"If you can take your guitar and fetch it from its /rack/, that's a
SELECT."

Here's what the select looks like:

SELECT * FROM guitar;

The wildcard selects all columns. You can also select the columns by
name:

SELECT name, type, tuned FROM guitar;

You can use a WHERE clause to only select some rows, and you can use
ORDER BY to order the results:

SELECT name, type, tuned FROM guitar 
   WHERE type="electric"
   ORDER BY name;

This would give you all the electric guitars. If you want one and only
one guitar, you should ask for it using the primary key:

SELECT name, type, tuned FROM guitar
    WHERE guitar_id=1033;

You can run the query from a command line tool, but most of the world's
SQL statements are executed from inside another program written in an
imperative language like Java or C++.

> If you can tune your guitar, that's an UPDATE.

Sure. The tuned attribute is updated from false to true.

UPDATE guitar SET tuned='yes' WHERE guitar_id=2346;

Don't forget the WHERE clause, or else you'll set tuned to 'yes' for all
of the rows!

 
> If you can put your guitar back in its case, that's an INSERT.

Um, no. An INSERT inserts a brand new row into the database. Thus, you'd
use an INSERT when you want to put a new guitar on your rack. Here's an
insert:

INSERT INTO guitar 
  VALUES ('Les Paul blue and white','Les Paul', 'electric', 'classic',

          false);

 
> If you can dash your guitar against a speaker in Townshendesque
mock-rage,
> that's a DELETE.

Yup. Remember your WHERE clause or you'll delete everything in your
table:

DELETE FROM guitar
   WHERE maker='Harmony';

This deletes all the guitars made by Harmony.
 
> If you can make a guitar, that's a CREATE. You sort of have to know
what
> you're doing, and how it will be stored/fetched/played and maybe even
how
> to make it impervious to Townshends.

Hmmm.... some more confusion here. 

I assume that you are talking about CREATE TABLE. In the new analogy
CREATE TABLE creates the rack, while INSERT puts a new guitar on the
rack.

The simplest way to create our table is with this statement:

CREATE TABLE guitar
   (guitar_id int primary key,
   name varchar(100),
   maker_id int,
   type varchar(100),
   tuned char(3));

CREATE TABLE is one of the more complex statements. You do a lot of the
tuning in the CREATE TABLE and/or ALTER TABLE statements, as well as
defining the relationship between tables and constraining the values
that may be entered in a table. When you create tables, you should also
create indexes for your tables. They improve the performance of your
queries.

> 
> All database work, as an old co-worker once told me, amounts to either
> "curdles" (Create/Update/Revert/Delete/List) (and there are far more
> complex forms than MySQL supports natively) or designing the system so
> it can all be done faster or more efficiently.

Never heard of curdles before... Googling "curdle create update revert
delete list database" returns 1 hit. I don't find list or revert
statements in the mySQL reference and haven't seen them with other
databases. What do they do?

Oracle divides SQL into Data Modification Language (select, insert,
update, delete) and Data Definition Language (the create statements).

Anyway, data modeling is another important aspect. By creating a good
data model, you can create a database that is conceptually easier for
developers to code against.

> If you can play something easy, like anything John Prine ever wrote,
with
> three chords and maybe a run, that's like a simple query, say "SELECT
foo
> FROM table ORDER BY bar".
> 
> If you can play something moderately difficult, like say Mike Cross
doing
> his jacked-up hillbilly bluegrass version of "Greenshields", that's
like
> a moderately complex query involving indexes and JOINs.

You don't use SQL to query indexes directly. Indexes make queries run
faster by structuring the data. In our example, we should create an
index on the guitar_id column. The database will create and maintain an
optimized data structure (usually a b-tree) so that the absolute minimum
of rows has to be examined.

If you don't have indexes on your columns that have all unique values,
then you are probably wasting CPU cycles. You're database is scanning
the entire table when it doesn't need to. However, indexes slow things
down on inserts, updates and deletes.

But the SQL query is generally the same, regardless of whether or not
you have indexes on the underlying table. (Caveat: some databases
support query hints that can affect how indexes are used.)

> And MySQL doesn't support the really weird stuff,
> anyway.

Some of the 'weird stuff' that Oracle supports includes specialized
indexing, storing XML in a column of your database (and using XPath and
SQL together in your query), stem text searching (a search on 'buy'
returns fields that contain 'buys' and 'bought' as well as 'buy') and
distributed databases (several geographically dispersed databases act as
one logical database).

> If you can think clearly and logically, you can design a reasonably
good
> basic database in a couple of hours without much training. And the
MySQL
> docs, while sometimes obtuse, are actually pretty good overall.

Thinking clearly and logically, along with a little background info, is
key.

I agree that the MySQL docs are pretty good overall.

I encourage everyone to read them.

A couple of other key items for the newbie:

* To make our example realistic, add a "quantity" column. Then you use
the guitar table to handle your inventory of guitars.

* A join is when you join multiple tables together. Let's say that you
want to get the address of the guitar maker, which is stored in the
guitar_maker table:

SELECT guitar.name, guitar_maker.address FROM guitar, guitar_maker 
  WHERE guitar.maker_id = guitar_maker.guitar_maker_id;

Conceptually, I think the join is the most important part of SQL.

* SQL uses three valued logic: You have true, false and null. The
inverse of true is false, but the inverse of null is null. In your WHERE
clauses, all the expressions have to evaluate to true. If an expression
has an operand that evaluates to null, the expression evaluates to null.


Thus, the following statement will return no rows:

SELECT * FROM guitar WHERE null=null;

You can use the 'is null' operator to deal with nulls in your table:

SELECT * FROM guitar WHERE maker_id is null;

* To learn how to design great databases, read up on normalization and
understand de-normalization. Normalization means that you don't have any
redundant data. Ostensibly, you normalize to save space. In a time of
cheap hard drives, it almost seems quaint. But normalization is very
important when you are designing your application's conceptual model. 

Unfortunately, queries on a normalized database require a lot of joins
between tables, and joins are expensive. Thus, you need to denormalize
for performance reasons. But after demoralizing, modification will
become more complicated and expensive. 

The general rule is: normalize when you are designing, and then
denormalize as needed during development. 

* There's more. Enough for now. Maybe some other db people can chip in.

Cheers!