Note: This is a re-write of an earlier post. About TYFK posts (Test Your Foundation Knowledge) see the post insert below.
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.“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.”
------------------------------------------------------------------------------------------------------------------
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 UPDATES
-12/24/20: Added 2021 to the POSTS page
-12/26/20: Added “Mathematics, machine learning and Wittgenstein to LINKS page
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 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.
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 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.
------------------------------------------------------------------------------------------------------------------
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