UNIT-I

DATA BASE MANAGEMENT SYSTEM

(PART-2)
Data Abstraction
• Database systems comprise complex data structures. In order
to make the system efficient in terms of retrieval of data, and
reduce complexity in terms of usability of users, developers
use abstraction i.e. hide irrelevant details from the users. This
approach simplifies database design.

• The developers keep away the complex data from the user and
remove the complications so that the user can comfortably
access data in the database and can only access the data they
want, which is done with the help of data abstraction.
• With the help of data abstraction, developers hide irrelevant
data from the user and provide them the relevant data. By
doing this, users can access the data without any hassle, and
the system will also work efficiently.
Level of Abstraction in a DBMS

• There are mainly 3 levels of data abstraction

1. Physical or Internal Level

2. Logical or Conceptual Level

3. View or External Level

Physical Level or Internal Level
• This is one of the layer of data abstraction where the raw data
is physically stored as files.

• This layer contains all the complex data structures and the data
accessing methods defined.

• The physical layer is the lowest level of data abstraction in

a DBMS.

• Database administrator decides how and where to store the

data in database and how the data is to be stored in these
physical hard drives.
• Physical level deals with actual storage details like data
organization, disk space allocation and data access methods.

• There is a data center that securely stores the raw data in detail
on hard drives at this level.

Example:

• When we access data we may get a single data or a table of

data.Moreover, by the term "relational database" we visualize
a table of rows and columns.But at a physical level, these
tables are stored in hard drives which are located at a very
secure data center.
Logical or Conceptual Level
• After taking the raw data from the physical or internal level,
the structure of the data is defined at the logical or conceptual
level.

• This is like a blueprint of the raw data.

• This layer does not have any information about how the end
user will view the data.

• It is intermediate level present next to physical level.

• It defines what data is present in database and their

relationships between them.
• It is less complex as compared to physical level.

• Programmers generally work at this level and depending on

data, structure of tables, relationships and their constraints is
decided at this level.
Example

• We have data of a few products like product id, product name,

and manufacturing date, and we have another set of data of
customers containing customer id, customer name, and
customer address.

• Now, we need to frame this data in proper tables of products

and customers. After that, we can even frame a join to show
which product has been ordered by which customer.
View or External Level
• View Level is the highest level of data abstraction in DBMS.

• This level is for the end-user interaction, at this level, end

users can access the data based on their queries.

• Users view data in the form of rows and columns. Tables and
relations are used to store data.

• Users can just view the data and interact with the database,
storage and implementation details are hidden from them.
Conclusion
• Data abstraction in DBMS means hiding unnecessary
background details from the end user to make the accessing of
data easy and secure.
• In DBMS, there are 3 levels of data abstraction:
• Physical Level or Internal Level: This is the layer where the
raw data is stored in file format on physical hard drives.

• Logical Level or Conceptual Level: In this layer, the raw

data is taken from the physical layer and organized in a proper
structure, like in tabular format.

• View Level or External Level: At this level, the end users get
the data depending on the queries. The same data can be
viewed in multiple ways, like tables, graphs, or pie charts.
3 Tier Schema Architecture in DBMS
• In DBMS, the 3-tier architecture is a client-server architecture
that separates the user interface, application processing, and
data management into three distinct tiers or layers.

• The 3-tier architecture is widely used in modern web

applications and enterprise systems

• A 3-tier architecture separates its tiers from each other based

on the complexity of the users and how they use the data
present in the database.

• It is the most widely used architecture to design a DBMS.

3 Tier Schema Architecture
Presentation Tier

• The presentation tier is the user interface or client layer of the

application.

• It is responsible for presenting data to the user and receiving

input from the user.

• This tier can be a web browser, mobile app, or desktop

application.

• End-users operate on this tier and they know nothing about

• any existence of the database beyond this layer.

• At this layer, multiple views of the database can be provided
by the application.

• All views are generated by applications that reside in the

application tier.
Application Tier:

• The application tier is the middle layer of the 3-tier

architecture.

• It is responsible for processing and managing the business

logic of the application.

• This tier communicates with the presentation tier to receive

user input and communicates with the data management tier to
retrieve or store data.

• This tier may include application servers, web servers, or APIs.

• At this tier reside the application server and the programs that
access the database.

• For a user, this application tier presents an abstracted view of

the database.

• End-users are unaware of any existence of the database

beyond the application.

• At the other end, the database tier is not aware of any other
user beyond the application tier.

• Hence, the application layer sits in the middle and acts as a

mediator between the end-user and the database.
Data Management Tier:

• The data management tier is the bottom layer of the 3-tier

architecture.

• It is responsible for managing and storing data.

• This tier can include databases, data warehouses, or data lakes.

• The data management tier communicates with the application

tier to receive or store data.

• At this tier, the database resides along with its query

processing languages.

• We also have the relations that define the data and their
constraints at this level.
Benefits of 3-Tier Architecture

The 3-tier architecture in DBMS provides several benefits,

including

Scalability: The architecture separates the application processing

and data management layers, which allows for easy scalability of
each layer independently.

Flexibility: The architecture allows for the replacement or upgrade

of one layer without affecting the other layers.

Security: The architecture provides an additional layer of security,

as the data management tier can be isolated from the application
and presentation tiers, reducing the risk of unauthorized access.
Database System structure environment
• A database environment is a collection of components that
regulates the use of data, management, and a group of data.

• These components consist of people, the technique of Handel

the database, data, hardware, software, etc. there are several
components available for the DBMS.

• Here, the hardware in a database environment means the

computers and computer peripherals that are being used to
manage a database, and the software means the whole thing
right from the operating system (OS) to the application
programs that include database management software like M.S.
Access or SQL Server.
• The database system consists of many components. Each
component performing very significant tasks in the database
management system environment.

• The database management system can be divided into five

major components, they are

1.Hardware

2.Software

3.Data

4.Procedures

5.People
Structure of Database Management System
• The database system is divided into three classifications

• Query Processor,

• Storage Manager, and

• Disk Storage.
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 –

1. DML Compiler (Data Manipulation Language):

It processes the DML statements into low level

instruction (machine language), so that they can be executed.
2. DDL Interpreter ( Data Definition Language):

– It processes the DDL statements into a set of table

containing meta data (data about data).

3. Embedded DML Pre-compiler:

– It processes DML statements embedded in an application

program into procedural calls.

4. Query Optimizer:

– It executes the instruction generated by DML Compiler.

– It determines an optimization strategy for the query

execution.
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(Data Control
Language) statements.

• It is responsible for updating, storing, deleting, and retrieving

data in the database.
1. Authorization Manager: It ensures role-based access control,
i.e,. checks whether the particular person is privileged to
perform the requested operation or not.

2. Integrity Manager: It checks the integrity constraints when

the database is modified.

3. 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.
4. File Manager: It manages the file space and the data structure
used to represent information in the database.

5. Buffer Manager: It is responsible for cache memory and the

transfer of data between the secondary storage and main
memory.
Disk Storage: It contains the following components –

1. Data Files: It stores the data.

2. Data Dictionary: It contains the information about the

structure of any database object. It is the repository of
information that governs the metadata.

3. Indices: It provides faster retrieval of data item.

Constraints of DBMS

• In DBMS, constraints refer to limitations placed on data or

data processes.

• This indicates that only a particular type of data may be

entered into the database or that only a particular sort of
operation can be performed on the data inside.
Constraints in SQL

• NOT NULL

• UNIQUE

• CHECK

• DEFAULT

• PRIMARY KEY

• FOREIGN KEY
• The following can be guaranteed via constraints

• Data Accuracy

• Data Consistency

• Data integrity
Types of Constraints in DBMS

• Domain Constraints

• Key Constraints

• Entity Integrity Constraints

• Referential Integrity Constraints

• Tuple Uniqueness Constraints

Superclass: The class or table whose methods and attributes are
inherited is called the superclass or base class. Another name for
it is the parent class.

Subclass/Derived Class :

• A subclass is a class derived from the superclass.

• It inherits the properties of the superclass and also contains

attributes of its own. An example is:

• In an inheritance structure, a subclass is a class or table that

receives some methods and attributes from another class.
Another name for it is the child class.
Inheritance

• "Inheritance is defined as a mechanism where the sub or child

class inherits the properties and characteristics of the super
class or other derived classes.

• It also supports additional features of extracting properties

from the child class and using it into other derived classes."

• Inheritance in a database management system (DBMS)

entity-relationship (ER) diagram is a mechanism that allows
subtypes to inherit attributes and relationships from their
supertype
• In an ER diagram, inheritance is denoted by an arrow that
points from the child entity to the parent entity. The child
entity inherits all parent's attributes, their types, and whether
they're mandatory and are primary identifiers.
• Using the ER model for bigger data creates a lot of complexity
while designing a database model.

• So in order to minimize the complexity Generalization,

Specialization, and Aggregation were introduced in the ER
model.

• These were used for data abstraction in which an abstraction

mechanism is used to hide details of a set of objects.
Generalization

• Generalization is the process of extracting common properties

from a set of entities and creating a generalized entity from it.

• It is a bottom-up approach in which two or more entities can

be generalized to a higher-level entity if they have some
attributes in common.

• Generalization is also called as ‘ Bottom-up approach”.

• In this low level entities combine to form a higher level entity.

• For Example, STUDENT and FACULTY can be generalized
to a higher-level entity called PERSON as shown in Figure

• In this case, common attributes like P_NAME, and P_ADD

become part of a higher entity (PERSON), and
specialized attributes like S_FEE become part of a specialized
entity (STUDENT).
Specialization

• In specialization, an entity is divided into sub-entities based on

its characteristics.

• It is a top-down approach where the higher-level entity is

specialized into two or more lower-level entities.
Aggregation

• Aggregation is a process in which the relation between two

entities treated as a single entity

• An ER diagram is not capable of representing the relationship

between an entity and a relationship which may be required in
some scenarios.

• In those cases, a relationship with its corresponding entities is

aggregated into a higher-level entity.

• Aggregation is an abstraction through which we can

represent relationships as higher-level entity sets.
Database Languages

• Data Definition Language(DDL)

• Data Manipulation Language(DML)

• Data Control Language(DCL)

• Transactional Control Language(TCL)

• Data Query Language (DQL)

• Data Definition Language

• DDL is the short name for Data Definition Language, which

deals with database schemas and descriptions, of how the data
should reside in the database.

• CREATE: to create a database and its objects like (table, index,

views, store procedure, function, and triggers)

• ALTER: alters the structure of the existing database

• DROP: delete objects from the database

• TRUNCATE: remove all records from a table, including all
spaces allocated for the records are removed

• COMMENT: add comments to the data dictionary

• RENAME: rename an object

• Data Manipulation Language

• DML is the short name for Data Manipulation Language

which deals with data manipulation and includes most
common SQL statements such SELECT, INSERT, UPDATE,
DELETE, etc., and it is used to store, modify, retrieve, delete
and update data in a database.
• Data query language(DQL) is the subset of “Data
Manipulation Language”.

• The most common command of DQL is SELECT statement.

SELECT statement help on retrieving the data from the table
without changing anything in the table.

• SELECT: retrieve data from a database

• INSERT: insert data into a table

• UPDATE: updates existing data within a table

• DELETE: Delete all records from a database table

• MERGE: UPSERT operation (insert or update)

• CALL: call a PL/SQL or Java subprogram

• EXPLAIN PLAN: interpretation of the data access path

• LOCK TABLE: concurrency Control

Data Control Language

• DCL is short for Data Control Language which acts as an

access specifier to the database.(basically to grant and revoke
permissions to users in the database

• G R A N T: g r a n t p e r m i s s i o n s t o t h e u s e r f o r r u n n i n g
DML(SELECT, INSERT, DELETE,…) commands on the
table

• REVOKE: revoke permissions to the user for running

DML(SELECT, INSERT, DELETE,…) command on the
specified table
Transactional Control Language

• TCL is short for Transactional Control Language which acts as

an manager for all types of transactional data and all
transactions. Some of the command of TCL are

• Roll Back: Used to cancel or Undo changes made in the

database

• Commit: It is used to apply or save changes in the database

• Save Point: It is used to save the data on the temporary basis

in the database
Data Query Language (DQL):

• Data query language(DQL) is the subset of “Data

Manipulation Language”.

• The most common command of DQL is 1the SELECT

statement.

• SELECT statement helps us in retrieving the data from the

table without changing anything or modifying the table.

• DQL is very important for retrieval of essential data from a

database.