SQL Basics
Querying Data
Filtering Data
Joining Tables
SQL Functions
Modifying Data
Defining Data
SQL Database Concept
In this tutorial, we’re going to understand how SQL database is organized and how it structure work? The following terms are commonly used in databases:
Server
A database server is a machine running software to store and manage databases. It consists of hardware and software to run database.
Usually database servers are high-powered networked computers, but you can use your computer as a local database server.
Database
The database is an organized collection of data to make it easily accessible and manageable. Think of a database like a library that contains a huge collection of books of different genres.
Schema
Schema is a logical container for grouping tables that are related to each other.
Why you may want to split the database into schemas?
- Group similar objects to reduce complexity
- To organize database tables into logical groups to make them more manageable.
- Makes it easy to manage to access the tables.
- You can give two tables the same names if they are stored in a different schema.
Tables
Tables are the place where you store your data in databases. It is the most important object and without it, we have no database.
How is SQL Database organized?
To understand how SQL databases are organized, think of the following analogy:
- A database is like a city library.
- Think of the scheme as a category in a library. Libraries are divided into different types of categories (science fiction, romance, adventure, history, sports, etc.). Categories help find materials quickly and easily by arranging similar books under the same category.
- A book in a library is analogous to a table in your database that contains the actual data.
In SQL databases, data is organized in a hierarchy:
- A company may have one database server or multiple.
- There may be one or more databases on the server.
- Each database has one or more schemas.
- A schema may have different tables.
- Each table has multiple columns.
There are a few differences for this organization for MySQL, SQL Server, and PostgreSQL.
- In MySQL databases and schemas are synonyms. You connect to your database, but can still query tables in other databases.
- In PostgreSQL and SQL Server, you connect to a single database and can only access schemas and their tables within the database.
My humble opinion, MySQL is sometimes misleading and confusing users. For example if you Create Schema in MySQL, the DBMS will create a database.
