DBMS Schemas: Internal, Conceptual, External

Database systems comprise of complex data structures. Thus, to make the system efficient for retrieval of data and reduce the complexity of the users, developers use the method of Data Abstraction.

There are mainly three levels of data abstraction:

Internal Level: Actual PHYSICAL storage structure and access paths.
Conceptual or Logical Level: Structure and constraints for the entire database
External or View level: Describes various user views

Let’s study them in detail

Internal Level/Schema

The internal schema defines the physical storage structure of the database. The internal schema is a very low-level representation of the entire database. It contains multiple occurrences of multiple types of internal record. In the ANSI term, it is also called “stored record’.

Facts about Internal schema:

The internal schema is the lowest level of data abstraction
It helps you to keeps information about the actual representation of the entire database. Like the actual storage of the data on the disk in the form of records
The internal view tells us what data is stored in the database and how
It never deals with the physical devices. Instead, internal schema views a physical device as a collection of physical pages

Conceptual Schema/Level

The conceptual schema describes the Database structure of the whole database for the community of users. This schema hides information about the physical storage structures and focuses on describing data types, entities, relationships, etc.

What is Linux Linux Beginner Tutorial

This logical level comes between the user level and physical storage view. However, there is only single conceptual view of a single database.

Facts about Conceptual schema:

Defines all database entities, their attributes, and their relationships
Security and integrity information
In the conceptual level, the data available to a user must be contained in or derivable from the physical level

External Schema/Level

An external schema describes the part of the database which specific user is interested in. It hides the unrelated details of the database from the user. There may be “n” number of external views for each database.

Each external view is defined using an external schema, which consists of definitions of various types of external record of that specific view.

An external view is just the content of the database as it is seen by some specific particular user. For example, a user from the sales department will see only sales related data.

Facts about external schema:

An external level is only related to the data which is viewed by specific end users.
This level includes some external schemas.
External schema level is nearest to the user
The external schema describes the segment of the database which is needed for a certain user group and hides the remaining details from the database from the specific user group

Goal of 3 level/schema of Database

Here, are some Objectives of using Three schema Architecture:

Every user should be able to access the same data but able to see a customized view of the data.
The user need not to deal directly with physical database storage detail.
The DBA should be able to change the database storage structure without disturbing the user’s views
The internal structure of the database should remain unaffected when changes made to the physical aspects of storage.

Advantages Database Schema

You can manage data independent of the physical storage
Faster Migration to new graphical environments
DBMS Architecture allows you to make changes on the presentation level without affecting the other two layers
As each tier is separate, it is possible to use different sets of developers
It is more secure as the client doesn’t have direct access to the database business logic
In case of the failure of the one-tier no data loss as you are always secure by accessing the other tier

Disadvantages Database Schema

Complete DB Schema is a complex structure which is difficult to understand for every one
Difficult to set up and maintain
The physical separation of the tiers can affect the performance of the Database

Summary

There are mainly three levels of data abstraction: Internal Level, Conceptual or Logical Level or External or View level
The internal schema defines the physical storage structure of the database
The conceptual schema describes the Database structure of the whole database for the community of users
An external schema describe the part of the database which specific user is interested in
DBMS Architecture allows you to make changes on the presentation level without affecting the other two layers