Course pages 2015–16

Databases

Study/Supervision/Revision Guide

• This is a good example of the kind of questions you might see. y2015p4q5.pdf
• Last year question 6 was an experiment. I won't do that again. I promise! y2015p4q6.pdf
• I like these questions from 2014. y2014p4q5.pdf, y2014p4q6.pdf
• Fantastic question from 2013. y2013p4q5.pdf, y2013p4q6.pdf

Never forget to ask What problem am I solving? Exercise: List all of the mistakes made in this article: http://www.techrepublic.com/article/mongodb-and-cassandra-put-relational-databases-on-notice/.

Lecture Slides:

• One-slide per page: db_2015.pdf
• Two-slides per page: db_2015_2up.pdf (huge file!)
• Martin Kleppmann's Guest Lecture (4 March)
  • https://speakerdeck.com/ept/beyond-relational-databases or http://martin.kleppmann.com/2016/03/04/cambridge-databases-course.html.
  • Other talks by Martin: https://speakerdeck.com/ept>
• Lecture 12: db2016_L12.pdf (1 slide per page)
• Lecture 12: db2016_L12_2up.pdf (2 slides per page)
• Sarah Mei's blog on problems with MongDB
• Obfuscated SQL
  • 2+2 example from Martin Kleppmann two_plus_two.txt.
  • Contest entry from Jamie Wood : riscv.sql and age.sql.
  • Contest entry from Nathanael Alcock : age-obfuscated-A.sql and age-obfuscated-B.sql

A few SQL examples that may clarify the relational implementation of many-to-many and one-to-many relationships (from an ER model).

• movies.sql, colleges_v1.sql, colleges_v2.sql, colleges_v3.sql.

Relational division example : relational_division.sql. An interesting paper on relational division : http://users.dcc.uchile.cl/~cgutierr/cursos/BD/divisionSQL.pdf..

Some open source database systems :

• HyperSQL : http://hsqldb.org.
  • To use from Java code (as used in Further Java) : Unzip the archive file, change to extracted directory, then launch a database GUI with java -cp {YOURPATH}hsqldb.jar org.hsqldb.util.DatabaseManagerSwing.
  • To use the read-execute-print loop use sqltool.jar, java -jar {YOURPATH}sqltool.jar. You may have to set up a sqltool.rc file in your home directory as documented at http://hsqldb.org.
• Postgres : http://www.postgresql.org/
• MySQL : http://www.mysql.com/
• SQLite : http://www.sqlite.org/

Primary sources:

• Tarkski's 1941 paper "On the Calculus of Relations".
• A short biography of Alfred Tarski http://en.wikipedia.org/wiki/Alfred_Tarski.
• Codd's original 1970 paper describing the relational model (reprinted here in 1983).
• A short biography of Edgar Codd http://en.wikipedia.org/wiki/Edgar_F._Codd.
• Chen's original 1976 paper on Enitity-Relationship models.
• A short biography of Peter Chen http://en.wikipedia.org/wiki/Peter_Chen.
• Fagin's definition of multivalued dependencies.
• Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals
• Hoare Logic : An axiomatic basis for computer programming, CAR Hoare, 1969

iMDB data:

• Raw iMDb data files
• Handy Python package for manipulating iMDB raw data http://imdbpy.sourceforge.net/

Of interest (reading for the fun of it):

• NoSQL Movement : http://en.wikipedia.org/wiki/NoSQL_(concept)
• Berkeley DB : http://en.wikipedia.org/wiki/Berkeley_DB
• Graph Databases : http://en.wikipedia.org/wiki/Graph_database
• Dremel: Interactive Analysis of Web-Scale Datasets
• F1: A Distributed SQL Database That Scales

Never forget to ask What problem am I solving?