Strong Entity Integrity: Part 1

DataJoint’s claim to fame is that it unifies the Entity-Relationship Model (ERM) and the Relational Model (RM) into one coherent model that is suitable for conceptual and logical modeling, data definition, and queries.

In SQL and the relational model in general, the principle of entity integrity makes sure that data stored in a table correspond to a set of identifiable entities, an important property for unifying the RM and the ERM. The entity integrity constraint states that every table (or relational variable) must have a primary key and the values of primary key attributes cannot be null. However, this constraint applies only to stored tables and does not explicitly extend to results of queries or subqueries. SQL and RM abandon the notion of entity integrity for derived data resulting from queries. The result of a relational algebra expression or a SELECT statement in SQL no longer possesses an explicit primary key and the correspondence to entity classes is not preserved.

DataJoint adheres to a stronger version of entity integrity:

All data (stored or derived) always appear in the form of sets of entities from a well defined and readily identified entity class with an explicit primary key.

DataJoint operators for queries are carefully constructed to preserve entity integrity in the derived result and in every subquery. This series of posts explains how DataJoint supports strong entity integrity across each of its operators and what compromises are made to achieve this.