Carnegie Mellon is one of the top universities for computer science and offers a wide selection of database courses. Its 15-445/645 Intro To Database Systems is a project-oriented course that provides an introduction to the internal architecture of database systems. Here's the opportunity to watch its recorded lectures of fall 2021 complete with homework - for FREE. 

Working with databases is not just about SQL and this course goes through all the underlying infrastructure that allows us to use SQL. That is, it's a complete course on the design and implementation of database management systems, with query languages (SQL, stored procedures) being one part of the following topics being taught:

• data models (relational, document, key/value)
• storage models (n-ary, decomposition)
• storage architectures (heaps, log-structured)
• indexing (order preserving trees, hash tables)
• transaction processing (ACID, concurrency control)
• recovery (logging, checkpoints)
• query processing (joins, sorting, aggregation, optimization)
• parallel architectures (multi-core, distributed)

The course is normally taught by Professor Andy Pavlov, but because he was shooting a Netflix documentary at the time, the job was left to his fellow colleagues Lin Ma and Andrew Crotty.

In "OtterTune AutoTweaks Your DBMS With Help From ML" I  looked into Dr Pavlov's other endeavor of OtterTune, a tool powered by self-driving DBMS technology out of R&D at Carnegie Mellon University that aims to alleviate the fundamental difficulties of tuning a DBMS. State of the art technologies.

As such it doesn't come as a surprise that under Dr Pavlov's supervision, the CMU 15-445/645 Intro To Database Systems is state of the art educational material too. Caveat again, this course is not about building applications that work with databases but it's about building the infrastructure that these apps work upon. Specifically it's about the design and implementation of database management systems, the software that manages databases. It's not about using a dbms to build applications like for example a web application that stores its data and interacts with a database, nor is this a class about administering a database system. This however does not mean that SQL is not being taught.With this in mind let's take a look at the syllabus:

01 - Course Introduction & Relational Model
02 - Intermediate SQL
03 - Database Storage I
04 - Database Storage II
05 - Buffer Pools
06 - Hash Tables
07 - Tree Indexes
08 - Index Concurrency Control
09 - Sorting & Aggregations
10 - Join Algorithms
11 - Query Execution I
12 - Query Execution II
13 - Query Planning & Optimization I
14 - Query Planning & Optimization II
15 - Concurrency Control Theory
16 - Two-Phase Locking Concurrency Control
17 - Timestamp Ordering Concurrency Control
18 - Multi-Version Concurrency Control
19 - Database Logging
20 - Database Recovery
21 - Introduction to Distributed Databases
22 - Distributed OLTP Databases
23 - Distributed OLAP Databases
24 - Final Review

The videos of the recorded lectures are available on Youtube and span over an hour in length, while the accompanying homework with its solutions can be found on the class's University site. There's also some projects which require you to be familiar with programming and debugging in C++. Of course these were asked to be handed in by the CMU students taking the class and were graded. For the rest of us who are being granted free access to the materials there's no such requirements of course but it would be beneficial to try them  ourselves in order to immense into the depths of the course.

Upon successful completion , students should be able to:

1. Use relational algebra to express database queries.
   
   
2. Use SQL to interact with database management systems.
   
   
3. Design appropriate database tables, using functional dependencies and normal forms.
   
   
4. Implement a disk-oriented database storage manager with table heaps and indexes.
   
   
5. Understand, compare, and implement the fundamental concurrency control algorithms.
   
   
6. Implement database recovery algorithms and verify their correctness.
   
   
7. Identify trade-offs among database systems techniques and contrast distributed/parallel alternatives for both on-line transaction processing and on-line analytical workloads.
   Interpret and comparatively criticize database system architectures.

Really intriguing material.

More Information

CMU 15-445/645

YouTube playlist

Related Articles

 OtterTune AutoTweaks Your DBMS With Help From ML

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info