Three Schema Architecture
Three Schema Architecture: An early proposal states for a standardized terminology and architecture for database system were developed and published in 1971 by DBTG (Data Base Task Group) appointed by CODASYL. A similar architecture and terminology were developed and published in 1975 by SPARC (Standards Planning and Requirement Committee) of American National Institute (ANSI) for the data representation. As a result of these and later reports, database can be viewed at three levels, known as external, conceptual and internal levels. These levels form three level architecture . These levels are represented by three schemas or three models known as three level schema or Three Schema Architecture for representation of the data.
The purpose of the three schema architecture is to separate the user applications and the physical database of the same file. The reason of this separation are:
- Different users need different views of the same data with in the same file.
- User should not have to deal directly with the physical database storage details.
- The DBA should be able to change the database storage structure or storage device without affecting other user’s views and applications of same file data.
These level are
- External Level
- Conceptual Level
- Internal Level
1). External Level:
The external level or view is closest to the users in the data file. It is concerned with the way the data is viewed by individual users of retrieving the information. You can say that external level is the individual user level in the three schema architecture for representing the data and information. A user can either be an application programmer or an end-user but DBA is an important special case that manages and control the database.
The external level consists of many different external views or external models of the database of the same organization. Each external view describes the part of the database that a particular user group is interested in and hide the rest of the database from that user group for data consistency and data integrity. In addition, different views may have different representation of the same data.
The external views are defined by means of external schemas that are written in the Data Definition Language (DDL). Usually, the DBA writes an external schema to create a user view of the data file of the organization. Each user’s schema gives a complete description of each type of external record that appears in the user’s view. The external schema are compiled by the DBMS and stored in its Data Dictionary.The DBMS uses the external schema created for specific user, to create a user interface to access data of the database. Thus the user-interface created through external schema accept and display information in the format the user expects. It is also act as barrier to hide information of database from users that is not permitted to him. Typically, it hides the conceptual, internal and physical details from users.
2). Logical or Conceptual Level:
The middle level in the three level architecture are known as logical or conceptual level. It describes the entire structure of the database such as entities, attributes, data types, relationship, constraints on the data and user operations. It hides the details of the physical storage structures that contains further information that may not be important to be shown.
The conceptual level support the external level to represent the data to end user as they need. This level is relatively constant and the DBA designs it after determining the present and the future information needs of the organization. However, to expand the conceptual level, the DBA adds new subject to fulfill the requirements of the organization, without affecting the external level.
The conceptual level is defined by means of the conceptual schema which include the definition of each of the various conceptual record types. The conceptual schema is a complete description of the information of database structure such as every record type with all its fields. It also include security and integrity rules. The conceptual schema is written in DDL, compiled by DBMS and stored in the data dictionary. The DBMS uses the conceptual schema to create the logical record interface, which defines and creates the working environment for the conceptual level to present data to end users. Actually, conceptual level is a collection of logical records of the data file to represent the information.
3). Internal or Physical Level:
The internal or physical level describes the physical storage structures of the database and file organization used to store data on physical storage devices. The internal level describe how the data is stored in the database. It works with operating system and DBMS for storing and retrieving data to and from the storage devices.
Although, both internal level and physical level is considered as a single level, but there is slight difference between them. Actually physical level in one that is managed by the operating system under the direction of DBMS, while the internal level is managed by DBMS.
An internal record is a single stored record. It is storage unit that is passed up to the internal level through stored record interface. The stored record interface is the boundary between physical level and the internal level. This interface is provided to the DBMS by operating system. In some cases, the DBMS itself may create this interface for representing the information. The physical level below this interface consists of database, which is stored and managed by operating system. The operating system creates the physical record interface to access the data of database stored on storage devices that maybe used different purposes.
The internal level is described by means of the internal schema which defines how data is represented, how record are sequenced what indexes exist etc. The internal schema is written in DDL.
