Thursday, July 26, 2012

Don't Confuse Tables with Relations!

REVISED: 10/23/16
"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 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 ... I would just like to know if Mr. Date ever referred to a table as "a collection of facts".
A relational database consists of relations, not tables. A relational table (R-table for short) is one way to visualize a relation on some physical medium (paper, screen) -- a picture of a relation used as a visual shorthand for it. The picture of a thing is not the same as the thing itself, so a R-table should not be confused with a relation: the physical arrangement of rows (picturing tuples) and columns (picturing attributes) on the medium is not meaningful, the logical relationships between attributes and among tuples are. They are expressed as integrity constraints in a specific data language, which comprise a database- and DBMS-specific formulation of the relation predicate that is a formal database representation of the informal business rules that assign meaning to the relation.

For example, the relationship between the attributes of the relation

pictured by the R-table
 100   Spenser    E21    06-19-1980  26150
 110   Lucchesi   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
 290   Parker     D21    05-30-1980  15340
 310   Setright   D21    09-12-1964  15900
is represented by a tuple constraint that represents formally in the database the informal entity rule
Employee identified by employee number (EMP#) has name (ENAME), is assigned to department identified by department number (DEPT#), was hired on hire date (HIREDATE) and earns salary
When attribute values of a specitic tuple pictured by the row
are substituted for the parenthesized parameters in the informal predicate (i.e., rule), they instantiate the predicate to a proposition -- a fact -- about the corresponding employee:
Employee identified by employee number 100 has name Spenser, is assigned to department identified by employee number E21, was hired on hire date 7/29/13 and earns a salary $112,000.
which is the type of fact the tuple represents -- its meaning.

Under the Closed World Assumption (CWA), the rows present in a R-table picture all the tuples that instantiate the relation predicate to true facts and all the rows that instantiate the predicate to false facts are absent from it.

So the short answer to the question is yes, a R-table pictures a relation whose tuples represent a set of facts -- true facts directly and false facts indirectly.

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