Topic Name Description
Course Introduction Page Course Syllabus
Page Course Terms of Use
Unit 1: Advanced SQL Page Unit 1 Learning Outcomes
1.1: Relational Algebra Review and Join Commands URL 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 URL Gonzaga University: Shawn Bowers' "Relational Algebra and Complex SQL"

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

1.2.1: SELF Join URL 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 URL 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 URL 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 URL Gonzaga University: Shawn Bowers' "Complex SQL"

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

URL 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 URL Tom Jewett's "Database Design with UML and SQL: SQL Technique: Views and Indexes"

Read this page for an overview of views and indexes in SQL.

Unit 2: Stored Procedures and Triggers Page Unit 2 Learning Outcomes
2.1: Stored Procedures and Their Usage in Relational Database Management Systems URL 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.

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

URL w3resource: "SQL Constraints"

Read this article, which describes SQL syntax and examples for constraints.

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

URL Oracle Community Wiki: "Trigger Development Example"

Read this example of a trigger.

Unit 3: Query Optimization Page Unit 3 Learning Outcomes
3.1: Stages in Query Processing Page 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 Page 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 Page 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 URL 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 Page Unit 4 Learning Outcomes
4.1: Transactions and the ACID Property of Transactions URL Avi Silberschatz's "Database System Concepts: Transactions"

Read this presentation, which covers transaction properties and serializability.

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

Read this lecture to learn about transaction properties and serializability.

4.2: Serializability and the Serializability Theorem URL 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 URL 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 URL 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 Page Unit 5 Learning Outcomes
5.1: Centralized and Client-Server Systems URL 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 URL 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 URL Avi Silberschatz's "Database System Concepts: Distributed Databases"

Read this lecture, which covers distributed databases.

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

Read this lecture, which covers distributed homogeneous databases.

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

Read this lecture, which covers distributed heterogeneous databases.

5.3.2: Concurrency Control Page 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 URL Free University of Bozen-Bolzano: Johann Gamper's "Distributed Query Processing"

Read this lecture, which discusses query processing.

Unit 6: Data Warehousing Page Unit 6 Learning Outcomes
6.1: Data Warehouse Introduction URL 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 URL 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 URL 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 Page Unit 7 Learning Outcomes
7.1: Object-Oriented Data Model URL 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.

Page 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 URL University of California, Santa Cruz: M. Keller's "Object-Relational Databases"

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

Page 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 Page Unit 8 Learning Outcomes
8.1: XML URL 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.

Page 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 URL 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 URL 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 Page Indian Institute of Technology Bangalore: S. Srinath's "XML Databases"

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