Sunday, July 3, 2022

Relations, Database Relations and Tables (SMS)

Note: In "Setting Matters Straight" posts I debunk online Q&As that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.

Q: “What is a relation in database?”

A: “Relational databases were so named in 1970 by computer scientist E. F. Codd because the tables are themselves relations, which is a mathematical term. What makes a relation (aka a table) a relation? Basically:
  • A relation has a heading, which names a finite set of columns.
  • Columns are defined by their name and their type.
  • A relation has a finite set of tuples (aka rows), and every tuple has the same set of columns (i.e. same name and type) as those named in the heading.
  • Being finite sets, both the set of columns in the heading and the set of tuples in the relation have no duplicates and no inherent order.
See? There’s nothing about relationships between tables in the definition of a relation. You could have a relational database that contains just one relation. If there’s any relationship described in a relation, it’s actually the relationship between the columns within a relation. That is, the value "Pittsburgh" goes with the value "Steelers" on a given row because the relation is defined as "pro football teams by city" and therefore there’s a linkage between some values in the set of football teams and the set of city names.”


DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. The site was discontinued in 2018. The content here is not available anywhere else, so if you deem it useful, particularly if you are a regular reader, please help upkeep it by purchasing publications, or donating. On-site seminars and consulting are available.Thank you.


06/26 Repeating Groups and 1NF (T&N)

05/21 SMS: Order and Relational Databases

05/02 SMS: "Relation Proliferation"?

- 08/19 Logical Symmetric Access, Data Sub-language, Kinds of Relations, Database Redundancy and Consistency, paper #2 in the new UNDERSTANDING THE REAL RDM series.
- 02/18 The Key to Relational Keys: A New Understanding, a new edition of paper #4 in the PRACTICAL DATABASE FOUNDATIONS series.
- 04/17 Interpretation and Representation of Database Relations, paper #1 in the new UNDERSTANDING THE REAL RDM series.
- 10/16 THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS, my latest book (reviewed by Craig Mullins, Todd Everett, Toon Koppelaars, Davide Mauri).

- To work around Blogger limitations, the labels are mostly abbreviations or acronyms of the terms listed on the
FUNDAMENTALS page. For detailed instructions on how to understand and use the labels in conjunction with that page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 were relabeled accordingly. As other older posts are rewritten, they will also be relabeled. For all other older posts use Blogger search.
- The links to my AllAnalytics columns no longer work. I re-published only the 2017 columns @dbdebunk, and within them links to sources external to AllAnalytics may or may not work.

I deleted my Facebook account. You can follow me @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture? posts, and my exchanges on LinkedIn.


  • Tables are not relations:
  • Database relations are mathematically relations, but not all relations are database relations.
  • A relation does not have a heading, columns, rows, or types;
  • The order of attributes and tuples is not meaningful.
  • Relationships among relations are part of the database definition (practically all databases have multiple relations).
  • A database relation not only does not "describe just a relationship among attributes": it is a relationship among domains that incorporates two kinds of relationship among attributes and several kinds of relationship among tuples.

Setting Matters Straight

A relation is a set that is a relationship among sets (domains) -- a subset of their cross-product -- a mathematical abstraction that represents nothing in the real world:

  • Is defined on nameless domains identifiable by order;
  • Its tuples are unique combinations of all domain values by mathematical definition;
  • Has:

- an intension -- the relation predicate (RP) -- the criterion of set membership that tuples must satisfy to qualify as set members;
- an extension -- the set of tuples that satisfy the RP.

Relational databases owe their name to the relations of mathematical relation theory, which Codd adapted for database management.

  • A database relation is a relation that represents in databases a group of entities of a single type:
  • Is defined on named domains, the order of which is not meaningful;
  • Has named attributes drawing their values from the corresponding domains;
  • Its tuples are unique by virtue of primary keys (PK);
  • Its intension (RP) constrains the extension for semantic consistency with relationships among properties and entities within the group that it represents.

The body of a R-table can visualize the extension, but not the intension (RP) of database relations. The column-and-row arrangement of R-table plays no role in the RDM.

Note: The PK uniqueness constraint is an example of a semantic constraint that is part of Codd's adaptation of relations to database management: in mathematics tuples are unique by definition and relations don't have PKs, but in database relations PK uniqueness constraints represent assignment of names to entities in the real world (The Key to Relational Keys: A New Understanding -- Primary Keys).


Further Reading

What Relations Really Are and Why They Are Important

Understanding Relations series

Relationships and Tables

Relationships, Rules, Relations and Constraints





No comments:

Post a Comment

View My Stats