DATABASE

Relations database was invented in 1970, and since then it has been growing largely due to its capability.

The Management system is the main reason behind its growth as they have implemented it very well for their purpose. It is the effective way to work with large data.

What is Database?

The database is the collection of data which can be referred as information stored in an organized way so that they can be managed.

What is DBMS?

DBMS or Data Base Management System is the application which is used to store, manipulate and retrieve data in the database. DBMS handles all the tasks related to Data.

In simple words, the DBMS created the database using the information you give.

Relational Database Management System

The RDBMS establishes the relationship between the tables in the database. It implements the Relational Model with data to work.

Relational database stores the data in a hierarchy where the attributes are having the relationship with the entity in the other table.

To start with RDBMS, you have to create a structure to define and store the data in the database. The table contains rows and columns.

The row is identified with the Keys.

Columns are identified by attributes

what is the difference between DBMS and RDBMS?

DBMS stores the data as files but here in RDBMS you will store all the data in table format and can be related to each other.

What is Relations?

The relation in the RDBMS products is considered as Tables with the set of data. Also, the data stored, retrieved using mathematical set can be included.

The attributes contain the data types. When you enter data into the column to form a record, the data should match the data type.

What is Data Type?

A data type is simply a classification of variables used to store the data.

Here the data type represents which value a variable should contain and what type of mathematical, logical and relational operations can be performed with that value without error.

The Top Relational Databases

Applications mainly depend on data. Here in this article, we are going to see three Open source RDBMS.

All of them are popular and widely used for application development.

They are

PostgreSQL

PostgreSQL is the most advanced Object-based Relation Database Management System which uses Object, Classes, and Inheritance.

MYSQL

We all know very about this Relational Database Management System. MySQL is very easy to install and use

SQLite

SQLite is the powerful embedded RDBMS used by applications which require database access.

PostgreSQL

PostgreSQL is the open source relational database management system developed by professionals from all over the world.

Any organizations do not own it, and the source code is entirely available with free of cost.

The specialty of the PostgreSQL is its versatility and strong support of platforms.

The platform it supports:

1. Linux
2. Unix
3. MAC OS X
4. Windows
5. Solaris
6. FreeBSD
7. NetBSD
8. OpenBSD
9. Advanced Interactive eXecutive by IBM

It also supports multimedia such as text, photos, videos, and audios.

PostgreSQL differs a lot from RDBMS as it entirely supports Relational system/Object Oriented Model which supports Reliable transactions(ACID).

Here you should know what is ACID in Databases?

ACID stands for Atomicity, Consistency, Isolation and Durability.

These are important for reliable transactions.

What is Atomicity?

Either an operation has to be completed entirely, or it should not happen. This is what we can call atomicity. For, e.g., if you are making a bank transaction and the power goes down, Here the entire operation won't be performed.

What is Consistency?

In a transaction, the database has to go from one state to another state. If any error occurs, they database should not be changed and should be reverted to the previous stats as it was before the transaction.

What is Isolation?

In the Isolation process, the transactions which are being executed sequentially. One Transaction doesn't know about another one here.

What is Durability?

If a transaction is executed, it should be recorded in a non-volatile memory to keep track of it even when the failure occurs due to various reasons.

The PostgreSQL is very powerful, and it supports multiple transactions very effectively without read-lock. Multi-version Concurrency is the reason behind that, and it is ACID compliance.

Here multiple transactions can read the memory at the same time, and they don't know each other's operational change in the memory.

PostgreSQL can be programmed highly, and it is extensible with stored procedures. They are the containers of executable codes which is required to be often executed.

But one thing to note here is that this DBMS does not have much popularity like MYSQL has. But a lot of operating systems have been providing the PostgrSQL application as their default package.

PostgreSQL Data-Types

Numeric Data Type

smallint
Stores whole numbers, small range.

integer
Stores whole numbers.Use this when you want to store typical integers.

bigint
Stores whole numbers, large range.

decimal
user-specified precision, exact

numeric
user-specified precision, exact

real
variable-precision, inexact

double precision
variable-precision, inexact

serial
auto incrementing the integer

bigserial
large auto-incrementing integer

Character Type

character varying(n), varchar(n)
variable-length with limit

character(n), char(n) fixed-length, blank padded
text variable unlimited length

Date/Time Type

timestamp [ (p) ] [ without time zone ]
both date and time (no time zone)

timestamp [ (p) ] with time zone
both date and time, with timezone

date
date (no time of day)

time [ (p) ] [ without time zone ]
time of day (no date)

time [ (p) ] with time zone
times of day only, with time zone

interval [ fields ] [ (p) ]
12 bytes

point
Point on a plane

line
Infinite line (not fully implemented)

lseg
Finite line segment

box
Rectangular box

path
Closed path (similar to polygon)

path
Open path

polygon
Polygon (similar to closed path)

circle
circle

Let us the PostgreSQL advantages and Disadvantages

Advantages of PostgreSQL

Open Source SQL RDBMS

POstgre SQL is open source RDBMS which can be downloaded free of cost.

Good community Support

PostgreSQL has been developed by many people from all over the world. You can ask all the doubts related to the Postgre in their forums.

The dedicated professionals and master users of PostgreSQL will answer all your questions.

Widely Supported

A lot of third parties are supporting PostgreSQL including Operating Systems and Programming.

Extendable

We have discussed Stored procedures usages in PostgreSQL previously. That is the main element for Advanced RDBMS.

Objective Based

It supports class, inheritance, and nests. It takes you o the next level in SQL.

Cloud Supported

It is cloud supported since it uses heavy resources cloud can be the solution for that.

Disadvantages

Hard for Newbies

Installation and Configuration of Postgre are hard for the new one who just starts with it.

Performance

Performance is the trouble part for the usage of Postgre when compared to other RDBMS.

Popularity

We have to agree that Postgre is Advanced RDBMS, but one thing it lacks is Popularity. So it is very hard for finding assistance for using Postgre

When you can use PostgreSQL

If you need data integrity and reliability, then go for ##PosgtgreSQL

Need of Custom Procedures

If you need to run complex task, then custom procedures are a good choice which is extensible.

Integrity concerns

If you want to move your entire database to any big solution provider, then PostgreSQL will be the perfect one for the switch.

Complexity with ease

If you need to create a database which is complex, the PostgreSQL gives you the functionality to maintain such complex database smoothly without limitations.

Do not use Postgre at this situation

Speed

If you need to perform the read operation fast, then Postgre can't help you for that.

Easy Setup

Postgre is hard to install and configure for newbies

Replication

If you only need replication, then MySQL comes in the place. Don't use the elephant to pull bullock kat.

MySQL

Why do we use MySQL?

MySQL is the popular open source RDBMS widely used by many developers. It runs numerous websites and applications online.

Starting with MySQL is very easy for developers.

Note: There are a lot of other applications which might not have the popularity but such those third party applications and libraries help you to work efficiently with RDBMS.

You might not have full SQL standard implemented in your system, but with the support of MySQL, you can use a lot of functionalities.

All the third party apps directly contact with MySQL daemon to get the data directly.

MySQL supported Data Types

There are three types in MySQL Database

Numeric Data Types

Date and time types

String Types

Numeric Data Types in MySQL

INT

Signed INT - Range from -2147483648 to 2147483647

Unsigned INT - Range from 0 to 4294967295

TINYINT

Signed TINYINT Range from -128 to 127

Unsigned TINYINT Range from 0 to 255

SMALLINT

Signed SMALLINT Range from -32768 to 32767

Unsigned SMALLINT Range from 0 to 65535

MEDIUMINT

Signed MEDIUMINT Range from -8388608 to 8388607

Unsigned MEDIUMINT Range from 0 to 16777215

BIGINT

Signed Bigint Range from -9223372036854775808 to 9223372036854775807

Unsigned BIGINT Range from 0 to 18446744073709551615.

FLOAT(M, D)

DOUBLE (M, D)

DECIMAL (M, D)

Date and Time Types in MySQL

DATE

Format YYYY-MM-DD

Range From 1000-01-01 and 9999-12-31

DATETIME

Format YYYY-MM-DD HH:MM:SS

Range From 1000-01-01 00:00:00 and 9999-12-31 23:59:59

TIMESTAMP

Format YYYYMMDDHHMMSS

TIME

Format HH:MM:SS

YEAR(M)

String Data Type in MySQL

CHAR(M)

VARCHAR(M)

BLOB or TEXT

TINYBLOB or TINYTEXT

MEDIUMBLOB or MEDIUMTEXT

LONGBLOB or LONGTEXT

ENUM

Advantages of MySQL

MySQL is very easy to install and use. The third party GUI for the MySQL works well.

High Functionality Support

MySQL supports a lot of functionalities which professionals are expecting from RDBMS. It gives support in both direct and indirect way.

Highly Secure

MySQL has the high-security features which are advanced in level.

Scalable and Powerful

MySQL can handle a lot of data and it is also scalable.

High Speed

It gives up some standards to increase the speed and come out of the corner.

Disadvantages of MySQL

Limitations

MySQL has some limitations, and it makes some applications unable to use MySQL.

Reliability problems

MySQL is not reliable for certain functionalities such as References, auditing, and Transactions.

Slow development

MySQL is open source product, and it has a lot of complaints regarding the development. However there some database which entirely runs with MySQL and provides the value to the user( E.g Marid DB)

When should you use MySQL?

Do you need more than SQLlite then adds MySQL in your deployment process?

MySQL as a stand-alone database server with the operational freedom along with some advanced features.

Highly Secured

MySQL is highly secured and provides the security to the user data access.

Web application usages

More than 50% of websites around the world has the MySQL running its operations. Even though there are some constraints, MySQL advantages hide them.

The Scalable and easy to manage and use tool helps a lot for long time users.

Customised solutions

If you are working on specific and customized solutions then you can use MySQL since it has high configuration setting and operation modules.

When should you not use MySQL

SQL Compliant

MySQL does not implement full SQL standards so that if you need to connect with RDBMS, it will be difficult for you to switch.

MySQL is not entirely SQL compliant.

Problem in Concurrency

MySQL is performing well at reading operations, but when coming to multiple read operations, it will become problematic.

Lack of certain features

Based on the Database engine you use, MySQL will lack certain features such as full-text search.

SQLite

SQLite is the powerful library which is integrated with applications to make use of it.

SQLite is the file based database which offers various tools to handle the data.

You can easily handle the data easily with fewer constraints. It is easier than the process based database.

The specialty of SQLite is it does not use any interface(sockets, ports) to communicate with the database.

If an application requires data, it directly works with functions which make the call to the files which contains the data.

The library underlying technology is the reason behind that. The SQLite works very faster.

Storages Classes in SQLite

NULL It contains Null Value

INTEGER Signed Integer Stores up to 8 byte

REAL It holds floating point (8 - byte IEEE floating point number)

TEXT It uses the encoding technique in database(UTF-8, UTF-16BE or UTF-16LE) to store the data string

BLOB Stores the value blob of data

Affinity Type in SQLite

TEXT It stores all the data using NULL, TEXT or BLOB.

NUMERIC Holds values using all five storage classes.

INTEGER Works like a column with NUMERIC affinity

REAL Works same as a column with NUMERIC affinity

NONE Allows only one storage class

Integer

INT
INTEGER
MEDIUMINT
TINYINT
BIGINT
SMALLINT
INT2
INT8
UNSIGNED BIG INT

Text

CHARACTER(20)
VARCHAR(255)
VARYING CHARACTER(255)
TEXT
CLOB
NVARCHAR(100)
NCHAR(55)
NATIVE CHARACTER(70)

NONE

BLOB
no datatype specified

REAL

REAL
DOUBLE
DOUBLE PRECISION

FLOAT

FLOAT

NUMERIC

NUMERIC
DECIMAL(10,5)
DATE
DATETIME
BOOLEAN

SQLite Advantages

Easily Portable

Since the SQLite is file-based, the database contains the single file on the disk which makes easier to port.

Standards

The SQLite seems to be easy DB Implementation, and it uses the SQL. Here we have to note one point. Some features are omitted here but however, it has some additional features.

Good for Development and Testing

Integrating with applications during the development process is easy. Since the developers want to scale the concurrency ability, they can SQLite for that easily.

SQLite has the rich features, and it offers more than the developer's expectation with simplicity.

Disadvantages of SQLite

No User Management in SQLite

A lot of advanced database management systems supports users. This will help in making privileges to the user access to the database and tables stored there.

But the SQLite does not have the feature.

No Possibility to tinker

The SQLite is easy to tune and simple to use. But there is no possibility to tinker the SQLite for additional performance. The reason is SQLite is not complicated so that you can't tinker to gain additional performance.

When to use SQLite

Embedded Applications

The applications which require portability and do not require expansion can use SQLite.

The single user local used applications, games and the mobile applications.

Disk access replacement

The application which requires to directly read and write on the disk can benefit from SQLite. It comes with Structured Query Language and additional functionality.

Testing

Hard to test multiple applications which are using SQLite.

When not to use SQLite

Applications with Multi-User

If you are using applications and all of them have multiple users, you have to better use full featured RDBMS such as MySQL.

Applications which require frequent writing

You cannot use SQLite for the purpose of Multi-writing because the SQLite allows only one user to write at the same time.

It reduces the speed and throughput.

Conclusion

You might have gained some good knowledge about the PostgreSQL, MySQL and SQLite.

Now you can quickly figure out which one is best for your purpose based on the analysis we have seen above. Now you would understand the SQLite vs MySQL performance.

If you got any question, then kindly leave a comment below, and if you have enjoyed the article..

..then kindly share the article on your preferred social media!