Course pages 2018–19

Databases

Last change: Fri Oct 12 11:59:22 BST 2018 (corrected ticking list)

Lecture notes

• database_2018.pdf (one slide per page)
• database_2018_2up.pdf (two slides per page)
• Understanding the Wisconsin accent

Additional materials

• Basic set theory
• A feeble attempt at a cross-tabulation in SQL ...
• ... and the output
• Using views to break up the credits table into 8 relations ...
• ... and putting the credits table back together from those views.
• A simple data cube derived from our movie database
• The SQL defining the cube : cube.sql
• A few sanity checks on the cube definitions:
• Check areas. cube_areas.sql with Output
• Check regions. cube_regions.sql with Output
• Check country dimension. cube_country_dimension.sql with Output
• Join country dimension with regions and areas cube_country_area_region.sql with Output
• Check date dimension. cube_date_dimension.sql with Output
• Check fact table. cube_fact_table.sql with Output
• Join everything to produce one big table: cube_one_big_table.sql with Output
• A few cube-oriented queries:
• Roll up to year, area, total cube_year_area_total.sql with Output
• Roll up to year, region, total cube_year_region_total.sql with Output
• Roll up to region, total cube_region_total.sql with Output

Suggested supervisions

• Supervision 1
• Supervision 2
• Supervision 3

Practical work

• Relational (due by Oct 17 ticking session)
• Getting started
• The database system: relational-db.jar
• Data set
• relational-db.zip, or
• relational-db.tar.gz
• Relational exercises (Tick 1)
• Your solutions are to be submitted using Moodle.
• Graph-oriented (due by Oct 24 ticking session)
• Getting started
• If you are having trouble with passwords, try taking the character "#" off the line "#dbms.security.auth_enabled=false" in the configuration file conf/neo4j.conf.
• Data set
• graph-movie-db.zip, or
• graph-movie-db.tar.gz
• Graph exercises (Tick 2)
• Document-oriented (due by Oct 31 ticking session)
• Getting started
• Data set
• document-movie-db.zip, or
• document-movie-db.tar.gz
• Document exercises (Tick 3)

Nothing below is examinable!

Some open source relational database systems

• HyperSQL : http://hsqldb.org.
• Postgres : http://www.postgresql.org/
• MySQL : http://www.mysql.com/
• SQLite : http://www.sqlite.org/

A few "NoSQL" pointers

• NoSQL Movement : http://en.wikipedia.org/wiki/NoSQL_(concept)
• A list of NoSQL database systems : http://nosql-database.org/
• 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

Further reading

• 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.
• Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals