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