3 Schema Architecture

GOAL:

To separate the user application and the physical database. That means provide users an abstract view of data to simplify user's interaction with the system by hiding certain details of how data are stored and maintained.

Main objective of three schema architecture is to enable multiple users to acess the same data with a personalised view while storing the underlying data only once.

3 Levels:

Physical Schema / Internal Level:

This is the lowest level of abstraction that describes the physical storage structure of the database. This describes how the data are stored. Describes complete details of data storages and access paths. In this level low level data structures are used.

Physical schema talks about storage allocation (N-ary tree etc), data compression & encryption etc.

Conceptual Schema / Logical Level:

The conceptual schema describes the design of a database at the conceptual level, describes what data are stored in database, and what relationships exist among those data. In this level high-level data structures are used.

User at logical level does not need to be aware about physical-level structures. DBA(Database Administrator), who must decide what information to keep in the database uses the logical level of abstraction. Goal of logical schema is to make database ease to use.

View Schema / External Level:

This is the highest level of abstraction aims to simplify users’ interaction with the system by providing different view to different end-user.

Each view schema describes that part of the database that a particular user group is interested and hides the rest of the database from that user group. At the external level, a database contains several schemas that sometimes called as subschema. View schema also provides security mechanisms to prevent users from accessing certain parts of database.