SQL Basics — SQL & NoSQL

As a developer, one of the most essential choices you must take is about which database technology to use given a specific use-case. For many years, the options were limited to different relational databases such as PostgreSQL, MySQL, Oracle which supported Structured Query Language (SQL).

Around the brink of the 21st Century, noSQL first came into picture describing non-relational databases. As opposed to conventional data storage techniques employed by SQL, noSQL used several techniques such as : key-value stores by Redis and Amazon DynamoDB, wide-column stores by Cassandra and HBase, document stores by MongoDB and Couchbase and graph databases by Elasticsearch and Solr.

Explaining SQL

Imagine you’re the sole homeroom teacher in a class of 150 kids. Due to the sheer volume of students in the class, it would be impossible for you to recall everyone’s names by memory let alone any other facts. This is where the attendance register comes in, allowing you to keep track of every student.

This is probably what the attendance register looks like :

+---------+-----------+------------+--------------+
| roll_id | name | birthday | totalPresent |
+---------+-----------+------------+--------------+
| 1 | Jessica | 22/09/2001 | 43 |
| 2 | Alfred | 05/12/2000 | 25 |
| 3 | Jordan | 08/06/1999 | 45 |
+---------+-----------+------------+--------------+

However, you soon realise that a singular register won’t suffice since each student chooses a different optional subject (PE/Arts/French) and you need to keep track of it. So, you use a different register to keep track of which student has which subject.

Upon first instinct you may want to do something as the follows:

+---------+----------+
| roll_id | subject |
+---------+----------+
| 21 | PE |
| 22 | PE |
| 23 | Arts |
+---------+----------+

While this works, the subject attribute stores type string which is not unique for each student and may need you to input the same string over and over again for several IDs.

This can be simplified by doing the following:

+------------+----------+
| subject_id | subject |
+------------+----------+
| 1 | PE |
| 2 | Arts |
| 3 | French |
+------------+----------+
+---------+-------------+
| roll_id | subject_id |
+---------+-------------+
| 21 | 1 |
| 22 | 1 |
| 23 | 2 |
+---------+-------------+

Relational Databases

What we saw above is called a relational database — a set of formally described tables from which data can be accessed or reassembled without having to reorganise the tables themselves.

A hallmark feature of the most popular relational databases is a query language called SQL (Structured Query Language). Had the teacher moved our manual system to a computer, she could easily query across all tables using the roll_id such as — which student has been present for the maximum number of days and is taking French?

Tables can have hundreds, thousands, sometimes even millions of rows of data. These rows are often called records.

SQL Relational Database

Tables can also have many columns of data. Columns are labeled with a descriptive name (say, age for example) and have a specific data type.

For example, a column called age may have a type of INTEGER (denoting the type of data it is meant to hold).

Among the world’s most popular choices for a SQL Database Management System is MySQL, which is open source and implemented primarily as a Relational Database Management System (RDBMS). A relational database management system (RDBMS) is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database.

Explaining NoSQL

Let’s take the example of a homeroom teacher again and visualise how utilising NoSQL techniques can be used to store a lot of data at once.

{    “_id”:”dkdigiye82gd87gd99dg87gd”,    “name”:”Cody”,    “birthday”:”09–12–2006",present” : [T, T, F, T, T, T, T, T…]}

As a teacher, you need to be mindful of any allergies a student may have to assist them instantaneously in case of an allergen attack, however, you need to keep in mind that not every student may have an allergy. In case of our previous example, where each column (attribute) needed a value, you would need to fill in null values for kids with no allergies meaning that you would end up 150 records of allergies regardless of whether some of them are null or not. When using NoSQL, fields (keys) are variable and can be optionally used in some collections,

{     “_id”:”dh97dhs9b39397ss001",    “name”:”Tanner”,    “birthday”:”09–12–2008",present” : [T, T, F, T, T, T, T, T….]    “allergies” : “peanut butter”}

NoSQL Databases

When people use the term “NoSQL database”, they typically use it to refer to any non-relational database. Some say the term “NoSQL” stands for “non SQL” while others say it stands for “not only SQL.” Either way, most agree that NoSQL databases are databases that store data in a format other than relational tables.

different types of NoSQL Databases

One way of understanding the appeal of NoSQL databases from a design perspective is to look at how the data models of a SQL and a NoSQL database might look in an oversimplified example using address data.

The SQL Case

For an SQL database, setting up a database for addresses begins with the logical construction of the format and the expectation that the records to be stored are going to remain relatively unchanged. After analysing the expected query patterns, an SQL database might optimise storage in two tables, one for basic information and one pertaining to being a customer, with last name being the key to both tables. Each row in each table is a single customer, and each column has the following fixed attributes:

Last name :: first name :: middle initial :: address fields :: email address :: phone numberLast name :: date of birth :: account number :: customer years :: communication preferences

The NoSQL Case

Each type of NoSQL database would be designed with a specific customer situation in mind, and there would be technical reasons for how each kind of database would be organised. The simplest type to describe is the document database, in which it would be natural to combine both the basic information and the customer information in one JSON document. In this case, each of the SQL column attributes would be fields and the details of a customer’s record would be the data values associated with each field.

For example: Last_name: “Jones”, First_name: “Mary”, Middle_initial: “S”, etc

You’ve reached the end of this article. I hope this article served as a good introduction to the world of databases, SQL and NoSQL.

--

--

--

As a beginner, coding concepts may seem complicated — deterring understanding and increasing dependency on several resources. This blog aims to simplify common concepts using analogies and scenarios for developers of all stages to easily understand.

Recommended from Medium

Password Vault Screen using Tkinter

HOW TO AUCTION RIGHT? 🧐

PASAI DONIBANE [ SAN JUAN ]

Building a web application with ASP.NET Core MVC, Entity Framework Core, MariaDB & Bootstrap.

How to Create a Simple Live Streaming Event with the help of AWS Elemental Live?

A simple CSS guide to classy Apple-like navigation bar

Apache Kafka: Log Compaction

How to Invest in Web 3.0: Benefits, Risks and Strategies

How to Invest in Web 3.0: Benefits, Risks and Strategies

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Arushi Aggarwal

Arushi Aggarwal

More from Medium

To import spreadsheet or excel data into a db file in SQLite format

Your Journey to Understand Regular Expressions in a PostgreSQL database

SQL Server checklist for better performance

ALL ABOUT EMBEDDED AND DYNAMIC SQL