CO312 Advanced Databases: Course Home Page 2016-17 Session

General Information about the course

The entire course will be taught by Peter McBrien, and comprise of four hours per week of combined lectures and tutorials running in Weeks 2 to 9. The exam for the course will be held in Week 11. There will be four written courseworks.

The syllabus and book list are held in DSS and on the departmental course page

Lecture Slides

The slides are placed here at the time of the lecture. Slides with grey links are provisional versions of the slides. Note that the course content is what is presented verbally during the lectures: you are expected to attend all lectures. Worksheets will be provided during lectures, and answers to those worksheets provided also during lectures.

  1. Introduction
  2. ER Modelling
  3. Relational Model and Algebra
  4. SQL: An Implementation of the RA
  5. SQL: A Programming Language
  6. Storage and Indexing
  7. Query Processing
  8. Distributed Databases
  9. Big Data: Pig Latin
  10. Temporal Databases
  11. Concurrency Control

Example Databases

There are five relational databases used in the course for examples, and these databases are made available for running example queries on both SQL Server and Postgres.

Further examples to download into your own database are found in my databases tools and data mini site.

Examples hosted on a Microsoft SQL Sever 2008 DBMS

To connect to the databases hosted on SQL Server, issue commands in the following form on a CSG Linux machine (and using password 'lab'):

sqsh -S sqlserver -D bank_branch -U lab

Once connected, queries may be entered, terminated by \go. Help on the environment may be found using \help.

The sqshrc file may be copied as .sqshrc in your home directory, and altered to change the default behaviour of sqsh. This file should by default be also present as /etc/sqshrc on CSG Linux machines, but may be absent from recent Ubuntu installations.

Note that this version of SQL Server does not support the various three valued logic tests IF UNKNOWN, IF NOT FALSE, etc. It has been set to use ANSI handling of NULL equality tests.

Examples hosted on a Postgres DBMS

To connect to the databases hosted on Postgres, issue commands in the following form on a CSG Linux machine (and using password 'lab'):

psql -h db -U lab -d bank_branch -W

Once connected, queries may be entered, terminated with a semicolon. Online help may be accessed with \help, and \exit used to exit the psql tool.

Note that this version of Postgres does not support CUBE or ROLLUP.