Sunday, May 10, 2020

TYFK: What Is A Database Relationship?



Note: This is a re-write of an earlier post. About TYFK posts (Test Your Foundation Knowledge) see the post insert below.

“Here two or more table[s] are related with each other. This is Database relationship. Database relationship is used a lot ... [in] relational database management systems ... shortly called RDBMS. Here is Join_data [sic] table and Interview_data table. For creating a relational database management system both of the table[s] must have a common field. Here Employee_ID is a common field ... Database relationship types: One-To-One relation, One-To-many relation, Many-to-many relation. 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.”
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.



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

SUPPORT THIS SITE 

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.

DATA FUNDAMENTALS 

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. 

LATEST UPDATES

  • 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.


LATEST PUBLICATIONS (order PAPERS and BOOKS)


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


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.
  • @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



  • Relational databases do not consist of tables, 'relational' does not come from "relationships among tables"a nd there are no "fields".
  • A relational database does not represent only three types of relationships.
  • Not all relations need have common attributes, which, in fact, should not be used as much as they have been in industry practice.

Debunking


A relational database represents formally at the logical level a multigroup -- a collection of related entity groups -- at the conceptual level[1]. It consists of relations[2] (which can be visualized as R-tables on a physical medium[3]) with attributes (which can be visualized as columns), the values of which are drawn from simple domains and are treated as atomic by the relational data sublanguage[4]. Each relation represents either an entity group, or a relationship among groups.

Note: Fields are a physical implementation term. A logical attribute representing a conceptual property may or may not be implemented in physical storage as a field in a file. Confusion of levels of representation is rampant and damaging in the industry[5].

At the conceptual level first order properties (1OP) are the properties of individual entities the sharing of which qualify them for group membership. Several categories of relationships are represented by a logical database.
  • Relationships within a group:
- among 1OPs are second order properties (2OP) of entities;
- among entities are third order properties (3OP) and are collective properties of groups;
  • Relationships among groups:
- due to relationships among their individual entity members*;
- due to relationships at the aggregate group level.
are fourth order properties (4OP) and are collective properties of the multigroup[6].

The three types of relationships mentioned in the quote are those giving rise to the 4OPs that I marked with an asterisk. For n groups their general form is M1:M2:M3:...Mn where Mi => 1. When n=2 the relationship reduces to M1:M2 (or M:N), M:1 and 1:1 being special cases (i.e., M2=1, or M1,M2=1)[7].

2OP, 3OP and 4OP relationships are specified as business rules (BR) in natural language that formalize as predicates expressible to the DBMS as constraints in a FOPL-based relational data sublanguage[8]. For example, 3OP relationships are declared as PK (uniqueness), functional dependency and aggregation constraints.

To date for n=2 groups a M:1 relationship has been represented by an embedded FK and a referential constraint, and a M:N relationship by an association relation with two FK attributes and two referential constraints. For n>2 groups there are n FK attributes and referential constraints -- one for each of the n relations it associates.

But according to a new understanding of the RDM, embedded FKs are no longer to be used -- all intergroup relationships, including the M:1 special case, should be represented by association relations[7,9].

Conclusion


Relationship is a term general enough to apply at both the conceptual and logical levels -- there is nothing wrong per se with 'database relationship'. The problem is that levels of representation are confused in the industry, which inhibits understanding. To avoid such confusion we have recommended a three-fold terminology: conceptual modeling, logical database design (rather than data modeling), and physical implementation[10]. In this context we urge to use database constraint at the logical level and reserve relationship for the conceptual level.


 
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.


References

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

[2] Pascal, F., What Relations Really Are and Why They Are Important 

[3] Pascal, F., Tables, So What? 

[4] Pascal, F., Simple Domains and Value Atomicity 

[5] Pascal, F., The Conceptual-Logical Conflation and the Logical-Physical Confusion 

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

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

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

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

[10] Pascal, F., Levels of Representation: Conceptual Modeling, Logical Design and Physical Implementation



No comments:

Post a Comment

View My Stats