Three Level Database Architecture

last updated 30-aug-18

Data and Related Structures

Data are actually stored as bits, or numbers and strings, but it is extremely difficult to work with the variety and complexity of data at this level. (See Physical Data Level below.)

It is helpful to view data at different levels of abstraction.Diagram to show the 3 level of adatabase architecture.


We will be concerned with three forms of schemas:








Internal Data Level

The physical schema of the internal level describes details of how data is stored: files, indices, etc. on the random access disk system.  It also typically describes the record layout of files and type of files (hash, b-tree, flat).

Early applications (1960's) only worked at this level - explicitly dealt with these internal details. E.g., minimizing physical distances between related data and organizing the data structures within the file (blocked records, linked lists of blocks, etc.)




Conceptual Data Level

Also referred to as the Logical level when the conceptual level is implemented to a particular database architecture.

Hides storage details of the internal/physical level.

The DBMS automatically maps data access between the logical to internal/physical schemas .

We will abstract the logical view as a conceptual view using Entity-Relationship Modeling, which is database architecture independent.


External Data Level

An external schema specifies a view of the data in terms of the conceptual level. It is tailored to the needs of a particular category of users. Portions of stored data should not be seen by some users and begins to implement a level of security and simplifies the view for these users.

In the relational model, the external schema also presents data as a set of relations.


Information that can be derived from stored data might be viewed as if it were stored in that manner.

Applications are written in terms of an external schema. The external view is computed when accessed.  It is not stored. Different external schemas can be provided to different categories of users. Translation from external level to conceptual level is done automatically by DBMS at run time. The conceptual schema can be changed without changing application:


Data Modeling

Schema: description of data at some level (e.g., tables, attributes, constraints, domains)

Model: tools and languages for describing:



Data Independencelogical and physical data independence

Logical data independence

Physical data independence




Entity-Relationship Model

A semantic model captures meanings and intents

E-R modeling is a conceptual level model

Proposed by P.P. Chen in 1970s

Relationships sets may also have descriptive attributes

Represented by E-R diagrams

ER symbols

simple ER diagram


Relational Model

Record- and table-based model

Relational database modeling is a logical-level model

Proposed by E.F. Codd

Successor to earlier record-based models—network and hierarchical

access relationship graph




Object-oriented Model

Uses the E-R modeling as a basis but extended to include encapsulation, inheritance

Objects have both state and behavior

Designer defines classes with attributes, methods, and relationships

Class constructor method creates object instances

Both conceptual-level and logical-level model

OO class diagram



Object-relational model

Adds new complex datatypes to relational model

Adds objects with attributes and methods

Adds inheritance

SQL extended to handle objects in SQL:1999




Semi-structured Model

Collection of nodes, each with data, and with different schemas

Each node contains a description of its own contents

Can be used for integrating existing databases

XML tags added to documents to describe structure

XML tags identify elements, sub-elements, attributes in documents

XML DTD (Document Type Definition) or XML Schema used to define structure

(Discussed later in the course in greater detail)