What is SQL ? Relational Databases (RDBMS) & Non-Relational Databases (JSON Data)
SQL is a Structured Query Language, which is a computer language for storing, manipulating, and retrieving data stored in a relational database.
SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.
Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, Address, City, PostalCode and Country. A field is a column in a table that is designed to maintain specific information about every record in the table.
A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in the above Customers table. A record is a horizontal entity in a table.
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
Why SQL?
SQL is widely popular because it offers the following advantages −
Allows users to access data in the relational database management systems.
Allows users to describe the data.
Allows users to define the data in a database and manipulate that data.
Allows to embed within other languages using SQL modules, libraries & pre-compilers.
Allows users to create and drop databases and tables.
Allows users to create view, stored procedure, functions in a database.
Allows users to set permissions on tables, procedures and views.
A Brief History of SQL
1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational model for databases.
1974 − Structured Query Language appeared.
1978 − IBM worked to develop Codd's ideas and released a product named System/R.
1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software which later came to be known as Oracle.
Some of The Most Important SQL Commands
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
RELATIONAL DATABASES
A relational database stores data in tables. The relationship between each data point is clear and searching through those relationships is relatively easy. The relationship between tables and field types is called a schema. For relational databases, the schema must be clearly defined.
Let’s look at an example:
Here we see three tables all providing unique information on a specific dog. A relational database user can then obtain a view of the database to fit their needs. For example, I might want to view or report all dogs over 100 pounds. Or you may wish to see which breeds eat dry food. Relational databases make answering questions like these relatively easy.
Relational databases are also called SQL databases. SQL stands for Structured Query Language and it’s the language relational databases are written in. SQL is used to execute queries, retrieve data, and edit data by updating, deleting, or creating new records.
POPULAR RELATIONAL / SQL DATABASES
SQL server
SQL Server is a relational database management system developed by Microsoft. They offer multiple editions with varying features to target different users.
Pros: SQL Server boasts a rich user interface and can handle large quantities of data.
Cons: It can be expensive – with the Enterprise level costing thousands of dollars.
MYSQL
First released in 1995, MySQL is free and open-source software, and one of the most popular databases in the world. It is used by many high-traffic websites like Facebook and YouTube. Pros: It’s free and open-source. There’s also a lot of documentation and online support. Cons: It doesn’t scale very well. MySQL tends to stop working when it’s given too many operations at a given time.
PostgreSQL
Where MySQL is based on the relational model, PostgreSQL is based on the object-relational model. Another free and open-source database, PostgreSQL was released in 1996 with an emphasis on extensibility. It’s able to handle complicated data workloads due to its diversified extension functions. Pros: As we said, extensible. If you need additional features in PostgreSQL, you can add them yourself – a difficult task in most databases. Cons: For beginners, installation and configuration can be difficult. There’s also not nearly as much documentation as more popular databases like MySQL.
Non-relational Database
A non-relational database is any database that does not use the tabular schema of rows and columns like in relational databases. Rather, its storage model is optimized for the type of data it’s storing.
Non-relational databases are also known as NoSQL databases which stands for “Not Only SQL.” Where relational databases only use SQL, non-relational databases can use other types of a query language.
There are four different types of NoSQL databases.
Document-oriented databases – Also known as a document store, this database is designed for storing, retrieving, and managing document-oriented information. Document databases usually pair each key with a complex data structure (called a document).
Key-Value Stores – This is a database that uses different keys where each key is associated with only one value in a collection. Think of it as a dictionary. This is one of the simplest database types among NoSQL databases.
Wide-Column Stores – this database uses tables, rows, and columns, but unlike a relational database, the names and format of the columns can vary from row to row in the same table.
Graph Stores – A graph database uses graph structures for semantic queries with nodes, edges, and properties to represent and store data.
Non-relational databases are becoming more popular as more and more businesses begin to leverage big data for analysis and reporting. Since critical data doesn’t always fit well into a pre-defined schema, NoSQL databases allow more flexibility.
Popular Non-Relational/ NOSQL Databases
MongoDB
MongoDB is a document store and is currently the most popular NoSQL database engine in use. It uses JSON-like documents to store data and is run over multiple servers. MongoDB allows for auto-sharding which is a type of database partitioning that separates very large databases into smaller, faster, more easily managed parts called data shards.
Pros: MongoDB is very easy to set up and provides a lot of professional support.
Cons: They don’t allow joins. Joins are used to combine data or rows from two or more tables based on a common field between them. MongoDB does have a LOOKUP function but tells its users not to rely on them.
Redis
Redis – Remote Dictionary Server – is a key-value store. It supports different kinds of abstract data structures such as strings, lists, maps, sets, sorted sets, and more. It’s also open-source.
Pros: It supports a large variety of data types and is easy to install.
Cons: Like MongoDB, it doesn’t support joins. It also requires knowledge of Lua, a high-level programming language.
Relational vs Non-Relational Databases
To summarize the difference between relational and non-relational databases: relational databases store data in rows and columns like a spreadsheet while non-relational databases store data don’t, using a storage model (one of four) that is best suited for the type of data it’s storing.
Keyword meaning
Sharding is a type of database partitioning in which a large Database is divided or partitioned into smaller data and different nodes.
