This course is intended to give students a solid background in relational DBMS. If you are interested in becoming an effective user of a DBMS, or a DBMS professional, this is a good introduction to the topic. The course has two main parts: the first part explains how to use a DBMS, the second part looks at how the DBMS works internally .
The first part will discuss the design of databases and how to work with the database. Or in other words: how can you squeeze your "real world" data (the product information of a bookstore, the student and activity information of a students' association) into the relational data model of the database system and how can you retrieve the data afterwards. In more detail, the first part will cover the conceptual design of databases using the entity-relationship model, and the relational data model. Then it will discuss how data can be accessed using Relational Algebra and SQL (the standard query language for relational database systems).
The second part of the course is system-oriented and discusses the
internals of a DBMS. Using a DBMS is one issue, using it efficiently
and correctly is a different story. DBMS are designed to work for very
different application areas and present a general purpose software. In
order to be able to tune a DBMS to your specific requirements you must
know how your database application is executed by the DBMS.
This
second part will cover two main areas: transaction management and
query execution. When you connect to the DBMS and access the
data, you do this in the context of a transaction. A transaction is
considered one logical unit of work. A classical example is a money
transfer from account A to account B. The DBMS provides some
important transactional guarantees . For instance, a
transaction is either executed completely or not at all (you don't
want that the amount is withdrawn from account A but does not appear
as a plus on account B). Understanding the concepts of transactions,
and how DBMS implement them is crucial for
keeping the data clean and consistent. The last topic will be about
how the DBMS retrieves the data you are asking for. We will talk about
how data is stored internally, what mechanisms exist to access this
data and how the DBMS determines which algorithm to use to answer to
user queries as fast as possible.
If we have time we will talk about an advanced topic, e.g. XML database systems.
Objectives:
In this course you will
Detailed Topic List (each topic between 2 and 4 lectures):
Prerequisites: COMP-206, COMP-251 and COMP-302: Knowledge of data structures, algorithms, programming languages and basics of program development. Knowledge in operating systems is of advantage.
Marking Scheme: There are four written assignments (all
probably having the same weight), four project deliveries (each with
the same weight), one midterm and
one
final. The scheme will likely be
In assignments the students have to solve concrete problems. In some cases, late turn-in might not be possible (e.g., if I want to post the solution before the midterm/final). Otherwise, a late turn-in will result in a penalty of 10% per day (e.g. if an assignment has in total 100 points, a student achieves 80 points but turns in the assignment a day too late, then he/she will receive 80-10=70 points. Students will do the assignments in pairs (pair matching is done by the instructor and is different for each assignment).
The programming project of this course is to develop and build a database application for a real-world domain. The students will design a schema, create a database using DB2, maintain, query and update the data, develop application programs, and implement a user-friendly interface. The project will be done step by step with four project assignments. The students will work in teams on the programming project. All team members are supposed to work together and participate equally in the project development.
Course Book: