L T P S J C
24CSEN2031 DATABASE MANAGEMENT SYSTEMS
3 0 2 0 0 4
Pre-requisite 24CSEN2001 - Data Structures
Co-requisite 24CSEN2011 - Operating Systems
Preferable Exposure None
Course Description:
This course provides fundamental and practical knowledge on database concepts by means of organizing the information,
storing and retrieving the information in an efficient and a flexible way from a well-structured relational model. This course
ensures that every student will gain experience in creating data models and database design
Course Educational Objectives:
• Bring into focus the role of a database management system in an organization and construct ER Diagram
• Demonstrate basic database concepts, including the structure and operation of the relational data model and basic
database queries using SQL
• Introduce the use of advanced database queries using Structured Query Language (SQL)
• Teach the logical database design principles and database normalization
• Demonstrate the concept of a database transaction, concurrency control, and data object locking and protocols
MODULE 1 INTRODUCTION TO DBMS AND DATABASE DESIGN 12 Hrs
Introduction to DBMS: File system vs DBMS, advantages of DBMS, storage data, queries, DBMS structure, Types of Databases
– Hierarchical, Network, Relational, Key-Value, Object-oriented, XML DB.
Overview of File Structures in database.
E-R model: Entities, attributes and entity sets, relationship and relationship set, mapping cardinalities, keys, features of ER
model, conceptual database design with ER model.
MODULE 2 RELATIONAL MODEL AND BASIC SQL 12 Hrs
Relational model: Integrity constraints over relations and enforcement, querying relational data, logical database design, views,
destroying/altering tables and views.
Basic SQL: Introduction to SQL, Basic SQL Queries: DML, DDL, DCL, TCL
MODULE 3 ADVANCES SQL AND PL/SQL 12 Hrs
Structured Query Language (SQL): Select Commands, Union, Intersection, Except, Nested Queries, Aggregate Operators, Null
values, Relational set operators, SQL join operators
Relational Algebra(RA): Selection, Projection, Set operations, Joins
Relational Calculus (TRC, DRC): Tuple Relational Calculus, Domain Relational Calculus
PL/SQL, Assertions, Triggers
MODULE 4 SCHEMA REFINEMENT AND NORMAL FORMS 12 Hrs
Introduction to Schema Refinement, Functional Dependencies, Reasoning about Functional Dependencies. Normal Forms,
Properties of Decomposition, Normalization, different types of dependencies.
(c) - page 1 of 3
INTRODUCTION TO TRANSACTION MANAGEMENT, CONCURRENCY CONTROL
MODULE 5 12 Hrs
AND CRASH RECOVERY
Introduction to Transaction Management: ACID properties, Transactions and Schedules, Concurrent Execution of Transactions,
Lock-Based Concurrency Control.
Concurrency Control: 2PL, Serializability and Recoverability, Introduction to Lock Management, Lock Conversions, Dealing with
Deadlocks, Concurrency control without locking.
Crash Recovery: Aries, Recovering from a System Crash
List of Experiments
S.no Topic Type
1 Developing a sample ER model for the specified database. Experiment
Create a database and learn to set various constraints (can use Sailors example from
2 Experiment
textbook1, University example from textbook2).
3 Familiarization of SQL DDL commands-create, alter, drop, rename and truncate Experiment
4 Use of DML commands-select, insert, update and delete Experiment
5 Use of different operators for nested sub-queries. Experiment
6 Use of Joins Experiment
7 Use of grouping functions Experiment
8 Creating Views Experiment
9 PL/SQL programming environment Experiment
10 Declaring triggers and use of cursors. Experiment
11 Database connectivity from a program. Experiment
Textbook(s):
1. Raghu Ramakrishnan and Johannes Gehrke, Database Management Systems, 3rd Edition, McGraw-Hill, 2014
,978-8131769591
2. H.F.Korth and A.silberschatz, Database System Concepts, 7th Edition, McGraw-Hill, 2021 ,978-9390727506
Reference(s):
1. D. Ullman,, Principles of Database and Knowledge – Base Systems, Vol 1, 1st Edition, Computer Science Press, 1990
2. Ramez Elmasri, Shamkant B. Navathe, Fundamentals of Database Systems, PearsonEducation, 2016
Course Outcomes:
1. Understand database design principles (L2)
2. Apply data modeling using E-R diagrams (L4)
3. Create refined data models using normalization (L6)
4. Build database queries using Structured Query Language (L6)
5. Understand the transaction management and concurrency control (L2)
Course Articulation Matrix:
POs PSOs
CO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 1 2 3 4
1 3 1 2
2 3 2 2 2
3 2 2 1
4 3 2
(c) - page 2 of 3
5 3 2 2 2
3 – High, 2 – Medium & 1 – Low Correlation
APPROVED IN MEETINGS HELD ON:
BOS : 03-02-2024 Academic Council Number: 27 Academic Council : 06-07-2023
SDG No(s). & Statement(s) :
2 & Zero Hunger : End hunger, achieve food security and improved nutrition and promote sustainable agriculture.
3 & Good Health and Well-being : Ensure healthy lives and promote well-being for all at all ages.
9 & Industry, Innovation and Infrastructure : Build resilient infrastructure, promote inclusive and sustainable industrialization and
foster innovation.
11 & Sustainable Cities and Communities : Make cities and human settlements inclusive, safe, resilient and sustainable.
SDG Justification(s):
SDG 2: Zero Hunger: Database management can be applied to optimize food distribution networks, ensuring that resources are
allocated efficiently and minimizing waste. SDG 3: Good Health and Well-being: Database management plays a role in
healthcare optimization, from scheduling appointments to resource allocation in medical facilities, improving overall health
system efficiency. SDG 9: Industry, Innovation, and Infrastructure: Database management in logistics, transportation, and
infrastructure planning can contribute to sustainable and efficient development. SDG 11: Sustainable Cities and Communities:
Database management can aid in urban planning, traffic management, and resource allocation, contributing to the development
of sustainable and smart cities.
(c) - page 3 of 3
