Madhavan Mukund

RDBMS and SQL

Aug–Nov 2025

Administrative details

• Instructor: Madhavan Mukund

• Teaching Assistants: None

• Evaluation:

  • Assignments, final exam

  • Weightage approximately 60%, 40%, to be confirmed

  • Copying is fatal

• Text and reference books:

  • Database System Concepts (7th Edition), Avi Silberschatz, Henry F. Korth, S. Sudarshan

Course plan

This list is approximate and subject to change.

• The relational model
  • History
  • Relational algebra
• SQL
  • Basic queries
  • Creating and updating tables
  • Aggregate queries
• Storing tables on disk
• Query processing
• Transactions and consistency

Assignments

• Assignment 1, 14 September 2025, due 22 September 2025

• Assignment 2, 2 October 2025, due 9 October 12 October 2025

• Assignment 3, 25 October 2025, due 3 November 2025

• Assignment 4, 21 November 2025, due 30 November 2025

Lectures

• Lecture 1: 14 Aug 2025
  (Class Notes)

  • Why DBMS? Overview of concepts to be discussed in the course
    • Reference: Silberschatz et al, Ch 1.1, 1.2

  Supplementary reading

  • A Relational Model of Data for Large Shared Data Banks, E.F. Codd, CACM, Jun 1970
  • A Brief History of Databases

• Lecture 2: 21 Aug 2025
  (Class Notes)

  • The relational model — mathematical relations, relations and tables, schema
  • Relational algebra: select, project, join
    • Reference: Silberschatz et al, Ch 2.1, 2.2, 2.6

• Lecture 3: 28 Aug 2025
  (Class Notes)

  • Relational algebra: natural join, set operations, renaming
    • Reference: Silberschatz et al, Ch 2.6

• Lecture 4: 04 Sep 2025
  (Class Notes)

  • Relational algebra: examples, expressiveness
    • Reference: Silberschatz et al, Ch 2.6

• Lecture 5: 18 Sep 2025
  (Class Notes, SQL demo )

  • Relation schema — keys, referential integrity, foreign keys, schema diagram
    • Reference: Silberschatz et al, Ch 2.2, 2.3, 2.4
  • Querying data in SQL – select statement
    • Reference: Silberschatz et al, Ch 3.3

• Lecture 6: 25 Sep 2025
  (Class Notes, SQL demo )

  • Querying data in SQL – select, join, set operations, renaming, nested queries, sorting output, aggregate values, grouping, string matching
    • Reference: Silberschatz et al, Ch 3.3, 3.4, 3.5, 3.7, 3.8

• Lecture 7: 16 Oct 2025
  (Class Notes, SQL demo )

  • Data definition in SQL – creating and dropping tables, databases
    • Reference: Silberschatz et al, Ch 3.2
  • Data manipulation in SQL – inserting, deleting, updating rows in tables
    • Reference: Silberschatz et al, Ch 3.9
  • Querying data in SQL – set comparisons, null values, natural join, outer join, views
    • Reference: Silberschatz et al, Ch 3.5, 3.6, 4.1, 4.2

• Lecture 8: 23 Oct 2025
  (Class Notes)

  • Relational database design: redundancy, lossless decomposition, functional dependencies, closure, normalization, BCNF, 3NF
    • Reference: Silberschatz et al, Ch 7.1, 7.2, 7.3, 7.4.1, 7.4.2

• Lecture 9: 30 Oct 2025
  (Class Notes, Lecture recording)

  • Storage – RAM, disk, SSD; fixed and variable length records; heap and sequential organization of files
    • Reference: Silberschatz et al, Ch 12.1-12.4, 13.1-13.3
  • Indexing: motivation, clustering index, dense and sparse indices, secondary indices, B+-trees
    • Reference: Silberschatz et al, Ch 14.1-14.3

• Lecture 10: 06 Nov 2025
  (Class Notes)

  • Query processing: query plans, assessing and optimizing cost, algorithms for selection, external merge sort, computing joins
    • Reference: Silberschatz et al, Ch 15.1-15.6

• Lecture 11: 13 Nov 2025
  (Class Notes, Lecture recording)

  • Query optimization: transforming relational algebra expressions, estimating outputs, choosing between evaluation plans
    • Reference: Silberschatz et al, Ch 16.1-16.4
  • Transactions: ACID properties
    • Reference: Silberschatz et al, Ch 17.1-17.2

• Lecture 12: 20 Nov 2025
  (Class Notes)

  • Transactions: ACID properties, transaction state diagram, logs, concurrent schedules, serializability, conflict serializability, testing for conflict serializability, transactions in SQL, locks, two-phase locking, deadlocks
  • Reference: Silberschatz et al, Ch 17.1-17.6, 17.8, 18.1 (upto 18.1.3), 18.2