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:

1. Internal Level: Actual PHYSICAL storage structure and access paths.
2. Conceptual or Logical Level: Structure and constraints for the entire database
3. 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.

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