Levels of Abstraction in a DBMS

There are threee levels of abstraction in a DBMS:

• External
• Conceptual
• Internal

Conceptual DBMS Level

It is the highest level of abstraction and describes the data in terms of the data model of the DBMS. It describes all relations stored in the database.

Using our previous example, we can sdescribe entities and relationships using the following conceptual schema:

employees(id: integer, name: string, salary: integer)
customers(id: integer, name: string, email: string, phone: string)
products(id: integer, name: string, category: string, supplier_id: integer, sale_price: money)
suppliers(id: integer, name: string, email: string)
sales(date: date, product:id, customer:id)

The choice of fields for each relation is not always obvious. The process of arriving at a conceptual schema is called conceptual database design.

Internal DBMS Level

It is the lowest level of abstraction and describes how the data are phisically stored. It is also called physical schema.

The physical schema describes how data is stored and the record layout of files.

External DBMS Level

The external schema allows data access to be customized and authorized at the level of individual users or groups. A database can have more than one external schema.

Each external schema consists of a collection of one or more views and relations.

An example external schema view could be:

sales_per_product(product_name: string, sales: money)

An user can treat a view from the external schema just like a relation and run queries to ask questions on them. But the records in the external schema are not stored explicitly, they are computed as needed.

The view sales_per_product used in this example was not included in the conceptual schema because it can be computed from the relations in the conceptual schema. Using a view in the external schema allows us to reduce redundancy and avoid inconsistencies.

User applications are written in terms of an external schema.

Besides the above mentioned advantages of using an external view to answer questions and display information to users, it also gives and additional layer of security because it hides certain information to certain groups of users.