DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

GITAM INSTITUTE OF TECHNOLOGY (GITAM)

Visakhapatnam

S1 S2 S3 S4
Year: 2021-22 Semester:
S5  S6 S7 S8
Department: CSE Core  Elective
Pogramme: B.Tech Section: B-3 & B 9
Paper Title : Database Paper Code : 19ECS333
Management
Systems
Credits : 3 Alloted 3
Hours:
Course Instructor : D.SUNEETHA
Contact details :
Room No : ICT -230
Phone No : 08912840270
Mobile No : 8985484764
Maid ID : [email protected]

Course Overview Database management is one of the most sought after skill today
in the IT industry. This course provides in-depth coverage of
managing, storing and retrieving information from the large volume
of data stored in databases. The participants will gain detailed
knowledge and skill set for designing and using relational databases,
the most widely used databases for businesses.
Course Objectives
CO 1: Understand the basic concepts of data base models and
he/she can explore the advantages and disadvantage of
different data base systems like file systems and DBMS.
(Data Modelling)
CO 2: Understand the features of Entity-Relationship model
and acquires knowledge to draw/ develop the conceptual
database models using Entity-Relationship models.
(Conceptual Modelling)
CO 3: Design logical database with all integrity constraints
over relations. (Logical Modelling)

CO 4: Write all types of SQL, relational algebra, relational

calculus queries over relations and he/she can be able to create
views on the existing relations.
(Query Language – SQL)
CO 5: Apply normalization steps in database design and
removal of data anomalies (Model Refinement)

CO 6: Describe the characteristics of database transactions and

how they affect database integrity and consistency.

CO 7: Understand the concurrency control mechanisms and

crash recovery algorithms.

Pre- requisites and co-requisites Knowledge in Programming and ,Data Structures

Evaluation Components Class participation / Assignments/ Quizzes/ 20 Marks
Regularity to classes
Coursera: 30 Marks CE in
Database Management Essentials, Lab
Michael Mannino
https://www.coursera.org/learn/database-
management/home/welcome
Unit wise Module Tests 20 Marks
Semester-end examination 60 Marks
Total 100 Marks
Assessment pattern Criteria will be elucidated in the class session-1
Lesson plan Presented in the subsequent pages
Text Books 1. Database Management Systems, by Raghu Ramakrishnan
and Johannes Gehrke McGraw-Hill , second Edition,2000
2. Database System Concepts, H.F.Korth and A.Silberschatz
McGraw-Hill, sixth edition, 2011

Reference Fundamentals of Database Systems, Elmasri Ramez, Navathe

Books Shamkant, Pearson Education, 2017

Course Notes Module wise Course notes will be uploaded into Moodle

Course Outcomes (CO) Upon completion of this course, students will be able to:
● Design a data base for an application Using E-R data
model and Relational Data model
● Refine the database using normalization techniques
● Implement the database in SQL
● Connect the database with a web application

Teaching Methods Online using Zoom

19ECS333: DATABASE MANAGEMENT SYSTEMS

L T P C

3 0 2 4
This course provides fundamental and practical knowledge on database concepts by means
of organizing the information, storing and retrieve 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 Objectives

 Demonstrate basic database concepts, including the structure and operation of the
relational data model.
 Introduce simple and moderately advanced database queries using Structured Query
Language (SQL).
 Explain and successfully apply logical database design principles, including E-R
diagrams and database normalization.
 Demonstrate the concept of a database transaction and related database facilities,
including concurrency control, and data object locking and protocols
.

UNIT I 8L+5P

Introduction to DBMS: Overview, File system vs DBMS, advantages of DBMS, storage

data, queries, transaction management, DBMS structure, people who work with Databases.
Data base Design: data models, the importance of data models.

E-R model: Entities, attributes and entity sets, relationship and relationship sets, mapping
cardinalities, keys, features of ER model, conceptual database design with ER model.

Learning Outcomes:
After completion of this unit, the student will be able to
 interpret the basic terminology of DBMS like data, database, database management
systems(L2)
 compare DBMS over File Systems(L2)
 define levels of abstraction with three tier architecture(L1)
 define the role of DBA and other users of DBMS(L1)
 model a given application using ER diagram(L3)

UNIT II 8L+5P

Relational model: Integrity constraints over relations and enforcement, querying relation
data, logical database design, views, destroying/altering tables and views. Relational Algebra
and Relational Calculus
Learning Outcomes:
After completion of this unit, the student will be able to
 match the integrity constraints from ER model to relational model(L1)
 translate an ER Model to Relational Model and vice versa(L2)
 compare the difference between views and physical tables and working with
views(L2)
 construct the given Query in Relational Algebra and Relational Calculus(L3)

UNIT III 8L+6P

Structured Query Language (SQL): Introduction to SQL, Basic SQL Queries: DML, DDL,
DCL, TCL, Select Commands, Union, Intersection, Except, Nested Queries, Aggregate
Operators, Null values, Relational set operators, SQL join operators

Database Application Development: SQL functions, procedural SQL, embedded SQL,

cursors, ODBC and JDBC, triggers and active database, designing active databases.

Learning Outcomes:
After completion of this unit, the student will be able to
 create and modify database using SQL query(L5)
 illustrate different types of query forms (simple queries, nested queries, and
aggregated queries) in SQL(L2)
 build Embedded SQL, cursors, triggers and active database using PL/SQL
programs(L3)
 develop knowledge about ODBC and JDBC connectivity to connect database(L3)

UNIT IV 8L+6P

Schema Refinement and Normal Forms: Schema Refinement, Functional Dependencies,

Reasoning about Functional Dependencies. Normal Forms, Properties of Decomposition,
Normalization, Different types of normal forms, different types of dependencies.

Learning Outcomes:
After completion of this unit, the student will be able to
 make use of about schema refinement process(L3)
 extend the concept of functional dependencies (FDs) and knows about anomalies(L2)
 illustrate knowledge about different types of normal forms and the importance of
normalization(L2)

UNIT V
10 L + 6 P
Transaction Management and Concurrency Control: 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, Specialized Locking Techniques,
Concurrency control without locking,

Crash Recovery: Aries, Recovering from a System Crash, Media recovery.

Learning Outcomes:
After completion of this unit, the student will be able to
 interpret the overview of transaction management in DBMS(L2)
 explain the importance of concurrency and concurrency control mechanisms(L2)
 develop knowledge about concurrency control with and without locks(L3)
 identify different types of crashes in DBMS(L3)
 apply crash recovery techniques to recover from DBMS crashes (L3

Text Book(s):
Raghu Ramakrishnan and Johannes Gehrke, Database Management Systems, McGraw-Hill,
3e, 2014.

References
1. H.F.Korth and A.silberschatz, Database System Concepts, McGraw-Hill, 6e, 2011.
2. RamezElmasri, Shamkant B. Navathe, Fundamentals of Database Systems, Pearson
Education,
7e, 2016.

Text Book(s)
1. Raghu Ramakrishnan and Johannes Gehrke, Database Management Systems,
McGrawHill, 2002.

2. H.F.Korth and A.Silberschatz, Database System Concepts, McGrawHill,

2002.

References 1. Ramez Elmasri, Shamkant B. Navathe, Fundamentals of

Database Systems, 2008.
2. Elmasri, Navathe, Somayajulu Gupta, Fundamentals of Database Systems,
Pearson Education, 2007.
LESSON PLAN

UNIT- 1

UNIT- 1 Learning outcome:

After going through this unit the students will be able to:

● Interpret the basic terminology of DBMS like data, database, database management systems(L2)
● Compare DBMS over File Systems(L2)
● Define levels of abstraction with three tier architecture(L1)
● Define the role of DBA and other users of DBMS(L1)
● Model a given application using ER diagram(L3)

S No Day Description of the topic to be covered Methodolgy

Adopted(Black
Board/Virtual
board/PPT/Practice in
Lab,etc)

1 Day1 Introduction to DBMS: Data, Database, Information,

knowledge, DBMS, History of DBMS
Virtual Classes using
Different types of Data Base Models Zoom App

2 Day2 Introduction to File systems, Differences between Virtual Classes using

DBMS and File Systems, Advantages of DBMS over Zoom App
FS, Levels of abstraction with three tier architecture

Data views: External, Conceptual , Physical ,Data

Independence , Physical and Logical

3 Day3 Storing Data in Data Base, Basic Structural Query Virtual Classes using
Representation. Introduction to Relational calculus Zoom App
and Relational algebra

4 Day4 Overview of Transaction Management. Structure of Virtual Classes using

DBMS . People who work with DBMS: Zoom App

Different role management, Role of DBA

5 Day5 ER Model : Entity, attribute, Entity Set, Relationship Virtual Classes using
and Relationship set with examples Zoom App

6 Day6 Additional Features of ER Model: Virtual Classes using

Zoom App
Key constraints
Weak entities
Class hierarchies
Aggregation

7 Day7 Conceptual Database Design with ER Model Virtual Classes using

Zoom App
Case study
8 Day8 MODULE-1 Revision Live examples

Assignment on MODULE-1: Refer to Moodle

Module -1 Quiz through Moodle

UNIT- 2

UNIT- 2 Learning outcome:

After going through this unit the students will be able to :

 Match the integrity constraints from ER model to relational model(L1)

 Translate an ER Model to Relational Model and vice versa(L2)
 Compare the difference between views and physical tables and working with views(L2)
 Construct the given Query in Relational Algebra and Relational Calculus(L3).

09 Day 09 Introduction to Relational Model: Virtual Classes using

Zoom App
Schema Definition, Relational Instance
Instance Vs Schema How to write a Relational
Schema. Constraint, Types of Constraints
Key constraints : Primary, Foreign , unique, not null
Domain Constraints, Enforcements of IC’s:
Alternative ways to handle foreign key violations
10 Day10 Querying Relational Data: Virtual Classes using
Zoom App
Query Language, Simple Queries

11 Day11 Logical DB Design: Virtual Classes using

Zoom App
ER to Relational

Entity set to tables, Relationship set to tables,

translating relationship sets with key constraints and
with participation constraints

12 Day12 Translating weak entity set , class hierarchies, Virtual Classes using
aggregation Zoom App

13 Day13 Introduction to views: Virtual Classes using

Zoom App
View definition, conversion of schema to view,
updates on views, View restrictions, Destroying/
altering tables and views

14 Day14 Relational algebra: Virtual Classes using

Zoom App
Selection, projection, set operations, Renaming ,
Joins: natural, conditional, Equi , Outer joins ,
Division, more examples on algebra queries

15 Day15 Relational Calculus : Virtual Classes using

Zoom App
Tuple relational calculus , syntax and semantics of
TRC queries, Examples on TRC queries.

Domain relational calculus , syntax and semantics of

DRC queries, Examples on DRC queries

16 Day16 MODULE-2 Revision Virtual Classes using

Zoom App

Assignment on MODULE-2:Refer to Moodle

Unit -2 Quiz
UNIT- 3

UNIT- 3 Learning outcome:

After going through this unit the students will be able to :

● Create and modify database using SQL query(L5).

● Illustrate different types of query forms (simple queries, nested queries, and aggregated queries)
in SQL(L2).
● Build Embedded SQL, cursors, triggers and active database using PL/SQL programs(L3).

● Develop knowledge about ODBC and JDBC connectivity to connect database(L3).

17 Day 17 Explanation about basic SQL query structure and its Virtual Classes using
syntax with suitable examples. Zoom App

18 Day 18 SQL query construction using key words: Virtual Classes using
Zoom App
Union, Intersect, except, Any, All, In;

19 Day 19 Explanation about Nested queries, Correlated nested Virtual Classes using
queries with suitable examples. Zoom App

20 Day 20 Explanation about Aggregated operations: Virtual Classes using

Zoom App
SUM,AVG,MAX,MIN, Explanation about null
values in relations

21 Day 21 Explanation about Embedded SQL and its Virtual Classes using
implementation using oracle db. Creation of cursors Zoom App

22 Day 22 Steps involved in ODBC and JDBC connectivity, Virtual Classes using
ODBC and JDBC connectivity program with suitable Zoom App
example using oracle db

23 Day 23 Types of Triggers and its creation using PL/SQL Virtual Classes using
programming, Zoom App

Explanation about active database; creation of active

databases.

24 Day 24 Virtual Classes using

MODULE-3 Revision
Zoom App

Assignment on MODULE-3:Refer to Moodle

Module-3 Quiz through Moodle

UNIT- 4

UNIT-IV Schema Refinement and Normalization

Unit-IV:Learning outcome:
After going through this unit the students will be able to :

 Make use of about schema refinement process(L3).

 Extend the concept of functional dependencies (fds) and knows about anamolies(L2).
 Illustrates knowledge about different types of normal forms and the importance of normalization.
(L2)

Significance of schema refinement process in DBMS. Virtual Classes using

Anomalies: Zoom App
25 25
Insertion anomalies, Deletion anomalies,Updating
anomalies and redundancy.

Explanation about mathematical concepts related to Virtual Classes using

26 26 functional dependencies (FDs). Problems solving on Zoom App
fds

Virtual Classes using

27 27 Explanation about normalization process
Zoom App

28 28 Steps involved in normalization process: Virtual Classes using

Zoom App
1ST normal form, 2nd normal form, 3rd normal form,
4th normal form, 5th normal form and BCNF

Steps involved in normalization process: Virtual Classes using

29 29 Zoom App
3rd normal form, 4th normal form, 5thnormal form and
BCNF

Steps involved in normalization process: BCNF, Virtual Classes using

30 30
Dependency preserving problem Zoom App

Lossless join dependency and loss join dependency Virtual Classes using
31 31
problem. Zoom App

Virtual Classes using

32 32 Unit-IV Revision and class room discussion
Zoom App

Assignment on MODULE-4, Refer to Moodle Virtual Classes using

Zoom App

Module -4 Quiz through Moodle

UNIT- 5

Transaction Management and Concurrency

UNIT-V
Control

Unit-V Learning outcome:

 Interpret the overview of transaction management in DBMS(L2).
 Explain the importance of concurrency and concurrency control mechanisms(L2).
 Develop knowledge about concurrency control with and without locks(L3)
 Identify knowledge about different types of crashes in DBMS.(L3)
 Apply crash recovery techniques to recover from DBMS crashes(L3)

Explanation about Transaction; Virtual Classes using

Zoom App
33 ACID properties:

Atomicity, Consistency, Isolation and Durability

Virtual Classes using

34 What is Scheduling, Schedules; Serializability
Zoom App

Virtual Classes using

35 View Serializabilty; Conflict Seriablizablity
Zoom App

36 Significance of Concurrency in DBMS transaction Virtual Classes using

management. Zoom App

Concurrency control schemes in DBMS: Virtual Classes using

37 Zoom App
Locking techniques: 2PL, Strict 2PL and Rigorous
2PL

Concurrency control schemes in DBMS: Virtual Classes using

38 Zoom App
Without Locking techniques: Time Stamp based
concurrency control.

Concurrency control schemes in DBMS: Virtual Classes using

39 Zoom App
Without Locking techniques: Optimistic Concurrence
control.

Concurrency control schemes in DBMS: Virtual Classes using

40 Zoom App
Without Locking techniques: Multiple granularities.
Explanation about Thomas write rule

Types of system crashes: Virtual Classes using

41 Zoom App
Software, Hardware and Power failures. ARIES crash
recovery algorithm

Virtual Classes using

42 MODULE-5 Revision and class room discussion
Zoom App

Assignment on MODULE-5:Refer to Moodle

Module – 5 Quiz through Moodle

SELF-LEARNING:
Assignments to promote self-learning, survey of contents from multiple sources.

S.N Topics CO ALM References/MOOCS

o

1 Database 3 -5 Certificatio https://www.coursera.org/learn/databas

Management n or Quiz e-management
Essentials
DELIVERY DETAILS OF CONTENT BEYOND SYLLABUS:
Content beyond syllabus covered (if any) should be delivered to all students that would be
planned, and schedule notified accordingly.

S.N Advanced Topics, Additional CO POs & ALM References/MOOC

o Reading, Research papers and PSOs S
any

1 APIs for Database Management

Systems for connecting Front end.

GENERAL INSTRUCTIONS
Students should come prepared for classes and carry the text book(s) or material(s) as
prescribed by the Course Faculty to the class.

NOTICES
Most of the notices are available on the LMS platform.
All notices will be communicated through the institution email.

Signature of COURSE CHAMPION: HEAD OF DEPARTMENT: