CSC 370 Database Systems: Fall 2006

Text:

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

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.

Marks so far: Link

Assignments:

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.

Assignments:
There would be three assignments. 

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

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