Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

"Real" copy of Codd's 12 (13) Rules for RDBMS Expand / Collapse
Author
Message
Posted Tuesday, July 13, 2010 7:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 7,745, Visits: 9,493
Codd's 1985 rule 6 (view updatability) is, according to his rules index at the back of the 1990 book, replaced by RV-4 and RV-5; but according to the main text RV-6 is the modified rule 6. So all three are given below.
They refer to Algorithm VU-1 which is an algorithm which tries to determine from a view definition whether the view is updatable but can deliver a "don't know" answer as well as "yes" and "no".

--------------------------------------------------------------------------------

RV-4 Retrieval Using Views
Neither the DBMS nor its principal relational language, RL, makes any user-visible distinctions between base R-tables and views with respect to retrieval operations. Moreover, any query can be used to define a view by simply prefixing the query with a phrase such as CREATE VIEW.

---------------------------------------------------------------------------------

RV-5 Manipulation Using Views
Neither the DBMS nor its principal relational language, RL, makes any user-visible manipulative distinctions between base R-tables and views, except that (1) some views cannot accept row insertions, and/ or row deletions, and/or updates acting on certain columns (Algorithm VU-1 or some stronger algorithm fails to support such action), and (2) some views do not have primary keys and therefore will not accept those manipulative operators that require primary keys to exist in their operands.

-----------------------------------------------------------------------------------

RV-6 View Updating
To evaluate the updatability of views at view-definition time, the DBMS includes an implementation of Algorithm VU-1 or some stronger algorithm. Neither the DBMS nor its principal relational language, RL, makes any user-visible manipulative distinctions between base relations and views, except that:

some views cannot accept row insertions, and/or row deletions, and/or updates acting on certain columns because Algorithm VU-1 or some stronger algorithm fails to support such action;

and

some views do not have primary keys (they have weak identifiers only) and therefore will not accept those manipulative operators that require primary keys to exist in their operands.

(This feature is a slightly modified version of Rule 6 in the 1985
set.)

-----------------------------------------------------------------------------------

I think that in his RV-6 Codd understated the extent of the modification from the old rule 6. Further on in the book he states

Unfortunately, the general problem of determining whether or not a view is theoretically updatable cannot be decided logically [Buff 1986].

and that's why he had to change the rule.


Tom
Post #951453
Posted Tuesday, July 13, 2010 4:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Awesome feedback. As a side bar... Heh... no appologies for wanting to for wanting to form an opinion on some things on my own (which I still have to do) but it does sound like he's correct about the original rules not being so important anymore.

Thank you both.

I'd heard the book that Tom recommended wasn't so hot but it's one of the few things I can actually find that was written by Codd instead of being paraphrased. I'll see if I can dig up one of those cheap copies Tom was talking about.

Thanks again, guys.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #951875
Posted Tuesday, July 13, 2010 6:00 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 7,745, Visits: 9,493
Jeff Moden (7/13/2010)
I'd heard the book that Tom recommended wasn't so hot but it's one of the few things I can actually find that was written by Codd instead of being paraphrased. I'll see if I can dig up one of those cheap copies Tom was talking about.

If you get it as PDF from ACM using the beta version of the portal you may be given a non-working download URL: if that happens don't panic, just try eliminating the folder names after the website root (the FT cfm is in the root) - but they've probably fixed that bug now. Or if you don't want to risk it, don't follow the link to the Beta version. It's a text PDF (either generated using OCR or converted from a postscript version, I guess) not a scanned image file so text-searchable, unlike many (undesirable) book PDFs.

You are right, it isn't a hot book - it's too long, nothing that long can be hot. It's many times as long as the original paper describing RM and many times as long as the RM-T paper. Codd's justification for the length was that he wanted to put together in one place all the contents of 50+ articles which he had written on the topic so that they would be available to the people working on RDBMS (well, we know how hard it is to get for example the original two "rules" papers, or the sigmod presentation of them the following year, so we know he had a point there). Despite its length it's good - but it would have been better if it had been published a few years earlier, when it would have had some chance of influencing the mainstream development of RDBMS and SQL, instead of in 1990 when all the big players already had massive investment in doing it wrong. Something like this book - in shorter form - might have changed the course of RDBMS development if it, rather than the two rules papers, had been published back in 1985 instead of in 1990.


Tom
Post #951910
Posted Wednesday, July 14, 2010 6:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
Amazing documentation, Thank you a lot Tom.

I'm saving these jewels on my private library for future reference


_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #952240
Posted Sunday, July 25, 2010 9:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 7,745, Visits: 9,493
Another rule (number 2) in its 1990 version. This one hadn't changed much from the 1985 version.

RM-1 Guaranteed Access

Each and every datum (atomic value) stored in a relational database is guaranteed to be logically accessible by resorting to a combination of R-table name, primary-key value, and column name. (This feature is Rule 2 in the 1985 set.)
_______________________________________________________
He follows this definition of the rule with some explanatory text:-

The access path supporting this feature cannot be canceled. Most other access paths, however, are purely performance-oriented, and can be both introduced and canceled. Both Feature RM-1 and Feature RM-3 are needed in order to support ad hoc query without pre-defined access paths.

Clearly, each datum in a relational database can be accessed in a rich variety (possibly thousands) of logically distinct ways. It is important, however, to have at least one means of access, independent of the specific relational database, that is guaranteed~because most computer-oriented concepts (such as scanning successive addresses) have been deliberately omitted from the relational model.

Note that the guaranteed-access feature represents an associative-addressing scheme that is unique to the relational model. It does not depend at all on the usual computer-oriented addressing. Moreover, like the original relational model, it does not require any associative-addressing hardware, even though the need for such hardware was once frequently claimed by opponents of the relational model.

The primary-key concept, however, is an essential part of Feature RM-1. Feature RS-8 requires each base relation to have a declared primary key (see Chapter 2). Feature RM-1 is one more reason why the primary key of each base relation should be supported by every relational DBMS, and why its declaration by the DBA should be mandatory for every base relation.
_____________________________________________________

(Feature RM-3 is support of the full power of a 4-valued predicate calculus; obviously you can't usefully do ad hoc queries without some sort of logic support, so the reference here isn't anything odd.)

It's interesting that Codd says that a need for associative-addressing hardware was claimed to exist by oponents of the relational model. Associative addressing disc hardware was originally proposed in the 1960s, several years before Codd proposed the relational model, and the justification for it was nothing to do with the relational model: it was that no-one had any CPUs (or reasonably priced arrays of CPUs) that could handle search at the data rates that could be provided by "modern" (1960-s vintage) discs. The first customer deliveries of Content Addressable FileStore (CAFs) occurred before any relational database was delivered, and were made to BT and several other ICL customers in the 1970s; the software transformed queries into instructions for search logic incorporated into the drive mechanism. This early version was built as a series of one-offs for each customer that wanted one, not as a standard product - the standard product came later when controller technology had improve suffieiently to permit the search logic (which was a bit advanced for its time - for example it included weighted quorum logic) to be moved from the drive mechanism to the disc controller. CAFS was included in all ICL Series 39 mainframes and most 2900 series mainframes from 1982 onwards. At this point it was used mainly for IDMS (which is certainly not not relational) and for IDMS-X, but later it was incorporated into the ICL port of Ingres to the VME operating system and it was also used and for searching databases (again not relational) of unstructured text (eg at Oxford and Southampton Universities) . Another version (SCAFS for "Son of CAFS": ICL in those days had no qualms about product naming standards) was based on firmware in a standard microprocessor. As well as IDMS-X, all of Informix, Oracle and Unix provided an interface for the incorporation of/interfacing to the CAFs support software (a standard library provided by ICL with the hardware) and IBM licensed the SCAFS technology from ICL for use with DB2; but I don't recall anyone claiming it was essential for relational database support. Certainly no-one in ICL made or believed any such claims - CAFS and SCAFS were about getting past the then existing hardware limitations on search capability (effectively limitations on scan,filter, and join speeds) and nothing to do with what the database model was - and as processor speeds improved and the cost of multi-processor systems came down associative-addressing disc hardware would cease to be commercially viable (if the performance estimates produced by Leung and Choo http://www.vldb.org/conf/1985/P282.PDF were correct, a relative improvement factor of 2.5 between processor and disc bang per buck would do that; I don't think any modern systems use it - it would be fairly pointless if the CAFS hardware used the same industry standard components as the server). As far as I know no-one but ICL (and IBM under license from ICL) ever produced any associatively addressed disc systems as a large-volume product. I guess that IBM (specifically the DB2 team) must be where the claims that associative addressing hardware was neccessary for support of the relational model came from - after all, they were the opponents of RM best known to Codd.



Tom
Post #958486
Posted Sunday, July 25, 2010 10:22 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 17,729, Visits: 15,597
PaulB-TheOneAndOnly (7/14/2010)
Amazing documentation, Thank you a lot Tom.

I'm saving these jewels on my private library for future reference


I agree. This is very good information.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #958495
Posted Sunday, July 25, 2010 12:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:56 PM
Points: 7,075, Visits: 15,325
Jeff - I believe you're looking for the October 1985 article series in Computerworld, by Codd, entitled:

Does your DBMS Play by the rules

From Codd's own description it's the first time he went into all 12 rules in a single article. The first article is the base definition of the 12 (and rule 0), with a follow-on article to describe the details and corrolary rules.

As far as I know, it's still under copyright, so I can't post copies.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #958504
Posted Sunday, July 25, 2010 12:32 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:34 AM
Points: 448, Visits: 3,366
Tom.Thomson (7/25/2010)
Note that the guaranteed-access feature represents an associative-addressing scheme that is unique to the relational model. It does not depend at all on the usual computer-oriented addressing. Moreover, like the original relational model, it does not require any associative-addressing hardware, even though the need for such hardware was once frequently claimed by opponents of the relational model.

The primary-key concept, however, is an essential part of Feature RM-1. Feature RS-8 requires each base relation to have a declared primary key (see Chapter 2). Feature RM-1 is one more reason why the primary key of each base relation should be supported by every relational DBMS, and why its declaration by the DBA should be mandatory for every base relation.


Guaranteed access is surely also important for derived relations as for base relations. Yet by the time Codd introduced the 13 rules he abandoned the idea of relational completeness so that primary keys are no longer required for views. Instead views can have what he called "weak keys" (tuples are "unique" but are identifiable only by nulls in place of values).

This seems like a horrible complication in my view because it means the "relational" database engine then has to deal with two types of data structure - one of which doesn't look like a relation at all. In consequence, different types of storage maybe have to be used for derived relations and base relations unless some compromise between the two can be found. It may also be that some types of query rewrite are no longer possible or are made more difficult because base and derived relations are no longer interchangeable.

In this respect and others the 13 rules depart from the simplicty and consistency of Codd's model as he originally described it - to its detriment in my opinion.


David
Post #958507
Posted Sunday, July 25, 2010 1:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Matt Miller (#4) (7/25/2010)
Jeff - I believe you're looking for the October 1985 article series in Computerworld, by Codd, entitled:

Does your DBMS Play by the rules

From Codd's own description it's the first time he went into all 12 rules in a single article. The first article is the base definition of the 12 (and rule 0), with a follow-on article to describe the details and corrolary rules.

As far as I know, it's still under copyright, so I can't post copies.


Thanks Matt... I'll check it out.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #958516
Posted Sunday, July 25, 2010 2:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:18 PM
Points: 35,267, Visits: 31,759
Heh... the more I learn of this subject and similar subjects especially when the human element is included, the more I realize that there's one and only one premise in all the world that will never ever change...


... "It Depends".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #958520
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse