Schema in DBMS

In this course, we will study schema, its types-physical, logical and view, and advantages and disadvantages of the database schema.

What is the schema in DBMS?

The term schema refers to the organization of data in the form of a blueprint of how the database is constructed that is divided into database tables in the case of a relational database. The formal definition of a database schema is a set of formulas or sentences called as integrity constraints that are imposed on a database. The integrity constraints are there to ensure the compatibility between parts of the schema. The schema can be categorized into three categories. Which are:

1. Physical or internal schema
2. Logical or conceptual schema
3. View or external schema

Schema in DBMS

Physical schema

☆ A physical schema can define the design of a database at the physical level.
☆ It is also known as the internal level or schema.
☆ In this level, we express how we want our data to be stored in blocks of storage.
☆ It is the lowest layer.
☆ In the physical layer, we keep the information regarding the actual representation of the entire database.
☆ For example, we can do the actual storage of the data or information on the disk in the form of records.
☆ The physical schema tells us about what data is stored in the database.
☆ It also tells us about how it never deals with physical devices. Instead, a physical schema sees a physical device as a collection of physical pages.

Logical schema

☆ A logical schema can be defined as the designing of the database at the logical level.
☆ It is also known as a conceptual level or schema.
☆ The programmers, as well as the database administrator (DBA), work at this level.
☆ In the logical schema, the data is described as a bunch of certain types of data records that can be stored in the form of data structures.
☆ In this level, the internal details like the implementation of data structures will be hidden.
☆ This is the middle layer.

View schema

☆ On an external level, we are only concerned about the data which is viewed by specific end users.
☆ It is also known as the external level or schema.
☆ This level contains some external schemas.
☆ The external schema level is closest to the user.
☆ In the external schema, we describe the segment of the database which is required for a certain user group and it should also hide the remaining details of the database from the specific user group.

What are the advantages of database schema?

☆ Management of data independent of the physical storage can be done.
☆ Migration to new graphical environments can now be done fast.
☆ We can now make changes on the presentation level without affecting the other two layers.
☆ In database schema as each tier is separate therefore we can use different sets of developers.
☆ Security is more as the client doesn't have direct access to the database logic.
☆ In any case of failure, there is no data loss

What are the disadvantages of database schema?

☆ The complete database Schema is a very complex structure and is difficult to be understood by everyone.
☆ Setup and Maintenance is difficult.
☆ The physical separation of the tiers may affect the performance of the Database.