Advanced Databases
Topic outline
-
-
Forum
-
-
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.
-
Page
-
-
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.
Completing this unit should take you approximately 13 hours.
-
-
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.
-
-
-
Read slides 3 through 48 (pages 3-24).
-
-
-
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.
-
-
-
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.
-
-
-
Read this page for an overview of using the Union, Union All, Minus, and Intersect operators in SQL.
-
-
-
Read slides 3 through 31 (pages 3-16).
-
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.
-
-
-
Read this page for an overview of views and indexes in SQL.
-
-
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.
Completing this unit should take you approximately 9 hours.
-
-
Read this chapter, which will provide and appropriate overview of using Stored Procedures and Functions in MySQL Server.
-
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.
-
-
-
Read sections 18.3 through 18.5 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.
-
-
-
Read this article, which gives an overview of SQL, including constraints in SQL, which are used to ensure database data integrity.
-
Read this article, which describes SQL syntax and examples for constraints.
-
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.
-
Read this example of a trigger.
-
-
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.
Completing this unit should take you approximately 13 hours.
-
-
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.
-
-
-
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.
-
-
-
Watch this lecture for a introduction to query processing and optimization with a focus Query plan execution.
-
-
-
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.
-
-
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.
Completing this unit should take you approximately 13 hours.
-
-
Read this presentation, which covers transaction properties and serializability.
-
Read this lecture to learn about transaction properties and serializability.
-
-
-
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.
-
-
-
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.
-
-
-
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.
-
-
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.
Completing this unit should take you approximately 17 hours.
-
-
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.
-
-
-
Read this lecture, which covers parallel databases. While you read, be sure to compare and contrast the components available in parallel databases.
-
-
-
Read this lecture, which covers distributed databases.
-
-
-
Read this lecture, which covers distributed homogeneous databases.
-
Read this lecture, which covers distributed heterogeneous databases.
-
-
-
Watch this lecture for an introduction to concurrency control mechanisms in distributed databases.
-
-
-
Read this lecture, which discusses query processing.
-
-
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.
Completing this unit should take you approximately 12 hours.
-
-
Read these slides for an introduction to data warehousing and data mining.
-
-
-
Read these slides for an introduction to SQL usage and OLAP extension operations.
-
-
-
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.
-
-
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.
Completing this unit should take you approximately 12 hours.
-
-
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.
-
Watch this lecture for an introduction to object-oriented databases.
-
-
-
Read these slides for an introduction to concepts related to object-relational databases.
-
Watch this lecture for an introduction to object-oriented 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.
Completing this unit should take you approximately 8 hours.
-
-
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.
-
Watch this lecture, which discusses XML and how it is used, including formatting, its use in browsers, its elements, and its attributes.
-
-
-
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.
-
-
-
Read these slides for an introduction to concepts related to XPath and XQuery.
-
-
-
Watch this lecture for an introduction to object-oriented databases.
-