Sir Padampat Singhania University, Udaipur

B.Tech. CSE
CSE2309 Data Base Management Systems L T P C Course Typ
Semester III 3 0 1 4 Core
Pre-requisites
Co-requisites NA
Anti- requisites NA
A. COURSE DESCRIPTION

The course provides a solid foundation and understanding on how huge data is managed by each
and every application is modern technologies and to store and retrieve data in efficient manner,
how query language is useful will be helpful. This course will give deep knowledge about data
storage and querying functionalities used in real life applications.

B. COURSE OBJECTIVES
 Design, query, and optimize relational databases using SQL and advanced techniques like
indexing and normalization.
 Identify and address real-world problems by applying secure, scalable, and efficient
database solutions, including distributed and NoSQL systems.
 Implement transaction management, concurrency control, and recovery techniques to
maintain data integrity and resolve conflicts.
 Develop and automate database operations using stored procedures, triggers, and
functions while meeting user requirements effectively.
C. COURSE OUTCOMES

Course Outcome KL

Understanding the basic concepts of relational data model, entity-

KL2
CO1 relationship model, relational database design, relational algebra,
normalization and SQL
Solving the given problem using Relational Algebra, Relational Calculus,
CO2 KL3
SQL and PL/SQL
CO3 Analyzing basic data storage schemes and real-life database applications KL4
Applying efficient query optimization techniques to solve different KL3
CO4
problems
Performing PL/SQL programming using concept of Error Handling, KL3
CO5
Package and Triggers

D. DETAIL SYLLABUS:
Module-1: Introduction to Database Management Systems, Types of DBMS,
Implementation of DBMS in storage and querying, Applications of DBMS, Architecture
 of Relational Database, Database Schema, Schema Diagram, Introduction and use of keys
in schema designing. Introduction to Structured Query Language (SQL), CRUD
Operations, and Functions.

Module-2: Design Process and Introduction to the E-R Model, Constraints and Attribute
Characteristics, Features of E-R Diagrams and Design Issues, Other notations for
modeling with different aspects of database design, Trivial and Non-trivial Dependencies,
Multilevel Dependencies, Algorithms for decomposition using Multilevel Dependencies.

Module-3: Purpose of Normalization, Introduction and Definition of Normalization,

Normalization Techniques: 1NF, 2NF, 3NF, BCNF, 4NF, Introduction and
Implementation of 5NF. Basic Concepts for B+ Tree Index Files, Multiple Key Access.
Querying: Overview, Measures of Query Cost, Selection Operation, Sorting, Join,
Evaluation of Expressions, Transformation of Relational Expressions, Estimating
Statistics of Expression Results, Evaluation Plans, Materialized Views. SQL Concepts:
SELECT, FROM, and WHERE Clause; Insert and DELETE Operations, Functions;
NULL Functions, Aggregators, Arithmetic and Logical Operators; Joins: Inner Joins,
Outer Joins, Cross Joins. PL/SQL: Cursors, Stored Procedures, Stored Functions,
Database Triggers.

Module-4: Introduction to Transactions using DBMS, ACID Properties (Atomicity,

Consistency, Isolation, Durability), Isolated Transactions, Use of Isolated Transaction
Applications using SQL, Locking Mechanism, Solution to Concurrency Related
Problems, Deadlock, Two-Phase Locking Protocol, Concurrency Handling Protocols and
Schemes. Transactional Recovery Algorithms, System Recovery, Two-Phase Commit
Protocol, Recovery and Log-based Recovery, Concurrent Executions of Transactions and
Related Problems.

Module-5: Introduction to Database Security, Discretionary Access Control, Mandatory

Access Control, Data Encryption. Introduction to Parallel and Distributed Databases,
NoSQL Databases, and Applications.

List of Practicals: -
S.NO. LIST OF EXPERIMENTS
1 Install and configure a DBMS such as MySQL, PostgreSQL, or Oracle.
2 Create a relational schema for a sample system like a library or online store.
3 Design and implement schema diagrams with primary, foreign, and composite
keys.
4 Perform CRUD operations (CREATE, READ, UPDATE, DELETE) on a
 database.
5 Retrieve data using SELECT statements with filtering conditions.
6 Explore SQL aggregate functions such as COUNT, SUM, AVG, MAX, and
MIN.
7 Use SQL string and date functions for data manipulation.
8 Design an E-R diagram for a real-world application like hospital or airline
reservation systems.
9 Convert an E-R diagram into a relational schema.
10 Identify and list functional dependencies from a given dataset.
11 Decompose a database schema into smaller tables using multilevel dependencies.
12 Verify lossless decomposition for resulting database schemas.
13 Normalize a dataset into 1NF, 2NF, 3NF, BCNF, and 4NF.
14 Design and implement a schema in 5NF for advanced normalization.
15 Implement indexing using B+ Tree structures for efficient data retrieval.
16 Write SQL queries to compare execution performance with and without indexing.
17 Perform query optimization using execution plans and analyze query costs.
18 Use advanced SQL queries with GROUP BY and HAVING clauses.
19 Perform various JOIN operations such as inner join, outer join, and cross join.
20 Create and query materialized views for optimizing data retrieval.
21 Implement stored procedures and functions for a sample application.
22 Write triggers to handle automatic updates like stock updates in an inventory
system.
23 Write SQL queries to demonstrate transaction handling using COMMIT,
ROLLBACK, and SAVEPOINT.
24 Verify ACID properties using interdependent transactions.
25 Implement the Two-Phase Locking protocol for concurrency control.
26 Install and configure a NoSQL database such as MongoDB or Cassandra.
27 Create a database in NoSQL and perform CRUD operations.

E. Study Materials

TEXT BOOKS
1. Database System Concepts, Abraham Silberschatz, Henry F. Korth & S. Sudarshan, McGraw Hill.
2. An introduction to Database Systems, C J Date, Addition-Wesley.
3. Understanding SQL by Martin Gruber, BPB

REFERENCE BOOKS

R1. Oracle – The complete reference – TMH /oracle press

R2. SQL – PL/SQL by Ivan Bayross

WEB Links for further study

1. https://www.geeksforgeeks.org/dbms
2. http://nptel.iitm.ac.in/video.php?subjectId=106106093
3. http://holowczak.com/oracle-sqlplus-tutorial
4. http://www.w3schools.com/sql
5. http://beginner-sql-tutorial.com/sql.htm

F. CO-PO MAPPING:

Course PO PO PO PO PO PO PO PO PO PO1 PO1 PO1

Outcom 1 2 3 4 5 6 7 8 9 0 1 2
e

CO1 3 2 2 2 3 1 1 1 1 1 2 2

CO2 3 3 3 2 3 1 1 1 1 2 2 2

CO3 2 3 2 3 2 1 1 1 1 1 1 2

CO4 3 3 3 3 3 1 1 1 1 1 1 3

CO1 3 2 2 2 3 1 1 1 1 1 2 2