DBMS: Database Management Systems

Topics covered

• Course overview

  • What a DBMS is
  • Databases versus file systems
  • Data models
  • Three-schema architecture

Expected skills: Explain the purpose of a DBMS and compare data models and database architectures.

Topics covered

• Relational model & basic SQL

  • Relations and relational data
  • Keys
  • DDL: CREATE and constraints
  • SELECT, INSERT and WHERE
  • ORDER BY

Expected skills: Create constrained tables and write basic SQL queries to retrieve, insert and order data.

Topics covered

• Intermediate & advanced SQL

  • Inner, outer and self joins
  • GROUP BY and HAVING
  • Aggregate functions
  • Subqueries
  • Set operations
  • Views

Expected skills: Combine tables and write grouped, nested and set-based SQL queries.

Topics covered

• Query languages & database design

  • Relational algebra
  • Entity–relationship model
  • ER-to-relational mapping
  • Cardinality

Expected skills: Express queries using relational algebra and convert ER designs into relational tables.

Topics covered

• Functional dependencies & normal forms

  • Functional dependencies
  • Attribute closure
  • Candidate keys
  • Decomposition
  • First and second normal forms: 1NF and 2NF

Expected skills: Use dependencies and closure to identify keys and analyse normalization.

Topics covered

• Functional dependencies & normal forms (continued)

  • Functional dependencies and keys
  • Third normal form: 3NF
  • Boyce–Codd normal form: BCNF
  • Decomposition
  • Lossless joins

Expected skills: Evaluate 3NF and BCNF and reason about lossless decomposition.

Topics covered

• Application development

  • Python and PostgreSQL with psycopg2
  • Database connections
  • Parameterized queries
  • Transactions

Expected skills: Connect Python to PostgreSQL and execute parameterized queries within transactions.

Topics covered

• Storage management

  • File organization
  • Records and blocks
  • Buffer management

Expected skills: Explain how database records are organized, stored and buffered.

Topics covered

• Indexing & hashing

  • Ordered indices
  • B-trees and B+-trees
  • Static hashing
  • Dynamic hashing

Expected skills: Compare indexing and hashing approaches for finding stored records.

Topics covered

• Transactions

  • ACID properties
  • Schedules
  • Serializability
  • Concurrency control
  • Locking
  • Deadlocks

Expected skills: Analyse transaction schedules and explain how locking controls concurrent operations.

Topics covered

• Backup & recovery

  • Types of failures
  • Log-based recovery
  • Checkpoints

Expected skills: Explain recovery from failures using logs and checkpoints.

Topics covered

• Query optimization

  • Query processing
  • Evaluation plans
  • Cost estimation

Expected skills: Compare query evaluation plans using their estimated costs.