Database Management Systems

CS25C13:

Database Management Systems

Course objectives: This course provides a comprehensive foundation in database system concepts, including architecture, data modeling, and query languages. The course discusses the concepts of transaction management, concurrency control and query optimization.

DBMS and Data Modeling: Purpose- architecture and types of DBMS-Data models: traditional, hierarchical, network, relational, object-oriented-DBMS architecture-Database users and languages (DDL, DML, DCL) – basic SQL queries

Practicals

• Installation and configuration of open-source DBMS

• Creation of databases and SQL operations

Data Modeling and Relational Design: Entity Types, Entity Sets-Attributes-Keys: Candidate, Primary, Foreign-Relationship Types & Roles - Constraints: Participation, Degree-Weak Entities & Keys - Converting ER Diagrams to Relations-Handling M:N relationships, ISA hierarchies-EER Extensions: Specialization/Generalization

Practicals

• Creation of ER Model for a real world scenario using Draw.io and Lucidchart

• Convertion of ER Diagram to Relational Schema

SQL Commands : Primary key, foreign key, unique, not null, check, IN operator, Functions - aggregate functions, Built-in functions –numeric, date, string functions, set operations, sub-queries, correlated sub-Queries: Use of group by, having, order by,join and its types, Exist, Any, All, view and its types. Transaction control commands

Practicals

• Creation of sample database and perform CRUD operations

• Combining data from multiple tables and perform complex data retrieval.

Database Design and Normalization : Functional dependencies-Normal forms: 1NF,2NF,3NF,4NF, BCNF-Decomposition, lossless joins-Impact of normalization on schema.

Practicals

• Identification of functional dependency, entities and attributes

• Implementation of the schema in SQL tool and populate with sample data.

Transactions, Concurrency and Recovery : ACID Properties-Transaction States (active, committed, aborted)-System and User Transactions - Serializability-Locking Protocols (2PL), Timestamp Ordering, Deadlocks-Isolation Levels - Log-Based Recovery: Write-Ahead Logging-Checkpoints-ARIES-Shadow Paging.

Practicals

• ACID properties (e.g., missing commit, unhandled rollback).

• Simulation of transaction in SQL

Query Processing and Indexing : Parsing, Validation, Query Trees-Query Execution Plans - Cost Estimation-Physical Plan Strategies-Heuristics and Cost-Based Optimization - Disk Storage, RAID-File Organization- B+-Tree Indexes-Hash-Based Indexing: Static & Dynamic-NoSQL

Practicals

• Query Optimization Simulation Using Query Trees

• Analyze and compare execution plans in MySQL/PostgreSQL

Assessment Weightage:

Weightage:

Continuous Assessment: 50%

 (i) . Activities: 10%

(ii) . Internal Theory Examinations: 30%

(iii) .Internal Laboratory Examination: 10%

EndSemesterTheory Examination: 50%

Mandated Activities with marks:

Assignments (30), Quiz (10), Project based learning (25), Flipped Classroom (10), Review of GATE questions (25).

Internal Examinations: TWO tests

References:

1. Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts, McGraw-Hill Education, 2021.

2. Carlos Coronel, Steven Morris, Database Systems: Design, Implementation, and Management, Cengage Learning, 2024.

3. RamezElmasri, Shamkant B. Navathe, Fundamentals of Database Systems, Pearson Education, 2015.