Madhavan Mukund



RDBMS and SQL

Sep–Nov 2022


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:


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


Lectures

  • Lecture 1: 15 Sep 2022
    (Class Notes, Video)

    • The relational model for data
    • Relational algebra: select, project, cross product, renaming

    Supplementary reading

  • Lecture 2: 22 Sep 2022
    (Class Notes, Video)

    • Review of relational algebra
    • Table design: an example
    • Joins
    • Keys

    Supplementary reading

    • Silberschatz et al, Chapter 2 slides ( PPT, PDF)
  • Lecture 3: 29 Sep 2022
    (Class Notes, SQL demo (Annotated session), Library database (MySQL dump, csv files) , Video)

    • Set difference in relational algebra
    • Introduction to SQL
      • Creating and displaying metadata about databases and tables
      • Basic examples of SELECT, NATURAL JOIN, renaming

    Supplementary reading

    • Silberschatz et al, Chapter 3 slides ( PPT, PDF)
  • Lecture 4: 6 Oct 2022
    (Video)

    • SQL data definition: creating tables, datatypes, integrity constraints, inserting and deleting rows, modifying tables
    • SQL queries: select-from-where, renaming columns and tables, string comparison, sorting output, removing duplicates, set-theoretic operations on tables, null values, aggregate functions and grouping

    Supplementary reading

    • Silberschatz et al, Chapter 3 slides ( PPT, PDF)
  • Lecture 5: 13 Oct 2022
    (Lecture slides, SQL Demo, Video)

    • SQL: Nested queries, updating tables, inner and outer joins, views, integrity constraints

    Supplementary reading

    • Silberschatz et al, Chapter 3 slides ( PPT, PDF)
    • Silberschatz et al, Chapter 4 slides ( PPT, PDF)
  • Lecture 6: 20 Oct 2022
    (Class Notes, Lecture slides, Video)

    • Database storage: disk storage, blocks, fixed and variable length records, sequential file organisation, data dictionary, buffer management, simple hashing

    Supplementary reading

    • Silberschatz et al, Chapter 10 slides ( PPT, PDF)
    • Silberschatz et al, Chapter 11 slides ( PPT, PDF)
  • Lecture 7: 28 Oct 2022
    (Class Notes, Video (sorry, no audio due to some glitch))

    • Maintaining an index: B+-trees, external merge sort, overview of query planning

    Supplementary reading

    • Silberschatz et al, Chapter 11 slides ( PPT, PDF)
    • Silberschatz et al, Chapter 12 slides ( PPT, PDF)
    • Silberschatz et al, Chapter 13 slides ( PPT, PDF)
  • Lecture 8: 3 Nov 2022
    (Class Notes, Video)

    • Measuring query cost, algorithms for join, query planning

    Supplementary reading

    • Silberschatz et al, Chapter 12 slides ( PPT, PDF)
    • Silberschatz et al, Chapter 13 slides ( PPT, PDF)
  • Lecture 9: 17 Nov 2022
    (Class Notes, Video)

    • Table design, functional dependencies and normal forms, transactions, serializability

    Supplementary reading

    • Silberschatz et al, Chapter 8 slides ( PPT, PDF)
    • Silberschatz et al, Chapter 14 slides ( PPT, PDF)