Three Level Database Architecture

last updated 29-aug-13

Data and Related Structures

Data are actually stored as bits, or numbers and strings, but it is difficult to work with data at this level.

It is necessary 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:








Physical Data Level

The physical schema 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 worked at this level - explicitly dealt with 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

Hides details of the physical level.

The DBMS maps data access between the conceptual to physical schemas automatically.



External Data Level

In the relational model, the external schema also presents data as a set of relations. 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


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

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 Model

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

E-R modeling is a conceptual level model

Proposed by P.P. Chen in 1970s

Relationships sets may 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)