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."
Celko is misquoting Darwin in saying that "The idea that you will always know everything is arrogant".
t o.web/Missing-info-without-nulls.pdf
Date/Darwin/Pascal propose that you codify what you don't know (so to speak). Read their proposed solution here:
http://www.hughdarwen.freeola.com/TheThirdManifes
And yes, XML DBMS are a throwback to IBM IMS and other hierarchical DBMS products. Anyone who has ever used a hierarchical DBMS will tell you that there are some pretty non-trivial problems that you cannot work around due to their hierarchical data model, yet XML DBMS proponents propose we go back to that old, inflexible system!
Thanks,
--
Matt
Drill baby drill - on Mars
Here is a history of SQL. I wanted to check the article's facts. Also, I was curious... History of SQL
No.
XML is a file format, it has nothing to do with objects, no more than HTML does, which is not at all.
However to counter your claim that XML is hard to represent in a relational database. Uhm. No, it's not.
XML consists (simplifying) of elements and attributes, elements may be nested.
A generic mapping to a relational database is that elements correspond to the entity tables, attributes correspond to columns in those tables, and the nesting of elements is modelled as a foreign key in the child entity records.
Whats so hard?
NZ Electronics Enthusiasts: Check out my Trade Me Listings
From the article:
It has been quite obvious that the designers of SQL had little understanding of data fundamentals in general, and the relational model in particular
Gimme a break. Love it or hate it, SQL is an amazingly powerful way to work with arbitrarily complex data sets. Need proof? It is the backbone of nearly every non-toy scale data storage project. No amount of psuedo-academic argument can make irrellavent the fact that it works.
Everybody goes through a phase where they bitch about SQL. So did I. And I built a clever OO DataModel module that abstracted it into pretty heirarchies and all sorts of clever crap. Then I tried actually building systems with it and realized I was better off with ugly ol' SQL.
I've been part of too many projects where people pulled out the UML books in favor of a decent First Normal Form DB design and led the team down the tubes.
I'm not saying these other methods don't have their place -- they do. But they aren't going to displace SQL because it has it's place also. And it's place isn't theoretical, it's been practically demonstrated a million times.
Cheers.
Readers interested in what Pascal and Date have to say may be interested in visiting Database Debunkings. It has lots of articles written by one or both of them.
Personally, I recommend the articles written by Date because they are clearly, concisely, accurately, and calmly written. Pascal's tend to turn into a rant, which I wouldn't mind but he always seems to refer to his books rather than give a detailed justification of his arguments.
As a rant, this article does a great job. But here's what is missing - what the heck is he talking about? Everything he says is liberally sprinkled with statements telling us these things are self-evident, when they are anything but. He is constantly is referring to how this will clearly show that, or pointing out that this proves this or that later on, but never gets there.
Can anyone summarize what is being said here in some sort of logical way? Because I'm confused. I see the title, I see no information supporting the title (unless, perhaps, I was to do the research myself).
SQL is meant for a relational database. XML is a hiearchial database... have you never worked on a project where your manager HAD to convert a projects database in XML because it was the new shiny buzzword? It NEVER works out very well because they are logically different. The same will go for SQL->XQuery.
"When life gives you lemons, don't make lemonade. Make life take the lemons back!" -- Cave Johnson
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.
Try to model anything moderately complex with XML, where things have many to many relationships with each-other. Nesting becomes impossible.
A legacy application is one that works. And the same can be said of SQL. Actually XML works too.
It's important to understand what XML is replacing - binary or proprietary interfaces. This means an acceptable tradeoff between human readability (a hugely underrated requirement of a structured data format) and efficiency.
An example is EDI vs XML. EDI "efficiency" accrues only to the intermediary that invented the means to setup trading relationships across their proprietary network. XML uses the end users' bandwidth but it simultaneously eliminates the intermediary completely. This single fact saves literally millions in kilocharacter and storage charges.
SQL is what my old compsci prof would call opportunity-cost efficient. It's quick and can be implemented a number of ways - it's a data query framework, not a requirement for databases. There may be better ways to do it but you'll have a tradeoff somewhere.
And if someone offers something better, then when they've finished telling you how good the new way is, wait and listen for the inevitable sales pitch.
"It's not your information. It's information about you" - John Ford, Vice President, Equifax
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.
This quote needs to be placed toward the beginning of the Grand Encyclopedia of Intellectual Arrogance. Let's see, you have flat tables with a defined primary key and you form relations between these flat tables.
I do agree that SQL is not the best possible query language, but it succeeds where the other languages fail, it is easy for people to grasp and manipulate. Likewise, HTML has many faults. Plain HTML is still the preferred choice of most web designers because it is easy to learn and write.
Personally, I think the primary intellectual impulse is to add convolution to simple processes. There will never be an end to the stream of blither about how nulls cannot exist, and anyone who simply uses an sequence counter as a primary key is the devil incarnate. HTML and SQL have two things that almost all the stuff coming from arrogant snits like this author lack. They were designed by people who were actually doing stuff.
This quote needs a position in the library of intellectual arrogance as well:
A snit crassly dismisses several millenia of literature because it is unstructured.
Quite frankly, meaning and structure are independent of each other. It is possible to find meaning in things with radically different structures. It is true that there is a correlation between structure and the ability to communicate meaning, but a healthy mind can find meanings in things that have not been normalized.
Likewise, you can have meaningless garbage in relational databases. A case in point is the large number of fake web sites that do things like join the FIPS database to product names so that they can have millions of pages that show up in search engines. Likewise, we see academician filling volume after volume of publications with meaningless tripe.
I know some people who do in some cases, but I wouldn't exactly call that a standard procedure. Or call those people DB designers for that matter. 'Cause that is NOT database design.
You design a DB best with a pen and a large sheet of paper. Or some drawing tool your extremely good at.
SQL is the language you feed you results into the box so it builds a more or less representative imprint of the abstract reality you've designed. Which can be as relational as you want it to - as long as it meets the physical constraints of non-abstract reality. As soon as you put it onto a computer, you'll have to cut corners. That's the difference between a database _model_ and a database _implementation_. That takes stuff into account like DB load, DB Server Features and data types.
Types for instance - somewhat relevant when dealing with DB Servers and SQL - are a thing you don't want to touch with a ten-foot pole when designing a _model_.
I'm suprised a supposedly db expert guy get's all worked up about this and doesn't seem to be able to keep apples and pears apart.
Anyone initially designing a non-trivial DB with SQL and - on top of that - bitchering about this DB language not being rational deserves a clobbering.
My 2 cents.
We suffer more in our imagination than in reality. - Seneca
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 problem is that most "Relational Database Management Systems" only represent one type of "relationship", that being "the table".
This, in turn, means that every operator (programmer, statement, etc) on the database must _individually_ "already understand" all the relationships that lie outside "the table" before they can act on the data at any significant scope.
That is, you, the programmer or operator must know, from some source besides the RDBMS itself, how the different tables work with eachother.
In simpler SQL-biased terms, you have to know, before you start, what is "good" to put in your WHERE CLAUSE to make a join. And then the RDBMS query optimizer needs to guess how to satisfy your needs in something other than glacial time.
Consider a new verb "EXPOSE thing, thing, thing, thing, thing..." that would fish out of a database the one-or-more relationships between the things, and produce a table-looking vector of "tuples" that consist sets of actual values for those things. This is what the theoretical "perfect" RDBMS would do.
Given (somewhat denormal 8-):
Customer ID -> Customer Name
Customer ID -> Street Address
Customer ID -> Zip Code
Customer ID -> Purchase Order ID
Purchase Order ID -> Part Number, Quantity
One should be able to "EXPOSE Part Number, Zip Code" and have the database "know the relationship" and produce the correct vector of tuples.
But that doesnt happen.
Now all the people bleating about the Higherarchial databases and bad things from the past are doing this harping because they remember the bad-old-days when a database would maintian one tree-structured set of relationships like this. In the higherarchical model, you could basically do this EXPOSE operation, but only if you had, by dint of pre-knowledge, asked for things lying on one linear path through the tree. (* simplified for brevity).
In essence, SQL requires you, the programmer, to be in the business of making up relationships that should be in the data or schema structure but isn't.
A magically complete RDBMS would take a series of vectors of the form "Independent Datum (key)->Dependent Datum (value)" (where either side of the arrow could be a list of atomic values). The RDBMS would then assemble and maintian tables or linked lists or whatever with no exposure of SQL-esque "tables" and the accessors would be storage method agnostic. (That is, there would be no such thing as a FROM CLAUSE.)
For instance, in the above list of relationships, Customer ID, Customer Name, Street Address, and Zip Code *could* all live in a "table", or not, but you would never know that. But a better vector of
Customer ID -> Street Name, Building Number, Suite to replace "Customer ID -> Street Address" has a table-feel, but would not bias against "EXPOSE Street Name, Part Number".
The power of this comes from being able to do:
EXPOSE Building, Part Number, Quantity
Where Building == "Word Trade Center 2";
And have the RDBMS already know the sequence of relationships to get from Building to (address elements) to Customer ID, to Part Number without the programmer writing the three stage join across the "uninteresting middle tables".
(The above presumes you have a building relation that has Street Name, Street Number -> Building or some such.)
All the XML nonsense is nonsense *_BECAUSE_* the strict-nesting enclosure requirements of XML make it "naturally" become hierarchically organized. But data exists outside the single-inheretance strict parantage trees that the hierarchical model dictates.
The problem is that SQL got real popular and so the idea of structural inferrence got pared down to tables and Query-Like constraints on tables before anybody had a chance to formalize the idea of living, encoded relationships between arbitrarily stored datum. So we never really got a language or system that could "EXPOSE".
Innocent people shouldn't be forced to pay for inferior software development.
--"Code Complete" Microsoft Press
table: order ID, part number, quantity shipped
select part number, sum(quantity shiped), avg(quantity shipped)
Group By Part Number;
This works with NULLs in the column for quantity shipped on parts which have not yet been shipped. If you just use zero for "no shipment" then your average number will have no real value for answering questions like "how much do we spend shipping these parts, on average?" etc.
If you wan't to throw an exception you can throw the execption or not in your program. In that case you fetch the individual values and do the math yourself and the "that's not a number" that is caused by the null gives you the chance to throw your exception.
But since, in aggregate operations, your program isn't even interractin with the data yet, where would such an exception go?
What would the SQL syntax be communicating a list of results PLUS a list of exceptions to your program? Which order would things be processed in?
Your boolean analogy is also flawed. "You have stopped beating your wife?" is not a yes-or-no question because it carries a predicate around with it that you may not fulfill for serveral reasions (not married; you are hetrosexual female, so you don't have a "wife", you have never beaten your wife so you can't "stop" doing it; etc). There are a surprisingly large number of "real data" that nature. For those of you who have trouble abstracting this, the "real comparason matrix" is "True, False, and Not Applicable". NILL buys you "Not Applicable" so very cheaply.
In poin of fact, people who don't like NULL, usually because they don't understand its purpose and use, make a hell of a lot of work for themselves.
My current employer has a large database of test values that grows by huge numbers of elements each day. The programmer "didn't understand" NULLs (ro RDBMS' for that matter) and has "-" in fields that should be NULL.
Consequently we cannot aggregate. All of our client applications end up haveing to bulk-fetch whole table ranges and run through elaborate statistical routines full of conditionals; or do separate fetches with "field != '-'" in the where clause and run a concordance operation in ram after the repeated bulk fetches.
This costs bocup time and degrades the quality of the product.
You call "academic bullshit", I suspect you have never had to work the really large or significant data sets. I suspect that you don't ever ask the server-side to aggregate for you. And I suspect you have never worked time-critical transactions across a "slow" link.
You can't have. You think of "NULL" in terms of equality.
I will give you the "syntatic" point that "Where X = NULL" ought to be unversal. But, for instance, the cartesian nightmare of having "NULL == NULL" in a join is beyond idiotic.
Innocent people shouldn't be forced to pay for inferior software development.
--"Code Complete" Microsoft Press
Would you please enlighten me on how an XSLT job operating on a structure like above and showing all the employees who have a salary over 100k, having more than 20 directs and have travelled in the past 12 months look like? Also please include their manages names and phone numbers int he result. And what performance it would have?
] " > /> />
/>
/> />, Phone #<xsl:value-of select="@PhoneNumber" />, />
It sounds like you're kidding (or being sarcastic), and I'm not going to debug it (I'd need data, anyway.), but probably something like this:
<xsl:template match="/root/Employees/Employee[@Salary>100000
<xsl:variable name="EmployeeID" select="@EmployeeID"
<xsl:variable name="NumReports" select="count(/root/Employees/Employee[@ManagerID = $EmployeeID])"
<!--
XSLT sucks with dates, no matter what your data is arranged like. Storing dates as YYYYMMDD does
at least allow for comparisons. Also, there is no facility that I know of to retrieve the current
date, so I'm going to hard-code that.
-->
<xsl:variable name="RecentTravel" select="boolean(/root/Trips/Trip[@EmployeeID = $EmployeeID and @Date>=20030628 ])"
<xsl:if test="$RecentTravel and ($NumReports>20)" >
<xsl:variable name="ManagerID" select="@ManagerID"
<xsl:text>
Name: <xsl:value-of select="@Name"
Manager Name: <xsl:value-of select="/root/Employees/Employee[@EmployeeID = $ManagerID]/@Name"
</xsl:text>
</xsl:if>
</xsl:template>
And this approach seems to perform just fine in MSXSL, which I believe is DOM-based. It might give a SAX-based engine problems, because it jumps around so much.
Lest I give a false impression, I'm not suggesting that this sort of XML replace relational databases. The point is that a roughly relational layout is still a good approach, even when you need to be working in XML.
One thing that everyone should understand: even though Pascal, Date, etc... argue that SQL is a bad implementation of the relational model, they *still* agree that it provides value, and that it is miles better than its hierarchical database predecessors. Since it is their job(s) to provide a reasoned critique of the field, it is only natural for them to rigorously compare SQL to the goals of the relational model. Many of their complaints fall in these categories (although there is much more):
1. Does too much -- too many ways of doing the same thing, and too many unecessary operations that could be better done another way.
2. Overly complex -- the SQL 1999 standard was something like 1200 pages.
3. Allows programmer to circumvent relational integrity. Things like "hidden identifiers", pointers, etc...
4. Too wrapped up in implementation -- users must spend a lot of time understanding the physical storage, rather than focusing on queries in abstraction.
5. Many small inconsistencies in SQL itself
But the problems with SQL are impossible to judge if you only know SQL. It's like the people who used to ask what was wrong with a perfectly good typewriter that made people want to use a word processor. To any who are curious, I suggest you do some reading. The absolute best simple introductions for these problems are in a two online documents by Hugh Darwen at www.thethirdmanifesto.com. Look for "The Askew Wall", and "The Importance of Column Names".