These three levels are shown in this fig.
Objectives of three level architecture are:
1.The DBA can change the structure of database . i.e without changing the application program e.g external schema.
2.Each user can access the data according to his/her requirements.
3.Users are independent of the storage complexities like indexing constraints etc of the database.
4.The conceptual structure of the database has no effect due to the change of the physical storage devices.
The External Level
The external level is one closed to the users. That is the one concerned with the way in which the data is viewed by individual users. At the external level the DBMS presents each user with a shared or single view or schema of the data. there are many views of the data at this level , and each view is a representation of part of the complete database. A view allows a user access to their portion of the database ,and shields the rest of the database from them. Each external view is defined by means of an external schema, which consists basically of definitions of each of the various types of external record found in that external view.
In external level, the different views may have different representations of the same data. For example one user may view date in the form as day,month,year while another may view as year ,month ,day.
The Conceptual Level
Conceptual level is the representation of entire contents of database. If the external level is concerned with individual user views, the conceptual level may be thought of as defining a community user view. In other words ,there will be many external views, each consisting of a more or less abstract representation of some portion of the database and there will be a single “Conceptual view”, consisting of a similarly abstract representation of the database in its entirety.
The conceptual view is defined by the various schema which includes definition of each of the various types of data or the various types of conceptual record. The conceptual schema hides the details of physical structure and concentrates on describing entities, data type , relationships, user operations and constraints. The view is normally more stable than the other two views.
The ultimate objective of the conceptual schema is to describe the complete enterprise-not just its data but also how that data is used, how it flows from point to point within the enterprise, what it is used for at each point, what controls are to be applied at each point and so on.
In most existing system the “Conceptual schema “ is little more than a simple union of all individual external schemas, with the addition of certain security and integrity rules.
Hence conceptual level is concerned with the following activities:
1.All entities, there attributes and their relationship.
2.Constraint on the data.
3.Security and integrity rules.
4.Semantic information about the data.
5.Validation checks to retain data consistency and integrity.
The Internal Level
The internal level is the closest to physical storage, that is the one concerned with the way in which the data is actually stored.
the following aspects are considered at this level:
1.Storage space allocation for data.
2.Record description for storage with stored sizes for data items.
3.Access path e.g. specification of primary and secondary keys, index and pointers.
4.Data compression and encryption techniques.
5.Optimization of the internal structures.
The internal view is described by means of the internal schema, which not only defines the various stored record types but also specifies the indexes are in and so on.
Advantage of three level architecture
1.Each user is able to access the same data but have a different customized view of the data as per their own needs.
2.A user can change his/her view and this change does not affect other user views
3.There user’s interaction with the database is independent of physical data storage organization.
4.The database administrator (DBA) is able to change the database storage structure without affecting the user’s view.
5.The database administrator (DBA) is able to change the conceptual structure of the database without affecting all users.
6.The database administrator (DBA) can change existing storage devices with the new storage devices without affecting others user’s.
with the three level architecture the Data Independence is possible in DBMS.