CSC 370 Database Systems: Spring 2009

Text:

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)

References:

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.

Assignments:
There would be three assignments. 

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

Project:
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.