Slashdot Mirror
SQL, XML, and the Relational Database Model
Kardamon writes "In an article on DBAzine, Fabian Pascal writes that SQL is not a good representation of the relational model, and is afraid the situation will get worse with XML and XQUERY. An overview of some of the reactions on the positions Pascal and also C.J. Date take on this issue is given in this article over at SearchDatabase.com by Sara Cushman."
Drill baby drill - on Mars
Basically what I take from this is that the table (e.g. SELECT * FROM foo) is simply a convenient logical representation of a stored relation. That is to say, foo can be implemented by the DBMS as a linked list, a tree, any data structure. The problem is that current SQL DBMS products do NOT do this and so we have the associated performance problems with normalized schemas. If the DBMS was truly a RDBMS then it could optimize the physical storage to improve performance.
The question is not "Are current SQL systems providing value" because certainly they are. They overthrew the hierarchic DBMS products for good reason - they were better. The real question is "Are the current SQL systems providing all the value they can". One can simply look at the wide array of DBMS offshoot products like XML DBMS, so-called "Multivalued DBMS" etc. to know that there are significant limitations of SQL products - ones which Date/Pascal/Darwin stress are not limitations of the Relational Model but merely these SQL products. To put words in their mouth, but I don't think they'd disagree at my summation, they'd suggest that if someone were to implement a Truly Relational Database Management System that these other products would quickly become obsolete.
Thanks,
--
Matt
I described a general vision for this sort of formalism in a prior slahsdot post. Suffice to say some progress has been made since then -- and work in other areas is starting to converge. There is much yet to be done.
Seastead this.
Most of the problems that I've seen with SQL commands that are more complex than they really should be are a result of mistaken assumptions made during the design phase of the database. As a result, extra tables get added late, and therefore create new "features" that code then has to be revised to take advantage of...
XML's going to be no better in this area. Mistakes made during the design phase will always come back to haunt while implementing and using the system. If a single query can't return the results desired, then that should have been thought of while designing the tables of the DB. Trying to get a query to specify "All things that are red" like Pascal suggests is only going to work if all objects implement the "color" property the same way. If somebody uses CMYK, somebody else uses RGB, and a third uses Play-Doh color names, it's still gonna be a mess that requires code to figure out who really matches whom.
I don't see how this "new model" fixes the real problems with working with SQL between databases that weren't designed to work with each other.
Of course XML is going to be hard to represent in a relational database.
Generic XML, sure, but you can always layout your XML in a relational style, like this:
<root>
<Table1>
<Table1Row Table1RowID="1"/>
<Table1Row Table1RowID="2"/>
</Table1>
<Table2>
<Table2Row Table2RowID="1" Table1RowID="1"/>
<Table2Row Table2RowID="2" Table1RowID="1"/>
</Table1>
</root>
Join support would be nice for simple jobs, but this works really well for more complicated jobs in XSLT. You can use a for-each on "/root/Table2/Table2Row", calling a template and drill down to "/root/Table1/Table1Row[@Table1RowID='$Table1ID'] within the template. This lets you use whatever hierarchy you want, rather than being stuck with the one hierarchy the original designer chose. Just like real relational databases.
> used to, but you can query on
Right on. Another nice thing about XPath is that it can be mapped onto other hierarchical structures. For example, the Java static analysis utility PMD uses XPath to query Java source code for problems. This XPath query checks for empty if statements:Good stuff; more XPath rules are here. Props to the Jaxen and SaxPath guys for their fine work!
The Army reading list
I'm not quite sure what you're getting at but if you've read his work he's never saying that the concept of a missing attribute is "bad" just that the "NULL" method of representing it as such is. He proposes several different methods in his book which I think I mentioned somewhere else.
If you're interested, it's Chapter 10 - "What You Don't Know Can Hurt You: Missing Information". Take a look at it for a more precise definition.
Thanks,
--
Matt
The author seems to have many serious misunderstandings about XML.
The fact is that in order for any data interchange to work, the parties must first agree on what data will be exchanged - semantics - and once they do that, there is no need to repeat the tags in each and every record/document being transmitted
A major point of XML is that the semantics should be explicit. If you don't repeat the tags, you reply purely on position to indicate meaning. This is a Bad Thing. For example, it does not allow sparse data in which non-default or null values can be excluded.
Inter-system data exchange requires an agreed-on efficient machine-readable delimited file format.
XML was designed to avoid the issue of 'yet another machine-readable format'. XML can be read reasonably efficiently, but always preserves meaning, ignoring the horrors of character sets and byte order. Compressed XML is a very efficient way to transmit data.
An important part of XML design was that it should allow information to be expressed in a way that is independent of the software that uses it. In this way, it has something in common with SQL. The point of 'human reading and writing' is that in the last resort, you still have you data! It also makes data transfer hugely easier to debug.
There is also a lot of confusion about the order of tags in XML. Its possible to specify in a schema or DTD that some tags are required and should be in a certain order, but its also possible to just not care about order. XML is neutral about this.
Use Open Office? unzip that .sxw or .sxc file, and what do you see? XML.
You probably use XML a lot without knowing it.
I've used Oracle and MS SQL Server in environments ranging from web sites to enterprise apps. The biggest problems I've seen are not usually problems with these DBMS; they are problems with the application.
I've mainly used MySQL for my large projects, and like you, those projects have not had more than several million entries. But my experience with SQL has been wonderful. I have spent several hours pounding my head on a desk trying to understand why something did or didn't work. But all my applications have run as smoothely and as quickly as one can expect.
I think XML is a step backward, if you're using it to replace a SQL Server database.
I agree - provided your application is best suited for SQL to begin with. There are some small problems where flexibility (XML) is more important than the great features SQL has to offer. I can't see a large number of XML databases at the core of huge operations though.
Slashdot Syndrome: the sudden, extreme urge to correct someone in order to validate one's self.
This guy is really smart. He takes issue with the design of SQL, and with some of the commonly-used relational-database concepts (such as the NULL). His criticisms here are valid, but his position seems to be extreme. With that said, he is absolutely right about XQuery/XPath/XQueryX++/whatever.
XML is great for data exchange. Schemas are a wonderful way to describe data. But it is completely inappropriate for querying. If you need to query XML, you should import it into a database then query it that way. The very design trade-offs made when building XML were to make it extensible, and hard to query. XPath is nice to have for simple dinky import scripts. But trying to build a whole hierarchical query language on top of XML is silly.
If someone wants to build a standardized hierarchical query language, that's great. Very few people use hierarchical databases and need such a language, but I wish them luck in that endeavor. But don't pretend that it is appropriate to use it on XML, or that it has anything to do with XML. XML is an interchange format. Leave it where it works well.
Sure, this is a nice syntax to describe one-off problem. Though for a database you'd hope that it is done once to transform it to something useful instead of 100 times per second.
To see the solution to almost all of the problems identified by Pascal, Date, Darwin and Codd please checkout Dataphor from Alphora. I am a user of their project and it is a marvel. It uses any vendor's DBMS for storage and heavy lifting, but adds untop of it true relational lanaguage and constructs. The system is so highly formalized that Dataphor provides *instant* derived user interfaces for all your tables in your existing database. After taking a few days to supply the missing information about your data that you currently cannot do in your DBMS the instant UI knows how to display all tables and forms in a logical workflow. It also handles all form validation and referencial integrity. If you change your database schema the UI is automatically updated because it is *derived*. It is not the result of some lame MS Access style wizard.
The consensus clearly is that the Relational Database is an unqualified success, and that is true. But don't confuse SQL and the Relational Database. SQL was an early hack intended for querying the RDB only.
Part of the problem is that the RDB was such as success that people do confuse the two. SQL, however, has shortcomings, that are addressed in the third manifesto.
This product http://www.alphora.com/ is a serious attempt ot address the shortcomings of SQL, with a new language for programming an RDB, and a new Query language.
SQL itself is not complete, and even the RDBs out there today break many of the rules as set out for the correct RDB.
Fabian Pascal is a bit of a nutter, you wouldn't ask him around to a dinner party, but he is correct.
XML is a badly thought out hack, but it works to an extent.
UML is hardly that, the U is really an act of hubris.
SQL can be improved, made more complete, and RDBs should be made to confrom more closely to the rules of what makes a real RDB.
That SQL is mostly a kludge?
Let me restructure that...
The experts who know what the heck the relational model is and is not argue that the language we use to query a specific type of relational-like database, that they call the SQL databases, the SQL language, has unsufficient representation power to represent the whole model, and hence can't be used to get the whole power of the model.
That's certainly interesting, and leaves us to ponder two things:
1) a) Just how much more power could we get? b) And at what cost?
2) What about alternatives, can we get that same power elsewhere, cheaper?
1)a) is beyond my mere predicate logic skills at this time.
1)b) The cost of a model for data storage, representation and management is directly linked to it's adaptability to the data you represent. The article mentions a lot of errors with NULLs(I remember thinking, while reading the article: a NULL was an attempt by the language developers to simulate an interrupt in a language that doesn't have any, this is of course, an oversimplification on my part, but considering stored procedures and triggers[SQL's own exceptions] weren't around yet, they sound like a good basis for further research.) There are a lot of other hidden "costs" for people who use a relational tool for not-quite-so-relational data, but that's not part of the cost of a relational language, per se.
2) Brings up a few notions: there are the types of databases relational databases replaced, like network databases, and there are attempted replacements, like object databases. There are also further possibilities that I will explore deeper later. Object databases can certainly be interesting, in the sense that by bundling data with code, you can have data that can handle itself, in the very basic sense that we humans apply it to ourselves. The problem is that we tend to have a very fuzzy, real-world view of such data, and can't work with it that easily(we are using computers to make data easier to work with, so if we had software that could handle real-world data complexity outside of our brains, we wouldn't be having this discussion). Object data is certainly very adept with data that has some broad commonalities, re-usable behaviours, and follows set-rules. We can call those business rules for now. Those business rules imply that a certain subset of "The Universe" interests us more than the rest, and follows predictable commonalities, making our mental models a lot less complex. On the other hand, object methodology is not always well understood, and the documentation and models it generates sometimes dwarf some production systems implemented to solve the same problems.
Now, at the beginning, Relational Systems were data-handling "toolkits" set to handle specific subsets of data, who also followed business rules.
That's interesting to my purpose, simply because I can envision, at this time(some vendors have similar concepts, but don't formalize them in any way), a new set of "toolkits" where the relational model is only one of many "toolsets" available.
Indeed, what is probably the most used sql-based server available(MySQL) has been lacking true relational functionality for most of its life, yet that doesn't make the tool less useful for most of its users. Future toolkits can inspire themselves by focusing on specific uses of technology to solve specific problems, and yet keep the SQL as a sort of security blanket, since that's where most of the training about databases(and indeed, usually most of the training about data, period, is in database classes and perhaps, some algorithmics classes)
After reading the linked articles about XML's weaknesses, though, I don't think it belongs into any toolkit of that nature. Simply because the tool that belongs in the toolkit is the "self-documenting data", and XML's weakness in that area is evidenced there. XML's early focus as a medium of e
The "arbitrary XML" part. You must have existing mappings set up to process the XML. New forms of XML thus require a great deal of work on the part of the DB developer
Um... I haven't read this article yet, but to respond to your comment.. um, no, not really. Here's a trivial possible setup to describe an arbitrary xml tree:
Table tagname: int tagname_id, varchar name
Table attrname: int attrname_id, varchar name
Table tag: int tag_id, tagname_id tagname, tag_id parent
Table attr: attrname_id attrname, varchar value, tag_id parent
What's so hard about that? I mean, given, you'd have to do a little bit more work to describe non-tag data or attributes with types other than string. You'd need an "order" column on the tag table if order of xml elements is significant. And it would kind of suck to get too much data out of this with SQL, you'd need either a rather large join or a big flurry of small sql selects (I assume that's what the article has to do with). But the relational model holds up just fine here. I mean, really, XML is just tagged heirarchal data, nothing that special...
Irritable, left-wing and possibly humorous bumper stickers and t-shirts
A database would hopefully aleady have track of "red things" and shouldn't have to traverse a tree full of other useless data.
:-( The upshot is that with processing and storage power at all time highs many (most?) databases have no need to concern themselves with running out of storage resources. More than enough resources for a multi-gigabyte database exist on even the smallest modern PC.
In fact, that's exactly what a good XML database will do. By indexing all the accessible fields, it's able to provide fast access to database wide queries like the one you suggested. Of course, these indexes do come at a cost of disk space and memory, so not everything is quite rosy in Denmark.
Javascript + Nintendo DSi = DSiCade
"XML is not meant to be read by humans, it's a data interchange format and thus meant to be read by machines" - Good heavens!
Someone saying something like that has really got a BS in BS! Or perhaps even worse: a PhD in BS.
XML is all about programmers being able to understand the data! Yes, because we are not anywhere near that nirvana of fully semantic systems that can (semi)automatically understand each other. NO! Programmers have to do the work to make the systems fit together and XML gives them the advantage that they do not have to reverse engineer another proprietary data format or dig into a horsepile of documentation. XML makes it easy to understand how to process the information at hand - without any extra work!
Also it's great format for storing small amounts of constantly changing data (like user preferences) cause it's extensible and with only a tiny bit of effort backward compatible as well.
Anyone trying to use XML for processing large amount of data (like data warehousing) is either nuts or doesn't give a damn about the speed or costs.
However anybody using XML for long term data storage is a genius since other "more efficent" formats will be obsolete ten years from now and the software that can read it can be extreamly difficult to obtain (anybody who has tried to decode data from some long gone accounting package from the '80-s knows what I am talking about).
So yes XML is self describing only to humans and that's the whole point of it. Formalizing data semantics is not the goal of XML, has never been and will never be, thats what we have RDF, RDFS, OWL and other nice initiatives from Semantic Web movement for.
He venerates the Founder. Finding a quote that supports your argument settles the matter. Codd the Wise avoided the errors that Chamberlin made; clearly the latter is the inferior intellect. And there's only a small core with the Founder. "We" are the true believers; all others are apostates and heretics.
Overstatement is a definite tell. Chamberlin's explanation of the difference between SQL and XML data is "unbelievable." The nesting argument is "ridiculous." Industry pronouncements are "incoherent." And most prominent of all is the cutting remark that's meaningless to anyone not in the know or already in agreement:
And I sure expect a polemicist to know enough about his art to understand when he's descended into self-parody.You can do some really neat tricks if every row is identified by a globally-unique integer (i.e. - not just for the table, but the whole database). For example, I can create a notes table that looks like this:
create table notes(rownum bigint primary key default nextval('rownums'), the_row bigint, notes text);
and have the_row be a foreign key to the whole rest of the database (unfortunately, I'm unaware of any DB that has direct support for this). Then, I can use this table to attach notes to ANY record in the whole database. You can use such generics very easily if you have a common format for a primary key.
In addition, only very rarely do I find a primary key that I know for absolute certain will be unique.
Engineering and the Ultimate
I think this whole dispute is not necessary. It's just a simple case of conflicting goals. One side wants "efficiency" and another wants "extensibility". What both sides miss is that real systems can't afford to choose just one of these, you have to get both. So you have to have just enough extensibility to allow reasonable extensions, and still attain reasonable levels of efficiency. But trying to get all of either thing will totally lose the game. So the XML camp is wrong to think that extensibility is everything (ever tried parsing XML in a real-time system?). And the "binary transfer format" camp is wrong to think efficiency is everything (ever tried to make two versions of your protocols to interoperate?).
-- Esa Pulkkinen
At the lowest layer, it was essentially a system which let you put together a SQL command incrementally. If you want to do an insert, you create an InsertCommand for the table, and then call setColumn() methods for the columns with the desired values. If you want to do an update, it is similar, but you can also add restriction clauses, either by simply requiring an exact match or with a method taking Operand, Relation, Operand. There was support for, essentially, "insert or update row" (like "create or replace view"), which would do the correct operation for the current state of the database without having you write each case and test them yourself. The support for "select" was somewhere in the middle of the range of functionality supported by different SQL extensions.
So it actually followed almost exactly the functionality provided by SQL, but it allowed you to put it together incrementally rather than with a string constant, so you could have a method on a different object which would take a query so far and a user and apply to the query the access restrictions for that user. Applicability to different problems should, therefore, by approximately the same as an average SQL implementation. It also had the advantage of being able to generate SQL suitable for different databases, so it had a wider applicabilty than completely portable SQL would.
Of course, the reporting engine was solving a specific problem in a clever way, using the framework. It is true that I extended the framework in a few ways (to include GROUP BY and nested queries) which writing the reporting engine, so you could argue that, until I used it to solve the third (or so) dissimilar problem, it wasn't yet sufficiently general. Still, it would regularly build up substantially more complex queries than either I or our DBA could have written in SQL.
A particular point I want to make about it is that it wasn't an OO databse system; it was an OO interface to queries in a relational database system. A lot of OO database systems in OO languages have been written, and these are generally less applicable to a large class of problems than relational databases. What most people write are persistance layers for objects, which are much less useful in a lot of situations.
Actually, many such things exist, RDF and Topic Maps XTM (http://shelter.nu/art-007.html) being two popular ones.
-- Home, James - it doesn't matter where that thing has b
No wonder you think SQL NULLs are the worst thing -- you can't even get the semantics right.
When either X or Y (or both) are NULL, then any expression evaluating it is NULL. Not false. Not true. NULL. Any SQL implementation that behaves differently is broken.
It's not the SQL standard's fault if your code's logic can't handle that case. Nor is it the standard's fault that you can't see fit to NOT USE the NULL feature when you don't want to (and any reasonable database even goes so far as to give you the option of making *sure* you don't use it -- that's what the NOT NULL declaration when defining a column is for).
Getting rid of NULL isn't going to help you when you suddenly discover that you really DO need to be able to represent "missing data" somehow.
Use 'slashdot stuff' in the subject line in any email you send me if you want to get past the spam filter.
It's one thing to say that either SQL or XQuery have their problems, because they do. It's quite another to say that SQL is bad because it doesn't live up to some arbitrary never-achieved (and perhaps unachievable) standard of relational purity that even Codd himself found superfluous. When Pascal does nothing but the latter, and in addition takes a dozen thoroughly unprofessional swipes at Chamberlin for having been involved in both SQL and XQuery, his professional jealousy is becomes thick enough to choke on. I wish he would, so we would be spared the incessant ranting of someone whose whole career has been marked by a lot of words and not a single deed to back them up.
Slashdot - News for Herds. Stuff that Splatters.
"The point of al those repeated tags is that machine time and bandwidth is very cheap now, but human time is not"
yeah, you tak a file that has 1000 linse, each line has 1024 characters, and 300 fields. Transmitted from Brazilla at 28K.
Now convert the to an XML format, and then try sending it. suddenly it take 10 times longer to get the data. Literally going from a couple of hours to all day. Not exactly cost effective.
XML is easy to read, if you know the format and context in which your reading it. It still needs to be designed, you still need to go to meetings, and you still need to create paperwork on the structure.
It's got its place, but it is not the end all, or even cloce for that matter.
The Kruger Dunning explains most post on
I work with a 1960's era throwback of a database that uses Unidata, (or U2), a derivitive of a Pick database. It uses multivalue attributes and sub-multivalue attributes in fields. The technology predates SQL and is dying (unlike BSD).
This is relevent because this dying technology inherently supports non-flat data. If you want to store a purchase order as a single record you can with as many line items and as many deliveries per line item as you like. Field size is limited only by available disk space.
After working with this non-flat data for a couple of years, I have concluded that anyone who wants to go back to it is nuts. It does work, you can run a business on it, but it is cumbersome.
Yes it's true that building up an object is easy because you can get a whole object in one read, but you end up with a bigger problem on the other side. When you want to look at your data differently you end up twisting and bending to get it.
For example if you wanted all the purchase orders due on a certain day, you could easily end up reading every record to find them. Yes, carefull indexing solves the problem and you can index the value of a multivalue list to get the po number, but flat tables and relations will solve the same problem more simply.
Some years ago, Xplain was a very good solution to the SQL problem (in a project i worked on for the Guadalajara government) Someone uses it today?
Thank you... yes. While Fabian Pascal may be arrogant at times, that is just a personal thing. What he says should be evaluated for whether it is true, not whether it is arrogant.
And yes, the relational model is indeed *much* more than just flat tables with keyed relationships. I thought this upon my first encouters with the relational model, but after actually studying the concepts, I realized that there is in fact something much deeper going on, and that it has a conceptual integrity that I only wish could exist in the most of the rest of computing.
Hmmm. You make some very good points. I may have to revise my position on XML databases. Indexing may certainly solve some of parsing problems inherent in XML, although this is likely to have other side effects. The database would need to maintain an internal index on each record/row/whatever, which might well add to the size of the database. I'm just thinking aloud here.
I also see the benefit of the flexibility of XML. Black boxes objects and all that. For example when you reach a user object, just pass in the contents of the tag to some sort of class factory (or an equivalent mechanism). As more information is added only the user object needs to change.
Arguably performance problems with XML may drop as more and more people implement systems using XML, something similar happened for RDBMSes, so long as there isn't some feature of XML that is inherent in its design that makes it perform poorly. Your comment on indexing counters what I believed was an XML performance killer.
Thanks for the comments, this has given me quite a bit to chew over. I think I need to do some further research on this and work it through.
meh
I was saying
1) a) Just how much more power could we get?
is beyond my mere predicate logic skills at this time.
Explaining just what power is missing from SQL to explain "the full power of relational models" in the context of the article, where it is said relational algebra can represent tree-like structures etc... is beyond me.
I am not interested in how hard relational is to use.
1)a) was referring to expressing, just what's missing in all the SQL servers, that could be reached if they were truly relational. In this case it has a lot more to do with how we define what you can AND on, a lot more than what operations you can use. I can understand AND just fine, I have a lot more problems with understanding how the experts say on one breath "it's not flat tables anymore" and on the next, no explanation on what operations you can use on a non-flat table... Say is the AND of a pair of arrays of floats the matrix product of the ands of the arrays? In what precision? Or are they just saying "the tables aren't flat, they can be linked" but you gotta restrict your operations on the virtual flat tables that overlay your more esoteric structures?
ADABAS D had arrays and such(it's now MySQL MaxDB I believe) inside sql-addressable tables, but that was a relatively poorly supported feature, once which wasn't well modeled( you can see from my examples why it wasn't well modeled, I think, it brings up questions about the domains of each operation)
We can certainly agree on the lousy marketer and packager, but you know what? The marketers won't help you write your complex queries either, Codd's theories just might(well again, they might not, but that's what the original post was about).
I'm sure I misunderstand more about predicate logic than you've understood in the time it took you to read these lines, but I was aware of those limitations, and kept my post in line with that.
You however chose to limit the relational model's power, to the logical operators it uses, without mentioning that the relational model also includes rules on how you can organize what objects you can apply those operators TO, which is a rather important distinction, and probably closer to weaknesses of SQL, as evidenced in the article anyways. Anyone can write relational algebra if you got scalar booleans, but if you got an array of complex numbers to multiply by a matrix of floats, and all that needs union(or any other type of join etc...) with userids, full names and addresses, it gets a little bit more fun.
Well "the rules of our system" also include just what we can NAND (or AND or XOR) ON, not just those operations. The definition domain of those operations is also important, that's why in SQL's fuzzy logic, ANDing with a NULL gives you a NULL.
Now if there's an impedence mismatch between sql and relational theory, it's probably on the definition domain of the operations(NULLs, non-scalars values, strings) and not on AND itself, which is pretty much a "simple" operator, at least when you limit yourself to scalar values like booleans, and logical predicates.
In your example, you still need part_number in table2 unless you allow only one part per order ID.
/sigh
The iterative join needed to then needed to print 10,000 invoices of average-of 20 parts per invoice gets too expensive to be reasonable.
It can be done, but it becomes anti-helpful.
Remember, normalization can go too far. By splitting the table you have now doubled your storage requiremet and multiplied the training, implementation, and execution costs since every operator (person or statement) and ad-hoc-query agency now needs to "know" that these two tables are needed to fulfil the "normally dense" query.
(That is, over the lifetime of the database, most orders will have been shipped so most quantity shipped datum will be present.)
With NULLs properly used, you can "SELECT COUNT(quantity_shipped)/COUNT(*)" on a single table and the answer will come from the traversal of one table or index. With two tables it would be a join.
If you actually look at the assembled wisdom of the field, you will note that you are supposed to STOP NORMALIZING when it stops helping and starts causing harm.
Innocent people shouldn't be forced to pay for inferior software development.
--"Code Complete" Microsoft Press