Relational and Non Relational Databases

Relational Databases

A relational database or relational database management system(RDBMS) stores data in the form of a table. The table consists of rows and columns, and in a relational database rows are referred to as records, and columns are referred to as fields. The term relational database was first coined in the 1970's by E.F. Codd at IBM, and later in 1974 Donald D. Chamberlin and Raymond F. Boyce released SQL or Standard Query Language, which allows the user to interact with the relational database system.

As already defined, a relational database consists of tables, and a table consists of rows and columns. Each table has a special column that contains only distinct and unique values that are called primary key. This primary key is used to define the relationship between the tables, and if the 1st table(Student) primary key, i.e., ID is used in the 2nd table(College_Student), then that column is called foreign key(StudentID). So, "ID" is the primary key for the Student table, and "ID"(StudentID) is the foreign key for the Course_Student table. Now, we have a database that contains two tables, now if we want to change the change our database design or improve this existing database design, there comes database normalization(It is the process of structuring the database to remove data redundancy and improve data integrity).

The most widely and popular way of interacting with a relational database is SQL(Structural Query Language), which allows access, filter, and modify data.

Popular Relational Database

• MySQL

It is an open-source database management system, launched in 1974 by the developers of IBM. The most common use of MySQL databases is for web databases.

Oracle Database

It is the first database designed for enterprise grid computing, the most flexible and cost-effective way to manage information and applications.

Microsoft SQL Server

It is relational database management which is developed by Microsoft. It is known for storing and retrieving data as requested by other software applications, which can be run using the same PC or another PC or network.

PostgreSQL

It is also an open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

SQLite

It is the most used database engine in the world. It is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured. It can be built into mobile phones and most computers.

MariaDB

It is one of the popular open relational databases initially designed by the developers of MySQL developers. It is known for its performance, stability, and openness(open-source).

Advantages of Relational Databases

• ACID Compliance: The presence of 4 properties, i.e., Atomicity, Consistency, Isolation, and Durability. When the database possesses these four ACID properties, they are said to be ACID compliant.
  • Atomicity: It refers to the integrity of the database transaction (By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway, i.e., transactions do not occur partially. Each transaction is considered as one unit, and either runs to completion or is not executed at all).
  • Consistency: It means that only data which follows those rules is permitted to be written to the database. If a transaction occurs and results in data that does not follow the rules of the database, it will be ‘rolled back’ to a previous iteration of itself (or ‘state’) which complies with the rules. This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.
  • Isolation: It refers to the ability to concurrently process multiple transactions in a way that one does not affect another. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order.
  • Durability: It is to make those failures invisible to the end-user. This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs. These updates now become permanent and are stored in non-volatile memory. The effects of the transaction, thus, are never lost.

Note: The ACID properties, in totality, provide a mechanism to ensure correctness and consistency of a database in a way such that each transaction is a group of operations that acts a single unit, produces consistent results, acts in isolation from other operations and updates that it makes are durably stored.

• Normalization: It is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update, and Deletion Anomalies. Normalization rules divide larger tables into smaller tables. Their purpose is to eliminate redundant data and ensure data is stored logically.

• Accuracy: Tables consist of primary and foreign keys, which ensures that no duplicate data is present in data. It will help us as no duplicacy is there, so it will enforce our data accuracy.

• High Security: In a relational database, we can divide the data among tables, and we can divide the tables as confidential or not. It can make our data safe. Let's under this with an example, Suppose a company has a large volume of data saved in MySQL server, and we have made some tables as confidential and some as public, and we need to do some analysis on confidential tables. When a data analyst wants to access those tables, they will get to enter the username and password to access those confidential tables and can modify, access, or analysis the tables, and everything gets recorded, depending on the access given to the tables.

Disadvantages of Relational Databases

• Loss of Information: Big organizations tend to have large databases containing a lot of information. When this information is transferred from one system to another, in this case, we might lose our data.

• Expensive: The cost to set up a relational database is very expensive. For this purpose, we need software to maintain the database, and a separate technician is required to maintain the software. This can increase in cost for a company.

• Slow Performance: If the database is huge and contains interconnected tables based on their primary keys. When getting the required data, the response given on the queries makes it slower, and it makes our databases quite complex to get the data.

Non-Relational Database

Non-Relational Databases are also called No-SQL databases, that doesn't require any table, fields, or records. This type of database has existed since the 1960s, but the term No-SQL was coined in the late 90's and early 21'st century.

NoSQL databases are completely different from SQL databases and work differently. It has to deal with semi-structured or unstructured data. Rather than containing tables, it consists of files within various folders. They can possess any kind of data, whether JSON, XML, etc. So, creating and managing data in NoSQL is easy and faster.

Facebook(Meta) is a very popular example of using NoSQL databases.

There are various types of Non-relational databases like:

• Documents Databases.
• Graph Databases.
• Wide Column Databases.
• Key-value Databases.

Popular Non Relational Database

• MongoDB

It is a cross-platform document-oriented database system, classified as a NoSQL database. As it is a document-oriented database, it uses JSON-like documents.

• Apache Cassandra

It is a free and open-source database system used to handle a large amount of data across many servers.

• Redis

It is a memory-based database. It can support many data structures like strings, lists, maps, sets, etc.

• Couchbase

It is also an open-source and document-oriented database system. It can serve many concurrent users by creating, storing, retrieving, manipulating, presenting data.

• Neo4

It is also an open-source database system. It is based on graphs-based databases with online backup and high availability extensions under the closed-source commercial license.

Advantages of Non-Relational Databases

• Unstructured Data: These types of databases can store both structured and unstructured, whereas relational databases can store only data in a structured way.

• Large Data & Cheap: These databases can scale to accommodate any type of data while maintaining a low cost.

• Performance & Fast These types of databases are defined for good performance, and it contains unstructured data, while in relational databases data is stored in tables, so accessing data is a bit slower than this.

Disadvantages of Non-Relational Databases

• ACID: NoSQL doesn't guarantee ACID transactions as they are BASE(Basically Available, Soft State, Eventual Consistency).

• Backup: The Disadvantage of Non-Relational Databases is that they didn't have a backup in these types of databases. MongoDB has some backup tools, but they aren't up to the mark. At last, NoSQL databases aren't mature enough to get the proper backup.

• Standard Rules In NoSQL databases, there are no standardized rules of databases. The design and query language vary from one NoSQL database to another, so there is no standard process to access the data, whereas, in Relational databases, we have some common ways to access the data.

Additional Resources

1. What is the difference between DBMS and RDBMS