Databases

Databases lie at the heart of modern commercial application development. Their use extends beyond this to many applications and environments where large amounts of data are be stored for efficient update and retrieval. Their principles and fundamental techniques are being extended today to the Web and to novel kinds of data, like XML.

The purpose of this course is to provide an introduction to the principles of database systems. We begin by studying database design, covering the entity relationship model. We then cover the relational data model, relational algebra and SQL. We begin by showing how database requirements are captured using entity-relationship models, and go on to the definition of databases and their queries in the relational model. Relational algebra and calculus are studied in some depth, as are the normal forms for expressing relationships subject to dependencies. The SQL query language is studied in the practical component of the course. The primary data structures and strategies for efficient querying of a database are studied and the course concludes with an application to a database containing spatial data. Throughout, the emphasis is on principles rather then implementation.

At the end of the course the student will be able to:

• Understand the relational model, and know how to translate requirements captured in an Entity-Relationship diagram into a relational schema
• Be able to reason about dependencies in a relational schema
• Understand normal form schemas, and the decomposition process by which normal forms are obtained
• Use relational query languages such as SQL
• Understand equivalences within relational algebra and their use in query optimization
• Understand indexing and its role in query plans
• Be able to create database-driven web interfaces

1. Introduction, E/R diagrams (Ch. 2) 
2. Relational model, E/R to relational (Ch. 3) 
3. Relational algebra and Calculus (Ch. 4) 
4. Overview of SQL (Ch. 5)
5. Schemas, dependencies and normal forms (Ch. 19)
6. Transactions Management (Ch. 16-17)
7. Storage and indexing (Ch. 8)
8. Tree Indexes (Ch. 10)
9. Query evaluation (Ch. 12)
10. Query optimisation (Chs. 14,15)

Introduction to database management systems; entity relationship modelling; the relational model; relational algebra.

Schema refinement, functional dependencies and normal forms, transaction management. Storage and indexing: B + trees and hashing.

Query optimisation.  Practical:Web databases via SQL and PHP.

The course text is:

• Database management systems, by Raghu Ramakrishnan and Johannes Gehrke. 3rd edition, McGraw-Hill 2002.

We may also make reference to:

• An Introduction to Databases Systems, 7th Edition, by C.J. Date. Addison-Wesley, 2000.