RDBMS and SQL
Sep–Nov 2023
RDBMS and SQL
Sep–Nov 2023
Administrative details
-
Instructor: Madhavan Mukund
-
Teaching Assistants: None
-
Evaluation:
-
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
Lectures
-
Lecture 1: 8 Sep 2023
(Class Notes)
- Why DBMS? Overview of concepts to be discussed in the course
- The relational model — mathematical relations, relations an
d tables, schema
- Reference: Silberschatz et al, Ch 1.1, 1.2, 1.6, 2.1, 2.2
Supplementary reading
-
Lecture 2: 12 Sep 2023
(Class Notes)
- The relational model — keys, referential integrity, foreign keys, schema diagram
- Relational algebra — select, project, join
- Reference: Silberschatz et al, Ch 2.3, 2.4, 2.5, 2.6
-
Lecture 3: 22 Sep 2023
(Class Notes)
- Relational algebra – select, project, join, set operations, assignment, renaming
- Sets vs lists – duplicates and ordering
- Query optimization, declarative programming
- Reference: Silberschatz et al, Ch 2.6
-
Lecture 3: 22 Sep 2023
(Class Notes)
- Relational algebra – select, project, join, set operations, assignment, renaming
- Sets vs lists – duplicates and ordering
- Query optimization, declarative programming
- Reference: Silberschatz et al, Ch 2.6
-
Lecture 4: 13 Oct 2023
(Class Notes (pdf))
- Introduction to SQL – table creation, updating tables, select command
- Reference: Silberschatz et al, Ch 3.1, 3.2, 3.3 (part)
-
Lecture 5: 20 Oct 2023
(Class Notes (pdf))
- Introduction to SQL – cartesian product, join, renaming and self-join, set operations, string comparison, ordering output, aggregate queries, nested output
- Reference: Silberschatz et al, Ch 3.3, 3.4, 3.5, 3.7 (part)
-
Lecture 6: 27 Oct 2023
(Class Notes (pdf))
- Introduction to SQL – aggregate operations, grouping, joins (natural, outer, inner), null values, set membership and comparison, testing for empty relations and duplicates, views
- Reference: Silberschatz et al, Ch 3.6, 3.7, 3.8, 4.1, 4.2
-
Lecture 7: 3 Nov 2023
(Class Notes (pdf))
- Introduction to SQL – constraints, cascading deletes/updates, built-in datatypes, interfacing with SQL
- Reference: Silberschatz et al, Ch 4.4
- Relational database design – redundancy, lossless decomposition, functional dependences, normalization, BCNF, 3NF, closure
- Reference: Silberschatz et al, Ch 7.1, 7.2, 7.3, 7.4.1, 7.4.2
-
Lecture 8: 10 Nov 2023
(Class Notes (pdf))
- 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, multilevel indices, B+-trees
- Reference: Silberschatz et al, Ch 14.1-14.3
-
Lecture 9: 17 Nov 2023
(Class Notes (pdf))
- 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
- Query optimization: transforming relational algebra expressions, estimating outputs, choosing between evaluation plans
- Reference: Silberschatz et al, Ch 16.1-16.4
-
Lecture 10: 24 Nov 2023
(Class Notes (pdf))
- Transactions: ACID properties, transaction state diagram, logs, concurrent schedules, serializability, conflict serializability, testing for conflict serializability, recoverable schedules, cascading rollbacks, transactions in SQL
- Reference: Silberschatz et al, Ch 17.1-17.8