Access isn’t a relational database
We're all related somehow
Database myths and legends (Part 6) This is a great example of a myth/legend that is both true and false; it all depends on how you define relational.
Ted Codd produced one of the first attempts to define exactly what the term relational Database Management System (DBMS) means. Since Dr. Edgar Codd is regarded as ‘the Father of the Relational Database’, most people assume he knew what he was talking about. He published two articles in Computerworld (14th and 21st October 1985) and in the first he wrote:
“In this paper I supply a set of rules with which a DBMS should comply if it is claimed to be fully relational.”
These rules are often referred to as Codd’s 12 rules, despite the fact that there are 13 of them. Codd numbered them from zero and many people didn’t read the rules, they simply looked at the highest number and assumed that there were 12 - apparently even including Codd, who didn't count Rule 0 and mostly referred to his "12 rules" himself.
The first rule makes one thing perfectly clear:
‘Rule 0: For any system that is advertised as, or claimed to be, a relational data base management system, that system must be able to manage data bases entirely through its relational capabilities.’
In other words, to be considered a relational database engine, the product in question has to obey all of the rules. He then goes on to list what he considers the essential requirements. Not only are the rules relatively stringent, it turned out that one was impossible to fulfil. Rule 6 sounds simple initially:
‘All views that are theoretically updatable are also updatable by the system.’
However, Codd’s expansion of that rule: “Note that a view is theoretically updatable if there exists a time-independent algorithm for unambiguously determining a single series of changes to the base relations that will have as their effect precisely the requested changes in the view” is a logical killer. In1988, H. W. Buff published a paper entitled “Why Codd’s Rule No. 6 Must Be Reformulated” which proved that it is impossible to define such an algorithm precisely.
So, to be relational, an engine has to meet all of the rules and one of those rules has been proved to be impossible to fulfill. Since it is now clear that no database engine can ever be relational, it follows that Access is not relational. QED.
I agree that this is simply an exercise in logic chopping (although I have heard precisely this argument used); however it is also true that some people who work very seriously in this field really do think that none of the mainstream engines are truly relational. Chris Date (Ted Codd’s closest co-worker) has stated in an interview he gave to Tony Williams here that the existing mainstream DBMSs are “all deeply flawed”.
Even if you think that this is a rather extreme view, there are more than enough other arguments to keep the regulars at the “Table and Join” pub debating long after last records have been called. So, let’s see how the discussion might go between two protagonists.
Of course, whilst it’s fine for trivial work, let’s face it; Access isn’t really a relational database.
You are just so right. A database is a collection of data, so Access isn’t a relational database, neither is Oracle, SQL Server etc.
OK, OK. What I meant was Access is not a relational engine.
Correct again. Access isn’t an engine. It makes calls to the Jet database engine which is supplied along with Access. Incidentally, in Access 2007 the Jet engine is finally being phased out (after 15 years of valiant service) and is being replaced by the new Ace engine.
Let’s not get pedantic here. Tell you what, from now on we’ll use the term ‘Access’ to include the Jet/Ace engine. It doesn’t matter how you wriggle and squirm, Access still isn’t relational.
Be more specific; in what way is it not relational?
What I mean is that it is nowhere near as relational as, say, Oracle.
But an engine can’t be partially relational. That’s like being slightly null. This relational business is binary: you either are, or you aren’t. Take a look at Codd’s first rule.
That’s silly. In common usage we know what the word ‘relational’ means. It refers to a database engine that stores data in tables. The tables have columns and rows, primary and foreign keys. Relational databases engines are the ones that allow joins between tables, use referential integrity to provide high data integrity, use SQL for querying and all of that jazz. That’s the relational database engine in the real world, not some airy-fairy definition by some guy 30 years ago.
Fair enough, you’ve convinced me. I’ve totally changed my mind. We should use the real world definition. In which case Access clearly is a relational database engine because it does all that you have mentioned and more.
No, that wasn’t the point. There are bits of the relational model that Access doesn’t support, like transactions.
Yes it does; they have been supported since Jet 4.0.
Ah, but it doesn’t support transaction logs!
True, but the whole question about transactions is immaterial to the current discussion because transactions aren’t really part of the relational model.
Of course they are.
No, you just associate them with the model because most relational engines provide transactional control. I agree that transactions are an essential part of building databases for business applications, but they aren’t part of the relational model itself. They are an add-on to the model. You don’t have to believe me, Chris Date also thinks so: “[Transaction theory] doesn’t have much to do with the relational model as such” - Databases in Depth, P. 121, Chris Date, May 2005, O’Reilly – the book’s reviewed here.
OK, but there are other things that Access doesn’t have that are part of the relational model – like full support for all flavours of referential integrity.
Well, we can digress and argue about whether the different implementations of referential integrity are part of the relational model, but I accept the principle that Access doesn’t support the entire relational model. However, every mainstream database engine on the planet has bits and pieces of the relational model that it doesn’t support. For a start, they all use SQL which is not a particularly good match to the relational model. Chris Date again, from the interview quoted:
“So yes, I do think SQL is pretty bad. But you explicitly ask what its major flaws are. Well, here are a few:
- “Duplicate rows;
- “Left-to-right column ordering;
- “Unnamed columns and duplicate column names;
- “Failure to support "=" properly;
- “High redundancy.”
So all mainstream database engines fail to support the model properly which means that Access is no different from the others, such as Oracle, that you happily classify as relational.
But what about…
And so on and so on and so on... The argument will still be raging at closing time because it’s a database argument and, by definition, no-one ever updates their views.
We are all entitled to an opinion about this myth (since it depends so much on your definition of "relational"), but at least we can try to pare the discussion down to some fundamental points.
- If you are a hard-line relational theorist, you may feel that none of the mainstream database engines is strictly relational. In which case, Access is not relational.
- If you are consider the likes of Oracle, SQL Server, DB2 etc. to be relational then you are accepting a more ‘real world’ definition of relational and you now have to decide whether Access falls within that set.
- If you decide it doesn’t, then you have to be able to show that Access supports significantly less of the model.
One trap worth avoiding is the use of arguments such as “Access isn’t relational because:
- “it doesn’t support transactions;
- “it can’t handle enough data;
- “it isn’t robust enough;
- “it is too slow;
- “when it crashes the data can become corrupt because it doesn’t log the transactions;
- “it comes from Microsoft and those guys don’t know anything about robust systems;
- “it can’t handle more than about five concurrent users without running like a dog.”
The problem here is that these characteristics have nothing whatsoever to do with the relational model. It is perfectly true that stability, speed, high concurrency and the ability to handle large data sets are all eminently desirable features of a relational database system. But to associate them with the underlying data model is to misunderstand, at a very fundamental level, what the relational model is about.
According to Date, “the relational model is concerned with three aspects of data: data structure, data integrity and data manipulation.” - An Introduction to Database Systems, Addison-Wesley. The model says nothing about performance, data volume, numbers of concurrent users etc.
Indeed, to use these arguments to support the view that Access is not relational is like saying "Penguins aren’t birds because they can’t fly.” Whilst it is true that many birds can fly, aerial navigation is not part of the formal spec. for Class Aves.
So, what do the professional think? Ted Codd is sadly no longer with us to give an opinion but happily Tony Williams asked Chris Date the following question:
Tony: Finally, I have to ask: When Chris Date wants to build a database, what product does he turn to? Do you have time for consumer-level products such as Access? Do you think they have a place?
Chris: My lawyer has told me to say “No comment” to this one. Seriously, I do have a policy of never answering this question directly; I'm only too aware of what might happen if I were thought to be endorsing some specific product. (Did you expect anything different?) So all I can do is offer some platitudes: Even though they're all deeply flawed, you do have to use one of the existing DBMSs, because they're all that's out there - they're the only game in town… pretty much (though I'm being a little unfair here to at least one product, Dataphor from Alphora, which does constitute an attempt to implement the ideas of The Third Manifesto). Which particular product you choose will naturally depend on your own specific requirements (and yes, it might be one of the “consumer-level” products such as Access).
Which doesn’t answer the question directly, but it does tell us that Chris has a sense of humour as well as a sense of proportion.®