DS310 — Data Mechanics

Preface

Welcome to DS310, Data Mechanics!

These are the course lecture notes for DS310, covering the foundations of data management: database design, the relational model and SQL, storage and indexing, transactions and recovery, data warehousing, and distributed and NoSQL systems.

Many lectures here were originally based on course notes by David G. Sullivan (BU). Some material is based on lecture notes by Jiawei Han (SFU), Micheline Kanber (SFU), and Nikos Mamoulas (BU). All have been adapted and revised for this course.

Format

This course notes site is built using Quarto and all the source pages are in Quarto markdown files. Each link below opens a reveal.js deck.

Data Modeling & SQL

• Course Overview — Database Design and ER Models
• The Relational Model
• Relational Algebra and a First Look at SQL
• SQL: Aggregates, Subqueries, Joins, and Outer Joins
• Normalization in Relational Databases; SQL Practices

Storage & Query Processing

• Storage Fundamentals
• Indexing
• Dynamic Linear Hashing
• Query Processing and Optimization

Transactions & Concurrency

• A First Look at Transactions
• Transactions and Schedules, Part II
• Concurrency Control: Locking
• Locking II: Strict/Rigorous Locks, Lock Upgrades, Deadlocks
• Timestamp-Based Concurrency Control

Recovery

• Recovery and Logging
• Recovery and Logging II

Warehousing & OLAP

• Data Warehousing and OLAP
• Data Warehousing and OLAP, Part II

Distributed & NoSQL

• Distributed Databases and Replication, Part I
• Distributed Databases and Replication, Part II
• Processing Distributed Data Using MapReduce
• NoSQL: Overview and a First Look at MongoDB
• NoSQL II: MongoDB Queries
• Distributed Atomicity and Group Commit