Topic Name Description
Course Introduction Course Syllabus
Unit 1: Advanced SQL Unit 1 Learning Outcomes
1.1: Relational Algebra Review and Join Commands Gonzaga University: Shawn Bowers' "Relational Algebra and Complex SQL"

Read slides 3 through 33 (pages 2-17). As you read this lecture be sure to think of how the necessary mathematical operations are likely to affect the outcome of your database actions.

1.2: Additional Join Operations Gonzaga University: Shawn Bowers' "Relational Algebra and Complex SQL"

Read slides 3 through 48 (pages 3-24).

1.2.1: SELF Join TutorialsPoint: "SQL SELF JOINS"

Read this page for a solid overview of the SELF JOINS command and how it is used to join a table. Practice utilizing the SQL commands using MySQL or another available SQL database.

1.2.2: FULL Joins TutorialsPoint: "SQL FULL JOINS"

Read this page for a solid overview of the FULL JOINS command and how it is used to achieve a full join of the left and right outer joins of a table. Practice utilizing the SQL commands using MySQL or another available SQL database.

1.3: Set-Theoretic Operators Tom Jewett's "Database Design with UML and SQL: Union and Minus"

Read this page for an overview of using the Union, Union All, Minus, and Intersect operators in SQL.

1.4: The HAVING Clause Gonzaga University: Shawn Bowers' "Complex SQL"

Read slides 3 through 31 (pages 3-16).

TutorialsPoint: "SQL HAVING Clause"

Read this page for a solid overview of the basics of using the HAVING clause command in SQL. Practice utilizing the SQL commands using MySQL or another available SQL database.

1.5: Views Tom Jewett's "Database Design with UML and SQL: SQL Technique: Views and Indexes"

Unit 2: Stored Procedures and Triggers Unit 2 Learning Outcomes
2.1: Stored Procedures and Their Usage in Relational Database Management Systems College of San Mateo: Bob Timlin's "Prepared Statements and Store Procedures and Functions"

Read this chapter, which will provide and appropriate overview of using Stored Procedures and Functions in MySQL Server.

MySQL 5.1 Reference Manual: "Stored Procedures and the Grant Table"

Read sections 18.1 and 18.2 for an overview of stored procedures and stored procedure syntax in RDMS. As you read please keep in mind the effects that stored procedures are likely to have on the Database System.

2.2: Stored Functions MySQL 5.1 Reference Manual: "Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()"

Read sections 18.3 and 18.4 for an overview of stored functions, triggers, and definer and invoker rights in RDMS. As you read compare and contrast the results that can be obtained utilizing Stored Functions in a Database System.

2.3: Constraints and Triggers Nguyen Kim Anh's "Structured Query Language"

Read this article, which gives an overview of SQL, including constraints in SQL, which are used to ensure database data integrity.

w3resource: "SQL Constraints"

Oracle Community Wiki: "Triggers Tutorial"

Read this article, which illustrates constraints and triggers supported by Oracle. A trigger consists of code that is executed when a predefined event occurs at the database, schema, view, or table level. Constraints are simpler than triggers, and are defined at the column or table level, and only check for data integrity.

Oracle Community Wiki: "Trigger Development Example"

Read this example of a trigger.

Unit 3: Query Optimization Unit 3 Learning Outcomes
3.1: Stages in Query Processing Indian Institute of Technology Bangalore: S. Srinath's "Query Processing and Optimization I"

Watch this lecture for a introduction to query processing and optimization.As you watch, be sure to take notes outlining the differences between the various aspects of the Query Plan Language. Determine the strengths and weaknesses between the Logical and Physical Query Plan and languages.

3.2: Query Processing Algorithms Indian Institute of Technology Bangalore: S. Srinath's "Query Processing and Optimization II"

Watch this lecture for a introduction to query processing and optimization with a focus on various algorithms that can be used.As you watch, be sure to understand the differences between the various query processing and optimizing algorithms. Determine which scenarios would be optimal for using a single-pass versus a multi-pass algorithm, etc.

3.3: Query Plan Execution Indian Institute of Technology Bangalore: S. Srinath's "Query Processing and Optimization III"

Watch this lecture for a introduction to query processing and optimization with a focus Query plan execution.

3.4: Cost-Based Query Optimization Gonzaga University: Shawn Bowers' "Query Optimization"

Read slides 3 through 20 (pages 2-10) for an overview of cost-based query optimization techniques. As you read this lecture be sure to understand the importance of utilizing the statistical methods for query optimization.

Unit 4: Concurrency and Recovery Unit 4 Learning Outcomes
4.1: Transactions and the ACID Property of Transactions Avi Silberschatz's "Database System Concepts: Transactions"

Read this presentation, which covers transaction properties and serializability.

University of the Pacific: Michael Doherty's "Transactions and Serializability"

4.2: Serializability and the Serializability Theorem Carnegie Mellon University: Gregory Kesden's "Serialization"

Read these notes on serializability. Be sure that you understand the subcomponents that make up the serializability theorem. Determine the factors that are likely to affect the outcome of the schedule.

4.3: Two-Phase Locking Avi Silberschatz's "Database System Concepts: Concurrency Control"

Read these slides, which cover two-phase locking timestamps, deadlocks, and multiversion concurrency control. As you read, focus on the mechanism and locks that affect concurrency control.

4.4: Recovery Avi Silberschatz's "Database System Concepts: Recovery System"

Read these slides, which cover two-phase locking timestamps, and deadlocks. As you read, focus on the components that are likely to affect the recovery of a database systems, as well as types of failures that can occur and how they can be corrected.

Unit 5: Database System Architectures Unit 5 Learning Outcomes
5.1: Centralized and Client-Server Systems Avi Silberschatz's "Database System Concepts: Database System Architectures"

Read this lecture, which covers server-system architectures and client-server architectures. While you read, compare and contrast the differences between the centralized and client-server systems.

5.2: Parallel Databases Avi Silberschatz's "Database System Concepts: Parallel Databases"

Read this lecture, which covers parallel databases. While you read, be sure to compare and contrast the components available in parallel databases.

5.3: Distributed Databases Avi Silberschatz's "Database System Concepts: Distributed Databases"

Read this lecture, which covers distributed databases.

5.3.1: Heterogeneous and Homogeneous Databases Massachusetts Institute of Technology: Stuart Madnick's "Distributed Homogeneous Databases"

Read this lecture, which covers distributed homogeneous databases.

Massachusetts Institute of Technology: Stuart Madnick's "Distributed Heterogeneous Databases"

Read this lecture, which covers distributed heterogeneous databases.

5.3.2: Concurrency Control Indian Institute of Technology Madras: D. Janakiram's "Concurrency Control for Distributed Transaction"

Watch this lecture for an introduction to concurrency control mechanisms in distributed databases.

5.3.3: Distributed Query Processing Free University of Bozen-Bolzano: Johann Gamper's "Distributed Query Processing"

Read this lecture, which discusses query processing.

Unit 6: Data Warehousing Unit 6 Learning Outcomes
6.1: Data Warehouse Introduction University of Zurich: Michael Böhlen's "Data Warehousing"

Read these slides for an introduction to data warehousing and data mining.

6.2: SQL OLAP Extensions University of Zurich: Michael Böhlen's "SQL OLAP extensions"

Read these slides for an introduction to SQL usage and OLAP extension operations.

6.3: An Algebraic OLAP Operator University of Zurich: Michael Böhlen's "Generalized Multi-Dimensional Join"

Read these slides for an introduction to concepts involved in the generalized multi-dimensional join. While you read, focus on the components used for creating the Algebraic OLAP Operator and how it can affect performance.

Unit 7: Object-Oriented and Object-Relational Databases Unit 7 Learning Outcomes
7.1: Object-Oriented Data Model University of Alberta: Osmar Zaïane's "Object-Oriented Databases"

Read these slides for an introduction to concepts related to object-oriented databases. While you read, compare the components available in object-oriented databases and how they are similar to object-oriented components in programming languages.

Indian Institute of Technology Bangalore: S. Srinath's "Object Oriented Databases I"

Watch this lecture for an introduction to object-oriented databases.

7.2: Object-Relational Database Systems University of California, Santa Cruz: M. Keller's "Object-Relational Databases"

Read these slides for an introduction to concepts related to object-relational databases.

Indian Institute of Technology Bangalore: S. Srinath's "Object Oriented Databases II"

Watch this lecture for an introduction to object-oriented databases.

Unit 8: XML and Databases Unit 8 Learning Outcomes
8.1: XML Duke University: Jun Yang's "XML Basics"

Read these slides for an introduction to XML concepts. While you read, focus on the purpose of using XML and how it differs from other options.

Indian Institute of Technology Kharagpur: S. Srinath's "Extensible Markup Language (XML)"

Watch this lecture, which discusses XML and how it is used, including formatting, its use in browsers, its elements, and its attributes.

8.2: XPath and XQuery Duke University: Jun Yang's "XPath, XQuery"

Read these slides for an introduction to concepts related to XPath and XQuery. While you read, focus on the differences between using XPath and other technologies such as SQL.

8.3: XSLT Duke University: Jun Yang's "XSLT"

Read these slides for an introduction to concepts related to XPath and XQuery.

8.4: Integrating XML and Databases Indian Institute of Technology Bangalore: S. Srinath's "XML Databases"

Watch this lecture for an introduction to object-oriented databases.