Tag Archives: RDBMS

DBMS VS RDBMS

RDBMS is extension of DBMS.

DBMS (Database Management System)

Software that provides a systematic way of creating, retrieving and managing data in a database.

DBMS Example : File System, XML, Dbase, Microsoft Access, LibreOffice Base, Fox Pro etc.

RDBMS(Relational Database Management Information System)

A DBMS based on relational model and store data in tables that related to each other.

RDBMS Example: MySQL, PostGre, SQL Server, Oracle, MariaDB, SqlLite etc.

Here area some main points that make differences between DBMS and RDBMS.

DBMS RDBMS
DBMS application stores data in files that have no relationship. RDBMS application stores data in tabular form which will have a relation between tables.
DBMS store data either in hierarchical form or navigational form. RDBMS table has one identifier called as primary key and data stored in tabular form.
DBMS does not support Normalization. RDBMS supports normalization.
DBMS does not provide any security with regards to data manipulation. RDBMS defines the integrity constraints for the purpose of ACID property.
DBMS does not support distributed database. RDBMS support distributed database.
DBMS is for small organization to deal with small data. It supports for single user. RDBMS is designed to handle large amount of data. It supports multiple users.
DBMS does not support client server architecture. RDBMS  support client server architecture.
DBMS have high data redundancy. RDBMS have low data redundancy.
DBMS takes more time to access data. RDBMS takes less time to access data.

SQL vs NoSQL

In this post you will know about the main high level difference between SQL and NoSQL type databases.

SQL vs NoSQL

SQL NoSQL
Relational Type Non-Relational Type
Structured Data Stored in Tables The un-structured data store in JSON format in file but graph of database show relationship.
Strict Schema Dynamic Schema
Vertical Scalable Horizontal Scalable
Structured Query Language Un-structured Query Language
ACID Transactions CAP Theorem
Requires downtime In most cases automatic, No outage required
Rigid schema bound to the relationship Non-rigid schema and flexible.
Helpful to design complex queries. No joins relationship, no any powerful tool to prepare complex queries.
Recommend and best suited for OLTP (Online Transactional Processing) Systems. Less likely to be considered for the OLTP System.
Storage : Table (Row->Entity, Column->Attribute)
RDBMS: Oracle, MYSQL, SQL Server, IBM DB2 etc.
Storage:
Key-Value: Redis, Dynamo
Document: MongoDB
Graph: Neo4j, InfiniteGraph
Wide-column- Cassandra, HBASE
SQL is not fit for Hierarchical work. NoSQL is the best fit for hierarchical work as it follows the key-value pair’s way to store values.

JDBC : Stored Procedure Example

A SQL stored procedure (SP) is a collection SQL statements , SQL command logic, which is compiled and stored on the database and execute on the server. Stored procedures can also be cached and reused. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

See Also :

Types of parameters of Stored Procedure:

  • IN: A parameter whose value is unknown when the SQL statement is created. You bind values to IN parameters with the setXXX() methods.
  • OUT: A parameter whose value is supplied by the SQL statement it returns. You retrieve values from the OUT parameters with the getXXX() methods.\
  • INOUT: A parameter that provides both input and output values. You *bind variables with the setXXX() methods and retrieve values with the getXXX() methods.

Example :

In this example will focus on how to execute procedures by JDBC instead of writing of procedure.

Table and Procedure :  Here is small procedure to getStudentName based on student id.


Create Table
CREATE TABLE `facingissuesonitdb`.`student` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(25) NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC));

Insert Records  
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('1', 'Saurabh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('2', 'Rajesh Gupta');
INSERT INTO `facingissuesonitdb`.`student` (`id`, `name`) VALUES ('3', 'Ramesh Gupta');

Create Stored procedure
USE `facingissuesonitdb`;
DROP procedure IF EXISTS `getStudentName`;

DELIMITER $
USE `facingissuesonitdb`$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getStudentName`(in student_id integer, out first_name varchar(25))
BEGIN
select name into  first_name from student where id=student_id;
END$

DELIMITER ;

Java code to execute stored procedure by JDBC.

package procedure_functions;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import connection.MyConnection;

public class StoredProcedureTest {

	private static final String PROCEDURE_QUERY = "{call getStudentName(?,?)}";

	public static void main(String[] args) {
	 try {
             //to get connection code check blog for Connection according to your need.
	     Connection conn = MyConnection.getConnectionMain("MySql");
	     CallableStatement csmt = conn.prepareCall(PROCEDURE_QUERY);
	     csmt.setInt(1, 2);
	     // Because second parameter is OUT so register it
	    csmt.registerOutParameter(2, java.sql.Types.VARCHAR);
	    csmt.execute();
	     String firstName = csmt.getString(2);// for retrieving out parameter
											     // after execution
	     System.out.println(firstName);
	    } catch (SQLException ex) {
			ex.printStackTrace();
	   }
	}
}

Output


MySql Connection created successfully !!
MySql Connection Creation end !!
Rajesh Gupta

More on JDBC

Follow below links to know more on JDBC and solving JDBC issues :