As I mentioned in my previous post, DataJoint is one of those rare technologies that comes around and can truly revolutionize your workflow making your life much easier.

The following are different functions that DataJoint has. For the purposes of this blog post, we will imagine ourselves as neuroscientists about to perform a neurophysiology experiment on our subjects, a set of mice.

We start off by importing DataJoint; our convention is to use dj as its alias.

>>> import datajoint as dj


From here we create a schema, which is where our tables will live. We give our schema some meaningful name, in this case, I will call mine ‘tutorial’, and we end the name with an _, giving us tutorial_.

>>> schema = dj.schema('tutorial_')


Creating Our First Table

Now that we have our schema, it is time to populate it with tables! Our first table is called Mouse and it is a Manual table. This is a category of tables where the information is inserted manually.

>>> @schema
   class Mouse(dj.Manual):
     definition = """
     # Experimental animals
     mouse_id             : int                          # Unique animal ID
     ---
     dob=null             : date                         # date of birth
     sex="unknown"        : enum('M','F','unknown')      # sex
     """


On the left side, under #Experimental animals, we see our column names. These are the features that we will document about our Mice. Each table has a primary key which is separated from the secondary keys by  — — — . In this case, the primary key is the mouse_id, and the secondary keys are dob (date of birth) and sex.

Inserting Data

>>> Mouse()
Mouse_id dob sex


Our table is currently empty and being a manual table, we must insert data into it. DataJoint has several ways of doing this. The first is using the .insert()function.

>>> Mouse.insert1((0, '2017-03-01', 'M'))      
>>> Mouse()  
Mouse_id      dob           sex      
0        2017-03-01        M    


Here we see that we can insert a tuple with the necessary information.

You can insert data via a dictionary.

>>> data = { 'mouse_id': 100, 'dob': '2017-05-12', 'sex': 'F'}  
>>> Mouse.insert1(data)                                                                                                                                                                                                                                                                                  
Mouse_id      dob           sex      
0        2017-03-01        M        
100      2017-05-12        F


You can also use a list of tuples.

>>> data = [(1, '2016-11-19', 'M'),(2, '2016-11-20', 'unknown'),(5, '2016-12-25', 'F')]      
>>> Mouse.insert(data)
Out[7]: Mouse_id      dob           sex          
0        2017-03-01        M          
1        2016-11-19        M          
2        2016-11-20   unknown      
5        2016-12-25        F        
100       2017-15-12       F


Data Integrity

One of the many benefits of DataJoint is its ability to uphold data integrity. Trying to insert a new mouse with mouse_id :0 results in a Duplicate error.

>>> Mouse.insert1({'mouse_id': 0,'dob': '2018-01-01','sex': 'M',})
---------------------------------------------------------------------------DuplicateError    
Traceback (most recent call last)<ipython-input-21-304369cd8a83> in <module>      2 {'mouse_id': 0,      3  'dob': '2018-01-01',----> 4  'sex': 'M',      5 })~/.local/lib/python3.7/site-packages/datajoint/table.py in insert1(self, row, **kwargs)    264         For kwargs, see insert()    265         """--> 266         self.insert((row,), **kwargs)    267     268     def insert(self, rows, replace=False, skip_duplicates=False, ignore_extra_fields=False, allow_direct_insert=None):~/.local/lib/python3.7/site-packages/datajoint/table.py in insert(self, rows, replace, skip_duplicates, ignore_extra_fields, allow_direct_insert)    336             except DuplicateError as err:    337                 raise err.suggest(--> 338                     'To ignore duplicate entries in insert, set skip_duplicates=True')    339     340     def delete_quick(self, get_count=False):DuplicateError: ("Duplicate entry '0' for key 'PRIMARY'", 'To ignore duplicate entries in insert, set skip_duplicates=True')


Creating A Table With Dependencies

>>> @schema          
   class Session(dj.Manual):            
       definition = """            
       # Experiment session          
       -> Mouse            
       session_date         : date         # date  
       ---            
       experiment_setup     : int   # experiment setup ID  
       experimenter         : varchar(100)  # experimenter name            
       data_path=''         : varchar(255)                 #            """


We can see that when creating the Session table, the first primary key is -> Mouse. This means that Session inherits the Mouse’s primary key as well as includes session_date.

Querying

One of DataJoint’s strengths lies in its ability to query. There may be times when you only want a subset of the data according to certain criteria.

Four Types Of Queries:

• restriction (&) and negative restriction (-): filter data
• join (*): bring fields from different tables together
• projection (.proj()): focus on a subset of attributes
• aggregation (.aggr()): simple computation of one table against another table

Restriction (&)

The & can be used to limit

>>> Mouse & 'mouse_id=0'
Mouse_id     dob        sex      
0     2017-03-01      M


We can also use the restriction with a couple of other keywords.

In[4]: Mouse & 'mouse_id = 0'Out[4]: Mouse_id     dob        sex           0     2017-03-01      M

>>> Mouse & 'mouse_id = 0'
Mouse_id     dob        sex      
0     2017-03-01      M

In Why reinvent the relational model?, DataJoint is defined as a convergence of the Relational Data Model and the Entity-Relationship Model.  Let’s review some terminology.

The term relational is often misunderstood. For example, Google serves up a misleading definition for a relational databases.  I have submitted my correction but we will see if it takes.

All databases recognize relations between items of information and that’s not what makes a relational database.  Relational databases derive their name from the concept of relations in set theory.  A relation is any subset of the Cartesian product of several domains where a domain is a set of values.  Individual elements of a relation are called tuples.

For example, imagine three domains: person with elements {Alice, Bob, Carol, Dave}, language with elements {English, Italian, Tagalog, Urdu}, and level with elements {1, 2, 3}.  The Cartesian product person×language×level contains 4×4×3=48 elements each of which is a tuple such as (Alice, English, 1) or (Dave, Urdu, 2).  The total number of possible relations of this class is 2⁴⁸ > 281 trillion.

In a relational database, a table can be thought of as a container for relations of a given class with its columns representing the domains. At any time, the contents of the table represents a relation and the table rows are then tuples in the relation.

When the Entity Relationship Model (ERM) is mapped into a relational design, entities become tables. The table itself represents of an entity class. The contents of the table represents a set of entities of that class and each row represents an individual entity.

Summary of DataJoint terminology

In a DataJoint pipeline, each node can be thought of as (a) an entity class in ERM lingo, (b) a relational variable (relvar for short) in relational terminology, or (c) a table in the conventional database lingo.

We no longer use relvar but the terms table and entity class (or simply class) are most common and appropriate.

The columns of a table are called attributes and the rows are tuples or entities.  The entire contents of a table is a relation or an entity set.

In MATLAB and Python, each table is defined and accessed through its dedicated DataJoint class.   The class first defines the entity class and its instances provide access to the currently stored entity set ≡ relation.

Different terminologies are used for emphasis.  For example, when we define the pipeline, we focus on the E-R terminology as it describes the real world.  However, data queries are often defined in relational terms.

The terminology with further crystalize with time shifting further toward E-R terminology but without sacrificing the logical rigor of the relational model.