Hello,

I have only read Elmasri/Navathe and I find it nearly impossible
to implement any non-trivial Enhanced Entity relationship diagram
in MySQL,
according to the mailing list, important foreign key constraints are
not supported,

===
"Morten Gulbrandsen" <mgu (AT) owi-aachen (DOT) de> wrote:
[skip]

ON DELETE SET DEFAULT is not supported.
That is why you can't create foreign key constraints.
===

What I have seen from other MySQL projects is that a combination of
MySQL and PHP or Perl is necessary to create a workaround beyond
MySQLs limitations.

Also Elmasri / Navathe discusses many RDBMS including

INGRES
Oracle of Oracle, Inc.
Sybase of Sybase, Inc.
INFORMIX of Informix, Inc.
PARADOX,
DBase IV,
WATCOM SQL,
SQL Server (of Sybase,Inc.),
SQL Server (of Microsoft),
MS Access

Postgres is mentioned but no discussion of MySQL,

Why ?

It is the most popular SQL server,
so I can start to believe that alone MySQL is no RDBMS,
unless it is combined with another programming language,

Is this true ?

From Elmasri:

To qualify as a genuine relational DBMS,
a system must have at least the following properties:

1.) It must store data as relations such that each column is
independently identified by its column name and the ordering of rows
is immaterial.

2.) The operations available to the user, as well as those used
internally by the system, should be true relational operations; that
is, they should be able to generate new relations from old relations.

3.) The system must support at least one variant of the JOIN
operation.

Which open source products can offer this and hence qualify for being
an RDBMS or even ANSI SQL compatibility ?




Yours Sincerely

Morten Gulbrandsen

Em Thu, 21 Aug 2003 01:16:04 -0700, Morten Gulbrandsen escreveu:

PostgreSQL, FireBird and SAP db strive to be full-fledged SQL
DBMSs.

But SQL is not relational.


--
_ Leandro Guimarães Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Responda Ã* lista, não a mim diretamente! +55 (11) 5686 2219
/ \ Dê nota se ajudei: http://svcs.affero.net/rm.php?r=leandro

Leandro Guimarães Faria Corsetti Dutra <lgcdutra (AT) terra (DOT) com.br> wrote

Pardon ?

if ANSI SQL standard is not documenting an RDBMS,
what is then relational ?

Where can I get more information about FireBird, SAP db ?
What is the main differences between Oracle, MySQL and PostGres ?

I sometimes believe that MySQL has to be combined with another Language,
in order to be an RDBMS,


Yours sincerely

Morten Gulbrandsen

SQL standards neither document an RDBMS nor has anything to do with
relational model. In fact you will not see the word 'relational' in the
standard documents. SQL partially models multi-sets (bags) and fails to
conform to many fundamental principles that primarily derive the relational
benefits.

For starters, get a good text book on database fundamentals (prefer Date's
Intro to database systems) and learn the definition of a relation & its
binding principles and you'll be able to understand why SQL is not a
faithful implementation of the relational model.

--
- Anith
( Please reply to newsgroups only )

"Morten Gulbrandsen" <Morten.Gulbrandsen (AT) rwth-aachen (DOT) de> wrote

A database management system that represents all data as values in
relations.


I suggest you contact the respective vendors.


Ask any three people that question and you will get twelve different
answers.


MySQL is a simple file processing tool. It would have to become a full data
management system that prohibits subversion to become any kind of dbms.

Morten,


"Morten Gulbrandsen" <Morten.Gulbrandsen (AT) rwth-aachen (DOT) de> kirjoitti viestissä
news:60ca69db.0308210016.822e230 (AT) posting (DOT) google.com...
foreign key constraints are supported in MySQL. Where did you find on the
mailing list the claim that they are not? I do not remember seeing that
claim for a long time. InnoDB does not support ON ... SET DEFAULT, but that
is not really a constraint, but an 'action', similar to a trigger.

http://www.innodb.com/ibman.html#InnoDB_foreign_keys
"
The syntax of a foreign key constraint definition in InnoDB:
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION
Both tables have to be InnoDB type, in the table there must be an INDEX
where the foreign key columns are listed as the FIRST columns in the same
order, and in the referenced table there must be an INDEX where the
referenced columns are listed as the FIRST columns in the same order. InnoDB
does not auto-create indexes on foreign keys or referenced keys: you have to
create them explicitly. The indexes are needed for foreign key checks to be
fast and not require a table scan.
....
An example:
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON DELETE CASCADE) TYPE=INNODB;
"

This holds for MySQL.

This holds for MySQL.

This holds for MySQL.

Thus, according to the definition of Elmasri and Navathe, MySQL/InnoDB is a
relational database. It is not a relational database according to so-called
Codd's 12 rules, but none of the well-known databases satisfies all those
rules.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a non-free hot backup tool for MySQL
Download MySQL-4.0 from http://www.mysql.com

Hi!

"Leandro Guimarães Faria Corsetti Dutra" <lgcdutra (AT) terra (DOT) com.br> kirjoitti
viestissä newsan.2003.08.21.22.38.11.649468 (AT) terra (DOT) com.br...
Note that SAP DB has been renamed MySQL MaxDB.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a non-free hot backup tool for MySQL

Hi!

"Bob Badour" <bbadour (AT) golden (DOT) net> kirjoitti viestissä
news:Vj52b.593$q25.72766566 (AT) mantis (DOT) golden.net...
Hmm... Elmasri and Navathe say that 'each column is independently identified
by its column name'. Now we can ask if they mean that the ordering of
columns in a UNION expression should be irrelevant.

In SQL (see http://www.contrib.andrew.cmu.edu/~s...ql/sql1992.txt) we
associate an ordering to the columns of a table, as well as the name of the
column. Ok, if Elmasri and Navathe mean that the ordering of columns in an
expression should not matter, then SQL does not satisfy 1.).

In SQL we think that an n-ary 'multiset' M consists of (1) an injection from
{1, 2, ..., n} to the set of possible column names and (2) a set of tuples
of the form (k, a1, a2, ..., an). Here k is a natural number > 0 which tells
how many times the tuple (a1, a2, ..., an) occurs in the multiset, and each
ai is an element from a basic set determined by the data type of the ith
column.

In mathematics, we can define a relation as a set of mappings N -> A, where
N is a set of column names and A is some set or class. Then the ordering of
columns really does not matter in a union of two relations.

If we define a 'relation' as a set like in mathematics, and not as a
'multiset' like in SQL, then the above is not a 'relation'. You have to add
DISTINCT to get a mathematical relation.

But if we define a 'relation' as a multiset, then the above is a 'relation'.
This is also relevant in 1.): SQL databases allow the storing of multisets.
But nothing prevents using SQL to store only relations in the mathematical
sense. And you must add the DISTINCT keyword to your queries to make sure
that SQL's operations produce only relations in the mathematical sense. I
think this is the reason why there has not been much need to replace SQL by
a mathematical relation language. You can use SQL in a way which produces
only mathematical relations.

The DISTINCT operation can be quite costly in some cases. SQL allows you not
to use it, while a mathematical relation query language would always need to
apply it.

Chris Date claims that the concept of a multiset is confusing, and having
only mathematical relations in a database query language would allow for
more query optimization. But I believe most real-world SQL queries produce
only mathematical relations, and we can apply all the optimizations present
with mathematical relations. One can also argue that a multiset is a natural
way to model the real world. For example, in quantum mechanics, electrons
can be indistinguishable. That is similar to having in a table two rows with
the exact same column values.

Anyone know how Elmasri and Navathe define a 'relation' in their book? Do
they say that some SQL databases, like DB2 or Oracle are 'RDBMS'?

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a non-free hot backup tool for MySQL

Hi!

"Heikki Tuuri" <Heikki.Tuuri (AT) innodb (DOT) com> kirjoitti viestissä
news:9272b.354$yu.1 (AT) read3 (DOT) inet.fi...
....
Apparently I can answer my own question.

From the contents of the book:
http://www.aw-bc.com/info/database/elmasri.html

FUNDAMENTALS OF DATABASE SYSTEMS, WITH E-BOOK, 3/e
Elmasri/Navathe
ISBN: 0-201-74153-9

"
8. SQL-The Relational Database Standard.
....
10. Examples of Relational Database Management Systems: Oracle and Microsoft
Access.
"

Thus we can conclude that MySQL is 'Elmasri-Navathe-relational'.

Best regards,

Heikki Tuuri
Innobase Oy
http://www.innodb.com
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a non-free hot backup tool for MySQL

Em Sun, 24 Aug 2003 19:00:24 +0000, Heikki Tuuri escreveu:

We can also conclude Elmasri and Navathe have been corrupted
by the market out of any sense...


--
_ Leandro Guimarães Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Responda Ã* lista, não a mim diretamente! +55 (11) 5686 2219
/ \ Dê nota se ajudei: http://svcs.affero.net/rm.php?r=leandro