SQL, NoSQL – Everything You Need to Know!

A famous joke on NoSQL did its round on internet -“A DBA walks into a NoSQL bar, but turns and leaves because he couldn’t find a table”.

Did the DBA headed back to his home or went to an SQL bar, the story never concluded. However the debate of SQL and NoSQL is still on! The simple reason being that all the sectors are now moving online and so the technology need to support this movement. The data which is growing bigger and bigger each day is needed by firms may be for analytics, for research or for sales. Either the companies simply store the lump sum data in another dead server or they with the new technology use this data to improve and enhance the sales. The storehouse in the technical world is referred to as “SQL Database” and quite recently another of this advancement has sneaked in — NoSQL. This post would narrate about the two technologies and the current warfare of which one is better, and why at MPower we use these two technologies to assist our client.

SQL NoSQL – Everything You Need To Know!

What is a Database?

A database could be imagined as a file folder with a lot of information, but placed in such a unique way that it’s easy in accessing, sorting, altering and analyzing the data. But this database stores the data in electronic format and so the file folder you imagined could be like a computer or a server or even your phone is as good as a database.

However a very efficient system DBMS or database management system is used to access the database. A DBMS could be defined as an interface to let you access, analyze and alter the data of one or more than one databases. Some of the DBMS available in the market are – MySQL, Microsoft SQL Server, PostgreSQL, Oracle, IBM DB2 and Sybase. In laymen terms, one can refer DBMS as an interface for the end users using the application and the database. However at times in the industry DBMS is also referred to as a database.

Types Of DBMS

Each DBMS offers a unique kind of framework to store and manage data, which could be termed as database models. The different framework available logically structures the data, and is the basic building block on how the data would look and function in the system. The different types of models available are –

Types of DBMS Model

Hierarchical Database

If the relationship among data in the DB is established in a such a way that one item is present as the subordinate of another one

Network Database

If the relationship among data in the DB is of type many to many

Relational Database

If the relationship among data is in the form of a table linked with three keys – relation, attributes, and domain

Object Oriented Database

If the data is represented in the form of objects as an analogy of Object oriented programming

A Quick Glimpse At the Past

Birth Of SQL

Donald D. Chamberlin and Raymond F. Boyce at IBM were the two inspirations who molded SQL in early 1970’s, which was initially named as SEQUEL [Structured English QUEry Language] to access and manage data stored in IBM’s unique quasi-relational database management system, System R. Later on the SEQUEL name had to change to SQL, as SEQUEL was a trademark of the UK-based Hawker Siddeley aircraft company.

In the later phase, Relational Software, Inc. [now Oracle Corporation] saw the potential in the idea designed by the two legends at IBM, and worked on to provide their own relational database to sell it to prospects like U.S. Navy, Central Intelligence Agency, and other U.S. government agencies. However, the first footsteps of the SQL database were taken in the year 1979 when Relational Software launched their version Oracle V2 (Version2) for VAX computers

Birth Of NoSQL

In 1998, Carlo Strozzi described his open source relational database, which was a lighter version as NoSQL. He explains it further that the new version is far away from its relational model and should, therefore, be named as No Relational i.e. NoREL.

Later on in the year 2009 Eric Evans mentioned the term NoSQL in an event to converse about open-distributed databases. The term is of significance as it states an evolution of database systems that are non-relational, basically challenging the traditional model.

Some of most admired NoSQL databases are Apache Cassandra, MongoDB, Solr, Redis, ElasticSearch, Splunk HBase, Neo4j, and Memcached.

NoSQL- the newbie and it’s New Features

NoSQL buzz is more of an extension of guerilla marketing that exclusively brings a bunch of broad group of technologists and technology under one banner. The sudden storm of buzz that strengthen the innumerable solutions that subsist under the term “NoSQL” were available in the past but only to those experts who had a unique requirement and the desire to build and develop it. A couple of core sectors where using NoSQL is a mandate as no other option could work and have shown a good deal of success. For any firm the juggle to stick to traditional model or pick the new concept is debatable and would not have an affirmative answer.

The major properties/features of NoSQL are, but before that just a note that not all the properties might be present in the NoSQL Databases available in the market. But as NoSQL technology is expanding each day, most of the features should have been made available –

1. Easy to use in conventional load-balanced clusters
2. Persistent data (not just caches)
3. Scale to available memory
4. Have no fixed schema’s and allow schema migration without downtime
5. Have individual query systems rather than using a standard query language
6. Are ACID within a node of the cluster and eventually consistent across the cluster

Are SQL and NoSQL Really Different?

The buzz is that SQL is a thing of past and NoSQL is the present and future! However, the hardcore reality seems a bit different. Let’s quickly look at the major differences the two offerings have and then decide if anyone is the winner, or there is a tie.

Functionality	SQL	NoSQL
Data Storage	SQL follows the relational model, which comprises of rows and columns. Each row represents all the information about one specific entry/entity, and columns are distinct data points.	NoSQL follows a non-relational model i.e. the data is not stored in a tabular form, instead it is stored in small chunks termed as Collections. The collection could be like graphs, key-value pairs or documents.
Schemas and Flexibility	Schemas are locked and static before the data entry	Schemas are dynamic and could be altered at runtime
Scalability	Scaling for an RDBMS is vertical that in turn means storing data across multiple servers and so is considered to be expensive	Scaling for a non-RDBMS is horizontal, one could use cheap servers for cloud management to store data, which in turn could be cost-effective
ACID amenability [Atomicity, Consistency, Isolation, Durability]	All RDMS are ACID compliant	NoSQLis not an ACID compliant technology.
CAP Theorem Amenability [ Consistency, Availability, Partial Tolerance]	CAP theorem adoption and application is not possible for SQL	NoSQL databases could let you choose between the two priorities as per the theorem.

Components of SQL

Each DBMS has four major components, or you can say the building blocks. There might be some variation from one system to another, but they all work on same basic principle –

Modeling Language

The language is used to define the schema or how a Database should be structured i.e. whether it should be a relational, a hierarchical or network model

Data Structure

The way to structure your data i.e. records, fields, objects, and files, so they are easily accessible and do not affect the performance.

Database Query Language

The language is essential and a mandate to allow different users to grill the database while analyzing, updating and also controls the security of the database.

Transactions

A relational DBMS must have a transactional mechanism that strictly assures the ACID compliance of the database. It also guarantees that data security would not be compromised in spite of the multiple accesses provided to multiple users.

Components of NoSQL

The debate about moving to a NoSQL DB from RDBMS is quite evident, and one of the key reasons being the flexibility of data models NoSQL offers to its users. A NoSQL data model that is also referred to as schema-less and could support many such use cases and scenarios that do not fit well with RDBMS. A NoSQL DB could readily accept all types of data – structured, semi-structured, and unstructured providing the flexibility to the user on how to structure the data. However in NoSQL world there is no industry standard on how to structure the data model and hence varies from one DB to another. As a result, it facilitates us to understand the key differences in the various NoSQL database models:

• Key-value stores – Collections comprised of unique keys having 1-n valid values
• Column families – Distributed data stores in which a column consists of a unique key, values for the key, and a timestamp differentiating current from stale values
• Document databases – Systems that store and manage documents and their metadata (type, title, author, creation/modification/deletion date, etc.)
• Graph databases – Systems that use graph theory to represent and store data as nodes (people, business, accounts, or other entities), node properties, and edges (lines connecting nodes/properties to each other).

Types Of NoSQL Data Model

Structure of NoSQL

Pictorial Representation Of Structure of NoSQL

The separate layer of data management and storage offers major benefit to DBA’s. As a DBA does not need to focus on the task of high-performance, scalable data storage, and concentrate on access to a data management layer tasks. It eases the life of DBA and also boosts the performance of the system.

NoSQL Query Language

The developer often have a simple query – How to query a NoSQL database? Nevertheless the huge data stored in the database is not of any use unless someone can retrieve it structures and analyze it. NoSQL databases do not provide a high-level declarative query language like SQL. Instead, querying these databases is data-model specific.

In the industry, there are two modes available to access the data stored on NoSQL platforms –

1. RESTful [Representational State Transfer] interfaces
2. Query API

However, a couple of other query tools has been developed and available in the market in an attempt to multiple NoSQL databases. These tools typically work across a single NoSQL category. Some of the NoSQL query language available in the market are UnQL[the query language specification for NoSQL] or JSON.

BASE Property of NoSQL

The RDBMS relies and embraces the ACID model, and NoSQL relies and showcases a softer model known as the BASE model. The BASE model offers the flexibility and multiple approaches to the management and curation of unstructured data. It consists of three principles:

Basic Availability

The NoSQL database approach focuses on the availability of data even in the presence of multiple failures. It achieves this by using a highly distributed approach to database management. Instead of maintaining a single large data store and focusing on the fault tolerance of that store, NoSQL databases spread data across many storage systems with a high degree of replication. In the unlikely event that a failure disrupts access to a segment of data, this does not necessarily result in a complete database outage.

Soft State

BASE databases abandon the consistency requirements of the ACID model pretty much completely. One of the basic concepts behind BASE is that data consistency is the developer’s problem and should not be handled by the database.

Eventual Consistency

The only requirement that NoSQL databases have regarding consistency is to require that at some point in the future, data will converge to a consistent state. No guarantees are made, however, about when this will occur. That is a complete departure from the immediate consistency requirement of ACID that prohibits a transaction from executing until the prior transaction has completed, and the database has converged to a consistent state.

The BASE model may not be apt for every situation, but it is a flexible option to the ACID model for databases that do not require stringent adherence to a relational model.

Pros and Cons of SQL and NoSQL

DB	Pros	Cons
SQL	Simplicity of relational model	Could only be scaled horizontally
	Solid theoretical basis and normalization rules	In case of technical issue, vendor support is needed causing delays
	Simple and very powerful SQL language that was resembling human language	Schema could not be updated dynamically
	Easy data manipulation
	ACID properties
	High level of standardization
	Standardized API
NoSQL	Mostly open source	Immaturity. Still lots of rough edges
	Horizontal scalability. There’s no need for complex joins and data can be easily shared and processed in parallel	Possible database administration issues. NoSQL often sacrifices features that are present in SQL solutions “by default” for the sake of performance.
	Support for Map/Reduce. It is a simple paradigm that allows for scaling computation on cluster of computing nodes	No indexing support (Some solutions like MongoDB have to index, but it’s not as powerful as in SQL solutions).
	No need to develop fine-grained data model – it saves development time	No ACID
	Very fast for adding new data and for simple operations/queries	Bad reporting performance.
	No need to make significant changes in code when data structure is modified	Complex consistency models (like eventual consistency). CAP theorem states that it’s not possible to achieve consistency, availability and partitioning tolerance at the same time. NoSQL vendors are trying to make their solutions as fast as possible, and consistency is a most typical trade-off.
	Ability to store complex data types (for document-based solutions) in a single item of storage	The absence of standardization.

How could we bridge gap between SQL and NoSQL?

We just had a glimpse of how the traditional and modern DB technology differ in many points, so which one to choose or is there a way to use both the technologies and reap the benefits. The decision is made based on the project requirements, so if your project requires a lot of data, and you need to run dynamic ad-hoc queries relational DB with SQL would be the best solution. However, if you want to keep your objects in a persistent state and have high-performance access to then use a key value store.

The bridging the gap could be approached by building a layer between the SQL code and interpreter, and the actual database underneath it, providing a way to run SQL queries on top of a NoSQL system (e.g., Cassandra), at the cost of a possible reduction in performance.

A brief on what we at Mpower use as our database management system

MPower Wallet offers many services to its client like Mobile and DTH Recharge, Bill Payments, and Money Transfer. Once a user enrolls for our services, our NoSQL database and logs files stores the user details and also the kind of service we had been using or browsed. Later the stored information is analyzed by applying certain filters say duration or service picked or even the region from which the service was accessed. The data provides a clear picture of the depth of the services and how is the market reacting to the offerings. The improvement areas are also shown which helps the team in streamlining and enhancing the market strategy. We just saw how the information stored in the database could be useful and help the team in serving our loyal customers better and efficiently.

Conclusion

The best platform for you depends greatly on what you need from your database, and what kinds of queries you are demanding of your data. It also depends on what kind of database management plan you have in place, whether or not you’re seeking a renovation on its current state

Samiksha Seth with inputs from Alok Kumar