DBMS

UNIT-I
Introduction to DBMS:
 Overview, File system vs DBMS
 advantages of DBMS,
 storage data,
 queries,
 transaction management,
 DBMS structure.
Data Models:
 Data modelling and data models, the importance of data models,
 data model basic building blocks
 The evolution of data models, degree of data abstraction.
UNIT-II
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.
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.
UNIT-III
Structured Query Language (SQL): Introduction to SQL, Data Definition Languange, Data Manipulation
Language, Data Control Language, and Transaction Control Language, SELECT queries, creating a view,
joining database tables, sub queries and correlated queries.
UNIT-IV
Schema Refinement: Problems caused by redundancy, decompositions, problems related to
decomposition, reasoning about functional dependencies, FIRST, SECOND, THIRD normal forms, BCNF,
lossless join decomposition, multi-valued dependencies, FOURTH normal form, FIFTH normal form.
UNIT-V
Transaction Management and Concurrency Control: What is a transaction? ACID Properties, Lock
Management, serializability, concurrency control with locking methods, concurrency control with
time stamping methods, concurrency control with optimistic methods, specialized locking
techniques.
 
TEXT BOOKS:
1. Fundamentals of Database Systems, Elmasri Navathe Pearson Education.
2. Data base Management Systems, Raghurama Krishnan, Johannes Gehrke, TATA
McGraw-Hill 3rd Edition.
 
REFERENCE BOOKS:
1. Data base System Concepts, Silberschatz, Korth, McGraw hill, Sixth Edition
2. An Introduction to Database systems, C.J. Date, A.Kannan, S.Swami Nadhan, Pearson, Eight
Edition for UNIT III.
 
DATA AND INFORMATION:

 Data are simply facts or figures - bits of information, but not information itself. Data
can be represented in alphabets(A-Z, a-z),in digits(0-9) and using special
characters(+,-.#,$, etc)
 When data are processed, interpreted, organized, structured or presented so as to

make them meaningful or useful, they are called information. 

File is a collection of related data stored in secondary memory.

A student secures 450 marks. Here 450 is data, marks of the student is

the information and hard work required to get the marks is knowledge.

Database

A database is organized collection of related data of an organization stored in formatted way

which is shared by multiple users.
 The main feature of data in a database are:

1. It must be well organized

2. It is related
3. It is accessible in a logical order without any difficulty
4. It is stored only once
 DATABASE APPLICATIONS
:

computerized library systems

automated teller machines

flight reservation systems

computerized parts inventory systems

DBMS:

A DBMS is a collection of inter related data and a set of programs to manipulate those data.

DBMS = Database + set of programs

A database management system (DBMS) is a computerized system that enables users to create and

maintain a database. The DBMS is a general-purpose software system that facilitates the processes of

defining, constructing, manipulating, and sharing databases among various users and applications.
 Examples of data bases

IBM DB2.
Microsoft Access.
Microsoft Excel.
Microsoft SQL Server.
MySQL.
Oracle RDBMS.

 
 Function of DBMS:

1. Defining database schema: it must give facility for defining the database
structure also specifies access rights to authorized users.
2. Manipulation of the database: The dbms must have functions like insertion of
record into database updation of data, deletion of data, retrieval of data
3. Sharing of database: The DBMS must share data items for multiple users by
maintaining consistency of data.
4. Protection of database: It must protect the database against unauthorized users.
5. Database recovery: If for any reason the system fails DBMS must facilitate data
base recovery.
File Management System

 It is nothing but a collection of programs which manage

and store data in files and folders in a computer hard disk.
 It helps in reading and writing data to the hard disk. It is

also called a conventional file system.

 Data redundancy is high and cannot be controlled easily in

file management systems.

DBMS VS FILE SYSTEM
DBMS File Processing System

Minimal data redundancy problem in DBMS Data Redundancy problem exits

Data Inconsistency does not exist Data Inconsistency exist here

Accessing database is easier Accessing is comparatively difficult

Data is scattered in various files and files may be of different format, so

The problem of data isolation is not found in database
data isolation problem exists

Transactions like insert, delete, view, updating, etc are possible in

In file system, transactions are not possible
database

Concurrent access and recovery is possible in database Concurrent access and recovery is not possible

Security of data Security of data is not good

A database manager (administrator) stores the relationship in form of A file manager is used to store all relationships in directories in file

structural tables systems.

Advantages Of DBMS

 Data independence
 Efficient data access
 Data integrity and data security
 Data administration
 Concurrent access and crash recovery
 Reduced application development time
 Advantages of DBMS

Reduction of redundancies:
Centralized control of data by the DBA avoids unnecessary duplication of data and
effectively reduces the total amount of data storage required avoiding duplication in the
elimination of the inconsistencies that tend to be present in redundant data files.
Sharing of data:
A database allows the sharing of data under its control by any number of application
programs or users.
Data Integrity:
Data integrity means that the data contained in the database is both accurate and
consistent. Therefore data values being entered for storage could be checked to ensure
that they fall with in a specified range and are of the correct format.
Data Security:
The DBA who has the ultimate responsibility for the data in the dbms can ensure that
proper access procedures are followed including proper authentication schemas for access
to the DBS and additional check before permitting access to sensitive data.
 Database users
Naive users :
Users who need not be aware of the presence of the database system or any other
system supporting their usage are considered naïve users . A user of an automatic teller
machine falls on this category.
Application programmers :
Professional programmers who are responsible for developing application programs
or user interfaces utilized by the naïve and online user falls into this category.
Sophisticated users: They interact with the system without writing programs. They
form requests by writing queries in a database query language.
Specialized users: specialized users write special database application programs to
interact with database. These may be CADD systems, knowledge-based and expert
systems, complex data systems (audio/video).
 Database users

Database Administration
A person who has central control over the system is called database administrator .
The function of DBA are :
1. creation and modification of conceptual Schema
definition
2. Implementation of storage structure and access method.
3. schema and physical organization modifications .
4. granting of authorization for data access.
5. Integrity constraints specification.
6. Execute immediate recovery procedure in case of failures
7. ensure physical security to database
 QUERIES IN DBMS

A query is a statement requesting the retrieval of information. The portion of a DML

that involves information retrieval is called a query language.

A DBMS provides a specialized language, called the query language, in which queries

can be posed. A very attractive feature of the relational model is that it supports

powerful query languages.

QUERIES IN DBMS
Transaction Management:

 A transaction is a set of logically related operations. For example, you are transferring money from your bank account to your

friend’s account, the set of operations would be like this:

 Simple Transaction Example

 1. Read your account balance

2. Deduct the amount from your balance

3. Write the remaining balance to your account

4. Read your friend’s account balanace

5. Add the amount to his account balance

6. Write the new updated balance to his account

Transaction states
 ACID Properties in transaction

management
Atomicity : This property states that a transaction must be treated as an
atomic unit, that is, either all of its operations are executed or none. There
must be no state in a database where a transaction is left partially completed.
 Consistency: A transaction enforces consistency in the system state by

ensuring that at the end of any transaction the system is in a valid state.
 Isolation: For every pair of transactions, one transaction should start

execution only when the other finished execution. I have already discussed
the example of Isolation in the Consistency property above.
 Durability: Once a transaction completes successfully, the changes it has

made into the database should be permanent even if there is a system

failure. The recovery-management component of database systems ensures
the durability of transaction.
DBMS STRUCTURE
 DBMS Structure:

.
• Data Files : It stores the database.
• Data Dictionary : It stores meta data (data about data) about the structure of the database.
• Indices : Provide fast access to data items that hold particular values.
• Statistical Data : It stores statistical information about the data in the database. This
information is used by query processor to select efficient ways to execute query.
DBMS ARCHITECTURE
1-Tier Architecture:
 In this architecture, the database is directly available to the user. It means the

user can directly sit on the DBMS and uses it.

2-Tier Architecture:
 The 2-Tier architecture is same as basic client-server. In the two-tier

architecture, applications on the client end can directly communicate with the
database at the server side. For this interaction, API's like: ODBC, JDBC are used.
 The user interfaces and application programs are run on the client-side.
 The server side is responsible to provide the functionalities like: query

processing and transaction management.

 To communicate with the DBMS, client-side application establishes a connection

with the server side.

DBMS ARCHITECTURE
3-Tier Architecture
 The 3-Tier architecture contains another layer between the client and

server. In this architecture, client can't directly communicate with the

server.
 The application on the client-end interacts with an application server

which further communicates with the database system.

 End user has no idea about the existence of the database beyond the

application server. The database also has no idea about any other user
beyond the application.
 The 3-Tier architecture is used in case of large web application.
Data Abstraction
Degree of data abstraction
Physical or Internal Level
 It is the lowest level of abstraction for DBMS which defines how the data is

actually stored, it defines data-structures to store data and access methods

used by the database.
Logical or Conceptual Level
 Logical level is the intermediate level or next higher level. It describes what

data is stored in the database and what relationship exists among those data.
It tries to describe the entire or whole data because it describes what tables
to be created and what are the links among those tables that are created.
View or External Level
 It is the highest level. In view level, there are different levels of views and

every view only defines a part of the entire data. It also simplifies interaction
with the user and it provides many views or multiple views of the same
database.
Data independence
 Data independence refers characteristic of being able to modify the
schema at one level of the database system without altering the
schema at the next higher level.
 Types of Data Independence
 Physical data independence

Physical data independence can be defined as the capacity to change the

internal schema without having to change the conceptual schema.
 Logical data independence

Logical data independence refers characteristic of being able to change

the conceptual schema without having to change the external
schema.
 Data storage in DBMS

A DBMS allows a user to define the data to be stored

in terms of a data model.
A data model is a collection of concepts that can be

used to describe data the structure of a database

Data models
The Importance of the Data Models
 Data constitute the most basic information units employed by

a system. Applications are created to manage data and to help

transform data into information.
 But data are viewed in different ways by different people. So

that there is a huge importance of data modeling in DBMS. 

Data models importance
1. Are a communication tool. Data models can facilitate
interaction among the designer, the applications programmer,
and the end user.
2. Give an overall view of the database
3. Organize data for various users
4. Are an abstraction for the creation of good database
Data Model Basic Building Blocks

 Entity − An entity represents a particular type of object in the

real world.
 Entity set − Sets of entities of the same type which share the

same properties are called entity Sets.

 Attribute − An attribute is a characteristic of an entity.
 Constraints − A constraint is a restriction placed on the data.

It is helpful to ensure data integrity.

 Relationship − A relationship describes an association among

entities.
E-R Model
Relational model
 Hierarchical Model
Network model
Object oriented data model
Evolution of data models