Three schema architecture of dbms

The three schema architecture describes how the data is represented or viewed by the user in the database. This architecture is also known as three-level architecture and is sometimes called The three schema architecture divides the database into three-level to create a separation between the physical database and the user application. In simple words, this architecture hides the details of physical storage from the user. The database administrator (DBA) should be able to change the structure of database storage without affecting the user’s view. This architecture contains three layers or levels of the database management system: • External level • Conceptual level • Internal level Three Schema Architecture of DBMS 1. External or View level: This is the highest level of database abstraction. External or view level describes the actual view of data that is relevant to the particular user. This level also provides different views of the same database for a specific user or a group of users. An external view provides a powerful and flexible security mechanism by hiding the parts of the database from a particular user. 2. Conceptual or Logical level: The conceptual level describes the structure of the whole database. This level acts as a middle layer between the physical storage and user view. It explains what data to be stored in the database, what relationship exists among those data, and what the datatypes are. There is only one conceptual schema per database. Database admi...

Database systems comprise of complex data structures. Thus, to make the system efficient for retrieval of data and reduce the complexity of the users, developers use the method of Data Abstraction. There are mainly three levels of data abstraction: • Internal Level: Actual PHYSICAL storage structure and access paths. • Conceptual or Logical Level: Structure and constraints for the entire database • External or View level: Describes various user views Let’s study them in detail Internal Level/Schema The internal schema defines the physical storage structure of the database. The internal schema is a very low-level representation of the entire database. It contains multiple occurrences of multiple types of internal record. In the ANSI term, it is also called “stored record’. Facts about Internal schema: • The internal schema is the lowest level of data abstraction • It helps you to keeps information about the actual representation of the entire database. Like the actual storage of the data on the disk in the form of records • The internal view tells us what data is stored in the database and how • It never deals with the physical devices. Instead, internal schema views a physical device as a collection of physical pages Conceptual Schema/Level The conceptual schema describes the Database structure of the whole database for the community of users. This schema hides information about the physical storage structures and focuses on describing data types, entities, relationships, ...

Following figure depicts the 3-schema architecture of DBMS. • The internal level has an internal schema, which describes the physical storage structure of the database. The internal schema uses physical data model and describes the complete details of data storage & access path for the database. • The conceptual level has a conceptual schema which describes the structure of the whole database for a community of the users. The conceptual schema hides the details of physical storage structures & describes entities, data types, relationships, user operation & constraints. • 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 & hides the rest of the database from the user group. A high level data model or an implementation data model can be used at this level.

The three-schema approach, or three-schema concept, in Overview [ ] The three-schema approach provides for three types of schemas with schema techniques based on formal language descriptions: • External schema for user views • • Internal schema that defines physical storage structures At the center, the conceptual schema defines the Over the years, the skill and interest in building information systems has grown tremendously. However, for the most part, the traditional approach to building systems has only focused on defining These two traditional views of data have been defined by analysts over the years on an application by application basis as specific business needs were addressed, see Figure 1. Typically, the internal schema defined for an initial application cannot be readily used for subsequent applications, resulting in the creation of redundant and often inconsistent definition of the same data. Data was defined by the layout of physical records and processed sequentially in early information systems. The need for flexibility, however, led to the introduction of The recognition of this problem led the ANSI/X3/SPARC Study Group on Database Management Systems to conclude that in an ideal data management environment a third view of data is needed. This view, referred to as a "conceptual schema" is a single integrated definition of the data within an enterprise which is unbiased toward any single application of data and is independent of how the data is physically sto...