CSC 370 Database Systems: Spring 2009

Instructor: Alex Thomo
Phone: (250) 472-5786
Office: ECS 556
Office Hours: T 2:30 - 3:30 p.m., F 1:30 - 2:30 p.m.
TA1: Priyanka Gupta
TA2: Hong-Yi Wang
Course Outline: Link
Announcements: Link


Database Systems: The Complete Book
by Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer D. Widom, 2nd Edition, Prentice Hall, 2008.
(1st Edition is Ok as well)


  1. Database Management Systems
    by Raghu Ramakrishnan, Johannes Gehrke, 3d Edition McGraw-Hill, 2003
  2. Fundamentals of Database Systems,
    by R. Elmasri, S.B. Navathe 4th Edition, Addison-Wesley, 2004.

Project: Notes. Apache Tomcat and Examples. Ssh Tunnel. JDBC. Servlets.

Book chapters covered so far: link.

Lecture Handouts:

  • Database Systems. Evolution of Database Systems. Architecture of Database Systems. Slides.
  • Entity-Relationship (E/R) Data Model. Multiway Relationships. Slides
  • Design Principles. Keys. Subclasses in ER. Exercises. Slides
    Weak Entity Sets. Slides
  • Basics of the Relational Model. From E/R Diagrams to Relational Designs. Converting Subclass Structures to Relations. Slides.
  • An Algebra of Relational Operations. Slides
    Relational Operations on Bags. Extended Operators of Relational Algebra. Slides
  • Simple Queries in SQL. Null values. Queries Involving More Than One Relation. Slides
  • Subqueries. Bag semantics and set operations in SQL. Duplicate removal. Grouping and Aggregations. Slides.
    Database modifications. Views. Slides
  • Intro to Indexes. Slides.
  • Constraints: Foreign Key Constraints. Constraints on attributes and tuples. Modification of Constraints. Assertions. Slides . Triggers. Slides .
  • Procedures Stored in the Schema. PL/SQL. The Impedance Mismatch Problem. Cursors. Slides.
  • Java Database Connectivity (JDBC) Slides .
  • Midterm Practice: SQL/Constraints Product-PC-Laptop-Printer Exercises. SQL/Supplier-Parts-Catalog.
  • Security and Authorization: Discretionary Access Control vs. Mandatory Access Control. Granting and Revoking Privileges. Enforcing Security through Views. Security in Statistical Databases. Slides. Examples.
  • Concurrency in Databases: Anomalies related to Concurrent Execution of Transactions. 2-Phase Locking. SQL support for transactions. Slides.
  • System Failures. Recovery Manager. UNDO Logging. REDO Logging. Slides . Examples. Slides .
  • Memory Hiearchy. Disks mechanics. Computation Model. Using Secondary Storage Effectively. Two Phase Multiway Merge Sort (2PMMS). Analysis of 2PMMS. Impact of block size. Sorting very large relations. Slides
  • Indexes. B-Trees. Lookup, Insertion and Deletion in B-Trees. Slides
  • Overview of Query Evaluation: Algorithms for Relational Operators. Introduction to Query Optimization. Slides .
  • Functional Dependencies. Rules About Functional Dependencies. Closure algorithm.
    Design of Relational Database Schemas. Examples. Recovering information from a decomposition. Slides.
  • 4th Normal Form Slides.
  • OLAP. Data Mining. Slides.
  • Final exam coverage. Slides.

There would be three assignments. 

Intro. Constraints. PL/SQL. Triggers. Trigger Examples. JDBC. Compilation/Execution/Connection.

In the implementation project (worth 20%) you will design a database for a realistic situation and build a number of operational applications on this database. You will use the ORACLE system. This would involve conceptual database design, followed by the implementation of the database; pwdthe system would be able to support all representative queries and transactions.

Project Teams:
The projects would be team work wherein you are required to team up with another classmate.

Notes: All assignments are due in class. There will be a grade penalty for late assignments.