What Is A Database Schema – Definition & Example

This article aims at exploring about “Database Schema”. I will start with a simple definition of database schema, followed by a real-time example for the same. Once that is done, we will apply the same logic to the concept of Database Schema and that will help us in better understanding of the subject. So, let’s get started!

Definition of a Database Schema

A database schema is the outline structure or a blueprint of a database. It is used to represent the logical view of the database. It shows us how the data is organized in tables and the relationships between them. It also summarizes all the limitations and structure of the data.

Example of a Database Schema

Let us try to understand this with a practical, real-time example, as follows:

• Consider that you want to build a house. In order to build a house, you need to plan accordingly first. So, what do you do? You take a piece of paper and start drawing the outline of the house that shows the perimeter of the house, how tall and how wide it would be.
• After you are done with that, you would also draw lines in this blueprint that would symbolize the different rooms of the house. In a house you generally have different rooms such as kitchen, bedroom, the main hall, etc. You also want to make sure that you plan accordingly so that you have enough space for each of the rooms that you want to allocate.
• Now let us assume that you have kitchen as the left most room, with the main hall in the middle and then, the bedroom to the right. Now you would draw this in the blueprint, right? So, once you have finalized this blueprint when you approach a builder to construct the house for you and show him your plan.
• When the Builder looks at this blueprint, he immediately understands your vision of the house. This blueprint lets him plan accordingly and if he has any concerns regarding your plan he would discuss them with you and you both can arrive at a mutual agreement as to how you would proceed further.
• So, the main thing to note over here is that how was the builder able to understand what you wanted precisely? That was because you had an underlined sketch that you shared with him and that had all the drawings that gave him a direct visual clue of your requirements. So, we can call this blueprint of your house as your “house schema”.
• So now let us assume that the Builder agrees to build your house. He then starts constructing the house based on the agreed blueprint. While he conforms to your requirements as to the layout of the house, at the same time, he would also have to make sure that the construction is based on the standards. He does this just to make sure that the final product (the house) stands strong, is reliable and also meets your requirements.
• So, in real time, the Builder would know how tall to build the walls, how wide the roofing must be etc. This is a physical realization of your logical blueprint. This results in the final product, your house.

So, building upon the example above, we will try to understand what a database schema is. Just like your house consists of different rooms and is an enclosure for your own space that you bought with your own money, here it is the database that acts as an enclosure for all of your data. Just like you have multiple rooms in your household, you have multiple tables in the database. Just like every room holds some furniture and different household items, each of the tables in a database hold rows of data.

Putting all of the above together and speaking in terms of a database, a database schema is nothing but a visual representation of what tables are used in the database along with the column names of each of the tables. In addition to this, the database schema defines the entities and the relationship among them.

Just as you approached a builder to construct your house, you approach a database designer to help construct you an efficient database design. This database designer is the one who actually constructs the entire database schema with the logical view to it.

A database schema consists of two categories:

• Logical database schema
• Physical database schema

Now let us understand about both the types of Schema by comparing it with our example above.

Logical database schema:

Similar to the blue print that you have provided to the Builder, you prepare a blueprint of the database, including the tables structure used, the key constraints and the relationship between the tables.

Physical database schema:

Similar to the call of the Builder on how tall the rooms should be and how wide the roofing must be, the physical database schema represents what actually gets implemented in your database management system. Your physical database model or schema is where your data will eventually get stored.

Just as the Builder uses brick and cement to comply with the standards accordingly, when the database is being created, physical constraints of storing the data are taken into account and the schema is designed accordingly. For example, Physical database schema can contain the data files, you log files, full files, other physical objects that reside on the operating system.

Conclusion

In simple words, database schema represents the structure of a database and the organization of different tables within this structure and the relationship between them. There are different schema objects contained in a schema, examples of which are tables, views, primary keys, monkeys, keep, stored procedures, relationships, etc.