DATABASE MANAGEMENT SYSTEMS
LECTURE NOTES
B.TECH II YEAR – II SEM (R17)
(2018-19)
DEPARTMENT OF
COMPUTER SCIENCE AND ENGINEERING
MALLA REDDY COLLEGE OF ENGINEERING &
TECHNOLOGY
(Autonomous Institution – UGC, Govt. of India)
Recognized under 2(f) and 12 (B) of UGC ACT 1956
(Affiliated to JNTUH, Hyderabad, Approved by AICTE - Accredited by NBA & NAAC – ‘A’ Grade - ISO 9001:2015 Certified)
Maisammaguda, Dhulapally (Post Via. Hakimpet), Secunderabad – 500100, Telangana State, India
, 2
SYLLABUS
(R17A0509) DATABASE MANAGEMENT SYSTEMS
Objectives:
To Understand the basic concepts and the applications of database systems
To Master the basics of SQL and construct queries using SQL
To understand the relational database design principles
To become familiar with the basic issues of transaction processing and concurrency control
To become familiar with database storage structures and access techniques
UNIT I:
Data base System Applications, Purpose of Database Systems, View of Data – Data Abstraction –
Instances and Schemas – data Models – the ER Model – Relational Model – Other Models –
Database Languages – DDL – DML – database Access for applications Programs – data base Users
and Administrator – Transaction Management – data base Architecture – Storage Manager – the
Query Processor
Data base design and ER diagrams – ER Model - Entities, Attributes and Entity sets – Relationships
and Relationship sets – ER Design Issues – Concept Design – Conceptual Design for University
Enterprise.
Introduction to the Relational Model – Structure – Database Schema, Keys – Schema Diagrams
UNIT II:
Relational Query Languages, Relational Operations.
Relational Algebra – Selection and projection set operations – renaming – Joins – Division –
Examples of Algebra overviews – Relational calculus – Tuple relational Calculus – Domain relational
calculus.
Overview of the SQL Query Language – Basic Structure of SQL Queries, Set Operations, Aggregate
Functions – GROUPBY – HAVING, Nested Sub queries, Views, Triggers.
UNIT III:
Normalization – Introduction, Non loss decomposition and functional dependencies, First, Second,
and third normal forms – dependency preservation, Boyee/Codd normal form.
Higher Normal Forms - Introduction, Multi-valued dependencies and Fourth normal form, Join
dependencies and Fifth normal form
UNIT IV:
Transaction Concept- Transaction State- Implementation of Atomicity and Durability – Concurrent –
Executions – Serializability- Recoverability – Implementation of Isolation – Testing for
serializability- Lock –Based Protocols – Timestamp Based Protocols- Validation- Based Protocols –
Multiple Granularity.
Recovery and Atomicity – Log – Based Recovery – Recovery with Concurrent Transactions – Buffer
Management – Failure with loss of nonvolatile storage-Advance Recovery systems- Remote Backup
systems.
UNIT V:
File organization:– File organization – various kinds of indexes. Query Processing – Measures of
query cost - Selection operation – Projection operation, - Join operation – set operation and aggregate
operation – Relational Query Optimization – Transacting SQL queries – Estimating the cost –
Equivalence Rules.
, 3
TEXT BOOKS:
1. Data base System Concepts, Silberschatz, Korth, McGraw hill, Sixth Edition.(All UNITS
except III th)
2. Data base Management Systems, Raghurama Krishnan, Johannes Gehrke, TATA
McGrawHill 3rd Edition.
REFERENCE BOOKS:
1. Fundamentals of Database Systems, Elmasri Navathe Pearson Education.
2. An Introduction to Database systems, C.J. Date, A.Kannan, S.Swami Nadhan, Pearson, Eight
Edition for UNIT III.
URLs:
Outcomes:
Demonstrate the basic elements of a relational database management system
Ability to identify the data models for relevant problems
Ability to design entity relationship and convert entity relationship diagrams into RDBMS and
formulate SQL queries on the respect data
Apply normalization for the development of application software’s
, 4
UNIT-1
Introduction to Database Management System
As the name suggests, the database management system consists of two parts. They are:
1. Database and
2. Management System
What is a Database?
To find out what database is, we have to start from data, which is the basic building block of any DBMS.
Data: Facts, figures, statistics etc. having no particular meaning (e.g. 1, ABC, 19 etc).
Record: Collection of related data items, e.g. in the above example the three data items had no meaning. But
if we organize them in the following way, then they collectively represent meaningful information.
Roll Name Age
1 ABC 19
Table or Relation: Collection of related records.
Roll Name Age
1 ABC 19
2 DEF 22
3 XYZ 28
The columns of this relation are called Fields, Attributes or Domains. The rows are called Tuples
or Records.
Database: Collection of related relations. Consider the following collection of tables:
T1 T2
Roll Name Age Roll Address
1 ABC 19 1 KOL
2 DEF 22 2 DEL
3 XYZ 28 3 MUM
T3 T4
Roll Year Year Hostel
1 I I H1
2 II II H2
3 I
We now have a collection of 4 tables. They can be called a “related collection” because we can clearly find out
that there are some common attributes existing in a selected pair of tables. Because of these common
attributes we may combine the data of two or more tables together to find out the complete details of a
student. Questions like “Which hostel does the youngest student live in?” can be answered now, although
LECTURE NOTES
B.TECH II YEAR – II SEM (R17)
(2018-19)
DEPARTMENT OF
COMPUTER SCIENCE AND ENGINEERING
MALLA REDDY COLLEGE OF ENGINEERING &
TECHNOLOGY
(Autonomous Institution – UGC, Govt. of India)
Recognized under 2(f) and 12 (B) of UGC ACT 1956
(Affiliated to JNTUH, Hyderabad, Approved by AICTE - Accredited by NBA & NAAC – ‘A’ Grade - ISO 9001:2015 Certified)
Maisammaguda, Dhulapally (Post Via. Hakimpet), Secunderabad – 500100, Telangana State, India
, 2
SYLLABUS
(R17A0509) DATABASE MANAGEMENT SYSTEMS
Objectives:
To Understand the basic concepts and the applications of database systems
To Master the basics of SQL and construct queries using SQL
To understand the relational database design principles
To become familiar with the basic issues of transaction processing and concurrency control
To become familiar with database storage structures and access techniques
UNIT I:
Data base System Applications, Purpose of Database Systems, View of Data – Data Abstraction –
Instances and Schemas – data Models – the ER Model – Relational Model – Other Models –
Database Languages – DDL – DML – database Access for applications Programs – data base Users
and Administrator – Transaction Management – data base Architecture – Storage Manager – the
Query Processor
Data base design and ER diagrams – ER Model - Entities, Attributes and Entity sets – Relationships
and Relationship sets – ER Design Issues – Concept Design – Conceptual Design for University
Enterprise.
Introduction to the Relational Model – Structure – Database Schema, Keys – Schema Diagrams
UNIT II:
Relational Query Languages, Relational Operations.
Relational Algebra – Selection and projection set operations – renaming – Joins – Division –
Examples of Algebra overviews – Relational calculus – Tuple relational Calculus – Domain relational
calculus.
Overview of the SQL Query Language – Basic Structure of SQL Queries, Set Operations, Aggregate
Functions – GROUPBY – HAVING, Nested Sub queries, Views, Triggers.
UNIT III:
Normalization – Introduction, Non loss decomposition and functional dependencies, First, Second,
and third normal forms – dependency preservation, Boyee/Codd normal form.
Higher Normal Forms - Introduction, Multi-valued dependencies and Fourth normal form, Join
dependencies and Fifth normal form
UNIT IV:
Transaction Concept- Transaction State- Implementation of Atomicity and Durability – Concurrent –
Executions – Serializability- Recoverability – Implementation of Isolation – Testing for
serializability- Lock –Based Protocols – Timestamp Based Protocols- Validation- Based Protocols –
Multiple Granularity.
Recovery and Atomicity – Log – Based Recovery – Recovery with Concurrent Transactions – Buffer
Management – Failure with loss of nonvolatile storage-Advance Recovery systems- Remote Backup
systems.
UNIT V:
File organization:– File organization – various kinds of indexes. Query Processing – Measures of
query cost - Selection operation – Projection operation, - Join operation – set operation and aggregate
operation – Relational Query Optimization – Transacting SQL queries – Estimating the cost –
Equivalence Rules.
, 3
TEXT BOOKS:
1. Data base System Concepts, Silberschatz, Korth, McGraw hill, Sixth Edition.(All UNITS
except III th)
2. Data base Management Systems, Raghurama Krishnan, Johannes Gehrke, TATA
McGrawHill 3rd Edition.
REFERENCE BOOKS:
1. Fundamentals of Database Systems, Elmasri Navathe Pearson Education.
2. An Introduction to Database systems, C.J. Date, A.Kannan, S.Swami Nadhan, Pearson, Eight
Edition for UNIT III.
URLs:
Outcomes:
Demonstrate the basic elements of a relational database management system
Ability to identify the data models for relevant problems
Ability to design entity relationship and convert entity relationship diagrams into RDBMS and
formulate SQL queries on the respect data
Apply normalization for the development of application software’s
, 4
UNIT-1
Introduction to Database Management System
As the name suggests, the database management system consists of two parts. They are:
1. Database and
2. Management System
What is a Database?
To find out what database is, we have to start from data, which is the basic building block of any DBMS.
Data: Facts, figures, statistics etc. having no particular meaning (e.g. 1, ABC, 19 etc).
Record: Collection of related data items, e.g. in the above example the three data items had no meaning. But
if we organize them in the following way, then they collectively represent meaningful information.
Roll Name Age
1 ABC 19
Table or Relation: Collection of related records.
Roll Name Age
1 ABC 19
2 DEF 22
3 XYZ 28
The columns of this relation are called Fields, Attributes or Domains. The rows are called Tuples
or Records.
Database: Collection of related relations. Consider the following collection of tables:
T1 T2
Roll Name Age Roll Address
1 ABC 19 1 KOL
2 DEF 22 2 DEL
3 XYZ 28 3 MUM
T3 T4
Roll Year Year Hostel
1 I I H1
2 II II H2
3 I
We now have a collection of 4 tables. They can be called a “related collection” because we can clearly find out
that there are some common attributes existing in a selected pair of tables. Because of these common
attributes we may combine the data of two or more tables together to find out the complete details of a
student. Questions like “Which hostel does the youngest student live in?” can be answered now, although
