Cage match: SQL vs the Relational Model (part 1)

The relational model underlies all modern relational databases, and it is a thing of beauty.

It defines relations (tables), attributes (columns), tuples (rows), and a whole relational algebra that spells out in detail what you can and cannot do to them. It is logically consistent, and in an odd way almost fractal in the way it treats its entities – subtables are tables, subrows are rows, and so forth.

It’s too bad, then, that no modern database implements more than a bastardized version of the relational model, isn’t it? Even SQL, the most standard and common method of accessing databases is a twisted orc to the relational model’s Galadriel.

The most glaring difference is that the SQL standard allows duplicate rows if there’s no unique constraint on the table. Under the relational model, tables are properly sets (as in the mathematical concept) of rows, which means no duplication. Most people who design SQL databases know, of course, that duplicate data is bad (mmmkay) and thus set unique constraints, but it’s really easy to get duplicate rows in a query result (which is also, technically, a table), especially when joining or unioning.

The other major difference between the relational model and its implementation in SQL is that the SQL standard allows null values. In other words, a row doesn’t have to have values in every column. Under the relational model, though, rows are properly tuples, another dusty math concept (for me anyway). Tuples can’t have null values.

One might reasonably ask why these differences matter. Would we be better off if SQL were a full implementation of the relational model, or would we all just be cranky because we had no way to represent lack of data in a row? Come back tomorrow for part 2 to find out.

(You should buy CJ Date’s book. It is all sorts of awesome.)

2 comments to Cage match: SQL vs the Relational Model (part 1)

  • My guess? We use the bastardized version because it’s easier for most app development. CPKs are a bitch to handle in web apps. NULL values can be handy. That said, surely these details could be abstracted away by a framework. Hmm…

  • As someone who spent years trying to explain to someone that knowledge of SQL was a prerequisite for application developers and not just the lone purview of DBAs (and that while I was a SQL expert, not a DBA) I’m glad to find a book like this.

    Got here from your post about gender as a text field, but I’m very happy to find your blog!