Everything you need to know about Databases
Learn the different types of databases, how an RDBMS works and their uses.
Data is stored on disk or in memory. How and where data is stored. Usually on disk, in memory or in the cloud (which is the same but stored in a data centre).
Being able to talk confidently about data comes from understanding what it is. In Layman’s terms, data is facts and statistics collected together for analysis. It is stored inside the memory of a computer system as binary code that we interpret through an engine.
By the end of this article you’ll be versed in:
- Difference between SQL and NoSQL databases
- What an RDBMS is
- Schemas
- What a SQL Engine is
- OLAP & OLTP
Databases are broadly split into two categories
1. Relational Databases (SQL)
2. Non-Relational Databases (NoSQL)
Relational Databases
SQL is used — which is a language — but most people us the term SQL and RDBMS interchangeably. RDBMS stands for Relational Database management system.
The relationship between the data is fixed and rigid. This is defined ahead of time by what is known as the schema.
The schema is the structure in and between the tables.
RDBMS has a rigid structure. It’s defined ahead of time. This is what companies used (and still do) for years.
Non-Relational Databases
No SQL as the name suggests is everything which doesn’t fit into the SQL mould. Everything that isn’t relational.
They have a more relaxed concept of a schema and relationships between tables. In general, they all have weak schemas, or none at all, and the relationships between tables are handled very differently.
These are some of NoSQL databases:
- Key-Value databases
- Wide Column Store
- Document
- OLTP and OLAP (Rows and Column stores)
- Graph
Let’s dive deeper into what an RDMS is:
The software used to store, manage, query, and retrieve data stored in a relational database.
An RDBMS is a type of DBMS that first appeared in the 70s and became extremely popular in the mid 90s for it’s more powerful features.
For RDBMS think:
- MS SQL
- Oracle Database
- Amazon Relational Database Service (RDS)
- MySQL
- IBM Db2
- SQLite
- PostgreSQL
- Amazon Aurora
- Azure SQL Database
All of these companies have created their own versions of a Relational Database Management System — each is similar, but has different functionality.
Oracle has always been very popular and the cloud providers are taking over a lot of the space for their ease of use and integration with other services, but you’ll come across most of the above. Be aware that:
MySQL: It is the default configuration for all WordPress installations.
PostgreSQL: (Also known as Postgres) is designed for more advanced developers. Consider it a more rugged version of MySQL.
SQLite: As a manageable and tiny SQL database, SQLite is best used for simple application testing.
What is a SQL Engine?
A SQL Engine recognises and interprets the SQL command from the user to be able to accesses the database where the data is stored. The SQL engine is the mediator between the query from the user and the database. It is a component of the system that is used to create, read, update and delete (CRUD) data from a database.
A SQL engine lies within the RDMS.
SQL Query
↓
Processor (for parsing) and Storage engine (file manager). This is the SQL Engine.
↓
Physical database
Enterprises use SQL server database engines to create relational databases for online transaction processing (OLTP) and online analytical processing (OLAP).
How SQL Database Engines Work
For users to interact with an RDBMS:
- Request in a valid query/database language i.e. SQL.
- The SQL engine can processes the request.
- The SQL storage engine writes to and retrieves data from a data warehouse server, often done by converting the data to a JSON file.
The SQL engine processes data in stages:
- 1. RDBMS parsing a SQL statement via a parse call, to get ready for execution. Three checks are completed — syntax check, semantic check, and shared pool check.
- 2. Query optimization. The RDBMS sifts through data in the fastest way, by choosing the most optimal algorithm and it then executes the query.
I hope you found this useful…
