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."

Comments

Isn't XML semi-object oriented?

[Neil Blender]

Of course XML is going to be hard to represent in a relational database. Unless your tables are ( id, object text) and you pull out your XML and parse it.

Re:Isn't XML semi-object oriented?

[MojoRilla]

Try to model anything moderately complex with XML, where things have many to many relationships with each-other. Nesting becomes impossible.

Re:Isn't XML semi-object oriented?

[yintercept]

One does not know whether to laugh or cry. It has been quite obvious that the designers of SQL had little understanding of data fundamentals in general, and the relational model in particular

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:

Indeed, data/information management requires "some organizing principle"; that is, structure; anything "unstructured" -- and many in the industry promote XML for that purpose -- is not data, but meaningless random noise that carries no information.

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.

Re:Isn't XML semi-object oriented?

[halaloszto]

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? me

Re:Isn't XML semi-object oriented?

[AKAImBatman]

Ok, so you break down the XML by its structure. Now how do you do a valid query on it? Since you've completely dereferenced its structure and stuck it in a relational model, you've cut yourself off from the prospect of doing XPATH type queries. Instead, you'll need to make multiple (perhaps hundreds?) of passes at the table to reconstruct its data structure. XMLDBs don't have this problem. They deal with the XML in its natural form and are thus able to index, order, and query in that fashion.

As I said before, you can do many of the same things with an SQL database as you can with an XML database. That's not the point. The point is working with the data in a form that is natural to it and will provide the best results.

Re:Isn't XML semi-object oriented?

[leomekenkamp]

(...)is not data, but meaningless random noise that carries no information.

A snit crassly dismisses several millenia of literature because it is unstructured.

He was not talking about 'data for human consumption', but rather 'data for machine consumption'. As far as a machine is concerned, all literature carries no information, because a machine cannot extrapolate meaning from it like humans can.

Re:Isn't XML semi-object oriented?

[AKAImBatman]

"In fact, that's exactly what a good XML database will do. By indexing all the accessible fields"

In other words, it pretends to be a ralational database.

What is it with people lately? Everyone seems to want a piece of the trolling action?

For your information, a set of indexes no more makes a relational database than being in a garage makes you a car. Index data structures are an inherent way to improve the performance of all databases, whether they be ISAM, Hiearchical, Object, or Relational.

What makes a relational database relational is the way that the data is loosely intertwined to develop logical connections. In theory, these connections can complete various thoughts about a piece of data. ISAM files are merely indexed data, while Hierarchical (including XML) databases contain a complete "data packet" covering a particular concept. (e.g. Customer contains name, a set of addresses, and a set of phone numbers.)

My Two Cents

[Doesn't_Comment_Code]

I think relational SQL databases are just fine, easy enough to use, and serve their purpose very well. They DO take some serious thought when designing tables and queries - but we shouldn't be afraid to think. If your head hurts from SQL, keep on it for a couple more minutes and you'll probably have it! If your head hurts from SQL, you've tried thinking about it - and you still don't get it - you're probably in the wrong business. Complex information retrieval is complicated and sometimes difficult to understand.

On to the next part. XML serves its purpose very well. Although I wouldn't consider XML and SQL to serve the same problem sets equally well. There are certain situations where SQL is perfect. And there are other situations where XML is preferable. If you think of the two as two sides of the same coin, I think you're making a mistake. Likewise, you can't just flip between the two on a whim. Choose the format that's most suited to what you want to do and go forward.

It aint broke and don't need fixin'

Ignores the good points of SQL

[b0lt]

Easy to use, easy to debug, easy to understand, powerful. Isn't this good enough?

SQL sucks?

[localman]

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.

Re:SQL sucks?

[kpharmer]

Keep in mind that Fabian Pascal is generally considered a crackpot purist. He's been insisting for years that there is no such thing as a relational database product - since none implement a purely relational model.

However, he hasn't delivered an exaple of one, he hasn't clearly articulated the differences between his vision and the commercial options, and he apparently refuses to acknowledge that some problems in life fail to fit well into the relational model (hierarchies, networks, inheritance, etc).

Much of what he, Celko, and Date complained about were actually responses by vendors to adapt to the real world. They were somewhat successful - and now SQL can be used successfully to solve a far greater set of problems than Pascal has ever admitted exist.

A perfect example of this nonsense is there insistence that good indexing in a 3NF model outperforms denormalized data in a star schema. Sounds great, absolutely doesn't work. Across eighteen years working with relational databases I've never seen their suggestions work. Of course they have a response to this - the vendors should just "make the databases faster" - like it's fucking magic or something.

Of course, this isn't to say that he's wrong about xquery - trying to work with unbalanced networks or hierarchies in which the rules change change throughout the schema causes a few problems.

We already have extensive support for recursion & networks in the more powerful RDMBS (db2 & oracle for sure). But combining that with data structures supporting optional branching, complete lack of declarative constraints, optional rules, etc - sounds like something that will never work well.

And going back to the days in which you have to spend a day writing code against a hierarchical database in order to answer a simple question sucks.

Hmmm, haven't people gotten tired of the xml hype yet?

Re:SQL sucks?

[Tassach]

people pulled out the UML books in favor of a decent First Normal Form DB

I'm not sure I'd ever use the words "decent" and "First Normal Form" in the same sentence.

In 15+ years as a database developer, I've never seen a non-trivial problem which could be modelled correctly in 1NF. In my experience, 3NF is pretty universially considered to be the default level of normalization. Any decision to deviate from 3NF, either up or down, should be documented and backed up with a solid engineering case as to why it's necessary.

Re:SQL sucks?

[iabervon]

Actually, SQL is the interface to every non-toy scale data storage project. Non-toy databases are not SQL inside, but use SQL as the high-level language that they compile into their execution plans. SQL is the sole interface language supported by databases with efficient internal implementations, and therefore all of SQL's flaws are overwhelmed in the marketplace by the fact that it's what the good software uses. It's like the named.conf format, which is widely used, but only because it's what BIND uses, not because it's actually good.

If Oracle started supporting a more friendly API, people would use it instead. I've actually written (for a former company) an OO system for putting together relational queries. With this, I wrote a reporting engine such that you could define a report in an XML file, and it would build the right query and organize the data. It ran better than hand-written SQL queries, and was much easier to write. Of course, it used a SQL back-end to interface with Oracle, but it would have been more efficient if it didn't have to build up a huge query string and then have Oracle parse it. It also would have been nice if I weren't constrained by SQL's limitations. Why can't I have a temporary table with completely specified contents nested in a SELECT statement? It would greatly improve my implementation of "average(x), index(range) for y in range in list(ranges)" (This is just a gloss; the actual code was a bunch of Java applied at various points to a query object.)

Re:SQL sucks?

[Anonymous Coward]

Hi, glad to hear your app has held up. The issue is that every business rule always has an exception. Not every part has a number, not every part number is unique, not every person has a SSN, not every SSN is unique (it's true).

While you can deal with this sort of thing in your appliction, it becomes much more difficult if your missing/duplicate data happens to be physical database key. I wonder how often your users have to work around things like that with your system (by not entering things, or using 'fake' numbers).

Re:'scuse my ignorance but...

[afidel]

The problem with standards is their's so many to choose from. Or in the case of SQL every vendor seems to think that the standardized language is inadequate and yet they make no roads towards improving the standard. This leads to every vendor having their own superset of the language which makes maintainability in cross database projects exceedingly difficult and migration in applications that aren't designed for it incredibly difficult. As to fundamental flaws in the concepts around SQL I have yet to hear a concrete argument against it, mostly vague rantings from people who's ideas weren't chosen by the marketplace to serve real world needs.

Re:'scuse my ignorance but...

[Tarantolato]

One problem is that as a language it lacks elegance and is awkward to build large queries in. More deeply smug relational weenies insist that it does not properly model the relational algebra model pioneered by Ted Codd.

I'm not sufficiently versed in database theory to understand the technical side, but SQL certainly does feel to me like a non-optimal solution.

What the?!...

[__aagmrb7289]

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).

Re:'scuse my ignorance but...

[tanguyr]

What exactly is the problem with SQL?

Wouldn't call it a problem, but there just seems to be something about it that drives all the Oo fanboys up the wall - maybe it's the fact that they can't make nice UML diagrams of a query or something.

There nothing wrong with SQL and RDBMS - they've been around for years and they'll be around for years to come. I have this argument each and every day at work with people who seem to think that the solution to (hypothetical) "database bottlenecks" is to bury everything in a quarter of a million lines of EJB code and invest a king's ransom in application server licenses to run it on. Don't get me wrong: i've seen some real horrorshow coding with SQL mixed into code, but a bad coder will produce bad code in any language. Until then, SQL works. What more can you say?

Re:"NULLS are bad." quote

[molarmass192]

I skimmed that article and I totally agree. The author fails to mention is that the S in SQL is for STRUCTURED, as in structured (aka. relational) data. Hierachical databases are by definition unstructured (LDAP, SleepyCat, etc). So no shit SQL doesn't mesh well with XML dbs, it was never meant to.

Good grief

[Dracolytch]

I'm a pretty good software developer, but if someone doesn't explain to me what the argument is in plain english without extreme haughtiness, I'm going to write off this whole issue as a pissing contest.

~D

Rediculous

[TheRealMindChild]

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.

Re:Rediculous

[LurkerXXX]

Don't worry, they don't really understand it either. Guys like this bash and bash SQL for not 'truely' being relational (and it's not, but it's the best by far that we have), but they fail miserably at proposing any workable system that *is* truely relational. It's not at all a trivial task. They'll throw a lot of theoretical stuff at you, but never show you a working product that is a feasible replacement for SQL. Tossing SQL/relational stuff into XML is going to get ugly.

Re:Rediculous

[(negative video)]

...the SQL language retrieves information from SQL databases, not relational ones (the former, Chamberlin's own contribution) due to failure to understand the latter. ...

Makes me wish I understood a bit more, for it's all a bit confusing.

Fabian Pascal is smart and well-informed, but a zealot. Like all zealots he is willing to sacrifice anything and everything for his vision of technical purity.

One of his specific complaints is about SQL NULL values being "unrelational". As an example, a real-world designer might use the following table in a genealogy DB:

CREATE TABLE people (
person_id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
birth DATE NOT NULL,
death DATE
)

If a person hasn't died yet, then people.death would be NULL. Well that just isn't relational enough for our friend Pascal. Since relations can be used to express optional values, then by God they have to be:

CREATE TABLE people (
person_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
birth DATE NOT NULL
)

CREATE TABLE deaths (
person_id UNIQUE INTEGER REFERENCES people,
death_date DATE NOT NULL
)

It's pure, correctly formed, and worse than useless. It causes a profusion of tables: one for every optional value. It turns every simple query ("tell me useful stuff about this person") into a join ("find matching rows from several tables about this person"). The database server has to waste time enforcing deaths.person_id's pointless UNIQUE constraint. Cascading deletion has to be used to clean up deaths when a row is deleted in people.

The simple fact is that the world is full of optional, single-valued data. NULL-allowed columns express that data efficiently, without confusion, and without breakage. Community college database designers have no trouble using the convention productively. It may be a little inelegant, but it is pragmatic and balanced engineering. The only whining you hear is from zealots like Pascal who heap fire and fury on others, but never seem to deliver the mythic PerfectoRDBMS.

Peddling a better way?

[BillsPetMonkey]

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.

Re:Peddling a better way?

[rjamestaylor]

XML doesn't eliminate the intermediary. Eliminating the intermediary eliminates the intermediary.

I mean, one can kiss of the intermediary with CSV files, financial 5-bit data files, packed decimal data files, string-and-cans dictation systems, etc.

And one can sign up to use any number of intermediaries with their brand-new XML data files backed with excellent XSDs, etc.

The data file does not bear on the intermediary.

I don't have time here but I will postulate what anyone doing data integration with disparate parties knows: XML only solves the parsing questions; data meaning still requires conferences and/or tomes of explanatory text to explain beyond the grammar of XML, XSD, etc.

Automated data integration is not possible unless one side is willing to do all the coding to match a well-publicized protocol with a test server for honing the solution.
<rant>

• 
  In fact, I think XML, with its inherent repetition of "column" and "field" names (as in the repeated-for-each-tuple tag names) is a conspiracy amongst Intel, Western Digital and Level3 to sell more advanced processors, bigger disks and higher bandwidth allotments.
  
  Doubt me? Name one project having complicated two-way data interchange that did not require a face-to-face meeting between development teams where both sides used all current-state-of-the-art XML tech and tools. Just one.
  
  The only thing not needed to discuss is "how do you delimit fields containing your field delimiter." Other than that all the old questions are still valid (as to meaning and optional representation) and new ones exist (DTD?, XSD?, doctype? Version of XML? Order of data elements? Attributes? Whitespace handling? Characterset?).
  
  XML: Big Waste of Time (for data integration). Bigger waste of process cycles, bandwidth and disk storage.

</rant>

Re:'scuse my ignorance but...

[Xentax]

No kidding. This sounds like Andy griping that Linus' "school project" is an inferior kernel.

Sounds like a semantic argument to me; where the Rubber Meets The Road, Linux is the kernel of a variety of widely used, production-quality OS's, while Minix is an academic *model* (on purpose, to be sure, but a *model* rather than a useable-on-a-daily-basis kernel, nevertheless). Similary, claiming SQL is crappy for various academic/theoretical reasons doesn't change the fact that it's in wide use today, as a concrete solution to any number of million- and billion-dollar abstract problems.

So, if SQL is so bad, maybe they should stop cursing the darkness, and show us the light. In the meantime, people will use (and incrementally) improve the tools at hand to solve the problems at hand.

Right now, SQL-based database solutions are generally the best solutions for *real* data problems that we have to solve, from mySQL-driven personal webpages, to enterprise-grade databases powering major websites, business-to-business e-commerce, and everything in between.

Invent a better mousetrap, and the world will beat a path to your door. Criticizing the mousetrap as an inferior pest control device doesn't do much to keep the mice out...

Xentax

Re:"NULLS are bad." quote

[Chops]

While you're at it, check out this tripe (from the article):

The relational model is predicate logic applied to databases. Predicate logic is the real-world's two-valued logic (true/false) ... logic guarantees correctness -- defined as consistency -- of query results. It is to preserve logical correctness, therefore, that Codd's Information Principle requires that all information in relational databases be represented as values in relations. The term "NULL values" suggests that Chamberlin does not realize that part of the problem with NULLs is that they are not values -- indeed, they are supposed to be markers for the absence of values. Whatever a database table with NULLs is, neither is it a relation, nor do NULLs represent anything in the real world and, consequently, correctness and the rest of the relational benefits are lost.

Incidentally, "inapplicable values" are a red herring. They are an artifact of bad database design. There is only one kind of missing value -- unknown -- and as I demonstrate in the above-mentioned chapter, it can be handled relationally, without the huge problems of SQL's NULLs.

I read this and pretty much gave up getting anything of value out of this article -- I hadn't understood much that went before it, though my distrust of all things XML had led me to believe this guy might know what he's talking about.

If you removed NULLs from relational database design, people would reinvent them (poorly) -- probably by using IDs of -1 or 0, or IDs to a special magic "null" row, which I suspect is what he's talking about by "it can be handled relationally." To suggest that missing or inapplicable values are not part of "the real world" is so wrong it's... well... wrong. Anyone who's actually done database work (or programming work, for that matter) knows this.

Re:'scuse my ignorance but...

[sql*kitten]

there just seems to be something about it that drives all the Oo fanboys up the wall

Yeah, I know what you mean. These kids can't wrap their tiny minds around the following concepts:

• A table is not a class
  
• A row is not an object
  
• A column is not a property
  

Whenever I see a project gone horribly wrong, and the language is C++ or Java, the problem usually is the system architect didn't grok the above statements. They should be tattooed onto the forehead of every OO programmer, so when they're "pair programming" they can read it off each other.

solution to (hypothetical) "database bottlenecks" is to bury everything in a quarter of a million lines of EJB code

I get that too - then I show 'em the logs that show the database processor is mostly idle as it waits for their application to either request more data or finish working on what it's got!

Re:"NULLS are bad." quote

[ceswiedler]

SQL NULLs are the worst thing since unslicable bread. They break boolean logic. You would think that if (X = Y) is false, then (X != Y) would be true. With SQL, if either X or Y or both are NULL, then any expression evaluating it is false.

I understand the argument (NULL indicates no data--so you can't claim it's equal to anything). Academic bullshit. Anyone who's maintained code using SQL NULL semantics will agree. If you really want to claim that NULL is so much 'not a value' that you can't compare it to anything, then do it the man's way and throw a goddamn exception. Of course, anyone can see that doing so would make code which MIGHT encounter a null value even MORE difficult to maintain, so they came up with this 'any comparison to NULL is false' crap.

The easiest way to define NULL is that it's equal to another NULL value, but not equal to anything else. Then I don't need any special 'is null' clause either.

The very definition of b0rken.

Re:'scuse my ignorance but...

[LostCluster]

1) You can write a given query and number of different ways. This is not necessarily a SQL problem but due to this the query optimizers have to be enormously complex to handle complicated queries and by association you can have queries which describes two identical sets but have vastly different runtimes/costs.
Just because two queries return the same results today do not mean that they will continue to do so in the future. If a value that used to be bounded from 1 to 10 suddenly is declared to be allowed to be cranked to 11, then suddenly "equal or greater than 9" and "equal to 9 or equal to 10" will have gone from always returning the same results to now specifying different sets. Clearly, the more specific code will execute faster, but if an assumed boundry no longer holds in the future, the program will become obsoleted and require revision to the less specific version. This isn't a language-specific issue, it's just a problem that crops up whenever a computer program encounters a situation its designer wasn't expecting.

2) Little/No support for relational domains (e.g. complex data types)
Not a bug, it's a feature. The S in SQL is for "structure"... go hammer out your data into a structured format rather than a complex one and then come back.

3) Non-updateable views (partially due to duplicate handling and/or allowing relations with no primary key)
Totals will always be a non-updatable view. You can't change the number of objects you have without creating some new objects or chosing to get rid of some existing objects. Fields in a one-to-many relationship cannot be changed because to do so would be ambigious... do you want to create a new entry in the other table, or do you want to rename an existing entry in the other table. Go do what you meant to do, then refresh your view.

4) Weak support for complex integrity constraints (e.g. business rules)
That's more an issue for applications rather than databases. The program or user that's creating the query should know what's allowed by business rules, because if the database is going to refuse a query due to business rule violations, that query shouldn't have been offered to the database in the first place. Those errors should be trapped upstream before they get that far. SQL triggers for business rules should be a last line of defense, not something that should be regularly asked to function.

5) No support for entity sub/supertype relationships
Plenty of support, just not intrinsically. Just use a one-to-many relationship in your DB structure and go along your way.

6) Supports NULLs (Date/Pascal/Darwin do not like NULLs)
That's like trying to do math without a concept of zero. Sometimes, things just don't apply and we put "N/A" on the form and "NULL" in the database.

The Criterion here is no longer machine efficiency

[maverick97008]

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. Any agreed-upon delimited format will do, and the criterion here is efficiency,

WRONG!!!
The point of al those repeated tags is that machine time and bandwidth is very cheap now, but human time is not. The beauty of XML is "agreed-upon" is optional now. We can understand the data without a formal meeting to come to an agreement.

As a guy who has spent a significant portion of his life in meetings about data mapping between systems, I love XML because of how easy it is to read, understand, communicate about and map to the structures I need the data for.

Re:'scuse my ignorance but...

[iSwitched]

You seem to have been exposed to some relatively clueless, or at least inexperienced J2EE coders.

No java guy worth his or her salt thinks that all that EJB code is there to 'work around' database bottlenecks. Anyone with any experience knows that if you want pure perfromance, you let the database do its thing with nice optimized stored procedures in the dbs sql dialect du jour. Of the many reasons for choosing EJB, one is to abstract the database away enough that your application will run against any database, with no re-coding.

You see, the opposite end of the extreme example you proposed is all those monolithic DB apps I've seem where the data-access AND business logic are tied up in multitudes of stored procedures, creating a porting and upgrading nightmares, should you ever want to change platforms.

Sure, if you're creating proprietary solutions for internal corporate problem-solving, and you're pretty sure you'll be running on Oracle forever, then go for it.

If you need multiplatform, databse-agnostic apps, then I've been happy with Java for a while now. ...And as for that Kings Ransom? Might I suggest JBoss or Orion.

Never heard so much nonsense..

[wdavies]

ok, aside from efficiencies in implementation and writing, the main concern should be with the expressability.

Question - can you express the recursive ancestor relation in SQL? You can express a single relation such as grand parent, but not the full relation.

Reason? Because SQL is not full relational calculus. It is basically propositional calculus (actually I maybe slightly wrong, and it falls in a higher calculus than propositional).

Prolog for example is closer, but still not true relational calculus (I forget whats missing).

There's a reason for SQL's limitations, and that's decidability - guaranteeing that the query will terminate... (admittedly in theory, and you can write some pretty horrendous statements).

Anyway, just wanted to get that off my chest. Doesn't say much about where XML falls in the scheme of thing - I guess strictlt speaking it doesn't - its just a layout. In fact, to compare XML and SQL is a complete misunderstanding. Its XSLT which should be compared.

Re:'scuse my ignorance but...

[shirai]

The one bone I have to pick about SQL is there is no standard way of retrieving the value of an Identity, Counter, AutoIncrement (or whatever your database calls it) field after inserting a record. This is brain dead.

Every SQL dialect has a special, unique way of getting the value in this field. Many (not all) things in SQL can be written to be compatible with virtually every database (if you are very very careful) except this one important thing. I know you can requery the database with all the fields or insert a unique ID manually but this seems like a horrible hack to me.

Who the f*ck designs databases in SQL anyway?

[Qbertino]

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.

Re:"NULLS are bad." quote

[gfody]

nulls shouldn't be used in the case where this behavior would be unwanted. a null isn't a blank value, it's a missing value. implementing a missing value relationaly would mean absence of a reference (the null is the result of a left outer join or simply isn't returned).

Re:"NULLS are bad." quote

[Anonymous Coward]

...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 ...

I take it you've never used

CREATE INDEX fooindex ON foo (fookey);

.Saying that the data is not indexed is a red herring. Lack of indexing is a limitation of your understanding of your RDBMS, not SQL or the relational model.

The concern of OODBMS proponents is not performance, it's data modeling. OODBMS proponents assert that by useing on object model they can easier represent complex real-world systems. RDBMS proponents counter that the lack of structure of an OODBMS leads to lack of representation of the semantics and to problems with searching. RDBMS' and the relational model excel at representing highly structured data and efficiently searching it. OODBMS' and the object model excel at representing complex structures without a high degree of structure, or where the structure needs to be flexible.

Which should you use? well that depends... I would not consider an RDBMS for a MMORPG. On the other hand, I would not consider an OODBMS for an accounting system.

Re:"NULLS are bad." quote

[CoughDropAddict]

I cannot think of a situation where I would want NULL=NULL to be true. I'm no SQL guru and I'm a bit rusty, but:

1. the only situation I can think of where you are comparing two values, where neither is a literal, is when you are joining on a pair of columns

2. if one of the values *is* a literal, there is no reason not to write "IS NULL" instead of "=NULL"

3. if you *are* joining on a pair of columns, letting "NULL=NULL" would not make any sense (you would get the cartesian product of all rows from both tables that have NULL in those columns)

4. also, if you are joining on a pair of columns, one of those should be a primary key, and you shouldn't have NULL values in a primary key column.

Can you illustrate a situation where you want NULL=NULL to be true? Perhaps the situation arises in procedural SQL (stored procedures and such) that I am not as familar with.

Theory vs. practice

[14erCleaner]

I'm not really much into data modelling theory and such, but I do have two perspectives from which to view this dispute:

I've done several years of application programming using SQL

I've also implemented the (XQuery-derived) query processing module for a native XML database

In my former life as a application programmer, I really liked SQL. It allowed some pretty complicated computation to be done in the query, and very concisely in many cases compared to doing the same thing in, say, C++. For example, things like grouping are very nice for many application purposes.

In my current job, I'm hoping to create an XML query language that supports the same sort of capabilities as SQL. Our XML query language implementation has decent path/predicate, sorting, and output structuring capabilities, mostly derived from earlier drafts of XQuery.

My feeling about XQuery 1.0 is that it is extremely bloated. XML seems really simple; querying it shouldn't be all that complicated, should it? But the XQuery committee has created several hundred pages of specifications for the new language. This seems excessive, to say the least. We basically have implemented a subset of an earlier version (with paths, predicates, sorting, XML construction, a few dozen functions), and stopped tracking what they were doing. This is kind of unfortunate, but we really don't have the resources to support his behemoth in all its awesome grandeur.

We just want a language that lets programmers efficiently access our database. I think we're on the right track. I'm not at all sure that XQuery is going to wind up as a long-term success, partly because of its bloat factor.

My favorite illustration of the XQuery bloat is this: early versions (up to about April 2002) of the XQuery language description contained this sentence in the introduction:
It is designed to be a small, easily implementable language in which queries are concise and easily understood.

Starting in August 2002, this was changed to:
It is designed to be a language in which queries are concise and easily understood.

The "small, easily implementable" part got smothered up by the avalanche of features they were adding.

Re:'scuse my ignorance but...

[D-Cypell]

Here's one that'll make you howl: "sorting is a presentation-tier concern"

Largely it is. If the user hits a table header to sort on the selected row, are we are supposed go back to the database and do a different 'order by'?... I dont think so!

If the sort is required purely to provide the user with a list in 'alphabetical' order then sorting in the presentation tier tends to be smarter because it reduces the 'bug-space'. That data generally passes through several levels of indirection, at any time someone may decide to replace an ordered collection with an unordered one and by the time it reaches the screen... it all out of sequence. Also, its very possible that while the data needs to be sorted to give users that 'fuzzy feeling' the same API can be used to provide a SOAP/XML-RPC/CORBA interface that doesnt require sorting (or rather, let the consumer decide). Why do this, fairly expensive, operation on a tier that doesnt always require that it is done?

If sorting is required in the middle-tier it is usually due to some search algorithm or something. In this case, I prefer to put the sort with the search so that it is clear. Where I have worked with pure SQL (tend to use ORM tools now) I like to put the SQL in a seperate repository that the DBA's can tweak without recompliation. That 'order by' gets dropped pretty quick when the PHB is complaining about DB performance... and WHOOPS... really weird bug in the search code.

So it seems to me, that when you factor in the real world issues around using the database as a sorting tool.... its not quite so 'howl-worthy'.

Re:'scuse my ignorance but...

[Anonymous Coward]

Argh. No, a row is a truth predicate. The correct mapping is object-per-table, each row representing a possible state of the object. As an _optimisation_, objects with identical attributes ("classes of objects") can be stored in the same table - but if you find yourself doing one-row-per-object, I _guarantee_ your data model will suck for later ad-hoc relational analysis. It's a self-fulfiling prophecy in that case that "RDBMS Sux" - the bit that's missing is "when used by an OO weenie who doesn't understand that RDBMS is a persistent predicate logic rulebase".

You Are Missing the point of NULLs entirly

[IBitOBear]

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.

Re:"NULLS are bad." quote

[tsarin]

With SQL, if either X or Y or both are NULL, then any expression evaluating it is false.

Actually, if if any members of the set (X, Y) are NULL, any comparison involving those members evaluates to NULL, not false.

The easiest way to define NULL is that it's equal to another NULL value, but not equal to anything else. Then I don't need any special 'is null' clause either.

No, the easiest way to define NULL is that you don't know what it is; it's unknown. It can't be equal, not equal, whatever to anything, even another NULL, for that very reason.

An example: I'm thinking of a number. Tell me, is it greater than 13? Unknown. Less? Unknown. &c. Try

select (NULL = NULL)::boolean;

(PostgreSQL syntax; substitute however it works for your favorite [O]RDBMS). If it's not brain-damaged, you'll get NULL. (Even MS SQL gets this one right, folks.)

The very definition of "b0rken" is people railing -- incorrectly, even -- against something they don't understand. The very definition of irony is when the thing in question is SQL NULL.

Re:XML Misunderstandings

[Decaff]

The author is actually actually arguing that tags are syntactic, not semantic.

He seems to be not sure what he is arguing, as far as I can tell. After all, XML is fundamentally a semantic markup, using HTML/SGML syntax. He seems to be arguing that sematics can be indicated by position, and that tags are unecessary and Any agree-upon delimited format will do. I strongly disagree with this.

You can use XML as a syntactical markup, but its rather a waste:

<record><data>1</data><data>Surname</data></reco rd >

This is just silly, and is no better than CSV, but seems to be the kind of thing the author is arguing for; positional markup in some format.

Its far better to have an XML schema that includes semantics:

<record>
<data type="int" name="id">1</data>
<data type="String" name="Surname">Surname</data>
</record>

or even better
<record>
<id>1</id>
<surname>Surname</surname>
</record>

If you do something like this, semantics ARE included in the tags. With the appropriate namespaces and schema/DTD, you can mark up pretty clearly what the data means in a way that just about anyone can understand.

Re:"NULLS are bad." quote

[Tony-A]

They simply believe that "nulls" are a hack around the real need of supporting full value domains.

Thanks, makes sense.
However, the required complexity required to handle "the real need of supporting full value domains" has to be sufficient to handle all possible reasons and scenarios where you do not have a simple value. You might get close with a Lisp back-end, but this is heading too much like needing a supercomputer to hand one number in an el-cheapo caculator.

Nulls are a hack. A nice simple hack. It is possible to build bigger and more complicated hacks and take things a wee bit further, but the further the screwier I'd expect.

Re:NULLs and Normalization

[PatientZero]

Now that I think of it, I suppose that first normal form wouldn't be violated as long as you ALWAYS separated out the nullable field. Considering the complexity this adds, it's difficult to say whether it is worth it or not.

Exactly! I've always been in the position of needing to get stuff done, and that has meant focusing on implementation at some point. While some people get all weird with NULL values, I simply look at it as a workable solution.

I would never fully normalize to the point above, but the discussion is helpful in clarifying the logical design. Who knows, maybe we'll get a "truly" relational DBMS someday. Until then, I'll keep the NULL, thanks! ;)

The problems of preemptive data typing

[ynotds]

(I just used my mod points in another thread, so I gotta hope other mods recognise the parent post.)

In the world of self taught dabblers, NULL is not well enough understood to be expected to do anything more than cause the kind of problems you alude to with the likes of '-' to (partially) imply what NULL should be used for.

SQL has to coexist with other components where an empty string and a numeric zero are assumed null and treated accordingly, the quantity shipped example you give being just as easy to understand and implement with zero meaning not shipped as with a separate null (just add "where quanity shipped > 0").

There are also several possible reasons for a data value to be left NULL or undef, not all of which are mutually exclusive. Is it "not yet", "not known" or "not applicable"? In the real world we sometimes need to pair a status enum and a (numeric or string) value column to properly represent a single logical datum which needs to sometimes take state values not sensibly representable by numbers or strings.

We used to use a string of 9s in a numeric key field to represent end of data and even today Perl's DBI interface uses the 0E0 kludge to represent a "true" zero.

Re:"NULLS are bad." quote

[doom]

Date/Darwin/Pascal propose that you codify what you don't know (so to speak). Read their proposed solution here:

Well, I read it and essentially what they're saying is that instead of having a single NULL that means "we don't have the info and we don't know why", they recommend having lots of single column tables pointing at the missing values, one table for each reason/excuse. So instead of having NULLs in the salary field, you've got tables that explain "No salary because the guy is on commision", "No salary because the guy is unemployed", "No salary because it is regarded as confidential", and so on.

What strikes me about this notion (outside of it's general clunkiness) is that it seems to assume that you will always know why you don't know. In reality, I think systems like this would sprout lots of tables called "unknown_value_for_unknown_reasons".

Pascal should go into Theology

[Precipitous]

Pascal's entire line of argument fails to explain why I should care. I RTFA'd. I even read some of the links. This is a topic near and dear to me, as I fight for better design, better technologies at work against various technology and platform zealots.

I'd argue that if you have a thesis or hypothesis, you need to make a testable predication and test it against the real world. That's the gist of science. Pascal tests XML and SQL against the Gospel of Formal Data Model's according to Codd. Pascal's arguments are theological, not practical, not scientific.

I want to know how XQuery will or will not make my job of designing and implementing solutions that save my company money. How will the problems of XQuery, or the blasphemous SQL implementations cause me problems that cost time? How do these deviations for orthodoxy really make code harder to write, harder to maintain, more prone to error? These are the relevant questions. These are issues that can also be tested against production scenarios.

I don't give a hoot about how well XML falls into Codd's formal model. I care whether or not it saves me time. I'll happily accept that normalization, taken not too literally, does saves me time. XML also saves me lots of time: makes a great prototype to store data in (gasp!) before I implement the database layer. Even for a somewhat complex schema, I can blast out a small XML document in a few minutes in my text editor of choice, and test out how the components use the data that they store. Sometimes, for small data sets such as configuration information, I even leave it in XML for years before finding a compelling reason to deal with an RDBMS. Web services (the horror!) have made the middle tier much more accessible - I can drive access to a service based on policy now, rather than to whom I can distribute the damn DLL and it's dependencies. None of this is perfect, but XML works where it makes a difference to me.

In short, regardless of how well or poorly Pascal addresses his questions, they are the wrong questions addressed in the wrong way. He should go into theology or critique Chinese literature and leave us working folks alone.

Put these arguments in perspective, please

[rycamor]

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".

Re:"NULLS are bad." quote

[Wastl]

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.

True. However, this encoding is usually very inconvenient (consider representing an HTML document or a structured piece of literature in this manner).

Besides this, nested structures are at least as logical as flat structures (I continue to call them flat because they *are*). Relational database logic is merely a fragment of first order predicate logic, one that is restricted to - guess what - flat relations, whereas first order predicate logic usually works with *nested* structures (called terms) and relations. XML and other nested data structures fit very well into logic, and in fact we (a research group in Munich and some other places) are working on a logic-based query language that exploits this similarity.

I agree with many of the statements that the author of the article makes, in particular regarding XQuery. However, some are so arrogant and *unproven* that it leaves the article in a bad light. Also, while he claims to have a good insight into database theory, I don't think he really has. SQLs big advantages are (1) it is easy to use and (2) it has a very limited expressive power which makes it easy to implement and efficient to evaluate. Other approaches have been considered, e.g. in deductive databases or knowledge base systems. However, those needed languages that were basically Turing complete or at least supported basic recursion (to implement transitive closure) and thus could lead to very inefficient queries.

Sebastian

Re:The academia is forgetting the whole point of X

[leomekenkamp]

XML is all about programmers being able to understand the data!

No. XML is all about storing meta-data alongside the data. And you are implying that programmers will not understand data that is not easy readable, like XML; this implication is an oversimplification.

(...) reverse engineer another proprietary data format (...)

XML is certainly not the only well-documented interchange format.

(...) dig into a horsepile of documentation.

Is is fairly easy to create XML documents that require horsepiles of documentation, just like any other format. Just look at the XML output of Microsoft Office; XML is no magic bullet.
(...) (like user preferences) (...)

No, one should use an API for that and be independent of storage meganism.

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

I would not like to retrieve all information from abovementioned Office XML files right now, let alone in ten years time.

So yes XML is self describing only to humans and that's the whole point of it.

Ehhm, no. Human readability is a side effect that probably led to its widespread adoption. Work is underway to make certain XML far less 'readable' to humans, but smaller and faster to process.

Formalizing data semantics is not the goal of XML (...)

No, because that would require AI, but formalizing meta-data semantics most certainly was a target set for XML to achieve the goal of better data interchange.

The Data man. problem must be solved at O/S level.

[master_p]

One foundamental error in today's operating systems is that they are datatype-agnostic. They simply don't know what the data they handle is. This task is left completely to applications, and each application usually provides its own way of managing data. This causes incompatibilities between applications, and these incompatibilities are not solved either by SQL or XML (that are nothing more than human-readable representations).

In my opinion, an operating system must primarily provide a data management solution. It must provide the common ways to organize, store, retrieve and process data. This means that the application should only care about the logic behind the data, not how data management is implemented. There are only a few methods of data organization anyway, and it is a shame that these methods are not available when an O/S is installed.

The algorithms that concern the data types should also be available along with the data types. This means that an operating system not only should provide data management, but it should be object-oriented: each "data node" in the system should be available as a class in the chosen programming language (if it supports such a concept).

The availability of data and their types on the O/S level would also boost security and safety, as it would not be possible for a 'devious' application to approach the data in any other way other than the intended one.

Finally, the concept of 'application' is also wrong, and I am saying this in the context of data: in our day and age, data not only multiply fast, but the types of data are frequently modified. The liquid status of data (and their data types) makes the concept of an 'application' (thousands of source code lines, cast in the stone, with a huge degree of coupling between them) a huge obstacle in really making computers useful. Applications need to be replaced by a live system of persistent objects that do simple jobs and inform the world (through events) about changes in their state or the results of their computations; the O/S should be responsible of organizing how objects communicate with each other (either in the same memory space, in different memory spaces, or in different computers).

Since the current situation is not exactly orthogonal (as described above), there are many misunderstandings and problems in defining concepts clearly; many thousands of dollars are spent in re-inventing the wheel, and many work hours and brain power is consumed in creating what should already be there...(and thus we can have a nice /. discussion when half of the posters say that SQL sucks and the other half saying that XML sucks!)