Database Management Systems
Relational models, SQL, transactions and the internals of how a database engine actually works.
§ 1Calendar
- Quiz 1—
- Quiz 2—
- End-term examination—
§ 2Previous Year Questions
-
DBMS weekwise PYQs
Past questions sorted by week.
discourse.onlinedegree.iitm.ac.inby Preethy ParthasarathyDiscourse login required
§ 3Resources
Course material2
-
Supplementary content
Extra material shared on Google Drive.
drive.google.comIITM login required -
Course YouTube channel
All lecture videos.
youtube.com
Notes3
-
DBMS notes
Written notes for the Diploma in Programming DBMS course.
medium.comby Pratham Bhalla -
Course notes
Notes and study material for this course.
iitmbsdegree.inby IITM BS Students' Corner -
Topic-wise notes
Topic-wise notes on ML Study Hub.
degreedost.comby ML Study Hub
Extra SQL practice5
-
W3Schools SQL exercises
Short exercises for the basics.
w3schools.com -
LeetCode Top SQL 50
A 50-problem SQL study plan.
leetcode.com -
HackerRank SQL
Graded SQL problems.
hackerrank.com -
CodeChef SQL case studies
Topic-wise case studies.
codechef.com -
SQL Practice
Practice queries in the browser.
sql-practice.com
Syllabus
The official course syllabus, transcribed from the IITM BS Data Science & Applications course booklet.
§ 1Course Description
This course provides a comprehensive introduction to databases and PostgreSQL, covering database management and design, along with key topics such as security, integrity, and concurrency.
Lectures-Tutorials-Practicals-Credits: 4-0-0-4
§ 2Faculty
- Prof. Partha Pratim Das, Department of Computer Science and Engineering, IIT Kharagpur
§ 3Week-by-week
Week 1: Introduction to DBMS; Motivation and need for DBMS.
Week 2: Relational model; Introduction to SQL.
Week 3: SQL examples; Intermediate SQL; Advanced SQL.
Week 4: Formal relational query languages; Entity - Relationship (ER) model and database design.
Week 5: Database design - functional dependencies, keys, closure, canonical cover; Lossless join and dependency preservation.
Week 6: Normal forms; Multivalued dependencies and 4NF; Database design case studies; Temporal data; Problem solving on normalization.
Week 7: Application architecture; Web applications; SQL integration with programming languages; Python with PostgreSQL.
Week 8: Algorithms and complexity; Data structures; Storage and file structures.
Week 9: Indexing techniques; Hashing; Index design; B-trees and B+ trees.
Week 10: Transactions - serializability, recoverability; Concurrency control.
Week 11: Backup techniques; Recovery mechanisms; RAID and fault tolerance.
Week 12: Query processing and optimization; RDBMS architecture; NoSQL; DBMS overview and summary.
OPPE
Practice material for the Database Management Systems online proctored programming exam.
§ 1Practice and past questions
Practice platform1
-
oppe.dev
Curated DBMS OPPE problems with a coding environment.
oppe.devby Ishan Das Sharma
Question banks1
-
DBMS OPE PYQ resources
Community thread collecting OPE questions. Not official.
discourse.onlinedegree.iitm.ac.inDiscourse login required
SQL questions2
-
OPE SQL questions
github.comby mynkpdr
-
OPE SQL questions
github.comby nelsondsouza
Python and PostgreSQL questions3
-
OPE Python-PostgreSQL questions
Solve any one.
github.comby mynkpdr -
OPE Python-PostgreSQL questions
github.comby nelsondsouza
-
OPE Python-PostgreSQL questions
github.comby NebulaTris
Live sessions
Topics from past live sessions, each linked to the exact moment in the video.
§ 1May 2022 term
Weeks 5 and 6 summary session9
- 3:16Lossless join decomposition with examples
- 30:28Dependency preserving with examples
- 46:06Doubt on dependency preserving and lossless join
- 47:47Closure set of FDs
- 51:11Candidate key and primary key
- 55:17Use of closure set of FDs
- 1:03:36Extraneous attribute with examples
- 1:23:25Equivalence of sets of FDs with example
- 1:34:16Canonical cover with examples
Quiz 2 revision session14
- 5:00Week 6 practice assignment Q9 (BCNF concept)
- 40:40Mock 2 Q8: functional dependency with example
- 49:03Mock 2 Q7: how to find a candidate key
- 52:46Mock 2 Q4: lossless join decomposition
- 1:09:21Mock 2 Q9: dependency preserving with example
- 1:21:00Mock 2 Q11: canonical cover with example
- 1:58:18BCNF with examplesImportant
- 2:23:45Mock 2 Q16: multivalued dependency example
- 2:33:59Week 6 content discussion
- 2:35:01Week 7 content discussion
- 2:38:20Mock 2 Q14: BST example
- 2:44:08Mock 2 Q15
- 2:50:44Mock 2 Q13
- 2:54:45Week 8 overview
Week 9 session6
Week 10 activity session10
- 7:00ACID propertiesto 15:32
- 15:33Transaction statesto 19:26
- 19:27Concurrent executionto 21:00
- 21:00Scheduleto 36:25
- 36:25Types of scheduleto 40:00
- 40:00Serializabilityto 45:00
- 45:00Conflict serializabilityto 1:04:00
- 1:04:00Problem on conflict serializabilityto 1:23:00
- 1:23:00View serializabilityto 1:44:00
- 1:45:00Practice problem 4to 2:06:00
Week 10 open session12
- 4:50Locking protocolto 5:36
- 5:36Shared lockto 6:22
- 6:22Exclusive lockto 7:00
- 7:00Locking matrixto 9:30
- 9:30Example of locking protocolto 13:15
- 13:15Example of a locking protocol that is not conflict serializableto 17:08
- 17:08Two-phase locking protocolto 44:10
- 44:10Strict two-phase locking protocolto 50:10
- 50:10Rigorous two-phase locking protocolto 56:00
- 56:00Problem on 2PL (practice assignment Q7)to 1:11:00
- 1:11:00Timestamp protocolto 1:43:00
- 1:43:00Thomas write ruleto 1:48:30
Week 11 activity session8
- 0:26View serializability with examplesto 47:51
- 47:51Week 10 doubtsto 58:23
- 58:23Backup and recoveryto 1:01:47
- 1:01:47Backup strategies with examplesto 1:30:11
- 1:30:11Log-based recoveryto 1:51:43
- 1:51:43Immediate database modification examplesto 1:58:25
- 1:58:25State of transaction examplesto 2:02:03
- 2:02:03Doubts on backup and recoveryto 2:22:03
§ 2Jan 2022 term
Older sessions. Watch these if the ones above are not clear. Please watch all the tutorials for weeks 5 and 6, and use the tutorials for the ER diagram part.