X

What are the SQL and NoSQL Database Technology?

This article will introduce latest database technology SQL and NoSQL, what it is, why to use it, and basic database concepts. In addition, we’ll introduce some of the key points used with SQL and NoSQL Database.

What is the Data?

Normally, Data is distinct pieces of information, usually formatted in a special way. It can be any Characters, including text and numbers, sound, pictures, or video and it can stored as bits and bytes in electronic memory card, or as facts stored in a person’s mind and stored in ‘0’ and ‘1’ computerised format. The data can be facts related to any object in consideration.

What is the DataBase?

Database is collection of data and information that is organized so that it can be easily accessed, managed, and updated. It support storage and  manipulation of data. Databases make data management easy.

The database is well stored their data into rows, columns, and tables, and it is indexed to make it easy to find relevant information. If new information is added then, Data gets updated, expanded and deleted. Databases process workloads to create and update themselves, querying the data they contain and running applications against it.

What is DBMS?

DBMS stands for Database Management System. DBMS is a collection of programs that enables user to create and maintain a database. It provide security and protection to database. It can maintain data consistency in the case of multiple user.

Component of Database System:

There are four components of database system.

  • User : Users may be Administrators, End Users, and System Developers.
  • Database Application: Departmental Application, Personal Application, and Enterprise Application etc.
  • DBMS: Software that allow users to define, create and manages database access.
  • Database: Collect logical data.

What is RDBMS?

The long form of RDBMS is Relational Database Management System. It is database management system based on relational model. In this model, data represent in the terms of  rows (tuples).

RDBMS is used to manage relational database. It is a collection of organized set of tables from which data can be accessed easily. It database is most commonly used in database. This database consists of number of tables and each table has its own primary key.

Fundamental of Table Basics

In Relational database, a table is a collection of data elements arranged in the  terms of rows and columns. The data or information for the database are stored in these tables. Table is the very simple form of data storage. Bellow show the example of Students details table.

Roll No Name Department Percentage
1 AA a 50%
2 BB b 60%
3 CC c 70%
4 DD d 80%

What is SQL Database?

The full form of SQL is Structured Query Language. It is an American National Standards Institute (ANSI) standard computer language for accessing and manipulating database systems. SQL pronounced as ‘SEQUEL’. Some general relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc.

Use of SQL:

  • SQL is used to execute queries against in database.
  • It is used to retrieve the data/information from the database.
  • By using SQL, you can insert and update information in database and also you can delete the information from database.
  • It can create new database.
  • SQL can create multiple tables in database.
  • It is used to create stored procedures in a database.
  • Create views in a database.
  • Set the permission on tables, procedures, and views.

SQL Commands:

SQL defines following data languages to manipulate data.

  • DML (Data Manipulation Language)
  • DDL (Data Definition Language)
  • DCL (Data Control Language)
  • TCL (Transaction Control Language)

i)DDL (Data Definition Language)

DDL is used to alter/modify a database or table structure. They are used to handle the design and storage of database objects. It saves all changes permanently in database.

Command Description
Create Create new table
Alter To alteration  in an existing table
Drop To drop table
Rename To rename tables
Truncate For delete data from table

1 – Create Command

The create command used to define column of the table individually. Each column have min of three attributes such as name, datatype, size.

  • Syntax: CREATE TABLE table_name( Column1 datatype, Column2 datatype,

  Column3 datatype);

2- Alter Command

It is used to modify our existing table content in database.

  • Syntax: ALTER TABLE table_name ADD(NewColumn Datatype);

3- Drop Command

This command is used to destroy the table and all data which is recorded in it.

  • Syntax: DROP TABLE table_name;

4- Rename Command

It is used to change the table name of existing table in database.

  • Syntax: RENAME old_table_name TO new_table_name;

5- Truncate Command

It is used to delete the data from table.

  • Syntax: TRUNCATE TABLE table_name;
  1. i) DML (Data Manipulation Language)

It’s commands are not auto committed. That means changes are not permanent to database. These are basic operations such as inserting new records in table, deleting unnecessary records, and updating/modifying existing records, and merging two row records from a table.

Command

Description
Select Retrieve data from table
Insert To insert new record
Update To update/modify in existing row
Delete To delete row

1) Select Command

By using this command we can showing all records and selected records from tables. It is used to fetching data from database.We are select all rows and all columns by using ‘*’ symbol.

  • Syntax: SELECT * FROM TABLE table_name;

And show the details by using respected columns.

  • Syntax: SELECT column_name FROM TABLE table_name.

2)Insert Command

Insert command is used to add new row in to a table.

  • Syntax: INSERT INTO table_name(Column1,Column2,Column3) values(value1,’value2’,value3);

3)Update Command

To change and modify data values in tables and It is used to set of the rows from the tables and It can update rows from the tables.

  • Syntax: UPDATE table_name SET some_column=some_value WHERE some_column=some_value;

4)Delete Command

This command is used to delete row from the tables.

  • Syntax: DELETE FROM table_name WHERE some_column=some_value;

iii)DCL (Data Control Language)

Data control language is used to provides command to grant and take back authority. It is security facility provides from Oracle. In that unauthorised user can’t get the details from the table.

Command

Description

Grant Allow user to access the database contains.
Revoke Take the permission from owner of object.

1)Grant Command

Database contains are created by the user. If user want to give the access of objects contains to another user, the owner of the object will have to give permissions for such access. This is called GRANTING of PRIVILEGES.

  • Syntax:

GRANT <object privileges>

ON <object_name>

TO <User_Name>

[WITH GRANT OPTION]

2)Revoke Command

This command is used to take the access from from another user. It means the object owner can revoke privileges granted to another user.

  • Syntax:

     REVOKE <Object_Privileges>

     ON <Object_Name>

      FROM <User_Name>

iii)TCL (Transaction Control Language)

These command are used to manage and control the transaction. It can also make changes permanent.

Command

Description

Commit

Permanently Save
Rollback To undo changes
Savepoint Temporarily save changes

1)Commit Command

Commit command used to make the permanent any changes made during the transaction. Also it is used to ends to the current transactions.

  • Syntax: Commit;

2)Rollback Command

It used to  ends the transaction but undoes any changes made during the transaction. All transaction locks acquired on tables are released.

  • Syntax: ROLLBACK  [WORK] [TO SAVEPOINT]  < Save-point_Name>

3)Savepoint Command

SAVEPOINT command is mark and saves the current point in the processing of a transaction. Whenever you are using a SAVEPOINT with a ROLLBACK statement, parts of a transaction can be undone.

  • Syntax:SAVEPOINT  <SavePointName>

What is NoSQL Database?

NoSQL is a term used to describe non-relational databases, high-performance. It is the class of Database Management System(DBMS). It is does not follow the rule or condition of relational database. The long form of NoSQL is ‘ Not Only SQL’. NoSQL databases design by using a variety of data models, including document, graph, columnar and key-value. NoSQL systems are typically used in very large databases. NoSQL databases are widely recognized for ease of development, high availability, scalable performance, and resilience. Below are several resources to help you get started using NoSQL databases.

NoSQL Database Types:

Bolow describe the following types are:

1- Document Database

It is also called as storing documents,store semi-structured data and descriptions of that data in document format. The documents are stored in JSON or XML format. Documents can contain many different key-value, or key-array, and even nested documents. Eg.CouchDB, MongoDB

2- Key-value stores

It is the simplest NoSQL databases. Every single item in the database is stored as Key (or an attribute name), together with its value. Examples – Riak and Berkeley DB.

3- Wide-Column stores

Data is stored in columns same as row of data. It can be found both in SQL and NoSQL databases. It can query large data volumes faster than conventional relational databases.

4- Graph databases

It is used to store information about networks of data, such as social connections. Graph data stores organize data as nodes. It can be built on relational (SQL) and non-relational (NoSQL) databases. The example of Graph DB are Neo4J and Giraph.

What is the difference between SQL and NoSQL

SQL

NoSQL

Type One type (SQL database) with minor variations Many different types including key-value stores, document databases, wide-column stores, and graph databases.
Development History Developed in 1970s Developed in late 2000
Database

Category

Databases are categorized as Relational Database Management System (RDBMS). Databases are categorized as Non-relational or distributed database system.
Development Model Both Open Source and Closed Source Open Source
Database Schema Fixed and Static Dynamic
Hierarchical Data It is not best suited for hierarchical data storage. It is best suited for hierarchical data storage.
Example MySQL, Oracle, Sqlite, MS-SQL and PostgreSQL BigTable, MongoDB, Redis, Neo4j, CouchDB, RavenDB

 

Prahshant Shendure:
Related Post