Database Management Systems (DBMS)

What a database system is, why it replaced file systems, and how it is organised.

• Data, database and DBMS
• File system versus DBMS
• Data abstraction: physical, logical and view levels
• Schemas and instances
• Data independence
• Three-schema architecture
• Database users and the DBA
• Types of keys: super, candidate, primary, alternate, foreign

• DBMS vs file system and types of keys

Modelling a real-world problem before building tables.

• Entities, attributes and relationships
• Simple, composite, multivalued and derived attributes
• Cardinality: one-to-one, one-to-many, many-to-many
• Participation: total and partial
• Weak entities
• Specialisation, generalisation and aggregation
• Converting an ER diagram to tables

• ER diagrams

The formal basis of relational databases and its query language.

• Relations, tuples, attributes and domains
• Integrity constraints: domain, entity and referential
• Selection, projection and renaming
• Union, intersection and set difference
• Cartesian product and joins
• Natural, theta and outer joins
• Division
• Relational calculus overview

Covered in 1:1 mentoring sessions

Defining, changing and querying data with the standard database language.

• DDL: CREATE, ALTER and DROP
• DML: INSERT, UPDATE and DELETE
• SELECT with WHERE, ORDER BY and DISTINCT
• Aggregate functions, GROUP BY and HAVING
• Joins and set operations
• Nested queries and correlated subqueries
• Views
• Constraints, triggers and stored procedures overview
• NULL handling

• Classic SQL interview queries
• INNER JOIN step by step
• GROUP BY one and several columns
• Subqueries in WHERE

Designing tables that avoid redundancy and anomalies.

• Update, insert and delete anomalies
• Functional dependencies and Armstrong's axioms
• Attribute closure and finding candidate keys
• Canonical cover
• 1NF and 2NF
• 3NF and BCNF
• Lossless-join and dependency-preserving decomposition
• Multivalued dependencies and 4NF

• Normalisation: functional dependencies, 1NF and 2NF
• Normalisation: 3NF and BCNF

Making groups of operations reliable.

• What a transaction is
• ACID properties
• Transaction states
• Schedules: serial and concurrent
• Conflict serializability and precedence graphs
• View serializability
• Recoverable and cascadeless schedules

• ACID properties, transactions and concurrency
• Dirty, non-repeatable and phantom reads

How a DBMS lets many users work at once without corrupting data.

• Lost update, dirty read, non-repeatable read and phantom
• Isolation levels
• Lock types: shared and exclusive
• Two-phase locking and its variants
• Deadlocks: prevention, detection and recovery
• Timestamp ordering
• Optimistic concurrency and multiversion concurrency control

• Isolation levels in MySQL
• Deadlocks: what they are and how to avoid them

Getting the database back to a correct state after a failure.

• Types of failures
• Log-based recovery
• Write-ahead logging
• Deferred and immediate update
• Checkpoints
• Undo and redo
• The ARIES idea
• Backups and restore

• Backups with mysqldump and restoring

How data is stored on disk and found quickly.

• Records, blocks and file organisation
• Heap, sorted and hashed files
• Primary, clustered and secondary indexes
• Dense and sparse indexes
• Multilevel indexing
• B-trees and B+ trees
• Static and dynamic hashing
• Cost of indexes on writes

• Indexing basics
• How a B-tree index works

How the database decides the fastest way to run a query.

• Parsing and translating a query
• Query trees and equivalence rules
• Selection and join algorithms
• Nested-loop, sort-merge and hash joins
• Cost-based optimisation
• Reading an execution plan

• Reading EXPLAIN

Where non-relational databases fit and the trade-offs they make.

• Why NoSQL appeared
• Key-value, document, column-family and graph stores
• The CAP theorem
• BASE versus ACID
• Sharding and replication
• Choosing SQL or NoSQL for a project

• SQL vs NoSQL at a high level