CHAPTER 2

Database System Architecture

By:- Shalu Kushwah

 INTRODUCTION
• 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.
• The client/server architecture consists of many
PCs and a workstation which are connected via
the network.
• DBMS architecture depends upon how users are
connected to the database to get their request
done.
INTRODUCTION
Schemas, Sub-schemas, and
Instances
 SCHEMAS
The overall design of the database is called schema or description of
database.
• Schema is a physical representation of data which is present in the
database management system.
• In simple words we can call a schema the structure of any database.
• Schema of a database can only modify the DDL statement but does
not change by performing certain operations like insertion, updating,
and deletion.
• Database schema explains the integrity constraints of the database,
domains of all attributes, foreign, and primary key of all the
relations.
• Example-
 Schemas, Sub-schemas, and
 Instances
SUB-SCHEMAS
• It is the subset of the schema and inherits the same property that a schema has. It gives
the users a window through which he/she can view only that part of the database which
he wants.
• For example − Student table in a database the programmer can access all fields of
table, but the user can access only two or three fields of it. Subschema describes both
views of the database.
 Types of Schema
Schema is of three types, which are as follows −
• View Schema − The design of a database at a view level is called view schema. This
schema generally shows the user interaction with the database system.
• Logical Schema − The design of a database at the logical level is called a logical
schema. A database administrator (DBA) and the programmers used to work at this
level. This level describes all the entities, attributes and their relationship with the
integrity constraints.
• Physical Schema − The design of a database at the physical level is called a physical
schema. This schema describes how the data is stored in the secondary storage devices.
There is only 1 logical and 1 physical schema per database and more than 1 view
Schemas, Sub-schemas, and
Instances
 INSTANCES
• Instances are the collection of information stored at a
particular moment. The instances can be changed by
certain CRUD operations as like addition, deletion of data. It
may be noted that any search query will not make any kind
of changes in the instances.
• Example –
Let’s say a table teacher in our database whose name is
School, suppose the table has 50 records so the instance of
the database has 50 records for now and tomorrow we are
going to add another fifty records so tomorrow the instance
have total 100 records. This is called an instance.
Three schema Architecture
Three schema Architecture
 Objectives of Three schema Architecture
The main objective of three level architecture is to enable multiple users to access
the same data with a personalized view while storing the underlying data only
once. Thus it separates the user's view from the physical structure of the database.
This separation is desirable for the following reasons:
• Different users need different views of the same data.
• The approach in which a particular user needs to see the data may change over
time.
• The users of the database should not worry about the physical implementation
and internal workings of the database such as data compression and encryption
techniques, hashing, optimization of the internal structures etc.
• All users should be able to access the same data according to their
requirements.
• DBA should be able to change the conceptual structure of the database without
affecting the user's
• Internal structure of the database should be unaffected by changes to physical
Three schema Architecture
• The internal level has an internal schema which describes
the physical storage structure of the database.
• The internal schema is also known as a physical schema.
• It uses the physical data model. It is used to define that how
the data will be stored in a block.
• The physical level is used to describe complex low-level data
structures in detail.
Three schema Architecture
• The conceptual schema describes the design of a database at the
conceptual level. Conceptual level is also known as logical level.
• The conceptual schema describes the structure of the whole database.
• The conceptual level describes what data are to be stored in the database
and also describes what relationship exists among those data.
• In the conceptual level, internal details such as an implementation of the
data structure are hidden.
• Programmers and database administrators work at this level.
Three schema Architecture
• 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.
• An external schema is also known as view schema.
• Each view schema describes the database part that a
particular user group is interested and hides the remaining
database from that user group.
• The view schema describes the end user interaction with
database systems.
Three schema Architecture
• The three schema architecture is also called ANSI/SPARC architecture or three-level
architecture.
• The three levels of DBMS architecture don't exist independently of each other.
There must be correspondence between the three levels i.e. how they actually
correspond with each other. DBMS is responsible for correspondence between the
three types of schema. This correspondence is called Mapping.
 There are basically two types of mapping in the database architecture:
• Conceptual/ Internal Mapping
• External / Conceptual Mapping
 Conceptual/ Internal Mapping
 The Conceptual/ Internal Mapping lies between the conceptual level and the
internal level. Its role is to define the correspondence between the records and
fields of the conceptual level and files and data structures of the internal level.
 External/ Conceptual Mapping
 The external/Conceptual Mapping lies between the external level and the
Conceptual level. Its role is to define the correspondence between a particular
external and the conceptual view.
Data independence

 Data independence is the ability to modify the scheme without

affecting the programs and the application to be rewritten. Data
is separated from the programs, so that the changes made to the
data will not affect the program execution and the application.
 We know the main purpose of the three levels of data abstraction
is to achieve data independence. If the database changes and
expands over time, it is very important that the changes in one
level should not affect the data at other levels of the database.
This would save time and cost required when changing the
database.
Data independence
1. Physical Data Independence means changing the physical
level without affecting the logical level or conceptual level.
Using this property, we can change the storage device of the
database without affecting the logical schema.
 The changes in the physical level may include changes using the
following −
• A new storage device like magnetic tape, hard disk, etc.
• A new data structure for storage.
• A different data access method or using an alternative files
organization technique.
• Changing the location of the database.
Data independence
2. Logical Data Independence
 Logical view of data is the user view of the data. It presents data
in the form that can be accessed by the end users.
 Codd’s Rule of Logical Data Independence says that users should
be able to manipulate the Logical View of data without any
information of its physical storage. Software or the computer
program is used to manipulate the logical view of the data.
 The changes in the logical level may include −
• Change the data definition.
• Adding, deleting, or updating any new attribute, entity or
relationship in the database.
Structure Components, and
Functions of DBMS
Structure Of DBMS
Structure Of DBMS
1. Query Processor: It interprets the requests (queries)
received from end user via an application program into
instructions. It also executes the user request which is received
from the DML compiler.
 Query Processor contains the following components –
 DML Compiler: It processes the DML statements into low level
instruction (machine language), so that they can be executed.
 DDL Interpreter: It processes the DDL statements into a set of
table containing meta data (data about data).
 Embedded DML Pre-compiler: It processes DML statements
embedded in an application program into procedural calls.
 Query Optimizer: It executes the instruction generated by
DML Compiler.
Structure Of DBMS
2. Storage Manager: Storage Manager is a program that provides an interface
between the data stored in the database and the queries received. It is also known
as Database Control System. It maintains the consistency and integrity of the
database by applying the constraints and executing the DCL statements. It is
responsible for updating, storing, deleting, and retrieving data in the database.
 It contains the following components –
 Authorization Manager: It ensures role-based access control, i.e,. checks whether
the particular person is privileged to perform the requested operation or not.
 Integrity Manager: It checks the integrity constraints when the database is
modified.
 Transaction Manager: It controls concurrent access by performing the operations
in a scheduled way that it receives the transaction. Thus, it ensures that the
database remains in the consistent state before and after the execution of a
transaction.
 File Manager: It manages the file space and the data structure used to represent
information in the database.
 Buffer Manager: It is responsible for cache memory and the transfer of data
between the secondary storage and main memory.
Structure Of DBMS
3. Disk Storage: It contains the following components –
 Data Files: It stores the data.
 Data Dictionary: It contains the information about the structure
of any database object. It is the repository of information that
governs the metadata.
 Indices: It provides faster retrieval of data item.
Components of DBMS
Same as ch-1
Functions of DBMS
 Data Storage Management
One of the most important tasks for DBMS is to create a
database for complex data and manage the data. It gives relief
to the user by creating a structure for the complex data sets so
that users can access it and manipulate them very easily.
 Security Management
Database systems provide a high level of security measures
using various security algorithms to keep the data safe and
ensure the data privacy. There are certain security rules that
ensure what data can be accessed from the database and
which user can access it. It is very important for the
organizations where multi-user databases are required.
Functions of DBMS
 Backup and Recovery Management
To keep the data safe and ensure the integrity, the database system provides the
features for backup and recovery management. If the system fails due to some
reason then it recovers the data and keeps the data safe.
 Database Access Language and Application Programming Interface
DBMS provides a database access language which is also called a query language.
Query languages are non-procedural languages used to access the database and
manipulate the data. SQL is an example of a query language.
Data Dictionary Management
Data dictionary management is also useful functionality provided by the Database
Management System. In the data dictionary, it stores the data and its related
information about its relationship.
Data Transformation and Presentation
DBMS provides the functionality of data transformation, which means
programmers need not worry about the logical and physical representation of the
data. DBMS stores the data in the determined data structure. For example, if a
user asks for the date from a database and he receives it as 14 December 2022,
but in the database, it is stored in different columns of month, date and year.
 Functions of DBMS
 Multi User Access Control
Multi User Access control is another feature which is provided by the
modern Database Systems. So, more than one user can access the
database at the same time without any problem. It is very useful for the
database of organizations where multiple database engineers are working
concurrently.
 Data Integrity Management
Database systems provide data integrity management by maximizing the
data consistency and minimizing the data redundancy. The data
dictionary is the feature database system used to store the relationships
of the data to keep the data integrity. Data integrity is needed where a
transaction based database system is present.
 Database Communication Interface
When a user requests data from the database, it uses some environments
like browsers (Chrome or Firefox etc.) to get the data.
Services Of DBMS

 The service provided by the DBMS includes:

 Authorization services like log on to the DBMS start the
database stop the Database etc.
 Transaction supports like Recovery, Rollback etc,
 Import and Export of Data.
 Maintaining data dictionary
 User's Monitoring
Data Models
The structure of the database is called the data models: A Collection of
conceptual tools for describing data, data relationship, data semantic and
consistency constraint. There are three different groups.
Object-based logical models
 The entity-relationship model
 The object-oriented model
 The semantic data model
 The functional data model
Record-based logical models
 Relational model
 Network model
 Hierarchical model
Physical models
 Unifying model
 Frame-memory model
Object-based logical models

 Object oriented data model is also based on using real life scenarios. In
this model, the scenarios are represented as objects. The objects with
similar functionalities are grouped together and linked to different other
objects.
The entity-relationship model
 Entity-Relationship (ER) Model is based on the notion of real-world entities and
relationships among them. While formulating real-world scenario into the
database model, the ER Model creates entity set, relationship set, general
attributes and constraints.
 Entity − An entity in an ER Model is a real-world entity having properties called
attributes. Every attribute is defined by its set of values called domain. For
example, in a school database, a student is considered as an entity. Student
has various attributes like name, age, class, etc.
 Relationship − The logical association among entities is called relationship.
Relationships are mapped with entities in various ways. Mapping cardinalities
define the number of association between two entities.
The object-oriented model
 The three main building blocks of the OODBMS are object structure,
object classes, and object identity. The following explains them.
 Object Structure: An object's structure refers to the components that
make up the object. An attribute is a term used to describe certain
characteristics of an item. A real-world entity with certain qualities that
makes up the object structure is hence referred to as an object.
 Messages - A message operates as a communication channel or as an
interface between an entity and the outside world. There are two sorts
of messages:
• Read-only message: The invoking message is referred to as a read-only
message if the called method does not alter the value of a variable.
• Update message: The invoking message is referred to as an update
message if the invoked method modifies the value of a variable.
The object-oriented model
 Methods: A method is a chunk of code that is executed when a message is given. Every time a method is used, a
value is returned as output. There are two categories of methods
• :Read-only method: A method is referred to as a read-only method when it has no effect on the value of a
variable.
• Update-method: An update method is one that modifies a variable's value in some way.
 Variables are used to store an object's data. The variables' data allows the objects to be distinguished from one
another.
 Object Classes: An instance of a class is an object, which is a real-world item. In order to create objects that differ
in the data they hold but have the same class definition, a class must first be defined. Messages and variables
recorded in the objects themselves relate to the objects in turn.
 class JOB
{ //variables
char name;
string address;
int id;
int salary;
//Messages
char get_name();
string get_address();
int annual_salary();
};
The semantic data model
Record-based logical models

 When the database is organized in some fixed format of records

of several than the model is known as Record-Based Data Model.
It has a fixed number of fields or attributes in each record type
and each field is usually of a fixed length.
Hierarchical Data Model
 In hierarchical type, the model data are represented by collection of
records. In this, relationships among the data are represented by links. In
this model, tree data structure is used.
 The basic logic structure of hierarchical data model is upside-down
“tree”.
 Network Data Model
 In network type, the model data are represented by collection of records.
In this, relationships among the data are represented by links. Graph data
structures are used in this model. It permits a record to have more than
one parent.
 For Example- Social Media sites like Facebook, Instagram etc.
Relational Data Model
 Relational Data Model uses tables to represent the data and the
relationship among these data. Each table has multiple columns and
each column is identified by a unique name. It is a low-level model.
Types of Database Systema
Centralized Database Systems
 A centralized database is stored at a single location such as a mainframe
computer. It is maintained and modified from that location only and usually
accessed using an internet connection such as a LAN or WAN.
 The centralized database is used by organizations such as colleges, companies,
banks etc.
Parallel Database Systems
 Nowadays organizations need to handle a huge amount of data with a high
transfer rate. For such requirements, the client-server or centralized system
is not efficient.
 A parallel database system seeks to improve the performance of the system
through parallelizing concept.
 Multiple resources like CPUs and Disks are used in parallel. The operations
are performed simultaneously, as opposed to serial processing.
 A parallel server can allow access to a single database by users on multiple
machines.
 It also performs many parallelization operations like data loading, query
processing, building indexes, and evaluating queries.
 Two main performance measures:
throughput --- the number of tasks that can be completed in a given time
interval
response time --- the amount of time it takes to complete a single task from the
time it is submitted
Performance Measurement of Databases :
Here, we will emphasize the performance measurement factor-like
Speedup and Scale-up. Let’s understand it one by one with the help
of examples.
 Speedup –
The ability to execute the tasks in less time by increasing the
number of resources is called Speedup.
Speedup= time original/time parallel
Where ,
time original = time required to execute the task using 1 processor
time parallel = time required to execute the task using 'n'
processors
Example:-

A CPU requires
3 minutes to
execute a
process

‘n’ CPU requires

1 min to execute
a process by
dividing into
smaller tasks
 Scale-up –
The ability to maintain the performance of the system when both workload
and resources increase proportionally.
Scaleup = Volume Parallel/Volume Original
Where ,
Volume Parallel = volume executed in a given amount of time using 'n' processor
Volume Original = volume executed in a given amount of time using 1 processor
 Example –
20 users are using a CPU at 100% efficiency. If we try to add more users, then
it’s not possible for a single processor to handle additional users. A new
processor can be added to serve the users parallel. And will provide 200%
efficiency.
 Client-Server Database Systems
 Client-Server Database Architecture is a type of DBMS structure in which
many clients are connected to a common server. It is a model in which a
server (which provides service) sends and manages the resources and
works according to the needs of the client.
 Here, the server hosts, delivers, and manages most of the resources and
services consumed by the client. Different clients request different
services, and the server will respond according to their needs.
 Database functionality can be divided into:
Back-end: manages access structures, query evaluation and
optimization, concurrency control and recovery.
Front-end: consists of tools such as forms, report-writers, and
graphical user interface facilities.
 The interface between the front-end and the back-end is through
SQL or through an application program interface.
 Server systems can be broadly categorized into two kinds:
transaction servers which are widely used in relational database
systems, and
data servers, used in object-oriented database systems
Distributed Database Systems
 A distributed database is essentially a database that is dispersed across
numerous sites, i.e., on various computers or over a network of
computers, and is not restricted to a single system. A distributed
database system is spread across several locations with distinct
physical components. This can be necessary when different people from
all over the world need to access a certain database. It must be handled
such that, to users, it seems to be a single database.
 Types:
1. Homogeneous Database: A homogeneous database stores data
uniformly across all locations. All sites utilize the same operating system,
database management system, and data structures. They are therefore
simple to handle.
2. Heterogeneous Database: With a heterogeneous distributed
database, many locations may employ various software and schema,
which may cause issues with queries and transactions. Moreover, one site
could not be even aware of the existence of the other sites.
 Data may be stored on several places in two ways using distributed
data storage:
1. Replication - With this strategy, every aspect of the connection is
redundantly kept at two or more locations. It is a completely redundant
database if the entire database is accessible from every location. Systems
preserve copies of the data as a result of replication. This has advantages
since it makes more data accessible at many locations. Moreover, query
requests can now be handled in parallel. But, there are some drawbacks as
well. Data must be updated often. All changes performed at one site must
be documented at every site where that relation is stored in order to avoid
inconsistent results. There is a tone of overhead here. Moreover, since
concurrent access must now be monitored across several sites, concurrency
management becomes far more complicated.
2. Fragmentation - In this method, the relationships are broken up into
smaller pieces and each fragment is kept in the many locations where it is
needed. To ensure there is no data loss, the pieces must be created in a way
that allows for the reconstruction of the original relation. As fragmentation
doesn't result in duplicate data, consistency is not a concern.