Computer Laboratory

Course pages 2014–15

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 some aspects of the NoSQL movement.

Lectures

  • Introduction. What is a database system? Different roles : database implementor, database administrator, database applications developer, database user. This course will focus on database systems from the perspective of application developers. [1 lecture]

  • Data models and query languages We explore and contrast two distinct data models: relational and semi-structured models. Relational database systems are the most widespread and typically provide SQL-based data querying and modification. We illustrate the basic constructs of SQL and On-Line Transaction Processing (OLAP). Some NoSQL databases (such as MongoDB) are based on semi-structured models, and have a more procedural means of inspecting data. [4 lecture]

  • Modeling (some bit of) the real world in a database Entity-Relationship (ER) modeling for the relational model. Object-oriented modeling for semi-structured data. What is a good representation? What are the design tradeoffs involved? A persistent problem: data redundancy leads to update anomalies. Relational solution: normalization! Semi-structured solution: good luck pal! [3 lecture]

  • Relational normal forms Functional dependencies (FDs) as a formal means of investigating redundancy. Relational decomposition. Schema normalization. Third normal form and Boyce-Codd normal form. [1 lecture]

  • Schema evolution The real world is constantly changing and your database design has to change with it. Semi-structured solution: look mom, no schema! Relational solution: good luck pal! [1 lecture]

  • Embracing redundancy If rarely updating but almost always reading, then normalization is too costly. On-line Analytical Processing (OLAP). OLTP versus OLAP. A somewhat related topic: 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). [2 lecture]

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

  • be able to program simple database applications

  • 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.).