“The data in a relational database is stored in form of a table. A table makes the data look organized. Yet in some cases we might face issues while working with the data like repetition. We might want enforce rules on the data to avoid such technical problems. Theses rules are called constraints. A constraint can be defined as a rule that has to enforced on the data to avoid faults. There are three kinds of constraints: entity, referential and semantic constraints. Listed below are the differences between these three constraints:Before we tackle the main subject, let's get some misconceptions out of the way. As we have explained so many times:
1. Entity constraints -- primary key, foreign key, unique, NULL -- are posed within a table and used to enforce uniqueness and to define no value [respectively].
2. Referential constraints -- foreign key -- are enforced with more than one table for referring other tables for analysis of the data.
3. Semantic constraints -- datatypes -- are enforced in a table on the values of a specific attribute and help the data segregate according to its type. Example: name varchar2(30).”--GeeksforGeeks.com
- Data is not "stored in a form of a table" -- it can be stored in any number of physical formats, at the discretion of DBMS designers and DBAs. Physical independence is a core advantage of the RDM.
- A table does not "make the data look organized". Data is by definition organized -- be it relationally or not -- otherwise it would be random noise not data. A database relation can be visualized as a R-table, but tables do not play any role in RDM.
- While some "repetition" (i.e., redundancy) is prevented by constraints (e.g., uniqueness), others are avoided by database design (e.g., 5NF DB relations).
And now to constraints.
------------------------------------------------------------------------------------------------------------------
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
- 08/31 TYFK: Normalized, Fully Normalized, Non-Normalized, Denormalized -- Clearing the Mess
- 08/13 OBG: The Myth of Market Based Education
- 08/05 TYFK:Facts, Properties, Relationships, Domains, Relations, Tuples
- 07/22 Documents and Databases
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.
------------------------------------------------------------------------------------------------------------------
Fundamentals
A relational database represents a conceptual model consisting of objects with properties and relationships. Properties-object modeling (PoM) is grounded in Ontological Commitment to Properties (OCP) according to which an object is a named collectiion of co-ccurring properties.
- Objects:
- primitive: entities;
- compound:
* entity groups;
* multigroup.
- Properties:
- individual: of entities[1];
- collective:
* of groups[3];
* of multigroup[4].
- Relationships:
- among entity properties[2];
- among entities[3]:
- among-groups[4].
Properties fall into four categories:
- 1st order properties (1OP): direct properties of individual entities [1] ;
- 2nd order properties (2OP): relationships among 1OPs are indirect properties of entities [2] ;
- 3rd order properties (3OP): relationships among entities within the same group are collective properties of the group [3];
- 4th order properties (4OP): relationships among groups are collective properties of the multigroup [4] .
In a relational database:
- Domains represent properties;
- Relations represent entity groups;
- attributes represent properties in the context of a specific group;
- tuples (sets of attribute values) represent facts (values of entity properties).
Constraints ensure consistency of the database with the conceptual model, which is why they are all semantic. There are five categories of relational constraints:
- Domain constraints
- Attribute constraints (1OPs)
- Tuple constraints (2OPs)
- Multi-tuple constraints (3OPs)
- Multi-relation constraints (4OPs).
Debunking
Business rules jointly specify the conceptual model in natural language and constraints are their formal database representation expressed in a data sublanguage. As such they are all semantic -- they do not prevent "technical problems" or "faults", but inconsistencies of the database with the conceptual model (misrepresentation thereof).
"Entity constraint" reflects common confusion of levels of representation: entity is conceptual, constraint is logical. Moreover, this terminology suggests that uniqueness and referential constraints are tuple constraints in our relational classification. But just because PKs/CKs and FKs are declared within the table definition in SQL is not a sound basis for lumping them together. This is obvious from the qualification that "referential constraints are enforced with more than one table for referring other tables" (what "analysis of the data"????). In fact:
- Uniqueness constraints are multi-tuple (why?) and represent 3OP rules;
- Referential constraints are multi-relation and represent 4OP rules.
We follow Codd (not Date and Darwen) and distinguish between database domains and programming datatypes (SQL does not support relational domains). Be that as it may,
- Why are only domain constraints semantic?
- What about the rest of relational constraints? Clear confirmation of lack of foundation knowledge in the industry: most practitioners know only SQL and are unaware of them, which explains all the absurd claims that the RDM does not support relationships.
We close with a rare online pronouncement that is actually correct:
“Because of the resemblance of SQL tables to relations, terms that involve relations get sloppily applied to tables. But although you can borrow terms and give them SQL meanings -- value, table, FD (functional dependency), superkey, CK (candidate key), PK (primary key), FK (foreign key), join, and, predicate, NF (normal form), normalize, 1NF, etc -- you can't just substitute those SQL meanings for those words in RDM definitions, theorems or algorithms and get something sensible or true. Moreover, SQL presentations of RDM notions almost never actually tell you how to soundly apply [them] to a SQL database. They just parrot RDM presentations, oblivious to whether their use of SQL meanings for terms makes things nonsensical or invalid.”
References
Semantics, Relations and the Missed Link: Constraints
What Meaning Means: Business Rules, Predicates, Integrity Constraints and Database Consistency
Relationships and the RDM
Levels of Representation: Relationships, Rules, Relations and Constraints
Integrity Is Not Only Referential: DBMS vs Application Enforced Constraints
What Meaning Means: Business Rules, Predicates, Integrity Constraints and Database Consistency
Third Order Properties and Multi-Tuple Constraints: An Example
No comments:
Post a Comment