Completed-01-Getting started with DataJoint.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Session 1: Getting started with DataJoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you have successfully connected to DataJoint (if not, please visit [Connecting to DataBase](00-ConnectingToDatabase.ipynb) first), let's dive into using DataJoint! In this notebook, we will:\n",
    "\n",
    "1. learn what a data pipeline is\n",
    "2. start designing our first data pipeline based on experiment requirements\n",
    "3. create the data pipeline in DataJoint\n",
    "4. insert some data into the pipeline\n",
    "5. perform basic queries to explore and fetch the data from the pipeline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As always, let's start by importing the `datajoint` library."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datajoint as dj"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# So... What is a data pipeline?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you visit the [documentation for DataJoint](https://docs.datajoint.io/introduction/Data-pipelines.html), we define a data pipeline as follows:\n",
    "> A data pipeline is a sequence of steps (more generally a directed acyclic graph) with integrated storage at each step. These steps may be thought of as nodes in a graph.\n",
    "\n",
    "While this is an accurate description, it may not be the most intuitive definition. Put succinctly, a data pipeline is a listing or a \"map\" of various \"things\" that you work with in a project, with line connecting things to each other to indicate their dependecies. The \"things\" in a data pipeline tends to be the *nouns* you find when describing a project. The \"things\" may include anything from mouse, experimenter, equipment, to experiment session, trial, two-photon scans, electric activities, to receptive fields, neuronal spikes, to figures for a publication! A data pipeline gives you a framework to:\n",
    "\n",
    "1. define these \"things\" as tables in which you can store the information about them\n",
    "2. define the relationships (in particular the dependencies) between the \"things\"\n",
    "\n",
    "A data pipeline can then serve as a map that describes everything that goes on in your experiment, capturing what is collected, what is processed, and what is analyzed/computed. A well designed data pipeline not only let's you organize your data well, but can bring out logical clarity to your experiment, and may even bring about new insights by making how everything in your experiment relates together obvious.\n",
    "\n",
    "Let's go ahead and build together a pipeline from scratch to better understand what a data pipeline is all about."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building our first pipeline: single electrode recording from mouse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's build a pipeline to collect, store and process data and analysis for our hypothetical single electrode recording study in mice. To help us understand the project better, here is a brief description:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> * Your lab houses many mice, and each mouse is identified by a unique ID. You also want to keep track of information about each mouse such as their date of birth, and gender.\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with more than one mouse in a day! However, on an any given day, a mouse undergoes at most one recording session.\n",
    "> * For each experimental session, you would like to record what mouse you worked with and when you performed the experiment. You would also like to keep track of other helpful information such as the experimental setup you worked on.\n",
    "> * In each experimental session, you record electrical activity from a single neuron. You use recording equipment that produces separate data files for each neuron you recorded.\n",
    "> * Neuron's activities are recorded as raw traces. Neuron's spikes needs to be detected for further analysis to be performed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pipeline design starts by identifying **things** or **entities** in your project. Common entities includes experimental subjects (e.g. mouse), recording sessions, and two-photon scans."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's revisit the project description, this time paying special attention to **what** (e.g. nouns) about your experiment. Here I have highlighted some nouns in particular."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> * Your lab houses many **mice**, and each mouse is identified by a unique ID. You also want to keep track of information about each mouse such as their date of birth and gender.\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with more than one mouse in a day! However, on an any given day, a mouse undergoes at most one recording session.\n",
    "> * For each **experimental session**, you would like to record what mouse you worked with and when you performed the experiment. You would also like to keep track of other helpful information such as the experimental setup you worked on.\n",
    "> * In each **experimental session**, you record electrical activity from **a single neuron**. You use recording equipment that produces separate data files for each neuron you recorded.\n",
    "> * Neuron's activities are recorded as raw traces. **Neuron's spikes** needs to be detected for further analysis to be performed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just by going though the description, we can start to identify **things** or **entities** that we might want to store and represent in our data pipeline:\n",
    "\n",
    "* mouse\n",
    "* experimental session\n",
    "* neuron\n",
    "* spikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint data pipeline, we represent these **entities** as **tables**. Different *kinds* of entities become distinct tables, and each row of the table is a single example (instance) of the category of entity. \n",
    "\n",
    "For example, if we have a `Mouse` table, then each row in the mouse table represents a single mouse!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When constructing such table, we need to figure out what it would take to **uniquely identify** each entry. Let's take the example of the **mouse** and think about what it would take to uniquely identify a mouse."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After some thought, we might conclude that each mouse can be uniquely identified by knowing its **mouse ID** - a unique ID number assigned to each mouse in the lab. The mouse ID is then a column in the table or an **attribute** that can be used to **uniquely identify** each mouse. Such attribute is called the **primary key** of the table.\n",
    "\n",
    "| mouse_id* |\n",
    "|:--------:|\n",
    "| 11234    |\n",
    "| 11432    |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we have successfully identified the primary key of the table, we can now think about what other columns, or **non-primary key attributes** that we would want to include in the table. These are additional information **about each entry in the table that we want to store**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the case of mouse, what other information about the mouse you might want to store? Based on the project description, we would probably want to store information such as the mouse's **date of birth** and **gender**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| mouse_id* | dob        | sex |\n",
    "|:--------:|------------|--------|\n",
    "| 11234    | 2017-11-17 | M      |\n",
    "| 11432    | 2018-03-04 | F      |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have an idea on how to represent information about mouse, let's create the table using **DataJoint**!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a schema - house for your tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Every table lives inside a schema - a logical collection of one or more tables in your pipeline. Your final pipeline may consists of many tables spread across one or more schemas. Let's go ahead and create the first schema to house our table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create the schema using `dj.schema()` function, passing in the name of the schema. For this workshop, you are given the database privilege to create any schema **starting with your username followed by a `_` charcter**. So if your username is `john`, you can make any schema starting with `john_`, such as `john_tutorial`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a schema called `pipeline`, prefixed by `username_`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "username = dj.config['database.user'] # using username information from database config\n",
    "schema = dj.schema('{}_pipeline'.format(username))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have a schema to place our table into, let's go ahead and define our first table!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating your first table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In DataJoint, you define each table as a class, and provide the table definition (e.g. attribute definitions) as the `definition` static string property. The class will inherit from the `dj.Manual` class provided by DataJoint (more on this later)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Mouse(dj.Manual):\n",
    "    definition = \"\"\"\n",
    "    # Experimental animals\n",
    "    mouse_id             : int                          # Unique animal ID\n",
    "    ---\n",
    "    dob=null             : date                         # date of birth\n",
    "    sex=\"unknown\"        : enum('M','F','unknown')      # sex\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at our brand new table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table was successfully defined, but without any content, the table is not too interesting. Let's go ahead and insert some **mouse** into the table, one at a time using the `insert1` method."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's insert a mouse with the following information:\n",
    "* mosue_id: 0\n",
    "* date of birth: 2017-03-01\n",
    "* sex: male"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.insert1((0, '2017-03-01', 'M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also insert as dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    'mouse_id': 100,\n",
    "    'dob': '2017-05-12',\n",
    "    'sex': 'F'\n",
    "}\n",
    "\n",
    "Mouse.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also insert multiple **mice** together using the `insert` method, passing in a list of data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "  (1, '2016-11-19', 'M'),\n",
    "  (2, '2016-11-20', 'unknown'),\n",
    "  (5, '2016-12-25', 'F')\n",
    "]\n",
    "\n",
    "# now insert all at once\n",
    "Mouse.insert(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, you can insert a list of dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "  {'mouse_id': 10, 'dob': '2017-01-01', 'sex': 'F'},\n",
    "  {'mouse_id': 11, 'dob': '2017-01-03', 'sex': 'F'},\n",
    "]\n",
    "\n",
    "# insert them all\n",
    "Mouse.insert(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data integrity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataJoint checks for data integrity, and ensures that you don't insert a duplicate by mistake. Let's try inserting another mouse with `mouse_id: 0` and see what happens!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.insert1(\n",
    "{'mouse_id': 0,\n",
    " 'dob': '2018-01-01',\n",
    " 'sex': 'M',\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Go ahead and insert a few more mice into your table before moving on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert more mouse!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fetching all data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a table with a few mice in it, let's learn how to retrieve (fetch) the data back!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can retrieve the content of the whole table with the `fetch` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = Mouse.fetch() # get all mouse!!\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, `fetch` returns the data as a NumPy structured array. You can also `fetch` as a list of dictionaries by passing in `as_dict=True`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = Mouse.fetch(as_dict=True)\n",
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also fetch specific attributes by specifying them in `fetch`. The result is then returned as a NumPy array of appropriate data type (`dtype`) to accomodate the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# only get the sex of all mouse\n",
    "Mouse.fetch('sex')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also retrieve multiple attributes simultaneously."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dob, sex = Mouse.fetch('dob', 'sex')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Often times, you don't want all data but rather work with **a subset of entities** matching specific criteria. Rather than fetching the whole data and writing your own parser, it is far more efficient to narrow your data to the subset before fetching.\n",
    "\n",
    "For this, DataJoint offers very powerful yet intuitive **querying** syntax that let's you select exactly the data you want before you fetch it.\n",
    "\n",
    "It is also critical to note that the result of any DataJoint query represents a valid entity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Restricting by attribute value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The **restriction** operation, `&`, let's you specify the criteria to narrow down the table on the left."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exact match"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse with id 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'mouse_id = 0'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All male mice (`sex = \"M\"'`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'sex = \"M\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All female mice (`sex=\"F\"'`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'sex = \"F\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inequality"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also use inequality in your query to match based on numerical values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse that is born **after 2017-01-01**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'dob > \"2017-01-01\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Mouse that is **not** male"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'sex != \"M\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Combining restrictions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can easily combine multiple restrictions to narrow down the entities based on multiple attributes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find all mouse that **is not male** AND **born after 2017-01-01**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & 'sex != \"M\"' & 'dob > \"2017-01-01\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Result of one query can be used in another query! Let's first find **all female mice** and store the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "female_mice = Mouse & 'sex = \"F\"'\n",
    "female_mice"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "and among these mice, find ones with **mouse_id > 10**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "female_mice & 'mouse_id > 10'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In computer science/math lingo, DataJoint operations are said to **satisfy closure property**. Practically speaking, this means that the result of a query can immediately be used in another query, allowing you to build more complex queries from simpler ones. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Restricting with a dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All male mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "restr = {\n",
    "    'sex': \"M\"\n",
    "}\n",
    "\n",
    "Mouse & restr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Fetch query results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have successfully narrowed down to the entities you want, you can fetch the query results just by calling fetch on it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All male mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "male_mouse = Mouse & 'sex = \"M\"'\n",
    "male_mouse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch it!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "male_mouse.fetch()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "or all in one step"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(Mouse & 'sex = \"M\"').fetch()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Working with tables with dependencies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations! We have successfully created your first table and learned to query and fetch it! We are now ready to tackle and include other **entities** in the project into our data pipeline. \n",
    "\n",
    "Let's now take a look at representing an **experimental session**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As with mouse, we should think about **what information (i.e. attributes) is needed to uniquely identify an experimental session**. Here is the relevant section of the project description:\n",
    "\n",
    "> * As a hard working neuroscientist, you perform experiments every day, sometimes working with **more than one mouse in a day**! However, on an any given day, **a mouse undergoes at most one recording session**.\n",
    "> * For each experimental session, you would like to record **what mouse you worked with** and **when you performed the experiment**. You would also like to keep track of other helpful information such as the **experimental setup** you worked on."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Based on the above, it appears that you need to know:\n",
    "\n",
    "* the date of the session\n",
    "* the mouse you recorded from in that session\n",
    "\n",
    "to uniquely identify a single experimental session."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that, to uniquely identify an experimental session (or simply a **session**), we need to know the mouse that the session was about. In other words, a session cannot existing without a corresponding mouse! \n",
    "\n",
    "With **mouse** already represented as a table in our pipeline, we say that the session **depends on** the mouse! We would graphically represent this in an **entity relationship diagram (ERD)** by drawing the line between two tables, with the one below (**session**) dependeing on the one above (**mouse**)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![mouse_sessions](workshop/figures/mouse_session.svg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thus we will need both **mouse** and a new attribute **session_date** to uniquely identify a single session. \n",
    "\n",
    "Remember that a **mouse** is already uniquely identified by its primary key - **mouse_id**. In DataJoint, you can declare that **session** depends on the mouse, and DataJoint will automatically include the mouse's primary key (`mouse_id`) as part of the session's primary key, along side any additional attribute(s) you specificy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "@schema\n",
    "class Session(dj.Manual):\n",
    "    definition = \"\"\"\n",
    "    # Experiment session\n",
    "    -> Mouse\n",
    "    session_date               : date                         # date\n",
    "    ---\n",
    "    experiment_setup           : int                          # experiment setup ID\n",
    "    experimenter               : varchar(100)                 # experimenter name\n",
    "    \"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can actually generate the entity relationship diagram (ERD) on the fly by calling `dj.ERD` with the schema object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dj.ERD(schema)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's try inserting a few sessions manually."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "  'mouse_id': 0,\n",
    "  'session_date': '2017-05-15',\n",
    "  'experiment_setup': 0,\n",
    "  'experimenter': 'Edgar Y. Walker'\n",
    "}\n",
    "\n",
    "Session.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's insert another session for `mouse_id = 0` but on a different date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "  'mouse_id': 0,\n",
    "  'session_date': '2018-01-15',\n",
    "  'experiment_setup': 100,\n",
    "  'experimenter': 'Jacob Reimer'\n",
    "}\n",
    "\n",
    "# insert them all\n",
    "Session.insert1(data)\n",
    "\n",
    "Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And another session done on the same date but on a different mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "  'mouse_id': 11,\n",
    "  'session_date': '2018-01-15',\n",
    "  'experiment_setup': 101,\n",
    "  'experimenter': 'Jacob Reimer'\n",
    "}\n",
    "\n",
    "# insert them all\n",
    "Session.insert1(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What happens if we try to insert a session for a mouse that doesn't exist?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_data = {\n",
    "    'mouse_id': 9999,  # this mouse doesn't exist!\n",
    "    'session_date': '2017-05-15',\n",
    "    'experiment_setup': 0,\n",
    "    'experimenter': 'Edgar Y. Walker'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "Session.insert1(bad_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying with multiple tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have two tables, we can perform more exciting queries!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Restricting by other table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "All mouse that has a session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combining restrictions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find sessions performed on a male mouse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Session & (Mouse & 'sex = \"M\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Give me all mice that have had an experimental session done on or before 2017-05-19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse & (Session & 'session_date <= \"2017-05-19\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deleting entries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have a good idea on how to restrict table entries, this is a good time to introduce how to **delete** entries from a table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To delete a specific entry, you restrict the table down to the target entry, and call `delete` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "(Mouse & 'mouse_id = 100').delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calling `delete` method on an *unrestricted* table will attempt to delete the whole table!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse.delete()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that when you try to delete all `Mouse`, `delete` also warned that `Session` entries will get deleted! \n",
    "\n",
    "DataJoint understands and keeps track of dependencies such that **no dependent entries will be left orphaned**. The `delete` operations automatically **cascades down** the dependency to ensure data integrity. We will revisit this point in the next session."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you want to see information from multiple tables combined together to be viewed (and queried!) simultaneously. You can do this using the join `*` operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse * Session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here each row represents a unique (and valid!) combination of a mouse and a session."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The combined table can be queried using any of the attributes (columns) present in the joined tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Mouse * Session & 'experimenter = \"Jacob Reimer\"' & 'sex = \"M\"'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Experiment with some more queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Congratulations! You have successfully created your first DatJoint pipeline, using dependencies to establish the link between the tables. You have also learned to query and fetch the data.\n",
    "\n",
    "In the next session, we are going to extend our data pipeline with tables to represent **imported data** and define new tables to **compute and hold analysis results**."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}