DBMS Schemas: Internal, Conceptual, External

What Is Data Abstraction in DBMS?

Database systems are built on complex data structures. To make the system efficient for data retrieval and to reduce complexity for users, developers use the method of data abstraction. Data abstraction hides the details of how data is stored and maintained, and exposes only what each type of user needs to see.

There are mainly three levels of data abstraction:

1. Internal Level: The actual physical storage structure and access paths.
2. Conceptual or Logical Level: The structure and constraints for the entire database.
3. External or View Level: Describes various user views.

These three levels are described by three corresponding schemas, together known as the ANSI-SPARC three-level architecture. A schema is the design or description of the database at one level; the data itself is called the instance. The diagram below shows how the levels sit above one another.

Let us study each level in detail, starting from the storage layer and moving up toward the user.

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 ANSI terms, it is also called a “stored record”.

Facts about internal schema:

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

Once the storage layer is defined, the logical design that sits above it is described by the conceptual schema.

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, and so on.

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

Facts about conceptual schema:

• Defines all database entities, their attributes, and their relationships.
• Holds security and integrity information.
• At the conceptual level, the data available to a user must be contained in, or derivable from, the physical level.

The conceptual design is a natural fit for a relational data model, where entities become tables and relationships become keys. Above the conceptual level, individual users see only the slice of data relevant to them, described by the external schema.

External Schema / Level

An external schema describes the part of the database that a 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 for that specific view.

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

Facts about external schema:

• The external level is only related to the data viewed by specific end users.
• This level includes one or more external schemas.
• The external schema level is nearest to the user.
• The external schema describes the segment of the database needed for a certain user group and hides the remaining details from that group.

Three-Level Schema Architecture Compared

The three levels are easiest to remember side by side. Each answers a different question, is aimed at a different audience, and changes for different reasons.

The levels do not work in isolation. They are connected by mappings, which are the mechanism that delivers data independence.

Mappings Between the Schema Levels

A mapping is the correspondence the DBMS keeps between two adjacent levels, so a request at a higher level can be translated into an action at a lower level. The three-level architecture uses two mappings:

• Conceptual/internal mapping: connects the logical design to the physical storage. It defines how the entities and attributes in the conceptual schema correspond to the stored records and files in the internal schema. When storage changes, only this mapping is updated.
• External/conceptual mapping: connects each user view to the overall logical design. It defines how the fields a user sees relate to the entities in the conceptual schema. When the logical design grows, only the affected mappings are updated.

Because these mappings absorb change, storage can be reorganised without rewriting user programs, which is exactly the data independence the architecture is designed to provide.

Goal of the 3-Level Schema Architecture

Here are some objectives of using the three-schema architecture:

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

Advantages of a Database Schema

• You can manage data independently of the physical storage.
• Faster migration to new graphical environments.
• The DBMS architecture lets you make changes at the presentation level without affecting the other two layers.
• Because each tier is separate, it is possible to use different sets of developers.
• It is more secure, as the client does not have direct access to the database business logic.
• If one tier fails, there is no data loss, because you are always secured by accessing the other tier.

Disadvantages of a Database Schema

• A complete database schema is a complex structure that is difficult for everyone to understand.
• It is difficult to set up and maintain.
• The physical separation of the tiers can affect the performance of the database.

FAQs