Computer Laboratory

Course pages 2013–14

Databases

Principal lecturer: Dr Timothy Griffin
Taken by: Part IB
Past exam questions
Information for supervisors (contact lecturer for access permission)

No. of lectures: 12
Suggested hours of supervisions: 3
Prerequisite courses: None

Aims

The aim of the course is to cover the fundamentals of databases as seen from the perspective of application writers. The course covers schema design techniques, SQL, data warehouses, On-line Analytical Processing (OLAP), federated databases, and the “NoSQL” movement.

Lectures

  • Introduction. What is a database system?

  • Basic SQL An overview of the core of SQL. Tables as sets of records. Select statement. Simple queries. The NULL value.

  • More advanced SQL Aggregate queries. Complex joins. Inner and outer joins. Integrity constraints. Materialized views.

  • The relational data model, the relational algebra Set-theoretic basis of SQL. Sets versus multisets. Relational algebra as an abstract query language.

  • The relational calculus. Relational calculus as an abstract query language. SQL has constructs taken from both the relational algebra and the relational calculus.

  • Entity-Relationship (E/R) modelling. Relational database schema design and data modelling.

  • Schema refinement I. The evils of redundancy. The benefits of redundancy. Functional dependencies (FDs) as a formal means of investigating redundancy. Relational decomposition. Armstrong’s axioms and Heath’s Rule.

  • Schema refinement II and Normal Forms. Schema normalisation. First and Second normal form. Third normal form and Boyce-Codd normal form. Multi-valued dependencies (MVDs) and lossless-join decomposition. Fourth normal form.

  • On-line Analytical Processing (OLAP). When to forget about data normalization. OLTP versus OLAP. What is a data cube? Data modelling for data warehouses: star schema.

  • Federated databases. Data exchange languages such as XML and JSON. A close look at some federated databases from EBI (http://www.ebi.ac.uk/services).

  • The “NoSQL” banner Relaxing ACID and relational frameworks. The debate about CAT (Consistency, Availability, Partition tolerance). Can a database really be schemaless? Cluster based computing and its implications. MapReduce queries.

  • “NoSQL” continued A brief survey of system designs: Key-value stores, column-oriented databases, document-oriented databases, graph databases.

Objectives

At the end of the course students should

  • be able to design entity-relationship diagrams to represent simple database application scenarios;

  • know how to convert entity-relationship diagrams to relational database schemas in the standard Normal Forms;

  • be able to program simple database applications in SQL;

  • understand the basic theory of the relational model and both its strengths and weaknesses;

  • be familiar with various recent trends in the database area.

Recommended reading

* Silberschatz, A., Korth, H.F. & Sudarshan, S. (2002). Database system concepts. McGraw-Hill (4th ed.).
Ullman, J. & Widom, J. (1997). A first course in database systems. Prentice Hall.
Date, C.J. (2004). An introduction to database systems. Addison-Wesley (8th ed.).