IIRF Online > Development > Database Design & Development > Database Management Systems (DBMS) > Fundamentals of Database Engineering
Fundamentals of Database Engineering by Udemy
Learn ACID, Indexing, Partitioning, Sharding, Concurrency control, Replication, DB Engines, Best Practices and More!
Course Highlights
- Learn and understand ACID Properties
- Database Indexing
- Database Partitioning
- Database Replication
- Database Sharding
- Database Cursors
- Concurrency Control (Optimistic, Pessimistic)
- B-Trees in Production Database Systems
- Database System Designs
- Difference between Database Management System, Database Engine and Embedded database
- Database Engines such as MyISAM, InnoDB, RocksDB, LevelDB and More
- Benefits of Using one database engine over the other
- Switching Database Engines with MySQL
- Database Security
- Homomorphic Encryption
Skills you will learn!
Curriculum
6 Topics
Welcome to the Course
Course Note 1
Course Note 2
Course Note 3
Note about Docker
Slides
11 Topics
Introduction to ACID
What is a Transaction?
Atomicity
Isolation
Consistency
Durability
ACID by Practical Examples
Phantom Reads
Serializable vs Repeatable Read
Eventual Consistency
ACID Quiz
4 Topics
How tables and indexes are stored on disk (MUST WATCH before continue)
Row-Based vs Column-Based Databases
Primary Key vs Secondary Key - What you probably didn't know
Databases Pages (Article)
14 Topics
Create Postgres Table with a million Rows (from scratch)
Getting Started with Indexing
Understanding The SQL Query Planner and Optimizer with Explain
Bitmap Index Scan vs Index Scan vs Table Scan
Key vs Non-Key Column Database Indexing
Index Scan vs Index Only Scan
Combining Database Indexes for Better Performance
How Database Optimizers Decide to Use Indexes
Create Index Concurrently - Avoid Blocking Production Database Writes
Bloom Filters
Working with Billion-Row Table
Article - The Cost of Long running Transactions
Article - Microsoft SQL Server Clustered Index Design
Indexing Quiz
9 Topics
B-Tree Section's Introduction & Agenda
Full Table Scans
Original B-Tree
How the Original B-Tree Helps Performance
Original B-Tree Limitations
B+Tree
B+Tree DBMS Considerations
B+Tree Storage Cost in MySQL vs Postgres
B-Tree Section's Summary
14 Topics
Introduction to Database Partitioning
What is Partitioning?
Vertical vs Horizontal Partitioning
Partitioning Types
The Difference Between Partitioning and Sharding
Preparing: Postgres Database Table Indexes
Execute Multiple Queries on the Table
Create and Attach Partitioned Tables
Populate the Partitions and Create Indexes
Class Project - Querying and Checking the Size of Partitions
The Advantages of Partitioning
The Disadvantages of Partitioning
Section Summary - Partitioning
How to Automate Partitioning in Postgres
12 Topics
Introduction to Database Sharding
What is Database Sharding?
Consistent Hashing
Horizontal partitioning vs Sharding
Sharding with Postgres
Spin up Docker Postgres Shards
Writing to a Shard
Reading from a Shard
Advantages of Database Sharding
Disadvantages of Database Sharding
Database Sharding Section Summary
When Should you consider Sharding your Database?
7 Topics
Shared vs Exclusive Locks
Dead Locks
Two-phase Locking
Solving the Double Booking Problem (Code Example)
Double Booking Problem Part 2 ( Alternative Solution and explination)
SQL Pagination With Offset is Very Slow
Database Connection Pooling
6 Topics
Introduction to Database Replication
Master/Standby Replication
Multi-master Replication
Synchronous vs Asynchronous Replication
Replication Demo with Postgres 13
Pros and Cons of Replication
2 Topics
Twitter System Design Database Design
Building a Short URL System Database Backend
12 Topics
Introduction
What is a Database Engine?
MyISAM
InnoDB
XtraDB
SQLite
Aria
BerkeleyDB
LevelDB
RocksDB
Popular Database Engines
Switching Database Engines with mySQL
7 Topics
What are Database Cursors?
Server Side vs Client Side Database Cursors
Inserting Million Rows with Python in Postgres using Client Side Cursor
Querying with Client Side Cursor
Querying with Server Side Cursor
Pros and Cons of Server vs Client Side Cursors
Article - Server Side Cursor Types in SQL Server
3 Topics
MongoDB Architecture
MongoDB Internals
MemCached In-Memory database Architecture
6 Topics
How to Secure Your Postgres Database by Enabling TLS/SSL
Deep Look into Postgres Wire Protocol with Wireshark
Deep Look Into MongoDB Wire Protocol with Wireshark
What is the Largest SQL Statement that You can Send to Your Database
Best Practices Working with REST & Databases
Database Permissions and Best Practices for Building REST API
9 Topics
Introduction to Homomorphic Encryption
What is Encryption?
Why Can't we always Encrypt?
What is Homomorphic Encryption
Homomorphic Encryption Demo
Clone and Build the Code
Going Through the Code and the Database
Searching The Encrypted Database
Is Homomorphic Encryption Ready?
16 Topics
Heap Index scan instead of Index only scan why?
What is the unit of the Cost in Postgres Planner?
All Isolation Levels - Explained Details
Snapshot and Repeatable Read Isolation difference?
I have an Index why is the database doing a full table scan?
Why Databases Read Pages instead of Rows?
How does Indexing a column with duplicate values work?
Should I drop unused indexes?
Why use serializable Isolation Level when we have SELECT FOR UPDATE?
Can I use the same database connection for multiple clients?
Do I need a transaction if I'm only reading?
Why does an update in Postgres touches all indexes?
What is the value of bitmap index scan?
What does Explain Analyze actually do?
Postgres vs MySQL (The fundamental differences)
Does Create Index block writes and Why?
13 Topics
WAL Redo and Undo logs
SELECT COUNT (*) can impact your Backend Application performance here is why
How Shopify Switched from UUID as Primary Key
How does the Database Store Data On Disk?
Is QUIC a Good Protocol for Databases?
What is a Distributed Transaction?
Hash Tables and Consistent Hashing
Indexing in PostgreSQL vs MySQL
Why Uber Moved from Postgres to MySQL (Discussion)
Can NULLs Improve your Database Queries Performance?
Write Amplification Explained in Backend Apps Database Systems and SSDs
Optimistic vs Pessmistic Concurrency Control
Postgres Locks - A Deep Dive
6 Topics
Introduction to ACID (Archived)
What is a Transaction? (Archived)
Atomicity (Archived)
Isolation (Archived)
Consistency (Archived)
Durability (Archived)
Fundamentals of Database Engineering
Related Database Management Systems (DBMS) Courses
Thank You!
Your review has been submitted successfully.