Database Management Systems| Data Model and Three-Schema Architecture

3.1 Data Model

By definition, a database is a collection of interrelated data, which represents some Universe of Discourse (UoD). Based on the UoD concerned, the database and the data stored in the database might vary. Thus it pops up the requirement of a structure for the database into which the actual data would be saved and from which the required data are retrieved. The data model is considered to be the collection of concepts which describes the structure of the database. The database and the structure represent the UoD.

The structure of the database includes;

(1) Data Type: Indicates what types of a values to be stored

(2) Relationships: Represents how data items are related to one another

(3) Constraints: Rules by which the manipulation of data is governed

Also, it is necessary to present the data to the user, irrespective of how these data is stored physically. The data model is the structural level which intermediates the physical level storage of data and the top level queries applied on the data. As the data model hides the details of physical storage of data from the user, it is considered as essential to achieve data abstraction.

3.2 Three-Schema Architecture

You have already refreshed your memory about the characteristics of database approach. The three schema-architecture supports the achievement of three such main characteristics. The following will briefly state the characteristics in concern.

(1) Insulation between program and data

The data access programs and the structure of the data files are separated from each other and therefore in most of the cases it is not necessary to change the access programs when the data file structure is changed.

(2) Support to multiple user views

In general, a database may be used by many users who need different information from the same data set stored. For example, from the Employee Information Database, the accountant might need to get the information about the salaries of the employees, and the Human Resources Manager would require the performance of each employee.

(3) Use of Catalog to store database description

DBMS catalog stores the description of data. The description includes the structure of each file, type & storage format of each data item and the constraints imposed on data. The description of data is also referred as “Meta data”. The maintenance of database catalogs allow DBMSs to handle more than one database In fact, the Three-Schema-Architecture separates the user application and the physical database. In other words, the users would be able to retrieve and manipulate data through the user application, with out having to consider and be aware of how data is stored physically. This improves the usability. The adoption of Three-Schema- Architecture enables DBMSs to shield the complexity of physical data storage and resent the data to the end user in a simple-to-handle, user friendly manner. The Figure 3.1 will illustrate how Three-Schema-Architecture is formulated to support this.

The Three-Schema-Architecture is presented with 3 different levels which correspond with the physical data storage, external user views at the two ends and an intermediary level which mediates the two ends. Let’s discuss about each level.

Internal Level:

– Has an Internal Schema

– Describes the physical structure

– Describes the details of data storage and access paths for the database

Conceptual Level:

– Has a Conceptual Schema

– Describes the structure of whole database

– Hides the structure of physical storage

– Describes entities, data types, relationships, user operations and constraints

– Based on a Conceptual Schema design in a high level data model

External Level:

– Includes a number of External schemas or user views

– Each external schema describes a part of the database, of which a particular user group is interested in

– Hides the rest of the database from the given user group

– Based on an external schema design in a high-level data model

3.2.1 Mappings between different levels

Now, let’s move on and see how these three levels inter-operate when presenting physical data to external users. When a user sends a request for data, it needs to be transformed to the lower levels, and the results produced in response to the request need to be transformed back to the upper level. The process of transforming requests and results between different levels is called “Mappings”

End User’s request is specified on the external schema, and then transformed into a request on conceptual schema. It will then be transformed in to a request on internal schema. The internal level will then produce the result which will be transformed accordingly and presented to the end user.

3.2.2. Data Independence

The data independence is discussed at two different contexts.

(1) Logical Data Independence: Ability to change the conceptual schema without having to change the external schemas or application programs

(2) Physical Data Independence: Ability to change the internal schema without having to change the conceptual schema