In this DBMS tutorial, you will learn what is a schema in DBMS and the types of the database schema with the help of useful diagrams.
Let’s Get Started, Happy Learning!
What is Schema in DBMS?
A schema in DBMS is the design of a database. A database schema represents the logical view of the data and describes how data is organized in a database. In a database management system, the schema defines the entities and relationships among them.
We can define a schema as a plan that describes the records and relationships existing at a particular level of the database. Sometimes a schema is also called the extension of the database, while an instance is called the state of the database.
Example of Database Schema
Let’s take an example of a student database to understand what is a schema in DBMS. The following diagram shows the structural view of the student database at different levels (internal, external and conceptual).
As you can see in the diagram, the schema shows only the design of the student database and the overall description of the student database. But it does not show the data present in those tables, meaning what are entries in the different fields of the student database such as entries in roll no, address name and fee.
Types of Database Schema
We divide the database schema into three parts, as given below:
- Physical Schema
- Conceptual Schema
- External Schema.
Physical Schema
A physical schema in DBMS is the design of the database at a physical level. It describes how the data is stored in blocks of storage. Basically, the storage of the data is described at this level. The physical schema of the database is also called internal schema.
For example, the above student database schema diagram shows how the records roll no, name, and address of the student are stored in memory.
Conceptual Schema
A conceptual schema is the design of the database at a conceptual (logical) level. At this level, we describe the data types of data records. It is also known as a logical schema in DBMS.
For example, In the above student database schema diagram, DBA described the data types of data records such as roll no (integer data type), name (character data type), and address (character data type).
External Schema
The external schema of the database is also called the view schema. An external schema is the design of the database at the view level. It describes how end-users interact with a database system. For different users, there may be different views of a database according to their needs.
For example, the above diagram of the student database schema shows the two different views of the student database.
View 1: In the first view of the student database, the user wants to view roll no, name, address and book number.
View 2: In the second view the user wants to view only roll no, name and fee.
Hence, according to the user requirements, data is displayed in the view schema of the database.
Your thoughts
Have you enjoyed this tutorial on what is schema in DBMS and the types of database schema? Let me know in the comments. Also, check out the following DBMS tutorials. It may help you.
Check out the following DBMS tutorials you may find helpful