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:
- Physical or internal schema
- Logical or conceptual schema
- View or external 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.
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.
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.