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 : sdwarapu@gitam.edu
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:
