Schemas, Mappings, and Instances

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 . 

At the highest level, we have multiple external schemas (also called subschemas) that correspond to different views of the data. 

At the conceptual level, we have the conceptual schema, which describes all the entities, attributes, and relationships together with integrity constraints. 

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 schemas 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, data types, and so on, 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.

                                 Figure - Schema

Two different external views of staff details exist: one consisting of a staff number (sNo), first name (fName), last name (lName), age, and salary; a second consisting of a staff number (staffNo), last name (lName), and the number of the branch the member of staff works at (branchNo). 

These external views are merged into one conceptual view. In this merging process, the major difference is that the age field has been changed into a date of birth field, DOB. The DBMS maintains the external/conceptual mapping; 

for example, it maps the sNo field of the first external view to the staffNo field of the conceptual record. The conceptual level is then mapped to the internal level, which contains a physical description of the structure for the conceptual record. At this level, we see a definition of the structure in a high-level language. 

The structure contains a pointer, next, which allows the list of staff records to be physically linked together to form a chain. Note that the order of fields at the internal level is different from that at the conceptual level.

 Again, the DBMS maintains the conceptual/internal mapping. It is important to distinguish between the description of the database and the database itself. The description of the database is the database schema. 

The schema is specified during the database design process and is not expected to change frequently. However, the actual data in the database may change frequently; for example, it changes every time we insert details of a new member of staff or a new property. 

The data in the database at any particular point in time is called a database instance. Therefore, many database instances can correspond to the same database schema. The schema is sometimes called the intension of the database, while an instance is called an extension (or state) of the database.