Three Schema Architecture

Three elements that must be considered when designing a database:

1. User view intended for database users
2. Logical schema intended for database designers (data modelers)
3. Physical schema intended for systems programmers (application developers)

Following are the definitions of these three terms:

1. The user view determines which users have access to specific data in the database.
2. The logical schema of a database is its overall logical plan. The logical schema comprises a series of diagrams (i.e., ERD diagrams) that define the content of database tables and describe how the tables are linked together for data access.
3. The physical schema of a database determines how data is stored on computer hardware (for example, hard drives).

Three of the four important characteristics of the database approach, previously listed, are

1. use of a catalog to store the database description (schema) so as to make it self-describing,
2. insulation of programs and data (program-data and program-operation independence), and
3. support of multiple user views. In this section we specify an architecture for database systems, called the three-schema architecture, that was proposed to help achieve and visualize these characteristics.

Then we discuss the concept of data independence further.

The goal of the three-schema architecture, illustrated in Figure 3-5, is to separate the user applications from the physical database. In this architecture, schemas can be defined at the following three levels:

1. The external or view level includes a number of external schemas or user views. Each external schema describes the part of the database that a particular user group is interested in and hides the rest of the database from that user group. As in the previous level, each external schema is typically implemented using a representational data model, possibly based on an external schema design in a high-level data model.
2. The conceptual level (logical schema) has a conceptual schema, which describes the structure of the whole database for a community of users. The conceptual schema hides the details of physical storage structures and concentrates on describing entities, data types, relationships, user operations, and constraints. Usually, a representational data model is used to describe the conceptual schema when a database system is implemented. This implementation conceptual schema is often based on a conceptual schema design in a high-level data model.
3. The internal level (physical schema) has an internal schema, which describes the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access paths for the database.

The overall description of the database is called the database schema. There are three different types of schema in the database and these are defined according to the levels of abstraction of the three-level architecture.

1. At the highest level, we have multiple external schemas (also called subschemas) that correspond to different views of the data.
2. At the conceptual level, we have the conceptual schema, which describes all the entities, attributes, and relationships together with integrity constraints.
3. At the lowest level of abstraction, we have the internal schema, which is a complete description of the internal model, containing the definitions of stored records, the methods of representation, the data fields, and the indexes and storage structures used.

There is only one conceptual schema and one internal schema per database. The DBMS is responsible for mapping between these three types of schema. It must also check the schemata for consistency; in other words, the DBMS must check that each external schema is derivable from the conceptual schema, and it must use the information in the conceptual schema to map between each external schema and the internal schema. The conceptual schema is related to the internal schema through a conceptual-internal mapping. This enables the DBMS to find the actual record or combination of records in physical storage that constitute a logical record in the conceptual schema, together with any constraints to be enforced on the operations for that logical record. It also allows any differences in entity names, attribute names, attribute order, and data types to be resolved. Finally, each external schema is related to the conceptual schema by the external-conceptual mapping. This enables the DBMS to map names in the user's view on to the relevant part of the conceptual schema.