Database Architecture Course

This is a course for Enterprise/Web Software Developers. It goes into details of Relational Databases (RDBMS):

• What they are good for
• How to design DB schemas
• How to properly access DB from our application code

Programme

Types of databases

• Log-structured Merge Trees (LSM)
• Heap files and Indexes
• Document-oriented
• Relational
• Graph-oriented
• OLTP vs OLAP
• Replication

Database Index

• B+ Tree, Hash Tables, Bitmaps
• Primary & Secondary index
• Composite Index
• Performance of Index vs Sequential Table Scan
• Analyzing Query Plan
• Foreign Keys, Unique Constraints
• Clustered Index, Covering Index

Joins

• Nested Loops
• Merge Join
• Hash Join
• Analyzing Query Plan

Concurrency

• Pessimistic Locking
• skip locked and background jobs
• Optimistic Locking
• Materializing locks
• Advisory locks

Transactions

• Durability & Write-ahead logging
• Data Anomalies: Dirty Write, Dirty Read, Non-repeatable Read, Phantoms, Lost Update, Write Skew
• Concurrency with locking. Locking, 2 Phase Locking (2PL); isolation: Read Committed, Repeatable Read, Serializable
• Concurrency with MVCC: MVCC, MVCC2PL; isolation: Read Committed, Snapshot, Snapshot Serializable (SSI)
• Hot backups
• Redo and Undo logs
• Dead Locks
• Index, MVCC, Visibility Maps
• Index vs Isolation vs Unique Constraints

Application code

• Evolving schema with migrations: (blocking) pre-deployment migration vs. background jobs
• DB Connection Pools and Thread Pools
• Bloom filter
• Hibernate (optional)