GateShala
Playground

Databases (DBMS)

Databases is a core GATE CS subject. These lessons go from the ER model and relational algebra to SQL, normal forms, transactions and B+ trees. Every SQL example runs on a real SQLite database when the site is built, so the result tables you see are exact, and the algorithms (attribute closure, candidate keys, serializability) come with small programs.

Start the first lesson

1Basics

  1. Introduction to DBMSWhat a database management system does, the three-schema architecture, data independence and the main database languages.
  2. The ER ModelEntities, attributes, relationships, cardinality and participation, weak entities, and how to turn an ER design into tables.

2Relational Model

  1. Relational Model and KeysRelations, tuples and domains; superkeys, candidate, primary and foreign keys; counting superkeys; and integrity constraints.
  2. Relational AlgebraSelection, projection, set operations, Cartesian product, joins, outer joins and division, with result sizes and a small Python implementation.
  3. Relational CalculusTuple relational calculus with ∃ and ∀, safe expressions, domain calculus, and how calculus compares with relational algebra.

3SQL

  1. SQL QueriesSELECT, WHERE, ORDER BY, aggregates, GROUP BY and HAVING, joins and set operations, with the order in which SQL evaluates a query.
  2. Subqueries and NULLsNULL and three-valued logic, nested and correlated subqueries, IN, EXISTS, ALL and ANY, the NOT IN trap, and views.

4Database Design

  1. Functional DependenciesFunctional dependencies, Armstrong's axioms, attribute closure, finding all candidate keys, equivalence of FD sets and the minimal cover.
  2. NormalizationUpdate anomalies, 1NF, 2NF, 3NF and BCNF, lossless-join and dependency-preserving decompositions, and multivalued dependencies with 4NF.

5Transactions

  1. Transactions and SerializabilityACID properties, transaction states, schedules, conflict and view serializability with precedence graphs, and recoverable, cascadeless and strict schedules.
  2. Concurrency Control and RecoveryShared and exclusive locks, two-phase locking and its strict variants, deadlock prevention with wait-die and wound-wait, timestamp ordering with the Thomas write rule, and log-based recovery.

6Storage and Indexing

  1. File Organization and IndexingBlocking factor and block accesses, primary, clustering and secondary indexes, dense and sparse indexes, multilevel indexes, B-trees and B+ trees, and hashing.

7GATE Practice

  1. DBMS GATE Practice SetMixed GATE-style questions on ER mapping, keys, relational algebra, SQL, functional dependencies, normal forms, transactions and indexing.