CSC 370 Database Systems: Fall 2006

Instructor: Alex Thomo
Phone: (250) 472-5786
Office: ECS 556
Office Hours: TWF 12:30 - 1:30 p.m.
TA: Marina Barsky
Course Outline: Link


Database Systems: The Complete Book
by Hector Garcia-Molina, Jeffrey D. Ullman, Jennifer D. Widom, 1st Edition, Prentice Hall, 2002


  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.

Marks so far: Link


Assignment 1. Solution.
Assignment 2. Solution - solution by Marina Barsky (TA).
Assignment 3. Solution. B-Tree.

E-Submission: Link

Project: Description. Notes. Examples. Servlets.

Lecture Handouts:

  • Database Systems. Evolution of Database Systems. Architecture of Database Systems. Slides.
  • Entity-Relationship (E/R) Data Model. Multiway Relationships. Slides
    Subclasses in ER. Design Principles. Slides
    Weak Entity Sets. Slides
  • Basics of the Relational Model. From E/R Diagrams to Relational Designs. Slides.
    Converting Subclass Structures to Relations. Slides
  • Functional Dependencies. Slides.
    Rules About Functional Dependencies. Closure algorithm. Slides
    Design of Relational Database Schemas. Slides
    Examples. Recovering information from a decomposition. 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. Outerjoins. Duplicate removal. Grouping and Aggregations. Slides. Examples.
    Database modifications. Views. Slides
  • Midterm Review Slides I. Slides II. Coverage.
  • Procedures Stored in the Schema. PL/SQL. Slides .
    The Impedance Mismatch Problem. Cursors. More PL/SQL. Slides .
  • Java Database Connectivity (JDBC) Slides .
  • Constraints: Foreign Key Constraints. Constraints on attributes and tuples. Modification of Constraints. Slides .
  • Triggers. Slides .
  • Security and Authorization: Discretionary Access Control vs. Mandatory Access Control. Granting and Revoking Privileges. Enforcing Security through Views. Security in Statistical Databases. Slides .
  • Memory Hiearchy. Disks mechanics. Computation Model. Slides
  • Using Secondary Storage Effectively. TwoPhase, Multiway Merge Sort. 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 .
  • Concurrency in Databases: Anomalies related to Concurrent Execution of Transactions. 2-Phase Locking. Slides I . Slides II .
  • System Failures. Recovery Manager. UNDO Logging. REDO Logging. Slides .
  • Final Review. Slides .
  • Final exam coverage, link .

My (old) slides from Fall 2005.

There would be three assignments. 

Intro. PL/SQL (1). PL/SQL (2). JDBC. Constraints. Triggers.

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; the 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.