[DB-SIG] More questions from a stubborn beginner.

[Magnus Lyckå]

At 00:38 2002-03-27 -0300, Ignacio Guerrero wrote:
>Hi there!,
>
>OK, I have a few questions. I've been practicing MySQL, It's a little bit=
 
>complex than I thought.
>
>- Can anyone tell me the difference between PRIMARY_KEY, KEY and INDEX?

A key is any column or combination of columns that will
be enough to identify any row. I.e. if a column can have
the same value for several rows, it's not a key.

For instance if you have a table with data about
cars, both REGISTRATION_NUMBER and CHASSIS_NUMBER
will be primary keys.

A primary key is a key that you have chosen to be the
primary key. (Perhaps CHASSIS_NUMBER in this case.)

An index is a construct which can either be used to
enforce non-primary keys (unique index) or just to
speed up database searches. (In the above case you
would make a unique index on the other key (REGIST-
RATION_NUMBER).) Please note that some SQL databases
don't actaully use the primary key statement in CREATE
TABLE for anything useful, so you might need to CREATE
UNIQUE INDEX for your primary key as well. Or is this
just a problem of the past?

I've written a little about relational databases at:
http://www.thinkware.se/cgi-bin/thinki.cgi/RelationalDatabase

>- Suppose I want a row to show only fixed data like, for instance, 
>('LivingRoom', 'BathRoom', 'BedRoom', 'Kitchen', 'Garage'). Should I use 
>SET or is there another way?

Are we talking about a data type called SET? That doesn't
exixt in my SQL book at least. Unless it appeared in a
standard after SQL 92 it's a MySQL extension.

The normal SQL solution is to have a table for these data.
A table is a SET! The whole relational algebra and calculus
is based on set theory.

A table is also much easier to change than a data type.

Just make a table for the room types with just one
column, and use foreign keys to that table wherever
you need room types:

See 3.5.6 Using Foreign Keys in the MySQL manual.

>- So, in general terms, how could I distribute this program without other=
 
>people having to install Python and MySQL?

with py2exe or McMillan installer they won't have
to install Python, but they will have to install
MySQL.

I suppose you could use a normal windows installer
program such as InstallShield to make your own
custom installation that will install everything
you need in one package. But that seems like over-
doing it to me.

If you want a simpler installation, you might use
the Python SQL engine "gadfly" instead. I've never
used that though, so I can't help you with details.
Note the gadfly is far from a full fledged SQL engine.
I don't think it has foreign key constraints for
instance.


-- 
Magnus Lyckå, Thinkware AB
Älvans väg 99, SE-907 50 UMEÅ
tel: 070-582 80 65, fax: 070-612 80 65
http://www.thinkware.se/  mailto:magnus@thinkware.se