Timewarp: What Is a Relational Database?

re: Timewarp: What Is a Relational Database?

Marc Shapiro — Thu, 06 Sep 2012 11:43:25 GMT

When you write "Tables of independent data can be linked, or related, to one another if they each have columns of data that represent the same data value, called keys." you make it sound like the word "relation" in "Relational Databases" has to do with foreign keys and joins between multiple tables. But it doesn't. It has to do with the relationship between the columns of a single table. Each table represents a relation. Many useful relations are actually functions (in the mathematical sense of both), which are represented by table with a primary key. The domain (input) of the function is the primary key columns; the range (output) is the other columns. Joins are like composition of functions (or of relations).

re: Timewarp: What Is a Relational Database?

KKline — Thu, 06 Sep 2012 12:36:27 GMT

Fantastic point to make, Marc. I may have over emphasized the aspect of relationships between tables and under emphasized the aspect of the relationship between columns within a table.

The columns within the table must indeed be directly related to the primary key. As the old saying goes "Every column in the table must depend upon the key, the whole key, and nothing but the key. So help me Codd". ;^)

-Kev

re: Timewarp: What Is a Relational Database?

Adam Machanic — Thu, 06 Sep 2012 14:07:55 GMT

So here's the $10,000,000 question: Is SQL Server a "relational" database product?

re: Timewarp: What Is a Relational Database?

mjswart — Thu, 06 Sep 2012 14:24:12 GMT

It looks like one, it smells like one. I think for any reason that matters, yes.

re: Timewarp: What Is a Relational Database?

Adam Machanic — Thu, 06 Sep 2012 17:40:04 GMT

But does it TASTE like a relational DBMS??

I am not so sure it looks or smells like one. And maybe that doesn't matter when it comes down to it; not like I don't live and breathe in the product every day, and usually get lots of work done with it. But it is interesting to think about at any rate.

re: Timewarp: What Is a Relational Database?

KKline — Thu, 06 Sep 2012 18:39:02 GMT

Look, smell, and taste?!? Ewww - too many senses involved in what should be a non-sensual experience!

But it's a great question. Certainly, all of the major RDBMSes support the 12 Principles by degrees. Access, for example, probably does an even poorer job than SQL Server does. Otoh, I believe that SQL Server passes muster reasonably well, say an A-, if not a perfect score of 100%.

And, ironically, most all of the core database engine enhancements that have come out in the past few releases are decidedly NOT relational. I'd point to SSAS, SSIS, ColumnStore, etc as features that go way outside of the domain of relational databases.

That's another reason why it's much harder to know everything about SQL Server than in the good ol' days. Many parts of the platform are governed by entirely different principles than those of the relatoinal approach. Star or snowflake schema? Sorry - not something in my experience. Know what I mean?

re: Timewarp: What Is a Relational Database?

Adam Machanic — Thu, 06 Sep 2012 23:18:22 GMT

Hi Kevin,

Which "major" RDBMSs are you referring to? I can't think of any.

As far as I'm concerned SQL Server -- and every other SQL DBMS -- gets an F when it comes to being relational. As alluded to by Marc, the Relational Model is so named because it deals with relations. A relation is a set, and SQL is not set-based; it's bag-based. So simply put no SQL DBMS is, or can be, a Relational DBMS.

In a truly relational DBMS the following table could not be created:

CREATE TABLE xyz (a INT NOT NULL, b INT NOT NULL);

Why? Because of the Guaranteed Access Rule (rule #2): All data must be accessible via a primary key. But in this case we have no primary key, so that rule is 100% violated.

Another important aspect of the Relational Model is the idea of closure. Basically, any operation on a relation must produces another relation. But in SQL we can easily violate that. Consider the following table, which has a primary key (so it can at least be called a relation):

CREATE TABLE xyz (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b));

Now let's insert some rows...

INSERT xyz

SELECT 1, 2

UNION ALL

SELECT 1, 3;

... and now we run the following query:

SELECT

FROM xyz;

... And we've just violated relational closure, because the result of our operation is a bag and is therefore not a relation. Oops.

Back to Codd's rules, SQL DBMSs do a decent job with most of the rest of the rules, although it can be argued that they don't tend to do very well when it comes to logical or physical data independence. But a full discussion on that topic is a bit beyond what I feel like typing into this comment box.

Finally back to Michael's point, yet again: Does any of this matter? Perhaps not. We all make our living working with SQL DBMSs. Whether or not these products are actually relational databases -- or something else entirely -- is essentially an academic question and won't change any of our lives. But it does make for an interesting conversation.

--Adam

re: Timewarp: What Is a Relational Database?

Robert Young — Sat, 08 Sep 2012 13:13:12 GMT

For reasons known (ultimately) to the Scions of Armonk, Codd was removed from the development of the Data Access Language (first called SEQUEL, for Structured English QUEry Language, thence SQL for murky reasons). Chamberlin, an IMS cowboy and no language or math theorist, got control. That's why SQL is the bastard language that it is.

Codd rarely, if ever, used the column/table metaphor. It's about relations. Read Date's Eighth Edition for the closest thing to pure Codd. Date disagrees with Codd on some points.

As a smart person once said (and I've long since lost the cite): "there are more hierarchies in code than in the real world". The xml zealots managed to convince the feeble minded that hierarchy is somehow the "natural" data structure of the world. Not even org charts are hierarchies. Yes, they are simple to create, but a devil to modify. Once you make it, the "relations" are hard coded. Talk to an IMS DBA about that. A RM structure is more work to specify, but if primary keys are true, then adding non-key data to a table or a new related table is trivial.

As to PK enforcement: a legacy of COBOL/IMS at the time. It is not a big deal to write an engine which requires PKs on all tables. Coders didn't, and don't, like the restriction. So RDBMSs don't implement it.

With the rise of multi-processor/core/thread/SSD machines that cost about as much as a Starbucks Latte, [de|un]normalized schemas will soon disappear, at least for new development. The smaller footprint of a 5NF database is so much smaller and robust, that even the COBava coders will be brought to heal. Coupled with The Cloud meme (just a New Age version of The Glass House mainframe central computer), where client side transaction control makes even less sense, we'll see the return to the client being just a 3270/VT-220. A dumb terminal. Ah the irony of it all.

re: Timewarp: What Is a Relational Database?

Davos — Mon, 10 Sep 2012 00:08:49 GMT

Pure Codd, sounds tasty.

Adam, I agree with the first violation about a table created with a Primary Key, but not the second example.

"And we've just violated relational closure, because the result of our operation is a bag and is therefore not a relation."

The returned results are a bag but the stored data structure is not and a select is not an 'operation' that changes that. I do get the point though, the rules have certainly been relaxed in practice, but that's just about being pragmatic as opposed to dogmatic.

re: Timewarp: What Is a Relational Database?

Adam Machanic — Mon, 10 Sep 2012 04:18:17 GMT

Davos:

You seem to be missing the point of closure: all operations take as their input a relation, and in turn output a relation, thereby allowing operations to be stacked or composed in any possible form.

SELECT *

FROM Table

-->

SELECT *

FROM

(

SELECT *

FROM SomeOtherTable

) AS x

Assuming that SQL were in fact relational, if the inner table expression in this example failed to return a valid relation the entire system would break down. (Guaranteed Access applies and means that any tuple in [x] should be able to be referenced via a primary key.) It doesn't matter whether or not the results are "stored."

Relaxing these restrictions for SQL was not in the least bit pragmatic. I can't count the number of occasions over the course of my career on which I've found serious data integrity problems or code bugs due to duplicate key scenarios. I shudder to think of the amount of money and time that has been wasted due to this decision.

--Adam

re: Timewarp: What Is a Relational Database?

Luke Jian — Mon, 10 Sep 2012 17:05:42 GMT

I agree with Adam here.

SQL Server does not comply with all 13 rules stated by by Codd to check if an DBMS is truly relational. But then again none of the major DBMSs are compliant either because the rules are too strict now and id does not give the flexibility that the market needs. I would grade SQL server a C- on the Codd Rule.

AFAIK the only true RDBMS that would pass the rules with A would be Dataphor according to Wikipedia: http://en.wikipedia.org/wiki/Codd%27s_12_rules

re: Timewarp: What Is a Relational Database?

Adam Machanic — Mon, 10 Sep 2012 17:57:02 GMT

Luke,

Why would complying with the rules make the systems any less flexible?

Put another way: What benefit do you get from duplicate rows that you couldn't get from, e.g., storing a single copy of the row and an integer ("count" or similar)?

Consider: If you go to the grocery store and buy 15 apples do you want 15 lines on your receipt, or a single line that indicates that you purchased 15 apples?

A system built around constraints that force people to make better design decisions sounds restrictive in theory but I can think of no reasonable design that it would hinder. (Unreasonable designs, like "we need to store all 15 apples separately for audit purposes that I think we might have, maybe, sometime in the future" do not count.)

--Adam

re: Timewarp: What Is a Relational Database?

Bret Lowery — Mon, 10 Sep 2012 19:57:37 GMT

I work with SQL Server... at Yahoo! I also work with columnar stores like ParAccel, appliances like Netezza, and NoSQL environs like Aerospike (formerly Citrusleaf). The right tool for the right job.

re: Timewarp: What Is a Relational Database?

Andy Irving — Mon, 10 Sep 2012 20:02:19 GMT

Any RDBMS that supports SQL isn't relational - no NULLs in the the relational model! And as Adam points out, you can create a table with no PK.

What's interesting though is that there's no readon why you couldn't stick a proper relation interface (something like Date & Darwin's tutorial D, or even something like LINQ) on top of the storage engine and query optimiser.

re: Timewarp: What Is a Relational Database?

Luke Jian — Mon, 10 Sep 2012 21:12:34 GMT

I agree with your points.

Actually the storage engine can do what you are saying (uniquely identify a row internally), its just not exposed to the users (like ROWID in Oracle).

When I was talking about flexibility I didn't have this rule in mind (e.g. system must use its relational facilities (exclusively) to manage the database).

There are a few others that would not make sense today,but overall I understand and share your pains!

re: Timewarp: What Is a Relational Database?

Adam Machanic — Tue, 11 Sep 2012 02:17:58 GMT

Andy: SQL's implementation of NULL has been the subject of a lot of criticism due to 3VL, but the idea of a missing-value token is definitely required in order to support semijoins, even if the schema doesn't use them anywhere. I don't think you can divorce it from the relational model unless you force everything to be normalized all the way to DKNF (at which point you could create domain-specific missing-value tokens). And then, again, you'd have the issue of closure and you'd have to force any intermediate or output relations to be in DKNF. Probably not especially feasible.

Luke: The storage engine can do that, but exposing it would violate the rule about physical independence.

re: Timewarp: What Is a Relational Database?

dvraggs — Tue, 11 Sep 2012 12:06:46 GMT

Should the tool be the whole system? The tool used to be a means to create a RDBMS. Using these tools and following the rules you create a RDBMS, not incompetent people being protected by a tool. You can create a RDBMS (key word System) using SQL Server, but it will not hold your hand. People + tool(s) create a RDBMS.

re: Timewarp: What Is a Relational Database?

Adam Machanic — Tue, 11 Sep 2012 13:48:33 GMT

dvraggs:

A DBMS is a tool used to create a database. A SQL DBMS does not create an RDBMS, no matter which people are involved. At the end of the day you have a database on a nonrelational platform and there's no way that's going to become relational. The platform itself simply does not support the required base set of rules.

Anyway, arguing that people should write everything themselves is a very slippery slope. Taking your logic one step further we should eliminate all constraints and DRI. People can hand-code that stuff. Oh, and we should eliminate tables too. People can write data structures in a 4GL pretty easily can't they? Don't need a query optimizer, because it's all just algorithms and people can learn those. And I guess I don't need a storage engine, because I am pretty good with ISAM. And I'll stop working in C#, because I can write my own garbage collection logic and libraries in C++...

You get the point, I hope. We create high level abstractions and rules to protect ourselves from reinventing problems and so that we can focus on more interesting tasks.

--Adam

re: Timewarp: What Is a Relational Database?

KKline — Tue, 11 Sep 2012 18:55:25 GMT

Adam, When you cite that no SQL dbms supports rule #2, "Data must be logically accessible by table, primary key, and column." you provide an example in which you can break rule #2.

But are these rules, like the famous line from Pirates of the Caribbean, rigid rules or general guidelines? Imo, when a SQL database is able to enforce #2, even though it doesn't specifically require #2 in any and every situation, it passes the test.

It's a semantic nuance, certainly. And I should go back to the original language to see if it's more clear in dispelling any leeway. But it's enough for me, when a dbms CAN support the rules (even if it allows those self-same rules to be broken under specific conditions), to say that it is indeed a relational database.

But I'm also a pragmatist and don't really care that much about the purity of the observation of the rules. I'm more interesting in learning about the duplicate key scenarios you mentioned and how to thwart them.

Luke, great tip on Dataphor! I'm going to check it out just out of curiousity. =^)

Very interesting comments, everyone!

re: Timewarp: What Is a Relational Database?

dbdebunker — Thu, 13 Sep 2012 19:44:06 GMT

Kevin,

Your heart is in the right place, but I don't think you identified properly the CORE issues and practical implications of the RM.

The comments do provide some good clarifications, but still miss some important points.

I guess this post and its comments is a good target for debunking, for educational purposes.

www.dbdebunk.blogspot.com

re: Timewarp: What Is a Relational Database?

Andy Irving — Tue, 18 Sep 2012 15:08:34 GMT

Adam: Yeah. Date & Darwin go further though, with 6NF. some ideas in this paper: http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf

personally i'd love a fully relational future, but then i also love functional programming :)

re: Timewarp: What Is a Relational Database?

Erwin Smout — Wed, 17 Oct 2012 15:39:07 GMT

Well, I sincerely believe that Oracle and SQL Server and DB2 and Sybase and ... should indeed go the way of punched cards. The sooner the better. Just so long as they are replaced by relational systems.

And it might have been better if those twelve rules were quoted, instead of paraphrased as heavily as they were.

re: Timewarp: What Is a Relational Database?

Erwin Smout — Wed, 17 Oct 2012 21:26:39 GMT

@KKline : I think the only reasonable way to interpret rule 2 is that the DBMS should be such that **IN ALL POSSIBLE CIRCUMSTANCES**, the access is guaranteed. If there exists any circumstance in which this is not the case, then the engine by itself doesn't satisfy the rule.

Your interpretation of "satisfies the rule if it is possible for the user to set things up such that the rule happens to be satisfied", makes even for a combination of COBOL+ISAM to satisfy the rule.