Wednesday, January 15, 2020

TYFK: What is better than relational databases?

Note: About TYFK posts (Test Your Foundation Knowledge) see the post insert below.
“Relational databases like MySQL, PostgreSQL and SQLite3 represent and store data in tables and rows. They're based on a branch of algebraic set theory known as relational algebra. Meanwhile, non-relational databases like MongoDB represent data in collections of JSON documents. The Mongo import utility can import JSON, CSV and TSV file formats. Mongo query targets of data are technically represented as BSON (binary JASON).

“Relational databases use Structured Querying Language (SQL), making them a good choice for applications that involve the management of several transactions. The structure of a relational database allows you to link information from different tables through the use of foreign keys (or indexes), which are used to uniquely identify any atomic piece of data within that table. Other tables may refer to that foreign key, so as to create a link between their data pieces and the piece pointed to by the foreign key. This comes in handy for applications that are heavy into data analysis.

“If you want your application to handle a lot of complicated querying, database transactions and routine analysis of data, you’ll probably want to stick with a relational database. And if your application is going to focus on doing many database transactions, it’s important that those transactions are processed reliably. This is where ACID (the set of properties that guarantee database transactions are processed reliably) really matters, and where referential integrity comes into play.”
First try to detect the misconceptions, then check against our debunking. If there isn't a match, you can acquire the necessary foundation knowledge in our POSTS, BOOKS, PAPERS, LINKS or, better, organize one of our on-site SEMINARS, which can be customized to specific needs.


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.

-12/24/20: Added 2021 to the
POSTS page

-12/26/20: Added “Mathematics, machine learning and Wittgenstein to LINKS page

- 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 the 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 columns there no longer work. I moved only the 2017 columns to dbdebunk, within which only links to sources external to AllAnalytics may work or not.

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.
- The PostWest blog for monthly samples of global Antisemitism – the only universally acceptable hatred left – as the (traditional) response to the existential crisis of decadence and decline of Western  civilization (including the US).
- @ThePostWest on Twitter where I comment on global #Antisemitism/#AntiZionism and the Arab-Israeli conflict.


Note: Language such as "piece of data", "link between data pieces", and "piece pointed to by the foreign key" is a good indicator of lack of foundation knowledge. Also, as one response correctly pointed out:
“You need to provide a definition of what you mean by "better" and a set of objective and comparative criteria which can be employed to accept or reject such a definition. I am not going to speculate upon what you might mean by "better" (e.g. the types of database available or implementation(s) thereof, types of data, scalability, security, business requirements, performance, hardware platform and so on). You need to consider these criteria because they provide the necessary context upon which a meaningful answer to be your question can be derived. Without these your question cannot be answered.”
True, but we actually can, based on the motivation for the RDM.

The Misconceptions

  • MySQL, PostgreSQL, and SQLite3 are SQL DBMSs, not "relational databases".
  • SQL tables are not relations, which is one of many reasons SQL databases are not relational.
  • Databases do not "represent data" -- their data represents information about the real world, and is not stored as tables.
  • A relational DBMS (RDBMS) is a software implementation of the relational data model (RDM), which is an adaptation and application of simple set theory (SST) expressible in first order predicate logic (FOPL) to database management.
  • Transaction management is a DBMS function that is not part of, and is orthogonal to the RDM.
  • The relational algebra (RA) is only the data manipulation component of the RDM, the other being the "structural"-integrity component.
  • There are many types of non-relational DBMSs; document management systems (such as MongoDB) cannot be considered DBMSs in the sense in which relational, or even SQL ones are.

Note: According  to conventional wisdom, the structural component of the RDM is the relation (not the table!). However, a relation is a relationship among attributes constrained by other real world relationships -- so the constraints are the structure.

  • RDBMSs use a relational data sublanguage, which -- for various reasons -- SQL is not, another reason SQL DBMSs are not relational.
  • Referential constraints (not foreign keys) represent relationships among entity groups in a conceptual model.
  • While FKs are usually indexed for performance, they are logical and distinct from "links" or indexes, which are a physical implementation feature (other constraints represent relationships among entity properties and among entities within each group).

Note: But see Association Relations vs. Foreign Keys, The RDM and Model Stability, and 5NF, Association Relations, and Join for a new understanding of FKs. 

  • Database relations have attributes defined on simple domains, the values of which are treated as atomic by the data sublanguage; every atomic value in a relation is logically accessible by relation name + attribute name + primary key value.
  • While PKs are indexed for performance, they are -- like FKs -- logical and distinct from physical indexes; PKs (not indexes) uniquely identify not "atomic values", but relation tuples.
  • That even SQL DBMSs, which are not relational, support transaction management and ACID indicates that these are orthogonal to a DBMS's relational fidelity. 

The Answer

There is nothing better than relational databases/DBMSs for conceptual models that focus on relationships among groups of entities, rather than  on relationships among individual entities (e.g., social networks)[1,2].

Note: I will not publish or respond to anonymous comments. If you have something to say, stand behind it. Otherwise don't bother, it'll be ignored.


[1] Pascal, F., Sets vs. Graphs 

[2] Pascal, F., Graph Databases They Who Forget the Past...

No comments:

Post a Comment

View My Stats