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.

What is the entity class of the result of relational projection

in relational algebra or its equivalent SQL expression?:

SELECT a1, a2 FROM r

Is the entity class of the result the same as that of r? What’s the primary key of the result?

Neither SQL nor RM provide a consistent answer. The answer depends on whether r has other attributes in its primary key besides a1 and a2.  If the primary key only has a1 and/or a2, then the entity identity is preserved and the output of the query has the same primary key and describes the same entity set as r.  

If the primary key of r contains other attributes besides a1 and a2, then SQL and RM produce a result that can no longer be reliably associated with entities in r and even the number of unique entities in the result may change. The new effective primary key becomes the combination (a1, a2), with no explicit claim of what entity class they identify. If r is a set of hammers, the projection may be a set of quite different, unidentified things. Entity integrity has broken down.

DataJoint’s projection operator r.proj('a1', 'a2') is constrained to always include the primary key of its argument r. The projection operator can rename primary key attributes but never exclude them from the result. The result is still a set of entities of the same class (even if some non-key properties may be removed or renamed or added). Each entity in the result is associated with an entity in the source r.

When coming across DataJoint, Python programmers often ask, “What is the difference between DataJoint and other ORMs such as SQLAlchemy or Django ORM?”

ORMs (object-relational mappers) are libraries that allow defining and manipulating data in relational databases using native constructs of the host language (e.g. Java, Python). Very commonly, ORMs represent tables as classes. Python already has several established ORMs such as SQLAlchemy, the Django ORM, Pony, and Peewee. Traditionally, ORMs are designed to provide a persistence layer for objects in applications.

In some respects, DataJoint may be classified as another ORM: it represents tables in the relational database as classes in Python and MATLAB. However, DataJoint is dedicated to do one job exceptionally well: to build data pipelines for science projects. It is centered on the concept of a workflow from data entry to data acquisition to processing and analysis. Data dependencies and data integrity are carefully maintained at each step by means of referential constraints and transactional processing. Complex data types such as multidimensional arrays are transparently serialized.

Compared to other ORMs, DataJoint is more data-centric: the structure and integrity of the data are of primary concern. This may be contrasted by other ORMs where the data storage is a secondary consideration in the overall application-centric design.

DataJoint is designed to ingrate data and computation. Modern neuroscience experiments involve many steps of processing, synchronization, and filtering of acquired data followed by many kinds of statistical analysis. DataJoint provides a streamlined AutoPopulate process whereby each DataJoint class defines both the structure of the data and the code for computing the data. Once the automatic computation is defined, DataJoint allows to automatically compute any missing data. A built-in job reservation process allows distributing the work to an arbitrary number of computing nodes.

Finally, DataJoint is designed for simplicity and quick learning. It is based on a minimal set of concepts sufficient to define, populate, compute, and query complex data pipelines.

Have you noticed that DataJoint’s ERDs (entity-relationship diagrams) form directed acyclic graphs (DAGs)?  For example, the following ERD depicts the preprocessing pipeline for two-photon imaging data in Andreas Tolias’ Lab (the code is at https://github.com/cajal/pipeline).

In this diagram, all the dependencies are directed downward.  Every edge is a foreign key from the downstream node to the upstream one.  Yes, it’s important to note that the arrows depict the direction of dependency, opposite to the direction of the foreign key.

Thus the ERD has no loops.  This make sense if you keep in mind that DataJoint is designed to support data pipelines, i.e. sequences of steps to perform in the course of a study from data acquisition to processing to analysis.

An investigator recently asked me whether DataJoint’s commitment to acyclic dependencies is a limitation of its representational power.  After all, conventional E-R designs do not have a consistent direction and can form cycles.  Textbooks on database design often feature tables with foreign keys into themselves.

For example, Panel A of the following figure depicts a textbook example of a cyclic relationship.  A member of the Employee class may optionally have a manager who is also an Employee.  This common design is often translated into a relational design with a table with a nullable foreign key referencing itself.

However, the same relationship can be expressed with an acyclic design (Panel B) by introducing a new entity class Subordinate with two relationships to Employee: is a and reports to. This design would translate into two tables: Employee with no foreign keys and Subordinate with two foreign keys into Employee. The first foreign key is defining: it forms the primary key of Subordinate. The second foreign key is made from dependent attributes. Panel C depicts the DataJoint ERD for this design.

The acyclic design has multiple advantages. The foreign keys are no longer nullable: if an employee does not report to anyone, her entry is excluded from Subordinate altogether.  The data become easier to enter, modify, and delete. For example, employees can be entered in any order followed by entering of the reporting relationships. Deleting a subset of employees becomes straightforward with one step of cascading delete. With a self-referencing employee table all these operations become problematic.

The Python code defining these two DataJoint classes would be as follows:

Python code for the Employee/Subordinate relationship

@schema

class Employee(dj.Manual):

   definition = """ # company employee

   emp_id : int # employee id within the company

   ---

   fullname : varchar(120)

   date_of_birth : date

   hire_date : date

   -> Department

   """

@schema

class Subordinate(dj.Manual):

   definition = """  # employee who reports to a manager

   -> Employee

   ---

   (reports_to) -> Employee

   """

Any ER design with a cyclic network of relationships can be refactored as a directed acyclic graph.

The synaptic connectivity example from yesterday’s post provides another example of transforming a cyclic relationship into an acyclic one.

The directed acyclic nature of DataJoint’s pipelines improves their interpretability and predictable appearance and enables more consistent internal handling of dependencies (e.g. in cascading deletes). The downward flow of dependencies suggests possible workflows: the data on top of the pipeline is populated first and the next steps are inferred from the graph.

In the traditional design process, the conversion from an E-R design to relational database design produces two types of tables: entity-representing and relationship-representing.  Simple 1:1 or N:1 relationships don’t need relationship-representing tables and require only a foreign key originating from the N side of an N:1 relationship. In contrast, M:N relationships, higher-order relationships (e.g. ternary L:M:N), or relationships with their own attributes require a dedicated relationship table.  Thus there are two types of relationships: those requiring a dedicated table and those that don’t (although both types require foreign keys).  Relationships with their own tables get their own name and can be referenced directly whereas the table-less relationships only get a foreign key and are identified by the entities that they link.

However, the distinction between entity-representing and relationship-representing tables is somewhat arbitrary.  We can always modify our E-R design to replace relationships that require tables with another kind of entity.  For example, synaptic connections between neurons can be thought of as a many-to-many relationship (See Figure below).  Alternatively, a new entity class, Synapse, could replace the relationship.   More abstractly, if we represent a graph of binary relationships, we could think of its edges as relationships or we could think of them as their own entities with their own relationships to the nodes.

I propose that, in the DataJoint model, we always redefine relationships that require their own tables as entities.  This purely semantic convention allows simplifying terminology and discussions.  We no longer need relationship-representing tables.  All tables represent entity classes and all relationships are expressed as foreign keys.  All relationships are directed and binary and their cardinality is always 1:1 or N:1.  If other kinds of relationships are necessary, they need to be redefined as entities.

Of course, users may think of some entities as relationships rather than entities.  For example, Synapse can be thought of as a many-to-many relationship between Neurons.  However, DataJoint’s notation and terminology will not distinguish relationship-representing tables as such.

The DataJoint model is the synthesis of the relational data model and the entity-relationship model.  It preserves the logical rigor of the relational model while preserving the conceptual clarity of the E-R model.  DataJoint is both models rolled into one: the basic units of DataJoint pipeline are entity classes that are also relation variables.

The E-R model and the relational data model both concern the structure of the database.  However, the relational model is also suitable for data queries, providing two distinct paradigms: relational algebra and relational calculus.  The latter became the foundation for SQL.

DataJoint unifies the two data models for data queries too.  It extends E-R concepts into its query language.  Its query language most closely resembles relational algebra but since it preserves entity integrity in all its operations, it can also be thought of as an algebra of entity sets or an entity set algebra.

DataJoint’s operators correspond to similar operations of relational algebra but are modified and restricted in ways that ensure that both the inputs and the outputs are meaningful entity sets.  Derived relation variables resulting from DataJoint expressions may be thought of as new entity classes with defining foreign keys into their input classes.  For example, the expression

experiment.Mouse & stimulus.Trial

can be thought of as a new table with a defining foreign key into experiment.Mouse whereas

experiment.Mouse * stimulus.Trial

is thought of as a new table with defining foreign keys into both experiment.Mouse and stimulus.Trial.

DataJoint’s restriction, projection, and aggregation operators preserve the same primary key as their argument and correspond to the original entity.   They can be thought of as new computed tables.  For this reason, DataJoint’s projection operator cannot project out the primary key attributes.

DataJoint’s aggr operator plays the role of the GROUP BY clause in SQL or the aggregation operation in relational algebra.  Unlike its counterparts, DataJoint’s aggr operator aggregates on entity set with respect to the other rather than group by an arbitrary set of attributes.

The same principle of preserving entity integrity permeates through all operators and is behind DataJoint’s lucidity. Therefore, to differentiate DataJoint’s query language from relational algebra, we can refer to it as an algebra of entity sets and entity set operators.

In a DataJoint table definition, the foreign key

-> experiment.Session

expresses the constraint that an entity of the current class shall never appear without a matching entity of class experiment.Session.
This definition also brings in the primary key attributes from experiment.Session into the current class definition if they are not yet part of the current class. We do not even need to know what the primary key of experiment.Session to impose the constraint.

In SQL, the same constraint has the following form:

animal_id INT NOT NULL,

session_id SMALLINT NOT NULL,

CONSTRAINT FOREIGN KEY (animal_id, session_id)

     REFERENCES experiment.Session (animal_id, session_id)

The SQL variant specifies several extra steps and extra pieces of information:

First, we must declare the attributes animal_id and session_id to be used for referencing the primary key attributes of experiment.Session. They must match the data types of the primary key attributes to experiment.Session. It is not helpful that SQL syntax allows defining a foreign key between attributes on incompatible datatypes. Although it is possible to declare foreign keys that reference other unique keys of the referenced table besides the primary key, it is uncommon and usually a bad idea. Some database engines even allow referencing sets of attributes that do not constitute a unique key, which leads to nonsense.

Second, the foreign key constraint specifies the names of the primary key attributes of experiment.Session. Again, since the most common and valid way to reference entities is by their primary key, this syntax is redundant.

Finally, the foreign key constraint specifies the names of the foreign key attributes of the referencing class. In DataJoint, we usually keep the same name as the primary key attribute of the referenced class, so this information becomes redundant also.

In case we do want to rename the foreign key attributes in DataJoint, we can use the following syntax

(subject, session) -> experiment.Session(animal_id, session_id)

An introductory course in Database Systems will likely present two closely related data models:  the Entity-Relationship Model (ERM) and the Relational Data Model (RDM).  The ERM is useful for conceptual modeling of real-world entities, their attributes, and relationships between entities of different classes.  The RDM supports logical modeling suitable for implementation.  Much of the course will focus on how to convert ERM designs into the RDM; such conversion remains an art even though automated tools have been proposed.  Furthermore, the process is irreversible: an ERM design cannot be straightforwardly recovered from its RDM counterpart.

Database programmers rarely bother with a formal ERM design.  With experience they learn to model entities and relationships in their heads and churn out SQL table declarations.  Just like a Zion operator from the Matrix movies perceives the state of the Matrix from the code raining on her green screen, database programmers infer the underlying conceptual design from existing table declarations and foreign key constraints defined by others.  Tools for reverse-engineering database schemas do not quite recover the entity-relationship design but help visualize the structure of tables and foreign key constraints to help infer it.

Why do we need two data models to design one database?  Why not have a single data model that can be used for both conceptual modeling and for implementation?  Why is the ERM not suitable for logical modeling and the RDM is a poor conceptual model?

I will speculate that part of the problem lies in the chronology of the two inventions.  The RDM was defined in 1969 (by Edgar F. Codd) whereas the ERM did not appear until much later, in 1976 (Peter Chen).  The RDM was inspired by the mathematical concept of relations from set theory.  A relation is defined as a subset of the Cartesian product of several sets  (domains).  Although his descriptions implied that relations corresponded to sets of real-world entities of various types, Codd formulated his model in much more general and abstract terms.  By the time the ERM was described, relational concepts were already firmly ingrained.

I will further speculate that had the chronology been reversed and had the relational model been constrained by E-R concepts, many of its core definitions and operations would have turned out quite different.  Perhaps we would have a relational-like model that kept its focus on modeled entities and their relationships.  Then perhaps this model would suit the needs of both conceptual and logical design.  Furthermore, abstract and arcane concepts such as functional dependencies and normal forms would be formulated in much more approachable terms such as proper delineation of entities.

The core idea of DataJoint is to reformulate the Relational Data Model to prioritize its effectiveness in the role of a Entity-Relationship Model.  The resulting data model should obviate the need for two separate processes, or, since ERM is rare in practice, greatly improve the conceptual aspects of the relational data model.

This unification of conceptual and logical modeling required major revisions of many established concepts in traditional database design.  Since SQL has long become the lingua franca of relational databases, we will often contrast how solutions in DataJoint differ from those in SQL.  Most DataJoint users learn database programming without ever touching SQL.  Even for them, such examples may still help clarify basic concepts.  For users who already know SQL and relational concepts from other sources, the examples will help map their knowledge to DataJoint.