Wednesday, February 5, 2020

TYFK: What is a database relationship?

 Note: About TYFK posts (Test Your Foundation Knowledge) see the post insert below.

“Here two or more table are related with each other. This is Database relationship. Database relationship is used a lot. Also known as relational database management system. It is shortly called RDBMS. Here is Join_data table and Interview_data table. For creating a relational database management system both of the table must have a common field. Here Employee_ID is a common field.
Database relationship types:
01. One-To-One relation
02. One-To-many relation
03. Many-to-many relation.
01. Minimum one common field is essential in all the tables. The data type of common field and field size will be same in all the tables.
02. There must be a primary key field in any of the table from all the tables.”

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.

Up to 2018, DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. In 2018 that website was discontinued. The content of this site 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. Thank you.
The industry is chockful of misconceptions due to lack of foundation knowledge. Corrections them are dismissed as "theory that is not practical", misinterpreted as "ad-hominem attacks", or ignored altogether, regardless of the amount and quality of reasoning and supporting evidence. Most practitioners -- be it user or vendor personnel -- cannot discern fallacies and do not realize the practical implications thereof and, thus, cannot associate problems with their real causes., hence the industry's "cookbook approach" and succession of fads.
What about you? Are you just a practitioner, or a thinking professional?
TYFK (Test Your Foundation Knowledge) posts will each present and debunk a pronouncement containing one or more misconceptions. First try to detect them, 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.

  • 01/14/20 Updated the LINKS page
  • 01/04/20 Updated the POSTS page with the 2020 posts
  • 12/08/19 Added two educational references on set theory to the LINKS page.

  • 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. 
  • Following the discontinuation of AllAnalytics site, 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: Evidence for Antisemitism/AntiZionism – the only universally acceptable hatred – as the (traditional) response to the existential crisis of decadence and decline of Western (including the US)
  • @ThePostWest Twitter page where I comment on global #Antisemitism/#AntiZionism and the Arab-Israeli conflict.


The Misconceptions

  • While practitioners routinely use the terms database and DBMS interchangeably, they are, of course, distinct (the former is an organized collection of data, the latter the software that manages it).
  • Relational databases do not consist of tables, and 'relational' does not come from "relationships among tables" (they consist of relations, a kind of mathematical sets adapted for and applied to database management that can be visualized as R-tables which do not play any part in the RDM).
  • A database relation does not have "fields" -- a physical implementation term -- but attributes defined on simple domains (when a relation is visualized as a R-table, attributes appear as columns). Thus, relations do not have "common fields", only some (see below) have foreign keys (FK) -- attributes that reference the primary keys (PK) of other relations.
  • There are several types of relationships at the conceptual level among individual entities within a group thereof, and  several types among entity groups. Of the latter, 1:1 and M:1 are just special cases of M:N relationships.
  • Notwithstanding the current understanding of the RDM (such as it is), 1:1 and M:1 relationships are not represented by referential constraints (the correct term for "relationships among tables") via "embedded FKs", but same as the general M:N case: by association relations. Consequently, not all relations have FKs, only association relations do, each of which has as many FKs as the relations it associates and together with which it is target to a referential constraint.

The Correct Answer

"Database relationships" are constraints and, in one case, combined with relations.

Confusing the three levels of representation leads to confusion of the corresponding types of models -- conceptual, logical, and physical -- models which are, in turn, confused with the data model used to formalize conceptual as logical models[1,2,3]. To preempt this common confusion we reserve the term relationship for the conceptual level.

A conceptual model consists of a multigroup -- a collection of related entity groups. There are several types of relationships[4], which are represented at logical level by constraints[5,6]. One type are intergroup relationships -- where 1:1 and M:1 are just special cases of M:N -- that are represented by association relations and referential constraints[7,8]. A relationship among n groups is represented by one association relation with n FK attributes and n referential constraints -- one of each for every one of the n relations it associates.

There are, thus, "simple" relations that represent groups of entities, and "compound" association relations that represent relationships among entity groups. The tuples of the former represent entities of one type, those of the latter can be viewed as representing "relationship entities", which can also have attributes of their own.

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., Conceptual Modeling for Database Design

[2] Pascal, F., Conceptual (Business) Modeling, Logical Database Design and Physical Implementation

[3] Pascal, F., Understanding Conceptual vs. Data Modeling Parts 1-4

[4] Pascal, F., Relationships and the RDM series

[5] Pascal, F., Relationships, Rules, Relations, and Constraints

[6] Pascal, F., What Meaning Means: Business Rules, Predicates, Integrity Constraints, and Database Consistency

[7] Pascal, F., Fourth Order Properties series

[8] Pascal, F., 5NF, Association Relations, and Join

No comments:

Post a Comment

View My Stats