DAUDAYAL INSTITUTE OF VOCATIONAL EDUCATION

C5-DBMS-I
UNIT-2

DBMS (Data Base Management System)

Database management System is software which is used to store and retrieve the database. For example,
Oracle, MySQL, etc.; these are some popular DBMS tools.

o DBMS provides the interface to perform the various operations like creation, deletion, modification,
etc.

o DBMS allows the user to create their databases as per their requirement.

o DBMS accepts the request from the application and provides specific data through the operating
system.

o DBMS contains the group of programs which acts according to the user instruction.

o It provides security to the database.

Advantage of DBMS
Controls redundancy

It stores all the data in a single database file, so it can control data redundancy.

Data sharing

An authorized user can share the data among multiple users.

Backup

It providesBackup and recovery subsystem. This recovery system creates automatic data from system failure
and restores data if required.

Multiple user interfaces

It provides a different type of user interfaces like GUI, application interfaces.

Disadvantage of DBMS
Size
It occupies large disk space and large memory to run efficiently.

Cost

DBMS requires a high-speed data processor and larger memory to run DBMS software, so it is costly.

Complexity

DBMS creates additional complexity and requirements.

Purpose of DBMS
o It uses a digital repository established on a server to store and manage the information.

o It can provide a clear and logical view of the process that manipulates data.

o DBMS contains automatic backup and recovery procedures.

o It contains ACID properties which maintain data in a healthy state in case of failure.

o It can reduce the complex relationship between data.

o It is used to support manipulation and processing of data.

o It is used to provide security of data.

o It can view the database from different viewpoints according to the requirements of the user.

DBMS Architecture
o The DBMS design depends upon its architecture. The basic client/server architecture is used to deal
with a large number of PCs, web servers, database servers and other components that are connected
with networks.

o The client/server architecture consists of many PCs and a workstation which are connected via the
network.

o DBMS architecture depends upon how users are connected to the database to get their request done.

Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of two
types like: 2-tier architecture and 3-tier architecture.

1-Tier Architecture
o In this architecture, the database is directly available to the user. It means the user can directly sit on
the DBMS and uses it.

o Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for
end users.

o The 1-Tier architecture is used for development of the local application, where programmers can
directly communicate with the database for the quick response.

2-Tier Architecture
o 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.

o The user interfaces and application programs are run on the client-side.
 o The server side is responsible to provide the functionalities like: query processing and transaction
management.

o To communicate with the DBMS, client-side application establishes a connection with the server side.

Fig: 2-tier Architecture

3-Tier Architecture
o The 3-Tier architecture contains another layer between the client and server. In this architecture, client
can't directly communicate with the server.

o The application on the client-end interacts with an application server which further communicates
with the database system.

o 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.

o The 3-Tier architecture is used in case of large web application.

Three schema Architecture
o The three schema architecture is also called ANSI/SPARC architecture or three-level architecture.

o This framework is used to describe the structure of a specific database system.

o The three schema architecture is also used to separate the user applications and physical database.

o The three schema architecture contains three-levels. It breaks the database down into three different
categories.

The three-schema architecture is as follows:

In the above diagram:

o It shows the DBMS architecture.

o Mapping is used to transform the request and response between various database levels of
architecture.

o Mapping is not good for small DBMS because it takes more time.

o In External / Conceptual mapping, it is necessary to transform the request from external level to
conceptual schema.

o In Conceptual / Internal mapping, DBMS transform the request from the conceptual to internal level.

1. Internal Level
o The internal level has an internal schema which describes the physical storage structure of the
database.
 o The internal schema is also known as a physical schema.

o It uses the physical data model. It is used to define that how the data will be stored in a block.

o The physical level is used to describe complex low-level data structures in detail.

2. Conceptual Level
o The conceptual schema describes the design of a database at the conceptual level. Conceptual level is
also known as logical level.

o The conceptual schema describes the structure of the whole database.

o The conceptual level describes what data are to be stored in the database and also describes what
relationship exists among those data.

o In the conceptual level, internal details such as an implementation of the data structure are hidden.

o Programmers and database administrators work at this level.

3. External Level
o At the external level, a database contains several schemas that sometimes called as subschema. The
subschema is used to describe the different view of the database.

o An external schema is also known as view schema.

o Each view schema describes the database part that a particular user group is interested and hides the
remaining database from that user group.

o The view schema describes the end user interaction with database systems.

o Data Models
o Data Model is the modeling of the data description, data semantics, and consistency
constraints of the data. It provides the conceptual tools for describing the design of a database
at each level of data abstraction. Therefore, there are following four data models used for
understanding the structure of the database:
 o
o 1) Relational Data Model: This type of model designs the data in the form of rows and
columns within a table. Thus, a relational model uses tables for representing data and in-
between relationships. Tables are also called relations. This model was initially described by
Edgar F. Codd, in 1969. The relational data model is the widely used model which is
primarily used by commercial data processing applications.
o 2) Entity-Relationship Data Model: An ER model is the logical representation of data as
objects and relationships among them. These objects are known as entities, and relationship
is an association among these entities. This model was designed by Peter Chen and published
in 1976 papers. It was widely used in database designing. A set of attributes describe the
entities. For example, student_name, student_id describes the 'student' entity. A set of the
same type of entities is known as an 'Entity set', and the set of the same type of relationships
is known as 'relationship set'.
o 3) Object-based Data Model: An extension of the ER model with notions of functions,
encapsulation, and object identity, as well. This model supports a rich type system that
includes structured and collection types. Thus, in 1980s, various database systems following
the object-oriented approach were developed. Here, the objects are nothing but the data
carrying its properties.
o 4) Semistructured Data Model: This type of data model is different from the other three
data models (explained above). The semistructured data model allows the data specifications
at places where the individual data items of the same type may have different attributes sets.
The Extensible Markup Language, also known as XML, is widely used for representing the
semistructured data. Although XML was initially designed for including the markup
information to the text document, it gains importance because of its application in the
exchange of data.

Data model Schema and Instance

 o The data which is stored in the database at a particular moment of time is called an instance
of the database.

o The overall design of a database is called schema.

o A database schema is the skeleton structure of the database. It represents the logical view of
the entire database.

o A schema contains schema objects like table, foreign key, primary key, views, columns, data
types, stored procedure, etc.

o A database schema can be represented by using the visual diagram. That diagram shows the
database objects and relationship with each other.

o A database schema is designed by the database designers to help programmers whose

software will interact with the database. The process of database creation is called data
modeling.

A schema diagram can display only some aspects of a schema like the name of record type, data
type, and constraints. Other aspects can't be specified through the schema diagram. For example, the
given figure neither show the data type of each data item nor the relationship among various files.

In the database, actual data changes quite frequently. For example, in the given figure, the database
changes whenever we add a new grade or add a student. The data at a particular moment of time is
called the instance of the database.
Data Independence
o Data independence can be explained using the three-schema architecture.

o 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.

There are two types of data independence:

1. Logical Data Independence

o Logical data independence refers characteristic of being able to change the conceptual schema without
having to change the external schema.

o Logical data independence is used to separate the external level from the conceptual view.

o If we do any changes in the conceptual view of the data, then the user view of the data would not be
affected.

o Logical data independence occurs at the user interface level.

2. Physical Data Independence
o Physical data independence can be defined as the capacity to change the internal schema without
having to change the conceptual schema.

o If we do any changes in the storage size of the database system server, then the Conceptual structure
of the database will not be affected.

o Physical data independence is used to separate conceptual levels from the internal levels.

o Physical data independence occurs at the logical interface level.

Fig: Data Independence