What is a Database Architecture

An early proposal for a standard terminology and general architecture for database systems was produced in 1971 by the DBTG (Data Base Task Group) appointed by the Conference on Data Systems and Languages (CODASYL, 1971). The DBTG recognized the need for a two level approach with a system view called the schema and user views called sub schema. The American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) produced a similar terminology mid architecture in 1975 (ANSI 1975). ANSI-SPARC recognized the need for a three level approach with a system catalog.

There are following three levels or layers of DBMS architecture:

• External Level

•Conceptual Level

• Internal Level

Objective of the Three Level Architecture

The objective of the three level architecture is to separate each user’s view of the database from the Way the database is physically represented. There are several reasons why this separation is desirable:

• Each user should be able to access the same data, but have a different customized view of the data. Each user should be able to change the way he or she views the data, and this change should not affect other users.

• Users should not have to deal directly with physical database storage details, such as indexing or hashing. In other words a user’s interaction with the database should be independent of storage considerations.

• The Database Administrator (DBA) should be able to change the database storage structures without affecting the user’s views.

. The internal structure of the database should be unaffected by changes to the physical aspects of storage, such as the changeover to a new storage device.

. The DBA should be able to change the conceptual structure of the database without affecting all users.

External Level or View level

It is the users’ view of the database. This level describes that part of the database that is relevant to each user. External level is the one which is closest to the end users. This level deals with the way in which individual users vie\v data. Individual users are given different views according to the user’s requirement.

A view involves only those portions of a database which are of concern to a user. Therefore same database can have different views for different users. The external view insulates users from the details of the internal and conceptual levels. External level is also known as the view level. In addition different views may have different representations of the same data. For example, one user may view dates in the form (day, month, year), while another may view dates as (year, month, day).

Conceptual Level or Logical level

It is the community view of the database. This level describes what data is stored in the database and the relationships among the data. The middle level in the three level architecture is the conceptual level. This level contains the logical structure of the entire database as seen by the DBA. It is a complete view of the data requirements of the organization that is independent of any storage considerations. The conceptual level represents:

• All entities, their attributes, and their relationships;

An Entity is an object whose information is stored in the database. For example, in student database the entity is student. An attribute is a characteristic of interest about an entity.

For example, in case of student database Roll No, Name, Class, Address etc. are attributes of entity student.

• The constraints on the data;

• Semantic information about the data;

• Security and integrity information.

The conceptual level supports each external view, in that any data available to a user must be contained in, or derivable from, the conceptual level. However, this level must not contain any storage dependent details. For instance, the description of an entity should contain only data types of attributes (for example, integer, real, character) and their length (such as the maximum number of digits or characters), but not any strange considerations, such as the number of bytes occupied. Conceptual level is also known as the, logical level.

Internal level or Storage level

It is the physical representation of the database on the computer. This level describes how the data is stored in the database. The internal level is the one that concerns the way the data are physically stored on the hardware. The internal level covers the physical\ implementation of the database to achieve optimal runtime performance and storage space utilization. It covers the data structures and file organizations used to store data on storage devices. It interfaces with the operating system access methods to place the data on the storage devices, build the indexes, retrieve the data, and so· on.

The internal level is concerned with such things as:

• Storage space allocation for data and indexes;

• Record descriptions for storage (with stored sizes for data items);

• Record placement;

• Data compression and data encryption techniques.

There will be only one conceptual view, consisting of the abstract representation of the database in it’s entirely. Similarly there will be only one internal or physical view, representing the total database, as it is physically stored.

Schema

It is important to note that the data in the database changes frequently, while the plans or schemes remain the same over long periods of time. The database plans consist of types of entities that a database deals with, the relationship among these entities and the ways in which the entities and relationships are expressed from one level of abstraction to the next level for the users’ view. The users’ view of the data (also called logical organization of data) should be in a form that is most convenient for the users and they should not be concerned about the way data is physically organized. Therefore, a DBMS should do the translation between the logical (users’ view) organization and the physical organization of the data in the database.

The plan or scheme of the database is known as Schema. Schema gives the names of the entities and attributes. It specifies the relationship among them. It is a framework into which the values of the data items (or fields) are fitted. The plans or the format of schema remains the same. But the values fitted into this format changes from instance to instance. In other terms, schema means overall plans of all the data item (field) types and record types stored in a database. Schema includes the definition of the database name, the record type and the components that make up those records

Types of Schema

There are three different types of schema in the database corresponding to each data view of database. In other words, the data views at each of three levels are described by schema.

A schema is defined as an outline or a plan that describes the records and relationships existing at the particular level. The External view is described by means of a schema called external schema that correspond to different views of the data. Similarly the Conceptual view is defined by conceptual schema, which describes all the entities, attributes, and relationship together with integrity constraints. Internal View is defined by internal schema, which is a complete description of the internal model, containing definition of stored records, the methods of representation, the data fields, and the indexes used.

There is only one conceptual schema and one internal schema per database. The schema also describes the way in which data elements at one level can be mapped to the corresponding data elements in the next level.

Thus, we can say that schema establishes correspondence between the records and relationships in the two levels. In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables. Schema are generally stored in a data dictionary.

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 intention of the database, while an instance is called an extension (or state) of the database.

Example: To understand the difference between the three levels, consider again the database schema that describes College Database system. If User1 is a Library clerk, the external view would contain only the student and book information. If User2 is an account office clerk then he/she may be interested in students detail and fee detail. Shows specific information actually available at each level regarding a particular user.

The external view would depend upon the user who is accessing the database. The conceptual level contain the logical view of the whole database, it represents the data type of each required field. The internal view represents the physical location of each element on the disk of the servers well as how many bytes of storage each element needs.

Mapping between Views

The DBMS is responsible for mapping between these three types of schema. Two mappings are required in a database system with three different views.

External/Conceptual Mapping: Each external schema is related to the conceptual schema by the external/conceptual mapping. A mapping between the external and conceptual views gives the correspondence among the records and the relationships of the external and conceptual views the external view is an abstraction of the conceptual view, which in its turn is an abstraction of the internal view. It describes the contents of the database as perceived by the user or application program of that view. The user of the external view sees and manipulates a record corresponding to the external view. There is a mapping from0 a particular logical record in the external view to one (or more) conceptual record(s) in the conceptual view.

Differences between External/Conceptual views

Following could be differences that exist between the two:

Names of the field’s and. records, for instance, may be different. A number of conceptual fields can be combined into a single external field, for example, Last_Name and First_Name at the conceptual level but Name at the external level. A given external record could be derived from a number of conceptual .records.

Conceptual/Internal Mapping: Conceptual schema is related to the internal schema by the 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 conceptual schema. Mapping between the conceptual and the internal levels specifies the method of deriving the conceptual record from the physical database.