Assignment 02 - The DataJoint Book

Due Sep 12, 2024

Design database tables to represent the followig entities. Make sure that each table has a proper primary key. Populate each table with several entries.

You manage a pet clinic. Design a table for the pets treated in your clinic. For identifying animals, use the US National Animal Identification System. Include name, species, date of birth, and sex.
Now represent the veterinarians in your clinic, including name, highest degree attained, date-of-birth, and hire date.
US States and Territories, including their capitals, and the year of acceptance into the USA.
Current members of the US House of Representatives. Include state, district, name, party, and phone number.
Current US governors.
Your homework assignments for this semester, including the course and the due date.
Students in this class, including names, github accounts, and emails.
Boston Marathon champions for each year for both men’s and women’s open division. Include name, nationality, and finish time.

Your assignment will be evaluated on proper design. Choose correct and sufficient attributes, data types, primary key, NOT NULL constraints. Answer the question of how entity integrity will be enforced. Avoid unnecessary attributes. Sample data must be propery inserted, at least 2-3 entries in each table.

Connect with DataJoint¶

import datajoint as dj
dj.conn()  # test the connection (optionally)

If you are only learning DataJoint, you are done. If you are reading this text to also learn SQL, you can use two ways to issue queries: with IPython magic commands or a client library.

Connect with IPython “Magic”¶

You can execute SQL statements directly from Jupyter with the help of “magic commdands”.

The following cell sets up the connection to the database for the Jupyter SQL Magic.

import pymysql
import os
pymysql.install_as_MySQLdb()

connection_string = "mysql://{user}:{password}@{host}".format(
    user=os.environ['DJ_USER'],
    host=os.environ['DJ_HOST'],
    password=os.environ['DJ_PASS']
)

%load_ext sql
%sql $connection_string

Then you can issue SQL commands from a Jupyter cell by starting it with %%sql. Change the cell type to SQL for appropriate syntax highlighting.

%%sql
-- show all users
SELECT User FROM mysql.user

We will use SQL magic only for fast interactive SQL queries. We will not use SQL magic as part of Python code.

Connect with Python client¶

To issue SQL queries from Python code, we will use a conventional SQL client, in this case pymysql.

# create a database connection
conn = pymysql.connect(
    host=os.environ['DJ_HOST'], 
    user=os.environ['DJ_USER'], 
    password=os.environ['DJ_PASS']
    )

# crewate a query cursor and issue an SQL query
cur = conn.cursor()
cur.execute('SELECT User FROM mysql.user')
cur.fetchall()

We are all set for executing all the database queries in this book!

Completed Assignment:¶