CS410: Advanced Databases

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.

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.

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.

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.

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.

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.

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.

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.

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.