Thursday, July 26, 2012

What Is a Relational Table? (UPDATED)


RF: I have one question if I may.  Joe Celko in SQL FOR SMARTIES Ch.1 claims in a discussion about duplicate rows that Dr. Codd and Mr. Date claim that a table "is a collection of facts." Is this correct? I have scoured my edition of AN INTRODUCTION TO DATABASE SYSTEMS and I can find no such reference. I have not bothered to look in the mentioned reference to Dr. Codd since I have the feeling that Celko's claim is baloney like most of that section.

I thought I'd ask you to get your point of view on this. I have gotten into a couple of arguments recently regarding this issue with some fellow database practitioners ... never mind explaining to them that a table and a relation really are different things ... sigh ... The infamous "Can of Cat Food" and Mr. Beech keep coming up. I won't get into that here since you and Mr. Date have beat that one to death already. I would just like to know if Mr. Date ever referred to a table as "a collection of facts" and if so where.
 
You know, being dedicated to learning my craft to the best of my capabilities, it really becomes increasingly frustrating to have to deal with the lack of fundamental knowledge that is out there. But, you know all that.... ;-)  I'm a peaceful guy, but today I actually got called a "db fundamentalist subversive", go figure. :-)) I guess I should be proud of that!





UPDATE (7/30/12)

A table in a relational database obeys a special discipline that gives it the properties of a mathematical relation:
  • single-valued cells
  • no duplicates rows
  • no meaningfully ordered rows
  • uniquely named, no meaningfully ordered columns
  • no missing values
Such a table, which, following Codd, I call a relational table (R-table), is a faithful representation of a relation (a representation of a thing is not the same as the real thing: a relation is an abstraction, a database table is "real"). An R-table is by definition in first normal form (1NF) or normalized. For example:



EMP#
ENAME
DEPT#
HIREDATE
SALARY
100
Spenser
E21
06-19-1980
26150
110
Lucchessi
A00
05-16-1958
38170
120
O’Connell
A00
12-05-1963
37950
130
Quintana
C01
07-28-1971
33800
140
Nicholls
C01
12-15-1976
35420
150
Adamson
D11
02-12-1972
30280
160
Pianka
D11
10-11-1977
27250


An R-table has an associated predicate.
… a predicate is defined to be a string of English words and individual variables, such that if the individual variables are replaced by appropriate designators, then the whole becomes a [proposition] with these designators as constituents. --W. Hodges, LOGIC 
The predicate associated with the EMPLOYEES R-table is:
Employee with employee number EMP# has name ENAME, is assigned to department DEPT#, was hired on hire date 6-19-1980 and earns salary of 26150.
The rows in the R-table are those whose values, when substituted for the variables, yield true propositions. For example, substituting the values of the first row yields the proposition:

Employee with employee number 100# has name Spenser, is assigned to department E21, was hired on hire date HIREDATE and earns salary of SALARY.
The substitution instantiates the predicate and the proposition is an instantiation of the predicate.

It is assumed by convention that the rows present in an R-table represent all true instantiations of the predicate and all false instantiations are absent from the table. This assumption is called the Closed Word Assumption (CWA).
The set of all true instantiations of the predicate is the extension of the predicate. An R-table represents the extension of the predicate.

Since the predicate extension consists of true propositions and true propositions are facts, an R-table represents a set of facts.

UPDATE 2: Philip comments:
Since an extension holds all the rows that make its predicate true, the other rows make it false. So there is a fact for every present row and a fact for every absent row: these together form the tble's associated collection of facts.

UPDATE: The revision of this post in response to concerns expressed
by readers eliminated an error and is more precise, but it confirms
the difficulty of being both accurate and accessible: the reply to the original question is now more accurate and complete, but longer and I suspect less accessible.

The post was originally a response to a simple question, so it
deserves a simpler answer. Here it is:

The rows of an R-table represent true propositions (which are consistent with
the business rules in effect). True propositions are facts. So in this sense an
R-table represents a set of facts.

(Originally posted on dbdebunk.com 4/28/06)



Do you like this post? Please link back to this article by copying one of the codes below.

URL: HTML link code: BB (forum) link code:

No comments:

Post a Comment