Three Schema Architecture

Three Schema Architecture

• Definition: Database schema is a collection of database objects like tables, views, indexes and so on associated with one particular database username. This username is called the schema owner.

• For example Student Schema can be owner of STUDENT and MARKS tables. The Course schema can be the owner of SUBJECT table.

• The goal of three-schema architecture is to separate the user application from the physical database.

• The architecture of database is divided into three levels based on three types of schema - internal schema, conceptual schema or external schemea.

1. Internal level:

• It contains internal schema.

• This schema represents the physical storage structure of database.

• This schema is maintained by the software and user is not allowed to modify it.

• This level is closest to the physical storage. It typically describes the record layout of the files and types of files, access paths etc.

2. Conceptual level:

• It contains conceptual schema.

• This schema hides the details of internal level.

• This level is also called as logical level as it contains the constructs used for designing the database.

• It contains information like table name, their columns, indexes and constraints, database operations.

• A representational data model is used to describe conceptual schema when a database system is implemented.

3. External level:

• It contains the external schema or user views.

• At this level, the user will get to see only the data stored in the database. Either they will see whole data values or any specific records. They will not have any information about how they are stored in the databases

• The processes of transforming requests and results between levels are called mappings.

• In the three schema architecture there are two mappings -

1) External - Conceptual Mapping and

2) Conceptual - Internal Mapping.