Sunday, April 11, 2021

(TYFK) Relations, Tables, and Semantic Consistency



Note: Each "Test Your Foundation Knowledge" post presents one or more misconceptions about data fundamentals. To test your knowledge, first try to detect them, then proceed to read our debunking, reflecting the current understanding of the RDM, distinct from whatever has passed for it in the industry to date. If there isn't a match, you can review references -- reflecting the current understanding of the RDM, distinct from whatever has passed for it in the industry to date -- which explain and correct the misconceptions. You can acquire further knowledge by checking out our POSTS, BOOKS, PAPERS, LINKS (or, better, organize one of our on-site SEMINARS, which can be customized to specific needs).


“A relation, or table, in a relational database ... must have a set of columns or attributes, and it must have a set of rows to contain the data. A tuple (or row) can be a duplicate. In practice, a database might actually contain duplicate rows, but there should be practices in place to avoid this, such as the use of unique primary keys (next up). Given that a tuple cannot be a duplicate, it follows that a relation must contain at least one attribute (or column) that identifies each tuple (or row) uniquely. This is usually the primary key. This primary key cannot be duplicated.”

 

Misconceptions

 
  • A relation is not a table and, thus, has neither columns, nor rows (certainly not fields);
  • "Duplicate tuples" is a contradiction in terms -- a table with duplicate rows does not visualize a relation (i.e., is not a R-table) -- and a database with duplicated data is not relational;
  • Unlike a mathematical relation, there is no such thing as a database relation without a PK, which would be semantically inconsistent (i.e. it would not be a faithful representation of group of entities, which are distinguishable in the real world);
 

Fundamentals

 

  • A database relation:
- is a relationship among domains (sets of values) -- a subset of their cross-product -- a set of tuples (sets of values drawn from the domains) or, in other words, a set of sets from sets;
- has attributes, which are representations (1:1 mappings) of the domains in the relation;
- is semantically constrained to be consistent with (i.e., represent faithfully) the entity group in the conceptual model represented by the database), including by a PK constraint:
. domains represent properties;
. relations represent entity groups;
. attributes represent entity properties;
. tuples represent (facts about -- properties of) entities;
. some constraints represent relationships among properties, entities and groups (some properties are relationships);
  • Duplication would violate the RDM and mean semantic inconsistency with (inaccurate representation of) the group.
  • A R-table visualizes a relation on some physical medium -- it plays no part in the relational model.


Further Reading


What Relations Really Are and Why They Are Important

Understanding Relations series

What Is a Relational Database




No comments:

Post a Comment

View My Stats