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.

Data Independence is defined as a property of DBMS that helps you to change the Database
schema at one level of a database system without requiring to change the schema at the next
higher level. Data independence helps you to keep data separated from all programs that make
use of it.

Types of Data Independence

There are two types of data independence:

1. Physical data independence

2. Logical data independence

Physical Data Independence

Physical data independence helps you to separate conceptual levels from the internal/physical
levels. It allows you to provide a logical description of the database without the need to specify
physical structures. Compared to Logical Independence, it is easy to achieve physical data
independence.

With Physical independence, you can easily change the physical storage structures or devices
with an effect on the conceptual schema. Any change done would be absorbed by the mapping
between the conceptual and internal levels. Physical data independence is achieved by the
presence of the internal level of the database and then the transformation from the conceptual
level of the database to the internal level.

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.

Examples of changes under Physical Data Independence

Due to Physical independence, any of the below change will not affect the conceptual layer.
  Using a new storage device like Hard Drive or Magnetic Tapes
 Modifying the file organization technique in the Database
 Switching to different data structures.
 Changing the access method.
 Modifying indexes.
 Changes to compression techniques or hashing algorithms.
 Change of Location of Database from say C drive to D Drive

Logical Data Independence

Logical Data Independence is the ability to change the conceptual scheme without changing

1. External views
2. External API or programs

Any change made will be absorbed by the mapping between external and conceptual levels.

When compared to Physical Data independence, it is challenging to achieve logical 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.

Examples of changes under Logical Data Independence

Due to Logical independence, any of the below change will not affect the external layer.

1. Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of

existing application programs
2. Merging two records into one
3. Breaking an existing record into two or more records
Difference between Physical and Logical Data Independence
Logica Data Independence Physical Data Independence

Logical Data Independence is mainly concerned Mainly concerned with the storage of the data.
with the structure or changing the data definition.

It is difficult as the retrieving of data is mainly It is easy to retrieve.

dependent on the logical structure of data.

Compared to Logic Physical independence it is Compared to Logical Independence it is easy to

difficult to achieve logical data independence. achieve physical data independence.

You need to make changes in the Application A change in the physical level usually does not
program if new fields are added or deleted from need change at the Application program level.
the database.
 Modification at the logical levels is significant Modifications made at the internal levels may or
whenever the logical structures of the database may not be needed to improve the performance of
are changed. the structure.

Concerned with conceptual schema Concerned with internal schema

Example: Add/Modify/Delete a new attribute Example: change in compression techniques,

hashing algorithms, storage devices, etc

Importance of Data Independence

 Helps you to improve the quality of the data

 Database system maintenance becomes affordable
 Enforcement of standards and improvement in database security
 You don't need to alter data structure in application programs
 Permit developers to focus on the general structure of the Database rather than worrying
about the internal implementation
 It allows you to improve state which is undamaged or undivided
 Database incongruity is vastly reduced.
 Easily make modifications in the physical level is needed to improve the performance of
the system.

Summary

 Data Independence is the property of DBMS that helps you to change the Database
schema at one level of a database system without requiring to change the schema at the
next higher level.
 Two levels of data independence are 1) Physical and 2) Logical
 Physical data independence helps you to separate conceptual levels from the
internal/physical levels
 Logical Data Independence is the ability to change the conceptual scheme without
changing
 When compared to Physical Data independence, it is challenging to achieve logical data
independence
 Data Independence Helps you to improve the quality of the data

Database Language
o A DBMS has appropriate languages and interfaces to express database queries and
updates.
o Database languages can be used to read, store and update the data in the database.
Types of Database Language
Data Definition Language
o DDL stands for Data Definition Language. It is used to define database structure or
pattern.
o It is used to create schema, tables, indexes, constraints, etc. in the database.
o Using the DDL statements, you can create the skeleton of the database.
o Data definition language is used to store the information of metadata like the
number of tables and schemas, their names, indexes, columns in each table,
constraints, etc.

Here are some tasks that come under DDL:

o Create: It is used to create objects in the database.

o Alter: It is used to alter the structure of the database.
o Drop: It is used to delete objects from the database.
o Truncate: It is used to remove all records from a table.
o Rename: It is used to rename an object.
o Comment: It is used to comment on the data dictionary.

These commands are used to update the database schema that's why they come under
Data definition language.

2. Data Manipulation Language

DML stands for Data Manipulation Language. It is used for accessing and manipulating data
in a database. It handles user requests.

Here are some tasks that come under DML:

o Select: It is used to retrieve data from a database.

o Insert: It is used to insert data into a table.
o Update: It is used to update existing data within a table.
o Delete: It is used to delete all records from a table.
o Merge: It performs UPSERT operation, i.e., insert or update operations.
o Call: It is used to call a structured query language or a Java subprogram.
o Explain Plan: It has the parameter of explaining data.
o Lock Table: It controls concurrency.
3. Data Control Language
o DCL stands for Data Control Language. It is used to retrieve the stored or saved
data.
o The DCL execution is transactional. It also has rollback parameters.

(But in Oracle database, the execution of data control language does not have the
feature of rolling back.)

Here are some tasks that come under DCL:

o Grant: It is used to give user access privileges to a database.

o Revoke: It is used to take back permissions from the user.

There are the following operations which have the authorization of Revoke:

CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

4. Transaction Control Language

TCL is used to run the changes made by the DML statement. TCL can be grouped into a
logical transaction.

Here are some tasks that come under TCL:

o Commit: It is used to save the transaction on the database.

o Rollback: It is used to restore the database to original since the last Commit.

Structure of Database Management System

Database Management System (DBMS) is a software that allows access to data stored
in a database and provides an easy and effective method of –
 Defining the information.
 Storing the information.
 Manipulating the information.
 Protecting the information from system crashes or data theft.
 Differentiating access permissions for different users.
The database system is divided into three components: Query Processor, Storage
Manager, and Disk Storage. These are explained as following below.
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.
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
executes 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.
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.