Harnessing Technology to make education free

Blog / ePortfolio / Forums / 289 courses / FAQ / digedcon

Advanced Databases

Purpose of Course  showclose

While CS403: Introduction to Modern Database Systems covered many of the core concepts behind database management systems, there are many other considerations that should be addressed if you intend to pursue a career in this field.  This course will expand upon what you learned about SQL in CS403 and introduce various other advanced topics, including query optimization, concurrency, data warehouses, object-oriented extensions, and XML.  While CS403 introduced the basics of database management systems, the additional topics covered in this course will help you become more proficient in writing queries and will expand your knowledge base so that you have a better understanding of the field.  By the end of this course, you should have a solid grasp on data warehouses and XML, which will prove to be invaluable as you progress further in your Computer Science studies.

Course Information  showclose

Welcome to CS410.  Below, please find general information on this course and its requirements. 

Course Designer:  Charles W. Lively, III

Primary Resources:

Requirements for Completion:  In order to complete this course, you will need to work through each unit and all of its assigned materials.  Specifically, be sure to focus on the tutorial exercises provided in Unit 1 to ensure a strong understanding of the SQL commands.

Please be aware that your mastery of the course material and final grade will be represented by your grade on the Final Exam.

In order to “pass” this course, you will need to earn a 70% or higher on the Final Exam.  Your score on the exam will be tabulated as soon as you complete it.  If you do not pass the exam, you may take it again.

Time Commitment: This course should take you a total of 97 hours to complete.  Each unit includes a “time advisory” that lists the amount of time you are expected to spend on each subunit.  These should help you plan your time accordingly.  It may be useful to take a look at these time advisories and to determine how much time you have over the next few weeks to complete each unit, and then to set goals for yourself.  For example, Unit 1 should take you 10.5 hours.  Perhaps you can sit down with your calendar and decide to complete subunits 1.1 and 1.2 (a total of 4 hours) on Monday night; subunit 1.3 (a total of 3 hours) on Tuesday night; etc.

Tips/Suggestions:  As noted in the “Course Requirements,” Introduction to Modern Databases is a pre-requisite for this course.  Please ensure that you have successfully completed this course and have a strong understanding of the fundamentals of SQL.



Learning Outcomes  showclose

Upon successful completion of this course, the student will be able to:

  • Write complex queries, including full outer joins, self-joins, sub queries, and set theoretic queries.
  • Write stored procedures and triggers.
  • Apply the principles of query optimization to a database schema.
  • Explain the various types of locking mechanisms utilized within database management systems.
  • Explain the different types of database failures as well as the methods used to recover from these failures.
  • Design queries against a distributed database management system.
  • Perform queries against database designed with object-relational extensions.
  • Develop and query XML files.

Course Requirements  showclose

In order to take this course, you must:

√    Have access to a computer.

√    Have continuous broadband Internet access.

√    Have the ability/permission to install plug-ins (e.g. Adobe Reader or Flash) and software.

√    Have the ability to download and save files and documents to a computer.

√    Have the ability to open Microsoft file and documents (.doc, .ppt, .xls, etc.).

√    Be competent in the English language.

√    Have read the Saylor Student Handbook.

√    Have access to a relational database management system.  A good open-source option is MySQL (dev.mysql.com).

√    Have completed CS101, CS102, CS107, CS201, CS202, and CS301 from “The Core Program” in the Computer Science discipline.

√    Have completed CS103/MA101and CS104/MA102 from the math requirements for “The Core Program.”

√    Have completed the CS403: Introduction to Modern Databases course.

Unit Outline show close


Expand All Resources Collapse All Resources
  • Unit 1: Advanced SQL  

    In this unit, we will look at SQL DML commands beyond the basic “select,“join,” and “group by,” as you will sometimes find it useful to filter a group using a having clause and/or to perform subqueries, which can be used to compare two results set in special ways.  We will also study the self-join as well as several set-theoretic operators.

    Unit 1 Time Advisory   show close
    Unit 1 Learning Outcomes   show close
  • 1.1 Relational Algebra Review and Join Commands  
    • Lecture: Gonzaga University: Shawn Bowers’ “Relational Algebra and Complex SQL”

      Link: Gonzaga University: Shawn Bowers’ “Relational Algebra and Complex SQL” (PDF)

      Instructions: On this webpage, scroll down to “Lecture 3” for an introduction to Relational Algebra and an introduction to the Join Commands in SQL.  Click on the hyperlink for “Lecture 3” to open the PDF document, and read slides 3 through 33 (pages 2-17).  This lecture covers subsection 1.1.  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.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 1.2 Additional Join Operations  
  • 1.2.1 SELF Join  
    • Reading: TutorialsPoint.com: “SQL SELF JOINS”

      Link: TutorialsPoint.com: “SQL SELF JOINS” (HTML)

      Instructions: Please read the entirety of this webpage 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.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 1.2.2 FULL Joins  
    • Reading: TutorialsPoint.com: “SQL FULL JOINS”

      Link: TutorialsPoint.com: “SQL FULL JOINS” (HTML)

      Instructions: Please read the entirety of this webpage 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.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 1.3 Set-Theoretic Operators  
  • 1.3.1 Union  

    Note: This subunit is covered by the reading assigned beneath subunit 1.3.

  • 1.3.2 Minus  

    Note: This subunit is covered by the reading assigned beneath subunit 1.3.

  • 1.3.3 Intersect  

    Note: This subunit is covered by the reading assigned beneath subunit 1.3.

  • 1.4 The HAVING Clause  
    • Lecture: Gonzaga University: Shawn Bowers’ “Complex SQL”

      Link: Gonzaga University: Shawn Bowers’ “Complex SQL” (PDF)

      Instructions: On this webpage, scroll down to “Lecture 5” for advanced topics in Relational Algebra and more advanced join commands.  Click on the hyperlink for “Lecture 4” to open the PDF document, and read slides 3 through 31 (pages 3-16).  This lecture covers section 1.4, 1.5, and the inclusive sub-subsections.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

    • Reading: TutorialsPoint.com: “SQL HAVING Clause”

      Link: TutorialsPoint.com: “SQL HAVING Clause” (HTML)

      Instructions: Please read the entirety of this webpage 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.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 1.5 Subqueries  

    Note:  This subunit is covered by the reading assigned beneath subunit 1.4 titled Complex SQL.

  • 1.5.1 The IN Operator  

    Note: This subunit is covered by the reading assigned beneath subunit 1.4.  Practice using the In Operator using MySQL or another available SQL database.

  • 1.5.2 The EXISTS Operator  

     Note: This subunit is covered by the reading assigned beneath subunit 1.4.  Practice using the EXISTS Operator using MySQL or another available SQL database.

  • 1.5.3 Correlated Subqueries  

    Note: This subunit is covered by the reading assigned beneath subunit 1.4.  Practice using the Correlated Subqueries using MySQL or another available SQL database.

  • 1.5.4 When to Use Subqueries vs. Joins  

    Note: This subunit is covered by the reading assigned beneath subunit 1.4.  Compare results from Subqueries versus Joins using MySQL or another available SQL database.

  • 1.6 Views  
  • 1.6.1 Definition  

    Note: This subunit is covered by the reading assigned beneath subunit 1.6.

  • 1.6.2 Roles  

    Note: This subunit is covered by the reading assigned beneath subunit 1.6.

  • 1.6.3 Indexes  

    Note: This subunit is covered by the reading assigned beneath subunit 1.6.

  • Unit 2: Stored Procedures and Triggers  

    Much of the work of database retrieval can be done on the server side as opposed to the client side.  The server can execute commands more efficiently and is capable of sending only the result set back to the client, rather than sending the entire data set back and forth between client and server.  In this unit, we will look at stored procedures, or blocks of code stored and executed on the server.  We will also look at triggers, which are blocks of code that execute on the server when certain events occur, such as the addition or deletion of data from a table.  Lastly, we will study rights when running a stored procedure.

    Unit 2 Time Advisory   show close
    Unit 2 Learning Outcomes   show close
  • 2.1 Stored Procedures and Their Usage in Relational Database Management Systems  
  • 2.2 Stored Procedures  

    Note: This subunit is covered by the reading assigned beneath subunit 2.1.

  • 2.2.1 Structure of a Procedure  

    Note: This subunit is covered by the reading assigned beneath subunit 2.1.

  • 2.2.2 Calling a Procedure  

    Note: This subunit is covered by the reading assigned beneath subunit 2.1.

  • 2.2.3 Examples  

    Note: This subunit is covered by the reading assigned beneath subunit 2.1.

  • 2.3 Stored Functions  
  • 2.3.1 Differences between a Function and Procedure  

    Note: This subunit is covered by the reading assigned beneath subunits 2.1 and 2.3.

  • 2.3.2 Calling a Function  

    Note: This subunit is covered by the reading assigned beneath subunits 2.1 and 2.3

  • 2.3.3 Examples  

    Note: This subunit is covered by the reading assigned beneath subunits 2.1 and 2.3.

  • 2.4 Constraints and Triggers  
  • 2.4.1 Purpose  

    Note: This subunit is covered by the reading assigned beneath subunit 2.4.

  • 2.4.2 Constraints in SQL  

    Note: This subunit is covered by the reading assigned beneath subunit 2.4.

  • 2.4.3 Structure of a Trigger  

    Note: This subunit is covered by the reading assigned beneath subunit 2.4.

  • 2.5 Definer vs. Invoker Rights  

    Note: This subunit is covered by the reading assigned beneath subunit 2.3.

  • Unit 3: Query Optimization  

    Queries written in SQL do not always run efficiently.  However, there are a number of techniques that you can use to optimize them.  In this unit, we will first look at indexes.  When tables are indexed on certain fields, the relational database management system can take advantage of quicker retrieval time when searching through the table.  Once you have a firm understanding of indexes, please move on to the discussion of query optimization.

    Unit 3 Time Advisory   show close
    Unit 3 Learning Outcomes   show close
  • 3.1 Stages in Query Processing  
  • 3.1.1 Logical and Physical Query Plan  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.1.

  • 3.1.2 Intermediate Query Plan Language  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.1.

  • 3.1.3 Physical Query Plan Language  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.1.

  • 3.1.4 One-Pass Algorithms  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.1.

  • 3.2 Query Processing Algorithms  
  • 3.2.1 Single-pass Algorithms and Limitations  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.2.

  • 3.2.2 Multi-pass Algorithms  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.2.

  • 3.2.3 Sort Based Algorithms  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.2.

  • 3.2.4 Hash Based Algorithms  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.2.

  • 3.2.5 Index Based Algorithms  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.2.

  • 3.3 Query Plan Execution  
  • 3.3.1 Index Based Algorithm Details  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.3.

  • 3.3.2 Rewriting Parse Trees  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.3.

  • 3.3.3 Cost Estimation and Heuristics  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.3.

  • 3.4 Cost-Based Query Optimization  
    • Lecture: Gonzaga University: Shawn Bowers’ “Query Optimization Lecture 18”

      Link: Gonzaga University: Shawn Bowers’ “Query Optimization Lecture 18” (PDF)

      Instructions: On this webpage, scroll down to “Lecture 18” on Query Optimization.  Click on the hyperlink for “Lecture 18” to open the PDF document, and read slides 3 through 20 (pages 2-10) for a detailed overview on cost-based query optimization techniques.  This lecture covers subsection 3.4.1 and 3.4.2.  As you read this lecture be sure to understand the importance of utilizing the statistical methods for query optimization.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 3.4.1 Statistics  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.4. 

  • 3.4.2 Histograms and Wavelets  

    Note: This subunit is covered by the lecture assigned beneath subunit 3.4.  Slide 12 begins the introduction for this sub-subunit.

  • Unit 4: Concurrency and Recovery  

    Database management systems must be able to support concurrency—that is, they must be able to support multiple users and processes accessing the same records in a table.  Modern database management systems handle concurrency in a variety of ways, including through the use of locking and versioning mechanisms.   Database management systems must also be able to ensure transaction consistency and recover from catastrophic failures.  In this unit, we will look at methods of ensuring that systems are capable of concurrency and recovery.

    Unit 4 Time Advisory   show close
    Unit 4 Learning Outcomes   show close
  • 4.1 Transactions and the ACID Property of Transactions  
  • 4.2 Serializability and the Serializability Theorem  
    • Reading: Carnegie Melon University: Gregory Kesden’s “Lecture 16”

      Link:  Carnegie Mellon University: Gregory Kesden’s “Lecture 16” (HTML)
       
      Instructions: Please read through the entire webpage for lecture notes on serializability.  Be sure to grasp a good understanding of the subcomponents that make up the serializability theorem.  Determine the factors that are likely to affect the outcome of the schedule.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 4.3 Two-Phase Locking  
    • Reading: W3uap.blogspot.com: Yale University: Avi Silberschatz, et al.’s “16: Concurrency Control”

      Link: W3uap.blogspot.com: Yale University: Avi Silberschatz, et al.’s “16: Concurrency Control” (PDF or PowerPoint)

      Instructions: Click on the link above, scroll down the webpage to “Part 5: Transaction Management,” and click on the “PDF” or “PPT” hyperlinks to download the section labeled “16. Concurrency Control.”  Please view the entire lecture slides (79 slides total) that cover two-phase locking timestamps, deadlocks, and multiversion concurrency control.  The selected slides cover sections 4.3 through 4.6 and all appropriate subsections.  As you read this lecture please be sure to gain a detailed understanding of the mechanism and locks that affect concurrency control.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 4.3.1 Aggressive and Conservative Schedulers  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.3.2 Basic Two-Phase Locking  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.3.3 Locking Performance  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.4 Timestamps  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.5 Deadlocks  
  • 4.5.1 Definition of a Deadlock  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.5.2 Deadlock Prevention  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.5.3 Deadlock Avoidance  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.6 Multiversion Concurrency Control  

    Note: This subunit is covered by the reading assigned beneath subunit 4.3.  

  • 4.7 Recovery  
    • Reading: W3uap.blogspot.com: Yale University: Avi Silberschatz et al.’s “17: Recovery System”

      Link: W3uap.blogspot.com: Yale University: Avi Silberschatz, et al.’s “17: Recovery System” (PDF or PowerPoint)

      Instructions: Click on the link above, scroll down to “Part 5: Transaction Management,” and select the “PDF” or “PPT” link for the section labeled “17. Recovery System.”  Please view the entire lecture slides (81 slides total) that cover two-phase locking timestamps, and deadlocks.  The selected slides cover section 4.7 and all appropriate subsections (4.7.1 through 4.7.4).   As you read through this lecture please be sure to determine the components that are likely to affect the recovery of a database systems.  Reading this lecture should give you a detailed understanding of the types of failures that can occur and how they can be corrected.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 4.7.1 Types of failures  

    Note: This subunit is covered by the reading assigned beneath subunit 4.7.

  • 4.7.2 Checkpoints  

    Note: This subunit is covered by the reading assigned beneath subunit 4.7.

  • 4.7.3 Undo/Redo  

    Note: This subunit is covered by the reading assigned beneath subunit 4.7.

  • 4.7.4 Rollforward/Rollback  

    Note: This subunit is covered by the reading assigned beneath subunit 4.7.

  • Unit 5: Database System Architectures  

    Databases often need to be distributed across a number of different locations, especially because today’s global companies need to be able to access data in multiple locations.  While one copy of a database could reside in a location and be accessed remotely, it is often more efficient and reliable to replicate data across servers or to have portions of data on databases that share access across locations.  In this unit, we will study distributed databases and related issues.

    Unit 5 Time Advisory   show close
    Unit 5 Learning Outcomes   show close
  • 5.1 Centralized and Client-Server Systems  
    • Reading: W3uap.blogspot.com: Yale University: Avi Silberschatz, et al.’s “20: Database System Architectures”

      Link: W3uap.blogspot.com: Yale University: Avi Silberschatz et al.’s “20: Database System Architectures” (PDF or PowerPoint)

      Instructions: Click on the link above, scroll down to “Part 7: System Architecture,” and select the “PDF” or “PPT” link for lecture “20: Database System Architectures.” Please view the entire lecture slides (37 slides total) that covers server-system architectures and client-server architectures.  When reading this material, compare and contrast the differences between the centralized and client-server systems.  The selected slides cover sections 5.1, 5.2, and inclusive subsections (5.2.1 through 5.2.3). 

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 5.2 Server-System Architectures  

    Note: This subunit is covered by the reading assigned beneath subunit 5.1.

  • 5.2.1 Transaction Servers and Data Servers  

    Note: This subunit is covered by the reading assigned beneath subunit 5.1.

  • 5.2.2 Interconnection Network Architectures  

    Note: This subunit is covered by the reading assigned beneath subunit 5.1.

  • 5.2.3 Distributed Systems  

    Note: This subunit is covered by the reading assigned beneath subunit 5.1.

  • 5.3 Parallel Databases  
    • Reading: W3uap.blogspot.com: Yale University: Avi Silberschatz, et al.’s “21: Parallel Databases”

      Link: W3uap.blogspot.com: Yale University: Avi Silberschatz, et al.’s “21: Parallel Databases” (PDF or PowePoint)

      Instructions: Click on the link above, navigate to the “Part 7: System Architecture” section, and click on the links for the “PDF” or “PPT” of lecture “21: Parallel Databases.”  Please view the entire lecture slides (43 slides total) that covers Parallel Databases.  When reading this lecture, be sure to compare and contrast the components available in parallel databases.  The selected slides cover section 5.3 all appropriate subsections (5.3.1 through 5.3.3). 

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 5.3.1 I/O, Interquery, and Intraquery Parallelism  

    Note: This subunit is covered by the reading assigned beneath subunit 5.3.

  • 5.3.2 Intraoperation and Interoperation Parallelism  

    Note: This subunit is covered by the reading assigned beneath subunit 5.3.

  • 5.3.3 Design of Parallel Systems  

    Note: This subunit is covered by the reading assigned beneath subunit 5.3.

  • 5.4 Distributed Databases  
  • 5.4.1 Heterogeneous and Homogeneous Databases  
  • 5.4.2 Distributed Data Storage and Transactions  

    Note: This subunit is covered by the lecture assigned beneath subunit 5.4.1.

  • 5.4.3 Commit Protocols  

    Note: This subunit is covered by the lecture assigned beneath subunit 5.4.1.

  • 5.4.4 Concurrency Control  
  • 5.4.4.1 Locking Protocols  

    Note: This subunit is covered by the lecture assigned beneath subunit 5.4.4.

  • 5.4.4.2 Time Stamping  

    Note: This subunit is covered by the lecture assigned beneath subunit 5.4.4.

  • 5.4.5 Distributed Query Processing  
  • 5.4.5.1 Replication and Fragmentation  

    Note: This subunit is covered by the reading assigned beneath subunit 5.4.5.

  • 5.4.5.2 Joining Tables  

    Note: This subunit is covered by the reading assigned beneath subunit 5.4.5.

  • Unit 6: Data Warehousing  

    Traditionally, database management systems have been used for transaction processing.  Recently, however, the field has seen a trend of creating separate data stores for the purposes of reporting and tracking historical data.  In this unit, you will learn about data warehouses and how they are implemented.

    Unit 6 Time Advisory   show close
    Unit 6 Learning Outcomes   show close
  • 6.1 Data Warehouse Introduction  
    • Lecture: University of Zurich: Michael Böhlen’s “Data Warehousing”

      Link: University of Zurich: Michael Böhlen’s “Data Warehousing” (PDF)

      Instructions: Click on this link above, scroll down to the “Data Warehouse Part” section of the webpage, and select the “slides” hyperlink for the lecture labeled “1.Data Warehousing” to open the PDF file.  Read and review the selected slides in their entirety (71 slides total) for a detailed introduction to Data Warehouses.  The selected slides cover section 6.1 and all inclusive subsections (6.1.1 through 6.1.4).

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 6.1.1 Uses of a Data Warehouse  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.1.

  • 6.1.2 On-Line Transaction Processing (OLAP) versus On-Line Analytical Processing (OLAP)  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.1.

  • 6.1.3 Data Integration  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.1.

  • 6.1.4 Data Warehousing Definition  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.1.

  • 6.2 SQL OLAP Extensions  
    • Lecture: University of Zurich: Michael Böhlen’s “SQL OLAP extensions”

      Link: University of Zurich: Michael Böhlen’s “SQL OLAP extensions”(PDF)

      Instructions: Click on the link above, scroll down to the “Data Warehouse Part” section of the webpage, and click on the “slides” hyperlink for the lecture labeled “2.SQL OLAP extensions.”   Read and review the selected slides in their entirety (65 slides total) for a detailed understanding of SQL Usage and OLAP extension operations.   The selected slides cover section 6.2 all appropriate subsections (6.2.1 & 6.2.2).

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 6.2.1 SQL Usage  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.2.1.1 Table Expression in Data Warehousing  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.2.1.2 Query Specification in Data Warehousing  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.2.1.3 Query Expression in Data Warehousing  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.2.2 OLAP  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.2.2.1 Group by Extensions Operations  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.2.2.2 SQL for Analysis and Reporting  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.2.3 Three-layer  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.2.

  • 6.3 An Algebraic OLAP Operator  
    • Lecture: University of Zurich: Michael Böhlen’s “Generalized Multi-Dimensional Join”

      Link: University of Zurich: Michael Böhlen’s “Generalized Multi-Dimensional Join”(PDF)

      Instructions: Click on the link above, scroll down to the “Data Warehouse Part,” and click on the “slides” hyperlink for the lecture labeled “3.Generalized Multi-Dimensional Join.” Read and review the selected slides in their entirety (51 slides total) for an introduction to concepts involved in the generalized multi-dimensional join.   When going over this lecture, try to focus on the components used for creating the Algebraic OLAP Operator and how it can affect performance.  The selected slides cover section 6.3 and all inclusive subsections (6.3.1 through 6.3.4).

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 6.3.1 Introduction to the Generalized Multi-dimensional Join  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.3.

  • 6.3.2 Algorithm for the Generalized Multi-dimensional Join  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.3.

  • 6.3.3 Exploring Subqueries in an OLAP Context  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.3.

  • 6.3.4 Characteristics of Distributed Data Warehousing  

    Note: This subunit is covered by the lecture assigned beneath subunit 6.3.

  • Unit 7: Object-Oriented and Object-Relational Databases  

    As object-oriented programming gained popularity in the 1990s, object-oriented database management systems that incorporated all of the principles of object-oriented design began to emerge.  A short time later, a hybrid approach known as object-relational databases was adopted.  In this unit, you will first learn about object-oriented databases and then object-relational extensions to relational database management systems.

    Unit 7 Time Advisory   show close
    Unit 7 Learning Outcomes   show close
  • 7.1 Object-Oriented Data Model  
  • 7.1.1 Complex Types  

    Note: This subunit is covered by the assignments beneath subunit 7.1.

  • 7.1.2 Query Processing in Object-Oriented Databases  

    Note: This subunit is covered by the assignments beneath subunit 7.1.

  • 7.1.3 Storage Structures for Object-Oriented Databases  

    Note: This subunit is covered by the assignments beneath subunit 7.1.

  • 7.2 Object-Relational Database Systems  
  • 7.2.1 The Object-Relational Model  

    Note: This subunit is covered by the lectures assigned beneath subunit 7.2.

  • 7.2.2 User Defined Types and Functions  

    Note: This subunit is covered by the lectures assigned beneath subunit 7.2.

  • 7.2.3 Query Processing  

    Note: This subunit is covered by the lectures assigned beneath subunit 7.2.

  • 7.2.4 Object-Relational System Architectures  

    Note: This subunit is covered by the lectures assigned beneath subunit 7.2.

  • 7.2.5 The SQL3 Standard  

    Note: This subunit is covered by the lectures assigned beneath subunit 7.2.

  • Unit 8: XML and Databases  

    While HTML is used to display data on webpages, XML is used to describe data and has quickly become a standard for transmitting data between organizations.  In this unit, we will discuss the structure of XML files, how to write queries against such files, and how to integrate XML into a relational database management system.

    Unit 8 Time Advisory   show close
    Unit 8 Learning Outcomes   show close
  • 8.1 XML  
    • Lecture: Duke University: Jun Yang’s “XML Basics”

      Link: Duke University: Jun Yang’s “XML Basics” (PDF)

      Instructions: On this webpage, scroll down to week 9, and click on the second “PDF” hyperlink listed under the “slides” column to download the 5-page PDF on “XML Basics.”  When you click on the pdf document, the presentation title will be “XML, DTD, and XPath.”  Read and review the selected slides in their entirety (28 slides total) for an introduction to XML concepts.  When going through this lecture, be sure to understand the purpose of using XML and how it differs from other options available.  The selected slides cover sections 8.1 through 8.4. 

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 8.1.1 Uses for XML  
  • 8.1.2 XML Browsers  

    Note: This subunit is covered by the assignments beneath subunit 8.1 and 8.1.1.

  • 8.1.3 Well-Formed XML  

    Note: This subunit is covered by the assignments beneath subunit 8.1 and 8.1.1.

  • 8.1.4 XML Elements  

    Note: This subunit is covered by the assignments beneath subunit 8.1 and 8.1.1.

  • 8.1.5 XML Attributes  

    Note: This subunit is covered by the assignments beneath subunit 8.1 and 8.1.1.

  • 8.2 Document Type Definition (DTD)  

    This subunit is covered by the reading assigned beneath subunit 8.1.

  • 8.3 Name Spaces  

    This subunit is covered by the reading assigned beneath subunit 8.1.

  • 8.4 XML Schema Types  

    This subunit is covered by the reading assigned beneath subunit 8.1.

  • 8.4.1 Elements  
  • 8.4.2 Attributes  
  • 8.4.3 Complex Types  
  • 8.5 XPath and XQuery  
    • Lecture: Duke University: Jun Yang’s “XPath, XQuery”

      Link: Duke University: Jun Yang’s “XPath, XQuery” (PDF)

      Instructions: On this webpage, scroll down to week 11, and click on the “PDF” hyperlink under the “slides” column for “XPath, XQuery” to download the 3-page PDF.  Read and review the selected slides in their entirety (17 total) for an introduction to concepts related to XPath and XQuery.   The selected slides cover sections 8.1 through 8.4.  When reading through this lecture be sure to understand the differences between using XPath and other technologies such as SQL.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above.

      See a broken link? Please let us know!

  • 8.5.1 Axes  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.5.

  • 8.5.2 Functions  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.5.

  • 8.5.3 Predicates  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.5.

  • 8.5.4 Expressions  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.5.

  • 8.5.5 FLWR Queries  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.5.

  • 8.5.6 Examples  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.5.

  • 8.6 XSLT  
    • Lecture: Duke University: Jun Yang’s “XSLT”

      Link: Duke University: Jun Yang’s “XSLT” (PDF)

      Instructions: On this webpage, scroll down to week 11, and select the “PDF” hyperlink under the “slides” column for “XSLT” to download the 4-page PDF file.  Read and review the selected slides in their entirety (22 slides) for an introduction to concepts related to XPath and XQuery.   The selected slides cover sections 8.6 and the appropriate subsections.

      Terms of Use: Please respect the copyright and terms of use displayed on the webpage above

      See a broken link? Please let us know!

  • 8.6.1 Syntax  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.6.

  • 8.6.2 Elements  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.6.

  • 8.6.3 Functions  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.6.

  • 8.7 Integrating XML and Databases  
  • 8.7.1 Storing XML in a Database  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.7.

  • 8.7.2 Creating XML Documents from a Database Table  

    Note: This subunit is covered by the lecture assigned beneath subunit 8.7.

  • Final Exam  

« Previous Unit Next Unit » Back to Top