Pronounced skee-ma, schema refers to a formalized, consistent structure of organizing information
In computing, the term schema refers to the diagrammatic representation or structured framework of a database used to interpret information or different types of data. It is the blueprint that provides the details of how a database is constructed and its relationship with other databases.
is the structure of a database system, described in a formal language supported by the database management system (DBMS). In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables.
Schemas are generally stored in a data dictionary. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure.
In this definition...
Types of schemas
There are three types of schemas based on their representation: Physical Schema, Logical Schema, and View Schema.
Physical Schemas
A physical schema, also known as an internal schema, is a representation of a data design that describes how data is to be constructed and stored physically in the database. At this level, data is represented as files, indices, tables, clusters, etc. A physical schema is used to evaluate storage estimates.
Being the lowest level in the database, it’s easy to make changes to a physical schema, like creating a new file or index and storing new files in the storage system. At this level, database administrators (DBAs) determine the data’s method of storage and storage structure.
For example, if a database administrator wants to represent the physical schemas of students and their projects, they can create two files: Datafile1 (Students) and Datafile2 (Projects).
Logical Schemas
A logical schema, or conceptual schema, is the design of the database that describes the structure of different types of data as fields, entities, attributes, and relationships along with their data types. It’s mainly used to create relationships between tables or data. However, the structure of data storage is hidden, meaning only database administrators and developers can work at this level. An example of logical schemas for students and project details might include:
Stu_ID Stu_Name Pro_ID
View Schemas
A view schema, or external schema, is the view-level structure of the database, and it defines how users interact with the database. View schemas provide different views of data for different groups of users.
For example, a user can see either the details of each student, their names and IDs, and their projects or the details of a particular student. The users may be teachers, students, and so on.
Importance of database schemas
Schemas help the database understand what the data is, and how a DBA or programmer can work with this data. It also allows the database engine to recognize the data patterns and respond to the user queries with the right information. A good schema always tries to make the information visible to the users.
In relational databases, schemas provide more priority to data consistency and the structures that are essential to understanding the relationships between the data. While in the non-relational databases, schemas help to achieve high performance by aligning the data in patterns which is easily accessible.
Who uses schemas?
In most companies, database administrators develop schemas to help the database management team or database users easily access the information. They use schemas to organize and align a company’s data securely. DBAs usually determine the method and structure for data storage.
Static Schemas vs. Dynamic Schemas
There are two basic types of databases: relational and non-relational. To understand the difference between them, it’s essential to know the difference between static and dynamic schemas.
Relational databases are mainly associated with static schemas, in which the data patterns are pre-defined, and the structure of the data cannot be changed. It can be configured only when creating a workspace. For example, textbooks use a static schema approach.
Dynamic schemas are more prevalent with non-relational databases, and they can be configured when adding additional data to the system. For example, a user can create and add categories to a customer table, and the schemas of the database change when the user adds more data.
Database Schemas vs. Database Instance
In a database management system, a schema is a relatively static view of a database that describes its overall structure. Comparatively, an instance is a dynamic collection of data that is stored in a database at specific moments.
This definition was updated in January 2022 by Siji Roy.
