DBMS Data Schemas

In this chapter, you will learn about the basic concepts of data schemas and how data are independent of one another within a database.

What is Schema in the Database Management System?

A schema can be defined as the design of a database. The overall description of the database is called the database schema. It can be categorized into three parts. These are:

  • Physical Schema
  • Logical Schema
  • View Schema

Three Levels of Data Abstraction

A physical schema can be defined as the design of a database at its physical level. In this level, it is expressed how data is stored in blocks of storage.

A logical schema can be defined as the design of the database at its logical level. In this level, the programmers as well as the database administrator (DBA) work. At this level, data can be described as certain types of data records which can be stored in the form of data structures. However, the internal details (such as an implementation of data structure) will be remaining hidden at this level.

View schema can be defined as the design of the database at view level which generally describes end-user interaction with database systems.

For example: Let suppose, you are storing students' information on a student's table. At the physical level, these records are described as chunks of storage (in bytes, gigabytes, terabytes or higher) in memory, and these elements often remain hidden from the programmers. Then comes the logical level; here in logical level these records can be illustrated as fields and attributes along with their data type(s), their relationship with each other can be logically implemented. Programmers generally work at this level because they are aware of such things about database systems. At view level, a user can able to interact with the system, with the help of GUI and enter the details on the screen. The users are not aware of the fact how the data is stored and what data is stored; such details are hidden from them.

Detail Explanation on 3 Layers of Schema

As we came to know that 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 portrayed in the above figure, at the highest level, there is multiple external schemas (view level schema) (also called sub-schemas) that match up to different views of the data. At the conceptual level, there is the conceptual schema or the logical schema that describes all the entities, attributes, and relationships together with integrity constraints. At the lowest level of abstraction, there is the internal schema or the physical schema that creates a complete description of the internal model, containing the classifications of stored records, the methods of representation, the data fields, storage structures used, etc. It is to be noted that there will be 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; which means, the DBMS must check that each external schema is derivable from the conceptual schema, and must use the information in the conceptual schema for mapping among those external schemas and the internal schema. It also allows any differences in entity names; attribute names, attribute order, data types, and so on, to be determined. Lastly, 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 the relevant part of the conceptual schema.


Courses
Subscribe Updates via Email

Join 49,000+ W3schools lovers and get all the latest tutorials, programs, algorithms in your inbox.