Objectives

This course will address the principles of building applications using database management systems. An emphasis will be placed on concepts of database modeling, design, query formulation, and transaction processing, and on the application of those concepts in a project.

The exams, which determine most of the course grade, will be theoretical in nature. There will also be a substantial project. For the project, we will use a commercial DBMS (Oracle) and an application server (Orion). The host languages used in the class will be Java (students may use other languages for their project).

The topics included here have a substantial overlap with the graduate course CSC540. If you are a graduate student or are planning to enroll in our graduate program or are pursuing a joint BS/MS degree, please take CSC540 instead.

Please review my course policies.

Textbooks

• Required: Databases and Transaction Processing: An Application-Oriented Approach by Philip Lewis, Arthur Bernstein, Michael Kifer, Addison Wesley, 2002 (ISBN 0-201-70872-8).
• Required: Course pack. The course pack contains copies of the transparencies I will use. It will only be available on-line. I will be writing on the slides in class, so it will help to have the course pack handy during class to take notes on.

Topics

The course is organized into three main modules: database modeling and design, query formulation, and transaction processing. The topics are introduced in this order to facilitate their application in the course project. For this reason, we don't follow the textbook order.

The following are the main topics along with their corresponding chapters of the textbook. The indicated class times are approximate estimates.

1. Introduction to Databases (0.5 weeks: sections 1.1-1.5)
2. Concepts and architecture (0.5 weeks: sections 2.1-2.5, 4.1)
• Traditional software systems
• System architecture
• DBMS
• Languages and interfaces
• Conceptual modeling
3. Data modeling (1 week: section 4.2, chapter 5)
   • Relational model
     • Keys
     • Constraints
     • Operations
   • Entity-relationship approach
     • Entity types
     • Attributes
     • Relationship types
     • Mapping ER models to relational models
   • Potential problems with ER models
   • Advanced entity-relationship approach
   • Mapping hierarchies and constraints to relational models
   • Case studies (self-study for project: sections 5.5, 5.7)
4. Project (0.5 weeks: chapter 3)
   • Description
   • Q & A
   • Case study (self-study for project: chapter 3)
5. Database design (1.5 weeks: chapter 8)
   • Information redundancy and anomalies
   • Functional dependencies
   • Decompositions
   • Normalization
     • 1NF
     • 3NF
     • BCNF
     • 4NF
   • Case-study and tuning (self-study for project: sections 8.12, 8.13)
6. Relational calculus (0.5 weeks: sections 7.1, 7.2)
7. SQL (1 week: sections 6.2, 6.3)
   • Data definition (self-study for project: section 4.3)
   • Queries
   • Updates
   • Aggregates
   • Grouping
   • Subqueries
   • Views
   • Constraints
   • Embedded SQL
8. Case Study (self-study for project: chapter 12)
9. XML (1 week: chapter 17)
   • Basics
   • Namespaces
   • XML Schema
   • XPath and XPointer
   • XSLT
   • XQuery
10. Transaction concepts (1 week: chapters 15, 20)
    • ACID properties
11. Database application programming (self-study for project: chapter 10)
    • JDBC
    • Transactions in JDBC
    • JDBC statements
    • Result sets
12. Isolation (1 week: section 23.1-23.5, 23.8)
    • Schedules
    • Recoverability
    • Serializability
    • Locking
    • Timestamps
    • Optimistic techniques
    • SQL isolation levels (self-study for project: sections 24.1, 24.2 (no need for 24.2.1 and 24.2.2))
13. Recovery (0.5 weeks: sections 25.1-25.4)
    • Concepts
    • Deferred update
    • Immediate update
    • Backup and recovery
14. Relational algebra (0.5 weeks: section 6.1)
15. Query processing and optimization (0.5 weeks: sections 13.1-13.4, 14.1-14.3)
    • Basic algorithms (self-study for HW: sections 13.1-13.3)
    • Computing joins
    • SQL to relational algebra
    • Heuristics
16. Project demos (1 week)

Self-Study Responsibilities

Some of the topics are important but are either quite straightforward or not a main focus of this course. These topics are identified as self-study topics above. Your knowledge of them will be evaluated through homework assignments or as part of your project. The self-study topics won't directly be covered in any exam, but they may have indirect connections in some cases.

You will be responsible for learning how to develop web-based interfaces for your project. You are encouraged to collaborate and share knowledge about interfaces.

Grading

There will be a lot of work - please plan to spend about 11-13 hours for this course (outside of class) each week. However, grades are based not on the amount of work performed but on understanding demonstrated through course work.

The following are the nominal weights for various components of the course. Please review my policy statement to understand how I assign grades.

Prerequisites

The following are the prerequisites. From long experience, I have learned that the material in CSC 226, in particular, is essential for this course.

Students failing to meet the prerequisites will be dropped administratively. If you don't have the prerequisites, please drop yourself to avoid losing an opportunity to take an alternative course. However, if you have taken the prerequisite courses, but are not comfortable with their contents, be warned!

• CSC 316: Data Structures, which has its prerequisites:
  • CSC 216: Programming Concepts
  • CSC 226: Applied Discrete Mathematics

Here is a (partial) list of topics that will be assumed: elementary set theory, relations, partial orders, functions, concept of a theorem, propositional logic, predicate logic. These topics are covered in CSC 226: Applied Discrete Mathematics. You may review Chapters 1 to 6 from the following book, which is sometimes used as the CSC 226 textbook: