RDBMS OLPT OLAP & ETL

RDBMS, OLTP, PLAP & ETL

In this chapter we will learn about Azure Data Stores (databases) and Azure Storage capabilities, how to protect your data from loss and how to keep it secured.

Rational Database management provides management of data by tables, using its tables rows and column relationships. Below figure descries typical implementation of RDBMS data solution.

In this users read and write data from table from commands or queries using SQL (Structured Query language). It consists of one or more table and further contains many rows and column. A column contains specific attributes of records like name , age , number , address etc., whereas rows defines an individual records like full details of person who is employee of certain organization. Each record in a row is represented by unique ID called Primary Key, here Employee ID is primary key of each records. A record in a table can reference to another record in another table, by primary key of another record that pointer or reference is called foreign key.

A rational Data base is categorized two database systems

• Online Transaction Processing (OLTP): This refers to transaction oriented application that changes data frequently via read and write, example: e-commerce.
• Online Analytical Processing (OLAP): It is mostly used in reporting and analyzing large data sets, such as Data warehouse. Data warehouse is accessed and processed using Azure Analytical Services , which creates data models using for rendering human readable reports, example: Power BI.

The process by which Data flows between OLTP , OLAP , is referred to as extract , transform load (ELT) , which can be implemented using Azure Data Factory (ADF).

 No SQL Database Systems

These database systems are non-rational and don’t have same table and column like rational database systems. NoSQL databases are used as key/Value store or document store with flexible schemas, A common use case for NoSQL is managing user sessions, user profiles, shopping cart data etc. 

The Non rational Database lacks a relationship between data existing in a table. The data stored using the NoSQL model is achieved by storing the given entity on a single row. Many NoSQL model lack triggers, Stored procedures and join features.

Below figure explains difference between RDMS and NoSQL Scenarios

NoSQL scale very well when amount of data is large to Gigantic in size. It scales very well that companies such as Facebook, LinkedIn and Google use NoSQL Database Structure to store and access their data.  

Storage Solutions:

If you want to deploy Applications in Greenfield scenarios in Azure, you should know the application requirements in order to choose proper storage solution.

Below are some Storage Solution , which can be used for Application built.

• Document Database
• Graph Database
• Key/value pairs
• Object Storage
• RDBMS
• Search Engine Database
• Data analytics / Data Warehouse
• Shared Files

Document Database:

It is a Database, that stores data typically in one or more following formats like XML, JSON, YAML.

The row in a document database consists of two columns: a key and a document. Key is unique, which is used to retrieve the document directly, which allow application to parse and process it. Using Document database, you can query the contents of the documents itself.

The Content are stored in document column and are small and typically in form of collations, lists, or scalar items.

Azure Cosmos DB is the Azure Solution that provides Document Database like capability.

Below figure describes the Document Database Structure

Key/Value Pairs

Key/value Stores consists of two column per row. It is used to store data of gigantic size. It does not supports aggregation means data cannot be found by querying. You can only retrieve data from the store using key’s value.

In this values can be stored as an array of strings separated by comma , as shown in below figure.

A common use-case , where key/Value is stored is Data caching. Azure Cache , Azure Cosmos DB , Azure Table Storage are some Azure products , that work same as Key/value Store.

Graph Database:

Graph Database uses Node and Edge to store data. The information contained with in Node is comprised of all data that defines it. An Edge is what defines relationship between nodes. Edge are routes and VNA ( Virtual Network Adapter ) that define and make connection between nodes. If you have group of Nodes ( VM) , each would have some attributes , that combinedly defines it as unique entity. Each VM in group will have some relationship with each other.