When a foreign key is a secondary attribute, its value may made optional in one of three ways.

Let’s say that an attribute of the entity set Student is the student’s major and the valid majors are in Major. We need to allow for students with and without majors. Here are the three ways to define this relationship with their respective strengths and weaknesses.

Solution 1: Make the foreign key nullable

Definition of nullable foreign keys is described in docs.datajoint.io. However, this feature is only scheduled for release in the upcoming datajoint-python version 0.11.

The definitions of the two tables would appear as follows:

:: Major

major  : char(8)    # abberviated name, e.g. BIOL, PHYS, NEUROSCI

---

major_name  : varchar(255)  # full major name

-> Department

:: Student

student_id  : int

---

full_name     : varchar(255)   #  e.g.  DOE, Jane S

date_of_birth : date

sex           : enum('M','F','U')

-> [nullable] Major

Solution 2: A separate association table

If an attribute or attributes are optional, sometimes it makes more sense to put them in a separate dependent table. For the example above, we would leave out the major from the student table and add a new table StudentMajor to associate a student with a major:

:: Student

student_id  int

---

full_name     : varchar(255)   #  e.g.  DOE, Jane S

date_of_birth : date

sex           : enum('M','F','U')

:: StudentMajor

-> Student

---

-> Major

Solution 3: Special value

Finally, one could keep the dependency required but add a special value to Major to indicate missing major, for example, ‘NONE’ or ‘UNDECL’.

Then Student will be declared as

:: Student

student_id  : int

---

full_name     : varchar(255)   #  e.g.  DOE, Jane S

date_of_birth : date

sex           : enum('M','F','U')

-> Major

Which solution is best?

Right away, let’s state that Solution 3 is rarely optimal. Special values to indicate missing values are generally considered poor technique because they require additional implicit semantics and rules. However, when such special values are institutionalized, this choice may be justified.

The choice between Solutions 1 and 2 may depend on other considerations. For example, if an attribute may change its value, it may be better to place it in a separate table as in Solution 2 rather than to keep it together with the permanent attributes. For example, can a student declare her major or change her major later on? Under Solution 1, changing the major would require an update of the existing student record. In contrast, under Solution 2, the major can be changed without perturbing Student.

In DataJoint, tuples are considered immutable and the proper way to manipulate data is through inserts and deletes rather than updates. Even though updates are implemented, they are more of a workaround for special cases.

Since Solution 2, has always been available, we are only now getting around to implement nullable foreign keys for Solution 1.

If the optional attribute is indeed permanent, then Solution 1 may be preferable because it saves us an extra table and potentially simplifies some queries. However, as always, dealing with NULL values may lead to some counterintuitive results in queries involving comparisons. Intuitive implementation of operators involving NULLs is what has kept me from promoting nullable foreign keys. We are working to define them more most logically.

For example, the results of Student - Major under Solution 1, would always produce the empty set with the current implementation of restriction since MySQL’s IN and NOT IN operators always yield NULL when testing NULL values. We will fix the logic so that Student - Major would yield students where major is NULL.

Example 1: global argmax

Restricting a relation to a subset based on the average or maximum value of some attribute seems like it should be an easy operation.

For example, consider the table with heading

Student: (student*, height)

Now let’s select the tallest student.

You may attempt the following in DataJoint:

Student & 'height=max(height)'

or in SQL:

‍

SELECT student

WHERE height=max(height)

However, both fail. Aggregation functions such as max, sum, or avg are not allowed in restrictions. Restrictions apply to each row and all their functions must operate on attributes in that row and they cannot examine other rows.

Therefore, in DataJoint (version 0.10.0+), the solution would be

‍

Student * dj.U().aggr(

   Student, 'max_height=max(height)'

   ) & 'height=max_height'

We can break this expression up into three steps:

‍

m = dj.U().aggr(Student, max_height='max(height)')

s = Student * m

r = s & 'height=max_height'

The first expression uses dj.U() (the relation with one row and zero attributes) to aggregate the maximum height. The resulting relation m has a single non-primary attribute `max_height` and one row with the highest value of height.

The second expression joins max_height to every row in Student. Now every row has both the height and the maximum height and we are ready for the restriction in the final step.

The literal translation into SQL would be

‍

SELECT

   student, height

FROM

   Student NATURAL JOIN

       (

       SELECT

           max(height) as max_height

       FROM

           Student

       ) as m

WHERE

   height = max_height

However, SQL also allows using results of subqueries as scalars in WHERE clauses, allowing a simpler expression

‍

SELECT

   student, height

FROM

   Student

WHERE

   height = (SELECT max(height) FROM Student)

Example 2: Group average

Let’s use the tables Enroll listing students in each course and Course listing all coures.

Enroll: (course*, student*)

Course: (course*, course_description)

Let’s find all students above average height in each course.

Again, a naive query might look something like

1

Enroll * Student & 'height > avg(height)'

This fails for the same reason: aggregation functions are not allowed in restriction expressions.

Again, we must first compute the averages per course and join them into the original expression before restriction:

‍

# extend Enroll with student height

e = Enroll * Student  

# average height in each course

h = Course.aggr(e, avg_height='avg(height)')  

# extend enrollment with average height per course

s = e * h

# restrict enrollment to above-average-height students

r = s & 'height>avg_height'  

Or, more succinctly,

‍

e = Enroll * Student  

r = e * Course.aggr(e, avg_height='avg(height)')

   & 'height>avg_height'

An efficient equivalent expression in SQL could be:

‍

SELECT

   course, student, height

FROM

   Enroll NATURAL JOIN Student NATURAL JOIN (

       SELECT

           course, avg(height) as avg_height

       FROM

           Enroll NATURAL JOIN Student

       GROUP BY course) as h

WHERE

   height > avg_height

One of the most difficult relational operations to comprehend is the relational division.

We re-define division as

Division B ÷ C with respect to A is the subset of A for which every matching element in C has a match in B.

In DataJoint, relational division is performed by the following expression:

D = A - (A*C - B)

Most traditional definitions also exclude the non-primary attributes from the result, making it equivalent to

D = A.proj() - (A*C - B)

From this definition, it follows that relational division is a form of restriction on A and it preserves its entity class and primary key.

The reason that the traditional formulations of relational division are so difficult to articulate is that they present division as a binary operator on B and C, introducing A implicitly through operations on the relations’ headings. DataJoint requires that A be explicitly formed. Therefore, division in DataJoint is a ternary operator.

As an example, consider the following fragment of a university database (in Python):

@schema

class StudentMajor(dj.Manual):

   definition = """  # Student with major

   -> Student

   ---

   -> Major

   """

@schema

class CompletedCourse(dj.Manual):

   definition = """  # Student's completed course

   -> Student

   -> Course

   ---

   -> Grade

   """

@schema

class RequiredCourse(dj.Manual):

   definition = """  # Course required for major

   -> Major

   -> Course

   """

Then the division CompletedCourse ÷ RequiredCourse with respect to StudentMajor is the list of all students with majors who have completed all the courses required for their major.

required_courses = StudentMajor()*RequiredCourse()

remaining_courses = required_courses - CompletedCourse()

candidates = StudentMajor() - remaining_courses

or as a single statement:

candidates = StudentMajor() - (

   StudentMajor()*RequiredCourse() - CompletedCourse())

Advanced: The conventional binary relational division can be mimicked using DataJoint’s special universal entity set dj.U by replacing A = dj.U(B.primary_key – C.primary_key) & B().

Argh, semijoins and antijoins are misnamed.

There is a zoo of joins in relational algebra (and SQL): cross join, equijoin, natural join, inner join, theta join, left join, and outer join. They follow a pattern: they have all the attributes of its operands. Semijoins and antijoins are different. They have only the attributes and the tuples from their left argument. Indeed, semijoins and antijoins are a form of restriction (or selection).

In DataJoint, the terms semijoins and antijoins are not used to avoid the confusion. Both these operators fit the definition of the restriction operator. DataJoint uses the same operators & and – as for other forms of restriction.

Therefore, the Strong Entity Integrity considerations for these operators are the same as in restriction.

However, there is something in common with the join: the join compatibility rules. Restriction by another relation should only be performed on attributes that are in the primary key or a foreign key in both operands: they must be part of the identifying attributes that is used to identify and match entities across different entity sets.

DataJoint’s principle of Strong Entity Integrity (SEI) requires that queries must not introduce new classes of entities: projection and restriction operators preserve the entity class of its operand whereas the join operator yields a new entity class but one comprising the combination of the entity classes of its operands. A DataJoint query make obvious the entity class of its result.

One operator that most egregiously violates this principle in relational algebra and SQL is the aggregation operator also known as GROUP BY. In its traditional formulation, aggregation allows performing summary operations on groups of tuples identified by a combination of grouping attributes. The effective primary key of the result are the grouping attributes. Therefore the query creates a new type of entity in the middle of a query that is not traced to any explicitly defined entity class. SQL allows a further deviation from entity integrity with its ROLLUP functionality producing results comprising mixtures of entity classes with different primary keys.

DataJoint respects SEI and defines aggregation as a binary operator allowing summary operations on subsets of entity set B grouped by entities from class A. It has the following notation: A.aggr(B, …') where … is a list of aggregation computations such as n='sum(attr)`. In this way aggregation is a form of projection, preserving the entity class of A.

A.aggr(B, …) is equivalent to SELECT … FROM B GROUP BY . As always, the primary key cannot be omitted.

DataJoint simply makes explicit what programmers imply implicitly when they perform a GROUP BY operation. When grouping, we conceive of some sort of entity represented by the grouping attributes. In most cases, that entity class is already defined, often in the form of a table. Yet SQL forces users to spell out the list of attributes rather than explicitly specify the aggregating entity set.

For example, consider the following table definition for college course grades:

@schema

class Grade(dj.Manual):

   definition = """

   -> Student

   -> Course

   ---

   grade: decimal(3, 2)  # grade e.g. 3.67

   """

Then studentss GPA can be computed as

Student().aggr(Grade(), gpa='AVG(grade)')

The entity class of the result is the same as that of the aggregating entity: the result is still a set entities of class Student.

In relational algebra, the join operator is defined as the Cartesian product (cross join) of two relations A and B followed by a restriction.

The unrestricted cross join constructs a new entity class as the combination of the two entity classes of its inputs.  Then the entity class of the plain cross join is straightforward: it is the combination of A‘s entities class and B‘s entity class. Then the primary key of the cross join should be the concatenation of the primary keys of A and B.

DataJoint provides only one kind of join: the natural join.  The join operator is further constrained to be valid only when all shared attributes are in the primary key or in a foreign key in both operands.  If the two operands share an attribute that is not part of a primary key or foreign key in either of them, DataJoint will raise an error.  As the primary key of the output, DataJoint designates the union of the primary key of the operands.  This definition follows the logic of the primary key of the cross join.

A natural join is a cross join followed by an equality restriction.  As we have already seen in the restriction discussion (Strong Entity Integrity: Part 3 — Restriction), equality restrictions involving primary key attributes can alter the true (minimal) primary key. As a result, a natural join may have one or more candidate keys that are subsets of the full primary key of the cross join.

Therefore, again, DataJoint makes a compromise for the sake of Strong Entity Integrity: It designates as the primary key the union of the primary keys of the join’s operands, supporting the idea that the entity class of a join is the combination of the entity classes of its operands. However, from the purely relational principles, we may often find a more concise candidate key whose entity class may not be well defined.

To analyze these scenarios, we make use of the notation of functional dependencies and apply Armstrong’s axioms to derive possible valid primary keys of join results.

Let ab → cd designate a relation with the primary key attributes a and b and dependent attributes c and d. The fact that ab comprise the primary key means that cd are functionally dependent on ab. When we natural join two relations, we must designate a new functional dependency that is congruent with the two original functional dependencies. DataJoint accomplishes this with the simple rule of keeping primary key attributes of the operands in the primary key of the result. In the table below, we apply Armstrong’s axioms to derive other candidate keys that may be shorter than DataJoint’s choice.

These derivations demonstrate that DataJoint often suggests a primary key that is a superset of shorter candidate keys. We justify this choice by the adherence to Strong Entity Integrity: The rules of entity identity must be simple for humans to predict and comprehend.

As an illustration, let’s consider the example of a table containing a filtered image that’s defined as follows (in Python):

@schema

class FilteredImage(dj.Computed):

   definition = """

   -> Image

   ---

   -> ImageFilter

   """

Here ImageFilter is not included in the primary key and is not part of the entity identity of FilteredImage. Therefore, there can be only one FilteredImage for each Image. This corresponds to the Lookup scenario in the table above. Then the join FilteredImage() * ImageFilter() simply appends the dependent attributes of ImageFilter to FilteredImage without altering the entity class: each entry can be fully identified by the image alone. Yet DataJoint will include the primary key of FilteredImage in the primary key of the result, maintaining the principle that the entity class of a join is any combination of the entity classes of its operands as if any join is a cross join.

We use the term restriction to refer to the selection of a subset of an entity set. If database terms, restriction selects a subset of rows from a table whereas projection selects columns. We avoided the term selection because in SQL queres, confusingly, the SELECT clause performs projection whereas the WHERE clause performs selection (restriction).

Since restriction does not affect the heading of its argument, restriction may seem as the most benign operation when it comes to entity integrity. It stands to reason that, if r is a set of hammers, then its subset

or the SQL query

SELECT * FROM r

WHERE color='red'

will also represent hammers.

In DataJoint, the equivalent expression is

r & 'color="red"'

and its primary key is the same as r‘s.

Is this reasoning bullet proof?

What if we restrict by an equality condition on a primary key attribute?

For example, if the primary key of r is (a,b), then r & 'a=5' will yield a relation where every tuple can be uniquely identified by b. From the purely relational point of view, b becomes the primary key of the result, i.e. the minimal subset of attributes uniquely identifying each tuple. a is no longer necessary at all since its value is known from the restriction.

This is where DataJoint makes a compromise in favor of the ERM and entity integrity: DataJoint keeps the original primary key even if it’s redundant — for the sake of entity integrity. DataJoint keeps a in the primary key of the result, which means that it cannot be projected out in subsequent operations (see Part 2 of this series).

The SQL equivalent of DataJoint’s (r & 'a=5').proj('c') if the primary key of r is (a, b) is

SELECT a, b, c

  FROM r

  WHERE a=5

In SQL you might prefer to omit the a since we do not need to retrieve its value:

SELECT b, c

  FROM r

  WHERE a=5

Although potentially more efficient, the SQL result has lost its entity class. Or put in another way, this query introduces a new unnamed implicit entity class with the primary key of b.

DataJoint’s mantra is “preserve entity integrity” even if it costs some efficiency (usually very little). True, it is possible that an experienced programmer can re-write a DataJoint query more efficiently in SQL. But the gain in clarity and logical coherence is well worth the cost. DataJoint enforces strong entity integrity for the sake of the human scientist and not to help the query compiler be more efficient. DataJoint’s query model ensures that new entities are not introduced implicitly willy-nilly mid-sentence in queries. The only way to introduce a new entity class is to explicitly define it.

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.