COMP-421: Database Systems

Names and Numbers:

Lecture: Tu/Th 1:05 AM-14:25 PM, Rutherford Physics Building 112
Instructor: Bettina Kemme
Office Hours Instructor:after class and by appointment

Outline

With the raise of e-commerce all of us use complex information systems on a daily basis -- by accessing the Minerva university system, by banking online, by buying a book online. Database management systems (DBMS) build the backbone software systems that store all mission-critical data of these information systems. The big DBMS vendors like Oracle, IBM (DB2) or Microsoft (SQL Server) are well known players on the IT market; Linux distributions (for instance Red Hat), include an open source database management system in their distribution package; even the Microsoft Office suite contains kind of a database management system. And recently, the NoSQL movement has shown that data management has become important in many new domains and markets. As a result, knowing at least the basic concepts behind these systems and how to use them is becoming an essential skill for IT specialists.

This course is intended to give students a solid background in relational DBMS. Furthermore is outlines non-relational solutions. 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 write queries that extract the data you are looking for. 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 later.

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 is important to keep data consistent when updates occur. Query execution is the process that efficiently finds the small set of data the user wants to see out of a possibly huge database.

At the end of the course we will talk about non-relational (NoSQL) data models and their: semi-structured data that is better able to handle document-type data and key-value stores that are better able to store and query huge amount of data in a distributed fashion.

Objectives:

In this course you will

learn the fundamental steps needed to develop a database application using a commercial DBMS
apply this knowledge to build a database application for a specific application domain
acquire the skills to quickly learn and understand new data models and DBMS interfaces
understand some of the internals of a relational DBMS, and with that,

get to know the structure and complexity of a large software system
see how knowing about the internals of the DBMS can help you improve your usage of a DBMS as an application programmer

get an understanding of performance in a DBMS
conduct a relatively large scale project in a team

Detailed Topic List (each topic between 2 and 4 lectures):

Introduction
Entity-Relationship Model
Relational Model and Data Definition Language
Relational Algebra
Functional Dependencies
SQL I: Simple Queries
SQL II: Advanced Queries
SQL III: Constraints and Triggers
SQL V: Application Programming
Transactions
Concurrency Control
Essentials of File and Disk Management
Recovery
Indexing
Query Execution
Semi-structured data (XML, JSon)
Key-value stores and map-reduce
Review

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.

likely Marking Scheme: There are 3-4 written assignments, four project deliveries, one midterm, and one final. The scheme will be

15% assignments (3 best out of 4 assignments)
16% project deliveries
10% midterm
59% final

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.

The first three assignments count 5% each. The fourth assignment can replace the grade in one of the three previous assignments.

The first three programming deliveries build a project in which students have to develope and build database application for a real-world domain. The students will design a schema, create a database using DB2, maintain, query and update the data, and develop a application programs. The last deliverable will be on map-reduce on large data sets. The students will work in teams of three on the programming project. All team members are supposed to work together and participate equally in the project development.

Course Book:

Raghu Ramakrishnan and Johannes Gehrke: Database Management Systems; McGraw Hill, 3rd Edition, 2003.

Alternative Literature

Abraham Silberschatz, Henry F. Korth and S. Sudarshan: Database System Concepts; McGraw Hill, 5th Edition, 2006.
Hector Garcia-Molina, Jeffrey D. Ullman and Jennifer Widom: Database systems : the complete book; Prentice Hall, 2002.

A note on academic integrity

McGill University values academic integrity. Therefore all students must understand the meaning and consequences of cheating, plagiarism and other academic offences under the Code of Student Conduct and Disciplinary Procedures (see http://www.mcgill.ca/integrity/ for more information).

French/English

In accord with McGill University's Charter of Students' Rights, students in this course have the right to submit in English or in French any written work that is to be graded.