 Database Architecture: The design of a DBMS depends on its

architecture. It can be centralized or decentralized. The architecture of a DBMS

can be seen as either single tier or multi-tier. An n-tier architecture divides the
whole system into related but independent n modules, which can be
independently modified, altered, changed, or replaced.

Three-level architecture has three levels: External (view) level, Conceptual

(logical) level, and physical (internal) level. The three levels of the architecture
provide the database abstraction. The data at each level is described by the schema
of that level.

The external view is defined by the external schema, the logical view is defined by
the conceptual schema and the internal view is defined by the internal schema. A
database schema is a blueprint of database that defines how data is organized
within a relational database.
User-1 User-2 User-n
External level describes External External External
the data relevant to a View-1 View-2 View-n
particular group of users
External
Level

External to Conceptual mapping

Conceptual level describes the

Conceptual Level
Logical Structure of data

Conceptual to internal mapping

Physical level describes the

Physical Level
physical Structure of data

Disk

Figure: Three-level architecture of DBMS

1
(i) External or User view Level: The external view describes how the users want
to see the data. This level provides the different data views to different types of
users, so external view may be defined as subset of the stored database. The
external views define the relevant data for application programs and hide the
rest of the database. Therefore, they provide the highest level of database
abstraction.

The mapping from the conceptual level to external level is done by the DBMS
at the run time. When the conceptual schema is modified, the external schema
is not modified but the mapping has to be modified. This is known as logical
data independence.

Example: If there is a relation named as student (Roll No., Name, Course,

Age, DoJ, Address), where one user may want to see only the Roll No., Name
& Course. Similarly, other user may want to see the Name, Age, and Address.

(ii) Conceptual Level: It describes the logical structure of all the database entities
and the relationships among them. Hence only one conceptual view is defined
and stored for the whole database of any organization.

This view focuses on what data is actually stored in database and relationship
among the data. It also specifies the checks or constraint to retain data
consistency and maintain data integrity.

When the physical schema is modified, the conceptual schema is not modified
but the mapping has to be modified. This is known as physical data
independence.

Example: If students registered in any department then below two entities are
considered & both are related with relationship “registered”.
a. Student (Roll No., Name, Course, Age, DoJ, Address) check Age >17
b. Department (DNo., DNAME, HOD)

2
(iii) Physical (Internal) view: The physical level describes how the data are
stored on disk and it provides the lowest level of data abstraction. The
internal view describes the data structure and file organization for all the
database entities and the access methods to be used by the database.

It is defined by the internal schema to describe the physical representation of

the database. The physical level is responsible for allocating the storage space
for the data on disk and it builds the indexes and retrieves the data.

 Data Independence: Data independence is one of the principal functions of

DBMS. In the data independence, , the structure of the database and the values
stored in the database can easily be modified without changing the application
program that process the data.

The three levels of abstraction and the mapping between these levels are
responsible for providing data independence. In other words, data independence
may be defined as the capability of changing one schema without changing the
schema at next higher level.

There are two levels of data independence: Logical data independence and
Physical data independence.

External view defined by

External Level external schema

External to conceptual mapping Logical Data Independence

Conceptual view defined

Conceptual Level by conceptual schema

Conceptual to internal mapping Physical Data Independence

Internal view defined by

Physical Level
internal schema

Figure: Data Independence in DBMS

3
(i) Logical data independence: Logical data independence is defined as the
ability to change the conceptual schema without changing the existing
external schema and we need not rewrite the application program.

The external schema describes only that portion of the database in which the
users are interested. The changes made in the logical schema are adjusted by
the mapping provided by the DBMS between the conceptual schema and the
external schema.

Example: if we add some extra entities, attributes or relationships into the

database, then we need not change the external schema.

(ii) Physical Data Independence: Physical data independence is defined as the

ability to change the internal schema without changing the conceptual schema
and we need not rewrite the application program.

The internal schema describes different storage structures, file organizations

or devices used for storing the data and the access methods used to retrieve
the data from the database. So the internal schema describes how data are
actually stored in a database and how to access them.

The changes made in the internal schema, file organization or the storage
devices are adjusted by the mapping provided by the DBMS between the
internal schema and the conceptual schema.

 Data Dictionary: The data dictionary is the mini DBMS that manage a
variety of information such as database structure, set of relationships, data
types, size of data types, constraints, authorizations, etc, about the contents of
the database.

It contains the metadata (a data about data) which provides data independence
in DBMS. Metadata is a complete framework of the database and a database is
maintained separately for it.

4
Data dictionary is the integral part of DBMS and used as an information
repository for metadata and the common code. Hence, the different schemas for
the database can be controlled through the data dictionary.

It provides the central storage of information to the database; therefore, the data
can easily be accessed, inconsistency can be reduced, the constraints can easily
be imposed and data independence can be incorporated.

 Database Administrator: DMBS is the centralized control of the database.

Database Administrator (DBA) is a person or group of persons that responsible
for centralized controlling of the various resources of database system such as
database, software, hardware, etc.

The DBA provides necessary technical support in creating and maintaining the
database, authorizing access to the database, ensuring security of the database,
etc. so the DBA is responsible for the overall control of the database.

Functions of DBA: some of the functions of DBA are as follows:

(i) Designing the overall database: According to the user’s need, the DBA
creates the structure of the database by using the data definition language
(DDL). The DBA also puts the actual values in the database corresponding to
the different attributes.

(ii) Defining the physical storage structure: The DBA is authorized for
deciding and modifying the storage structure of the database according
requirement of organization.

(iii) Granting Authorization: DBA has authority to grants the different types of
authorization access of the database to different types of associated users.

(iv) Enforcing integrity constraint: When multiple users share a large

database, it is likely that database may not remain integral. So, the DBA

5
decides the constraints to be imposed on different entities or attributes to
ensure database integrity.

(v) Helping in routine Maintenance: The DBA is responsible for

o Coordinating and monitoring the use of DBMS;
o Providing enough free storage space for normal operations of the DBMS;
o Monitoring performance
o Responding to changes in requirements;
o Managing periodic backups;
o Repairing damage due to improper use of hardware and/or software
o Acquiring hardware and software resources in case of failure or as required
o Ensuring Security of database

 Database Languages: There are three schemas corresponding to three

levels of the DBMS. There should be some languages to specify the internal,
conceptual and external schema and to enable the user to feed the data into the
schema (structure of the database), to access the database and modify it. These
languages are called DBMS languages and can be broadly classified into two
categories:

(i) Data Definition Language (DDL): DDL stands for data definition language
and it is used to define the structures like schema, database, tables, constraints
etc..

The DDL statements are compiled using the DDL compiler, which generates
a set of tables that are stored in data dictionary. Common commands of DDL
statements include CREATE, ALTER, DROP, TRUNCATE etc.

CREATE Statement: The create command is used to establish a new

database, table, index, or stored procedure.

DROP statement: It destroys an existing database, table, index, or view.

6
ALTER statement: It modifies an existing database object.

TRUNCATE statement: It is used to delete all data from a table. It's much
faster than DELETE

The DDL statements are used to perform the following task:

a. Defines the schema and mapping between them

b. Creating and modifying database objects such as tables, indices, and users.
c. Store values of the attributes or tuples into the schema or table
d. Impose constraint on the tables or attributes.
e. The storage structure of each table on the disk and the access method are
specified by special DDL statements called the data storage language (DSL).
f. The DSL is used to specify the internal schema of the database and the
mapping between the internal schema and conceptual schema.
g. The data dictionary is created as the database schema is defined.
h. A data dictionary is updated automatically by DDL statements as the
database is modified.

(ii) Data Manipulation Language (DML): A Data Manipulation Language

(DML) is a computer programming language used for inserting, deleting, and
modifying data in a database. The common commands of DML are as follows

SELECT: Retrieve data from the database.

INSERT: Insert data into a table.
UPDATE: Update existing data within a table.
DELETE: Delete records from a database table

The DMLs are also categorized into two categories:

(i) Procedural DML: In this language, the users have to specify not only the
data to be retrieved but also the method (how) to retrieve it. Relational
algebra is an example of procedural DML.

7
(ii) Non-procedural DML: In this language, the users have to specify only the
data to be retrieved without specifying the method (how) to retrieve it.
Relational calculus is an example of non-procedural DML.

The DML statements that request only the retrieval of the data are also
known as query statements. In some DBMS like SQL (Structured Query
Language), the DDL and DML are combined together.