Saturday, June 11, 2022

SMS: Order and Relational Databases



Note: In "Setting Matters Straight" I post on LinkedIn online Q&As that involve fundamentals under the header "What's Right and Wrong with this Database Picture" and then debunk them here. 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: “I'm not sure what this means: "The order of the rows and columns is immaterial to the DBMS?" -- could anyone explain?”

A: “It means two things:
The engine is under no obligation to insert new rows immediately following the previously inserted row(s)... During processing of selects, the optimizer is free to use any index it finds efficient to use or none at all... For this reason, if the order of returned data is important to your processing, then you must include an ORDER BY clause.”

Q: “How do you reorder fields in the database?”

A: “Depends on how you define "reorder". What view of your data are you trying to set the order. Are you in Table Design view? ... Are you looking at form? The answer is different depending on what you are referring to.”
--Quora.com

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

SUPPORT THIS SITE
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.

LATEST POSTS

05/21 OBG: No RDBMS without Relational Domains

05/02 SMS: "Relation Proliferation"?

04/25 SMS: Relational Database and Set Theory

LATEST PUBLICATIONS (order from PAPERS and BOOKS pages)
- 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).

USING THIS SITE
- 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.

SOCIAL MEDIA
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.
------------------------------------------------------------------------------------------------------------------

Fundamentals

The RDM is an adaptation of mathematical relation theory expressible in FOPL to database management.

A database relation is a 5NF relation constrained semantically to represent a group of entities of a single type:
  • entity properties are represented by attributes;
  • (facts about) entities are represented by tuples; and,
  • relationships among properties and among entities within a group are represented as constraints.
Two adaptations of the theory are:
  • mathematical relations have nameless domains identified by their order and no attributes;
  • database relations have named domains and attributes.

The two formost fundamental principles of the RDM are enshrined in Codd's famous 12 rules:

  • All information in a relational database is represented explicitly and in exactly one way: as attribute values (drawn from domains) in relations (Rule #1: Information Principle).
  • Each and every atomic value is guaranteed to be logically accessible by a combination of  relation name + primary key value + attribute name.  (Rule #2: Guaranteed Logical Access).

If a database relation is visualized as a R-table:

  • attributes display as columns;
  • tuples display as rows; but
  • constraints are reflected, but are not visible in the R-table
  • column-and-row arrangement in the tabular display plays no role in RDM.

Setting Matters Straight

Implicit in the IP is that information meaningful to users and applications is represented explicitly as values and not implicit (i.e., "hidden from the RDBMS) in the order of attributes or tuples (for which reason we prefer order that is 'not meaningful' to "immaterial". It means that:

  • Database design encodes no information in the order of attributes and tuples and, thus, none is lost in any re-ordering for performance optimization.
  • Presentation of results (and any computation) is an application, not a DBMS function and, thus, performed at will on the retrieved data in the application development language, not in the relational data sublanguage.

Thus:

  • In "design view" (where database relations are created), order is not meaningful, so there is no re-ordering;
  • In "form view" (where applications are created), the form (interactive) language re-orders the results retrieved from the database by the DBMS.

Note: Design and Form views are features of Microsoft Access. The former is used not just for logical design, but also for physical implementation (most products do this), in where a default order can be identified (implemented by an index) that affects both storage and display; since a PK must be unique, a unique index is created to enforce the PK constraint.

  • ORDER BY is an application function. While it is convenient to give users access to it from within the data sublanguage, the language should delineate and make clear the functional distinction. It is precisely because SQL does not that the above question arises (but then, SQL is neither relational, nor strictly data sublanguage, nor well designed). 

 

 

 

 

No comments:

Post a Comment

View My Stats