What is a Schema?

As we have studied how data abstraction helps in defining different views for the same database we will now study how the designing of this database is done. What are data structures, storage methods, representation methods, storage structures that should be used during our database designing? Now we will study in detail what is a schema and its types. So, let's get started.

Schema

The design of the database is called a schema. This tells us about the structural view of the database. It gives us an overall description of the database. A database schema defines how the data is organised using the schema diagram. A schema diagram is a diagram which contains entities and the attributes that will define that schema. A schema diagram only shows us the database design. It does not show the actual data of the database. Schema can be a single table or it can have more than one table which is related. The schema represents the relationship between these tables.

Example: Let us suppose we have three tables Employee, Department and Project. So, we can represent the schema of these three tables using the schema diagram as follows. In this schema diagram, Employee and Department are related and the Employee and Project table are related.

There are three levels of the schema. The three levels of the database schema are defined according to the three levels of data abstraction .

• View Schema
• Logical Schema
• Physical Schema

View Schema or External Schema

View Schema defines the design of the database at the view level of the data abstraction. It defines how an end-user will interact with the database system. There are many view schema for a database system. Each view schema defines the view of data for a particular group of people. It shows only those data to a view group in which they are interested and hides the remaining details from them.

Example : A website has different views depending upon the user's authorization. A college website has a different view for students, faculty and dean. Similarly, a companies website would have a different view for the employee, accountant and manager.

Logical Schema or Conceptual Schema

Logical Schema defines the design of the database at the conceptual level of the data abstraction. At this level, we define the entities, attributes, constraints, relationships, etc. and how their relationship would be logically implemented. The programmers and the DBA work at this level and they do all these implementations.

External/Conceptual Mapping is done between the external schema and logical schema to transform the request from an external schema to the conceptual schema. This mapping relates the external schema with the logical schema.

Example: If we have to define an employee schema then it will have attributes like Emlpoyee_id, Name, Age, Salary, Phone_no etc. So, the data types for these attributes would be defined here. Also, if we have more than one table in our schema then how these tables would be related is also defined here. Like if we have some more tables like department table and project table then how these tables would be related is defined here.

Physical Schema

This is the design of the database defined at the physical level of data abstraction. This tells how the data will be stored in the storage device. The data can be stored in the form of file, indices etc. It totally depends on the database administrator(DBA) that how he wants to store the data and what are the storage structure that has to be used. It is often hidden from the programmer and the users how the data is stored here. For example, if we are storing the data of employee in the database, this file can be stored anywhere and a user doesn't have any knowledge of it.

Conceptual/Internal Mapping is done between the conceptual schema and physical schema to transform the request from conceptual schema to physical schema. This mapping relates the conceptual schema with the internal schema. It is done so that if any changes are done in the external storage structure then the mapping is changed accordingly so that the conceptual level is not affected.

There can be many external schemas for a database system but only one conceptual and physical schema.

So, this all about schema and the three levels of the schema. That's it for this blog.

Do share this blog with your friends to spread the knowledge. Visit our YouTube channel for more content. You can read more blogs from here .

Keep Learning :)

Team AfterAcademy!