Data queries have the form of expressions using operators to derive the desired table. The expressions themselves do not contain any data. They represent the desired data symbolically.
Once a query is formed, the fetch methods are used to bring the data into the local workspace.
Since the expressions are only symbolic representations, repeated
fetch calls may yield different results as the state of the database is modified.
DataJoint implements a complete algebra of operators on tables:
The subset of entities from table
The subset of entities from table
Combines all matching information from
Selects and renames attributes from
Same as projection but allows computations based on matching information in
All unique entities from both
Principles of relational algebra¶
DataJoint’s algebra improves upon the classical relational algebra and upon other query languages to simplify and enhance the construction and interpretation of precise and efficient data queries.
Entity integrity: Data are represented and manipulated in the form of tables representing well-formed entity sets. This applies to the inputs and outputs of query operators. The output of a query operator is an entity set with a well-defined entity type, a primary key, unique attribute names, etc.
Algebraic closure: All operators operate on entity sets and yield entity sets. Thus query expressions may be used as operands in other expressions or may be assigned to variables to be used in other expressions.
Attributes are identified by names: All attributes have explicit names. This includes results of queries. Operators use attribute names to determine how to perform the operation. The order of the attributes is not significant.
Binary operators in DataJoint are based on the concept of matching entities; this phrase will be used throughout the documentation.
Two entities match when they have no common attributes or when their common attributes contain the same values.
Here common attributes are those that have the same names in both entities. It is usually assumed that the common attributes are of compatible datatypes to allow equality comparisons.
Another way to phrase the same definition is
Two entities match when they have no common attributes whose values differ.
It may be conceptually convenient to imagine that all tables always have an additional invisible attribute,
omega whose domain comprises only one value, 1.
Then the definition of matching entities is simplified:
Matching entities can be merged into a single entity without any conflicts of attribute names and values.
This is a matching pair of entities:
and so is this one:
but these entities do not match:
All binary operators with other tables as their two operands require that the operands be join-compatible, which means that:
All common attributes in both operands (attributes with the same name) must be part of either the primary key or a foreign key.
All common attributes in the two relations must be of a compatible datatype for equality comparisons.
These restrictions are introduced both for performance reasons and for conceptual reasons. For performance, they encourage queries that rely on indexes. For conceptual reasons, they encourage database design in which entities in different tables are related to each other by the use of primary keys and foreign keys.