Relational and Non relational database.
Tags: database, postgres, mongodb, datascience
Published: 2022-04-03 15:03:19

**Database**A collection of information organized to provide efficient retrieval.
**Relational database management systems(RDBMS)** SQl: PostgreSQL ,SQlite, oracle.
**Non relational database management systems** NOSQL: MongoDB, Redis, Aerospike, Couchbase.

**CAP Theorem** It comprises of relational and non-relational databases. CAP stands for consistency, Availability and partition Tolerant.
**Relational database management systems** There are organized and defined in structured sticker schemas.
-Normalized data is the process of storing data into columns and tables to reduce redundancy.
Reliable relational DBs are ACID compliant: 
-Atomicity-modification falls on nothing rule.
-Consistency- Valid data is brought into the database.
-isolation- Requires multiple simultaneous transactions do not impact each other's execution.
-Duration-Ensures any transaction committed in the database are not lost.

**Disadvantages of relational databases**

-Scalability
RDMS are historically intended to be run on a single machine. This means that if the requirements of the machine are insufficient, due to data size or an increase in the frequency of access, you will have to improve the hardware in the machine, also known as vertical scaling.

This can be incredibly expensive and has a ceiling, as eventually the costs outweigh the benefits. Plus, there will potentially come a stage where you simply cannot get hardware capable of hosting the database. The only solution would be to buy a machine that supports better hardware but none of that is cheap.

-Flexibility
In relational databases, the schema is rigid. You define the columns and data types for those columns, including any restraints such as format or length. Common examples of constraints would include phone number length or minimum/maximum length for a name column.

Although this means you can interpret the data more easily and identify the relationships between tables, it also means that making changes to the structure of the data is very complex. You have to decide at the start what the data will look like, which isn’t always possible. If you want to make changes later, you have to change all the data, which involves the database being offline temporarily.

-Performance
The performance of the database is tightly linked to the complexity of the tables — the number of them, as well as the amount of data in each table. As this increases, the time taken to perform queries increases too.


**Non relational databases**
-information is stored in a dynamic ,non-normalized and more flexible manner.
-More designed to be distributed than relational.
-Follow the base system: basically Available, soft state, eventual consistency.
**Major types of NoSQL**
-Key value-stored in big hash tables pf keys and values.
-column oriented- two dimensional arrays whereby each key has one or more key/values pairs attached to it.
-Document stored-similar to key value but with structured and encoding of data.
-Graph based- are like trees with edges, nodes and properties for index-free adjacency.

**Comparison between SQL and NoSQL**
    SQL
 -Strict and structured schemas, tabular data(design).
-Light declarative language, standardized use even with subtly different syntaxes.(CRUD Queries)
-CAP,ACID compliance. Consistency enforced after each transaction.(Transactions)
  NoSQL
-Schemaless, document oriented, dynamic and unstructured.(design)
-No standard interface, differs from system to system.(CRUD Queries).
-CAP,BASE systems, fault tolerant transactions for eventual consistency.
**How to choose**
First you will have to understand the kind of data that you have and how it is stored.
For NOSQL-if you will have a lot of unstructured data and aggregate information with nesting denormalized data.
For SQL- normalized, space efficient, but costly joins!

**In summary what you need to know about relational databases/Non relational database:**

**Relational databases**

-They work with structured data.
-Relationships in the system have constraints, which promotes a high level of data integrity.

-There are limitless indexing capabilities, which results in 
 faster query response times.

-They are excellent at keeping data transactions secure.

-They provide the ability to write complex SQL queries for data 
 analysis and reporting.

-Their models can ensure and enforce business rules at the data 
 layer adding a level of data integrity not found in a non- 
 relational database.

-They are table and row oriented.

-They Use SQL (structured query language) for shaping and manipulating data, which is very powerful.

-SQL database examples: MySql, Oracle, Sqlite, Postgres and MS-SQL. NoSQL database examples: MongoDB, BigTable, Redis, RavenDb, Cassandra, Hbase, Neo4j and CouchDb.

SQL databases are best fit for heavy duty transactional type applications.

**Non relational database**:

-They have the ability to store large amounts of data with little structure.

-They provide scalability and flexibility to meet changing business requirements.

-They provide schema-free or schema-on-read options.

-They have the ability to capture all types of data “Big Data” including unstructured data.

-They are document oriented.

-NoSQL or non-relational databases examples:MongoDB, Apache Cassandra, Redis, Couchbase and Apache HBase.

-They are best for Rapid Application Development. NoSQL is the best selection for flexible data storage with little to no structure limitations.

-They provide flexible data model with the ability to easily store and combine data of any structure without the need to modify a schema.