Matz

Data is generated everywhere using different system, application and devices, in multiple structures and format

Data is a valuable asset , which provide useful information and help to take critical business decisions when analyzed properly

It is necessary to store, analyze and capture data since it has become a primary requirement for every company across the globe.

Finding out Different Data Formats

Data structure in which the data is organized represent entities, And each entity has one ore more attributes or characteristics.

Data can be classified into different formats -Structured -Unstructured -Semi-Structure

Structured This is fixed schema and has different fields and properties. Schema is organized in a tabular format in rows and columns. Rows represent each instance of a data entity and column represent attribute of the entity.

Semi-Structured This has some structure and allow variation between entity instances. One example of semi-structured data is JSON(JavaScript Object Notation)

Unstructured This has data without any structure. Example can be document, images, audio, video and binary files.

Various options to store data in files

Two broad categories of data store in common use

File store Storing the data on a hard disk or removable media such as USB drives or on central shared location in the cloud

File Format used to store data depends on a number of factors including

Type of the data being stored

Application that will need ro read/write and process data

Data files readable by human or optimized for efficient storage and processing

Common File Formats

Delimited text files Data is separated with field delimiters and row terminators. Most commonly used format is CSV Data

-JSON Data is represented in hierarchical document schema which is used define object that have multiple attributes.

Databases

-XML Data is represented using tags enclosed in angle brackets to define elements and attributes.

-BLOB Data is stored in binary format 0's and 1's.Common type of data stored as binary include images, audio, video and application specific documents.

-Optimized File Format Some specialized file formats that enable compression, indexing and efficient storage and processing have been developed.

Common optimized file format include Avro, Optimized Row Columnar Format(ORC) and Parquet.

Various options to store data in database

Two ways data are stored in database -Relational Database -Non-Relational Database

-Relational Database This is used to store and query structured data. Data stored in the represent entities. Each instance of an entity is assigned a primary key which uniquely identifies and these keys are used to reference the entity instance in another table. Table are managed and queried using SQL which is based on ANSI standard.

-Non-Relational Databases This is often referred as NOSQL Database. There are 4 common types of nonrelational database commonly used

KeyValue Database - Each record consist of a unique key and associated value

Document Database - Specific form of Key Value database

Column Family Database - Store tabular data in rows and columns

Graph Database - Which store entities as nodes with link to define relationship between them

Understand Transactional data processing solutions

A system records transaction that encapsulate specific events that the organization want to track. Transaction system are often high volume handling millions of transaction every day often referred as Online Transactional Processing OLTP. OLTP system support so called ACID semantics

Atomicity-Each transaction is a single unit which either fails or succeed completely. Consistency-Transaction can only take data in the database from valid state to another. Isolation- Concurrent transaction cannot interfere with each other. Durability-When a transaction is committed, it remains committed.

OLTP is often used for supporting Line of Business Application

Understand Analytical data processing solutions

Analytics can be based on a snapshot of the data at a given point in time or a series of snapshot. It uses read-only system that store vast volumes of historical data.

Analytic usually look like

Data file stored in central data lake for analysis

ETL process copies data from files and OLTP DB into a Datawarehouse. 3.Data in data warehouse is aggregated into OLAP(Online analytical processing) model. 4.Data in data lake, data warehouse and OLAP can be queried to produce reports, visualization and dashboards.

Different Types of user might perform data analytic work at different stages -Data Scientist might work directly with files in a a data lake to explore and model data -Data Analyst query table directly to produce reports and visualization -Business user consume aggregated data in the form of reports and dashboards.

What is SOLID Principle?

'In object-oriented computer programming, SOLID is a mnemonic acronym for five design principles intended to make software designs more understandable, flexible and maintainable.'

SOLID is an acronym for the following.

S: Single Responsibility Principle (SRP)

O: Open-closed Principle (OCP)

L: Liskov substitution Principle (LSP)

I: Interface Segregation Principle (ISP)

D: Dependency Inversion Principle (DIP)

S: Single Responsibility Principle (SRP)

SRP says, "Every software module should have only one reason to change.".

Any class must have one and only one reason to change. In other words, a class must only have a single responsibility. This principle is simple, but not always easy to follow

Example

class Customer {

public function createCustomer(Request $request)

{ // Create customer

}

}

class Order {

public function submitOrder (Request $request)

{ // Submit Orders

}

}

O: Open/Closed Principle

The Open/closed Principle says, "A software module/class is open for extension and closed for modification."

Objects or entities must be open for extension but closed for modification. A class must be easily extensible without requiring modification of the class itself. The goal of this principle is to help create well-encapsulated, highly cohesive systems. 

Example

interface ISavingAccount {

public function CalculateInterest();

}

class RegularSavingAccount implements ISavingAccount {

public function CalculateInterest()

{

//Calculate interest for regular saving account

}

}

class SalarySavingAccount implements ISavingAccount {

public function CalculateInterest()

{

//Calculate interest for regular saving account

}

}

L: Liskov Substitution Principle

The Liskov Substitution Principle (LSP) states, "you should be able to use any derived class instead of a parent class and have it behave in the same manner without modification.". 

Derived types must be completely substitutable for their base types. New derived classes must just extend without replacing the functionality of old classes.

Example

class Bird{

// Methods related to birds

}

class FlyingBirds extends Bird

{

public function Fly()

{

Return “I can Fly”;

}

}

class Ostrich extends Bird

{

// Methods related to birds

}

I: Interface Segregation Principle (ISP)

The Interface Segregation Principle states "that clients should not be forced to implement interfaces they don't use. Instead of one fat interface, many small interfaces are preferred based on groups of methods, each serving one submodule.".

Clients must not be dependent upon interfaces or methods they don't use. Keeping interfaces segregated helps a developer more easily know which one to call for specific functions and helps us avoid fat or polluted interfaces that can affect the performance of a system. 

Example

interface OnlineClientInterface

{

public function acceptOnlineOrder();

public function payOnline();

}

interface WalkInCustomerInterface

{

public function walkInCustomerOrder();

public function payInPerson();

}

class OnlineClient implements OnlineClientInterface

{

public function acceptOnlineOrder()

{

//logic for placing online order

}

public function payOnline()

{

//logic for paying online

}

}

class WalkInCustomer implements WalkInCustomerInterface

{

public function walkInCustomerOrder()

{

//logic for walk in customer order

}

public function payInPerson()

{

//logic for payment in person

}

}

D: Dependency Inversion Principle

The Dependency Inversion Principle (DIP) states that high-level modules/classes should not depend on low-level modules/classes. First, both should depend upon abstractions. Secondly, abstractions should not rely upon details. Finally, details should depend upon abstractions.

This principle helps maintain a system that’s properly coupled. Avoiding high-level class dependencies on low-level classes helps keep a system flexible and extensible.

Example

interface EBook

{

function read();

}

class PDFBook implements EBook

{

function read()

{

return "reading a pdf book.";

}

}

class EBookReader {

private $book;

function __construct(EBook $book) { $this->book = $book; } function read() { return $this->book->read(); }

}

$book = new PDFBook();

$read = new EBookReader($b);

$read->read();

Conclusion:

What are Joins ?

Joins in sql command which are used to combine rows from one or more tables, based on the related column between those tables.

In a JOIN query, a condition indicates how two tables are related:

Choose columns from each table that should be used in the join. A join condition indicates a foreign key from one table and its corresponding key in the other table.

Specify the logical operator to compare values from the columns like =, <, or >.

Example

SELECT Employee_id,Employee_name, Employee_DOB, Department_Name FROM #Departments INNER JOIN #Employees ON #Departments.Department_id = #Employees.Department_ID

As shown in the query above, first we need to specify the columns we want to retrieve within the SELECT clause, then we need to specify the tables we need to read from and to specify the join type within the FROM clause, also we need to specify the columns used to perform the join operation after the ON keyword

Different Types of Joins

1.) Inner Join

Return matching records from the left and the right tables.

2.) Left Outer Join

Return all the records from the left table and matching records from the right table.

3.) Right Outer Join

Return all the records from the right table and matching records from the left table.

4.) Full Join

Return all the records from left and right table

Keep Learning!

What is SQL ?

SQL stands for Structured Query Language. It is used to manipulate and execute queries on databases which has tables, views, triggers, procedures, functions and many more. SQL Commands

SQL Commands are divided into four subgroups

1.) DDL - Data Definition Language

it deals with Database Scheme and description and how to data should reside in the database.

Example are

Create Table, Alter Table, Drop Table, Truncate Table

2.) DML - Data Manipulation Language

It deals with Data Manipulation used to store, modify , retrieve, delete and update data in the database.

Examples are

Select , Insert, Update, Delete

3.)DCL - Data Control Language

It deals with rights, permissions and other control of the database system.

Examples are

Grant , Revoke

4.) TCL - Transaction Control Language

It deals with transaction within the database.

Examples are

Commit, Rollback, SavePoint,Begin Transaction.

In the next blog we will learn about more SQL Topics.

Keep Learning!!!