Database Fundamentals

What you'll learn

• Explain why DBMSs exist, the three-schema architecture and the main data models.
• Design conceptual models with ER diagrams and map them correctly to relational tables.
• Work with keys, integrity constraints, relational algebra and core SQL queries.
• Use functional dependencies to find keys and normalise relations to 3NF and BCNF.
• Explain transactions, ACID, serializability, isolation levels, locking, MVCC and crash recovery.
• Describe storage, B+ tree indexing, query processing, NoSQL trade-offs and distributed databases.

Syllabus

What a Database System Is

1. Why Databases Instead of Files
2. Three-Schema Architecture and Data Independence
3. Data Models

Entity-Relationship Modelling

1. Entities, Attributes and Relationships
2. Weak Entities, Specialisation and Aggregation
3. Mapping ER Diagrams to Tables

The Relational Model and Relational Algebra

1. Relations, Keys and Integrity Constraints
2. Relational Algebra
3. SQL Essentials for Fundamentals

Functional Dependencies and Normalisation

1. Functional Dependencies, Closures and Keys
2. Normal Forms: 1NF, 2NF, 3NF and BCNF
3. Lossless Decomposition, Dependency Preservation and Denormalisation

Transactions and Isolation

1. Transactions and ACID
2. Schedules and Serializability
3. Anomalies and Isolation Levels

Concurrency Control and Recovery

1. Locking, Two-Phase Locking and Deadlocks
2. Timestamps, Optimistic Control and MVCC
3. Logging, Checkpoints and Crash Recovery

Storage, Indexing and Query Processing

1. Pages, Files and the Buffer Pool
2. Indexes: B+ Trees and Hashing
3. Query Processing, Join Algorithms and EXPLAIN

Beyond a Single Relational Database and Revision

1. NoSQL, CAP and BASE
2. Replication, Partitioning and Distributed Transactions
3. Practical Database Design Checklist
4. Revision and Exam Questions