What is schema in MySQL

The data in MySQL is managed in databases in the form of tables, which further consist of rows and columns. What is Schema?  Schema is different from databases as it does not have all the privileges that a database holds, it has only privileges of tables, rows, and columns which defines the structure of a database.

In this write-up, we will explain what a schema is, how it works, and what are the differences between the schema and database.

What is schema

A database schema is an architecture of the database which holds the information about the pattern of how the data should be placed in the table but it should be clear that a schema has no association with the data itself, it just tells the possible ways in which a data should be placed in the database. We can define, the schema as the structure of any database that tells us about the representation of the table, it also defines the number of rows, columns of the table, the primary and foreign keys associated with the tables,  and also defines the datatypes of the data to be inserted in the tables.

Mostly in companies, Database Administrators, are responsible for providing a proper schema for any database, according to which Database Developers, develop the databases.

Comparison Between Database and Schema

What are the types of Schema

Schema can be divided into two types on the basis of their functions as shown in the chart below.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/Screen-Shot-2021-10-15-at-3.05.09-AM-e1634238374985.png" data-lazy- height="262" src="data:image/svg xml,” width=”558″>

Physical Schema: It is the type of Schema that can be viewed by the users, it deals with the methods of storing the data and how they can be represented in the database.

Logical Schema: It is the type of schema which tells us about the concept behind the creation of the database, it explains the formation of tables, the relationship of tables with each other in a database, and the keys used in the tables which can be the primary key as well as a foreign key. Assume the above example of “school_record_of_students”, now this defines the number of rows and columns of the table and it also links it with the other tables, let’s say, “record_of_grade_2_students” with the help of primary and foreign keys.

How schema works in MySQL

We will try to create the database, named,”company_abc” and a schema, named, “school_abc”, we will create the tables and try to insert data in both tables and observe the results, but before the creation of tables we will create a database as

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-01.png" data-lazy- height="176" src="data:image/svg xml,” width=”704″>

Use this database to create the table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-02.png" data-lazy- height="156" src="data:image/svg xml,” width=”450″>

Create a table and name it “employees_data”.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-03.png" data-lazy- height="84" src="data:image/svg xml,” width=”904″>

Insert data in the table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-04.png" data-lazy- height="82" src="data:image/svg xml,” width=”904″>

To display the table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-05.png" data-lazy- height="354" src="data:image/svg xml,” width=”904″>

Similarly, we will create a schema ”school_abc”:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-06.png" data-lazy- height="176" src="data:image/svg xml,” width=”802″>

Use the newly created schema:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-07.png" data-lazy- height="130" src="data:image/svg xml,” width=”480″>

Create a table in schema school_abc,

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-08.png" data-lazy- height="90" src="data:image/svg xml,” width=”904″>

Now insert the data in the table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-09.png" data-lazy- height="142" src="data:image/svg xml,” width=”904″>

To display the table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-10.png" data-lazy- height="360" src="data:image/svg xml,” width=”904″>

To show the databases.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/what-is-schema-mysql-11.png" data-lazy- height="756" src="data:image/svg xml,” width=”532″>

We can observe that in MySQL not only is Schema created and displayed in the same way as Database has been created but also the table has been created in both schema and database.

Conclusion

Schema is the structure that can help the developers in creating many databases following a single schema. In this article, we have learned that schema is a logical representation of the database and it differs from the database as it doesn’t occupy any space whereas the database occupies some space on the server, but with the help of examples we have deduced the results that in MySQL, the schema is just a synonym of database and can perform the same functions which a database can perform.