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

Database	Schema
Stores the data in the tables	Provides the logical representation of a database on basis of tables
DML (data modification language) is used to manage data in the database	DDL (data definition language) is used to manage the representation of tables
Data can be edited at any time	Modifications are not supported
It includes tables, schemas, and all other constraints of the database	It includes only structures of tables and privileges related to tables
It occupies memory on the server	It occupies no memory

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.

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

CREATE DATABASE company_abc;

Use this database to create the table:

USE company_abc;

Create a table and name it “employees_data”.

CREATE TABLE employees_data (employee_id INT, employee_name VARCHAR(50), employee_age INT);

Insert data in the table:

INSERT INTO student_data VALUE (1, “Steward”, 25);

To display the table:

SELECT * FROM employees_data;

Similarly, we will create a schema ”school_abc”:

CREATE SCHEMA school_abc;

Use the newly created schema:

USE school_abc;

Create a table in schema school_abc,

CREATE TABLE student_data (student_id INT, student_name VARCHAR(50), student_age INT);

Now insert the data in the table:

INSERT INTO student_data VALUE (1, “John”, 16);

To display the table:

SELECT * FROM student_data;

To show the databases.

SHOW databases;

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.