Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Hi, For decades, people have debated in comp.databases.theory and elsewhere how a 'relational database' should be defined. Codd's original 1970 paper sketches the relational algebra as a query language of a relational database, though Codd is not very precise about what the query language exactly should be. The paper also mentions integrity constraints. Relations in the 1970 paper are defined as mathematical sets, that is, no duplicate rows in tables or any query results are allowed. Thus, no SQL database is Codd-1970-relational, because SQL allows duplicate rows. Codd's 12 rules, from about 1987, demand that a 'relational database' must satisfy several strict conditions. For example, any view 'theoretically updateable should be updateable with the data manipulation language'. Mainly because views and tables are pretty much the same in the theory. No existing database is even close to being Codd-12-relational. Indeed. On the other hand, for example, Elmasri and Navathe in their university textbook define a 'relational database' less strictly, and mention Microsoft Access and Oracle as examples of an 'RDBMS'. Thus, MySQL apparently is Elmasri-Navathe-relational, but not Codd-1970-relational. The same holds for DB2, Oracle, and MS SQL Server. I guess it also comes down to what you're used to work with. If you're used to using domains, check constraints etc, you might consider any database engine that doesn't have these, a lesser database engine. Luckily - as said before - things are improving. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Begging to differ, no vendor that I'm aware of claims to be compliant with a paper or textbook. They tend to comply with an adopted standard such as: ANSI/ISO/IEC 9075-1(through 5):1999 ISO/IEC 9075-1(through 5):1999 Yes, a strange thing too IMHO. The SQL standard defines a so-called standard for accessing (relational) data. But it doesn't define a relational database engine - it defines a way to access data it and partly based on the relational theory. Collectively known as SQL:1999. While I'd offer that MySQL *is* a relational database, (even though I'm very new to the environment); I think there's some ways to go before MySQL has core compatibility with SQL:1999. That's what's more important to me, the adoption of accepted standards. Ha, I beg to differ when it comes to accepted standard here... it might be some kind of standard, but the implementations are almost always different. And I believe the SQL standard has gone wy beyond it by defining triggers and procedures as well. Then again - if the (PSQL) language would be standard, you would be able to switch engines pretty quick :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Hi, If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. First of all ... Relational Database Engine - not relational database. :-) (this may sound like nitpicking to you, but a database is NOT a database engine) I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. You are very right here. I for sure wouldn't buy Oracle if I wouldn't need it and MySQL is capable of lots of things - no doubt there. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? To become at least a bit relational, a database engine should be able to enforce integrity. Now, MySQL - by itself - cannot do that. Yes, InnoDB can do referential integrity - that's a good start, albeit not enough. Check constraints, anyone? Domains, anyone? Views and updateable views? And WHY do we have to care about table-types? Isn't this a physical thingy? And there's probably more - some of which I don't understand... As said before - MySQL is very suited for lots and lots of types of applications and is improving a lot. Heck, that's the reason why I will be supporting MySQL in Database Workbench :-) Oh, and John, SearchDatabase or Google isn't always right :-) http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci212885,00.html (actually, the SearchDatabase site has a lot of wrong stuff - XML? :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)]
---BeginMessage--- Well, my database should be able to check some entry data and decide if these data will be accepted or rejected in a table that records all the received data. For example: I'll have a table 1 that contains a list of all the equipments that can send information to the database. The table 2 that records the data sent should check whether the data is coming from a valid equipment or not. If the table1 just mention 'machine 1' and 'machine 2', some data coming from 'machine 3' should be rejected. Juliana bluejack wrote: 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] ---End Message--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Is MySQL Relational? (was: Foreigner keys in MySQL?)
so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003, Jon Frisby wrote: No. Codd's rules defining what is a relational database are more specific than relational algebra can express. Essentially the requirements to be a relational database are a superset of the operations defined by relational algebra. -JF -Original Message- From: Saqib Ali [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
On Tue, 28 Oct 2003 11:56:54 -0800, Jon Frisby [EMAIL PROTECTED] wrote: MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. Some standard or another? You make it sound as if there is no definitive answer to the question of what constitutes a relational database. That is absolutely not true. We need only look to the I do enjoy these sorts of discussion, because they help me improve my own understanding of both the philosophies and the technologies in question. So thank you for your lengthy and informative reply. individual who created the concept of the relational database: E.F. Codd. Since he invented the concept of the relational database (way back in 1970), his word is definitive as to what constitutes a relational database and what does not. It is important to take a deep breath and realize that when someone asks Is MySQL a Relational Database they are probably *not* asking whether it comprehensively and successfully implements the inventor's requirements, however definitive. The probable question is, is it this *kind* of database (engine) or is it something else? I think we are all in agreement that MySQL does not perfectly implement the concept of a relational database, but that its inspiration is the relational model and not some other model. I think the original questioner should be clear on that now, as well. No existing RDBMS is even close to full compliance. Oracle would rate about 60% compliance. Others are lower except Ingres and FirstSQL which rate about 65% compliance. A statement like this pretty much nails the lid of the coffin down on the value of the inventor's definitive requirements. I believe in real world applications, rather than platonic ideals. However fabulous the original idea is, if *nobody* wants to implement it, then we might as well be talking about time travel or something. Pure speculation. By rule #2, MySQL (and most other RDBMSs) have already fallen down: Any RDBMS that allows you to define a table without a unique key of some sort fails rule #2. I actually think this is more controversial than your next example, because it leaves the power in the hands of the developers. Although the RDBMS doesn't force the database designer into correct behavior, it allows it. Rule #3 is somewhat more controversial. It requires at least two different kinds of null. Codd referred to them as A-mark, and I-mark: This is a far more interesting point because it is a feature that would be very useful, and which I have never seen implemented. I would sooner see RDBMS software implement this than conform to rule #2. So basically, MySQL complies with about 5 of the 13 basic requirements for a relational DB. And given that the most compliant RDBMS is only hitting two or three more points, I think we all need to loosen our ties and agree that while the distinction between 'relational database' in common parlance and in theory is both important and interesting, MySQL is a relational database engine. But thanks for all the links. Great to have these resources. -bluejack -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Hi! For decades, people have debated in comp.databases.theory and elsewhere how a 'relational database' should be defined. Codd's original 1970 paper sketches the relational algebra as a query language of a relational database, though Codd is not very precise about what the query language exactly should be. The paper also mentions integrity constraints. Relations in the 1970 paper are defined as mathematical sets, that is, no duplicate rows in tables or any query results are allowed. Thus, no SQL database is Codd-1970-relational, because SQL allows duplicate rows. Codd's 12 rules, from about 1987, demand that a 'relational database' must satisfy several strict conditions. For example, any view 'theoretically updateable should be updateable with the data manipulation language'. No existing database is even close to being Codd-12-relational. On the other hand, for example, Elmasri and Navathe in their university textbook define a 'relational database' less strictly, and mention Microsoft Access and Oracle as examples of an 'RDBMS'. Thus, MySQL apparently is Elmasri-Navathe-relational, but not Codd-1970-relational. The same holds for DB2, Oracle, and MS SQL Server. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Saqib Ali [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, October 28, 2003 10:10 PM Subject: RE: Is MySQL Relational? (was: Foreigner keys in MySQL?) so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003, Jon Frisby wrote: No. Codd's rules defining what is a relational database are more specific than relational algebra can express. Essentially the requirements to be a relational database are a superset of the operations defined by relational algebra. -JF -Original Message- From: Saqib Ali [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack
RE: Is MySQL Relational? (was: Foreigner keys in MySQL?)
Begging to differ, no vendor that I'm aware of claims to be compliant with a paper or textbook. They tend to comply with an adopted standard such as: ANSI/ISO/IEC 9075-1(through 5):1999 ISO/IEC 9075-1(through 5):1999 Collectively known as SQL:1999. While I'd offer that MySQL *is* a relational database, (even though I'm very new to the environment); I think there's some ways to go before MySQL has core compatibility with SQL:1999. That's what's more important to me, the adoption of accepted standards. -- Michael Brando Senior Manager of Engineering Applied Biosystems 3833 North First Street San Jose, CA 95134-1701 -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 12:29 PM To: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) Hi! For decades, people have debated in comp.databases.theory and elsewhere how a 'relational database' should be defined. Codd's original 1970 paper sketches the relational algebra as a query language of a relational database, though Codd is not very precise about what the query language exactly should be. The paper also mentions integrity constraints. Relations in the 1970 paper are defined as mathematical sets, that is, no duplicate rows in tables or any query results are allowed. Thus, no SQL database is Codd-1970-relational, because SQL allows duplicate rows. Codd's 12 rules, from about 1987, demand that a 'relational database' must satisfy several strict conditions. For example, any view 'theoretically updateable should be updateable with the data manipulation language'. No existing database is even close to being Codd-12-relational. On the other hand, for example, Elmasri and Navathe in their university textbook define a 'relational database' less strictly, and mention Microsoft Access and Oracle as examples of an 'RDBMS'. Thus, MySQL apparently is Elmasri-Navathe-relational, but not Codd-1970-relational. The same holds for DB2, Oracle, and MS SQL Server. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Saqib Ali [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, October 28, 2003 10:10 PM Subject: RE: Is MySQL Relational? (was: Foreigner keys in MySQL?) so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003, Jon Frisby wrote: No. Codd's rules defining what is a relational database are more specific than relational algebra can express. Essentially the requirements to be a relational database are a superset of the operations defined by relational algebra. -JF -Original Message- From: Saqib Ali [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether
Re: Is MySQL Relational? (was: Foreigner keys in MySQL?)
so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib, there are multiple relational algebras. No db fully implements Codd's specs. The degree to which a db like MySQL implement's Codd's rules and algebra is just one arbitrary measure of 'relationality'. We have to decide which features we want, and which we can do without. PB
