Databases

Hello Friends! I would like to share some points regarding databases. I know it’s a very vast subject & it can’t be covered in one blog; nonetheless I would like to share some points regarding database & database testing. We will have two sections for this. In the first section we will discuss some Database Concepts, and in the second section we will discuss some Tips for Database Testing & Checklist for Database Testing.

So let’s start with the first section i.e. Database Concepts. In this we will cover following points in short:

• What is Database?
• Different Types of Database.
• What is SQL?
• Different language types used in SQL

What is Database?

• A database is a vast collection of information that is masterminded so that it can be easily accessed, managed, and updated.

• Database is can also be defined as a collection of persistent data that is used by the application systems of a given enterprise.

• An integrated set of data held in a computer, especially one that is accessible in various ways.

• Databases are usually designed to offer a very organized mechanism for storing, managing and retrieving information.

• A database is also a well-organized list of facts and information.

• In database data, respective information is arranged in such a way that user can easily search the record.

• In short we can safely say that, a database is a collection of programs that allows the user to store, modify & extract the information from a database.

Then the question as to what is the exact difference between a simple list and a database?

A database is something that necessarily permits its user to extract a specific group of disparate facts from within a collection of facts.

Types of DBMS:

There are four major types of DBMS, Let’s look into them in detail:

• Hierarchical – this type of DBMS mainly employs the “parent-child” relationship of storing data. However this type of DBMS is rarely used nowadays. Its structure is just like that of a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is a good example of hierarchical database. Configuration settings are stored as tree structures with nodes.
• Network DBMS – this type of DBMS necessarily supports many-to-many relations. This usually results in complex database structures and RDM Server is one such example of database management system that implements the network model.
• Relational DBMS –this type of DBMS defines database relationships typically in the form of tables, also known as relations. However unlike network DBMS, RDBMS does not support many to many relationships. Relational DBMS usually have pre-defined data types that they can support and it is one of the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server.
• Object Oriented Relation DBMS – this type essentially supports storage of new data types. The data that is to be stored is in the form of objects. The objects that are to be stored in the database have attributes (i.e. gender, age) and there are also methods that define what to do with the data. PostgreSQL is the best example of Object oriented relational DBMS.

What is SQL:

Structured Query language (SQL) which is usually pronounced as “S-Q-L” or sometimes as “See-Quel” is actually the standard language for dealing with Relational Databases. SQL can be effectively used to insert, search, update, and also delete database records. However this doesn’t mean that SQL cannot do things beyond that. As a matter of fact, it actually can do lot of things including, but not limited to, optimizing and also maintenance of databases. Relational databases such as MySQL, Oracle, Ms SQL server, Sybase, etc. uses SQL! SQL syntaxes used in these databases are almost similar.

Relational databases have the following advantages:

• SQL (relational) databases have a much more mature data storage and management model. This is quite important for enterprise users.
• SQL Database allows authorized user to view only that much of data that they are authorized to. Rest of the data is kept hidden from them.
• SQL databases support stored procedures which effectively allow database developers to implement part of the business logic into the database.
• SQL databases (Oracle Databases, MySQL, PostgreSQL etc. have better security models compared to NoSQL databases. NoSQL databases such as the MongoDB, HBase etc. are available.

Different Language Types used in SQL:

SQL uses different types of languages and here is the list of the languages that are used in SQL & each Language is having multiple operations:

1. DDL- Data Definition Language
2. DML- Data Manipulation Language
3. DCL- Data Control Language
4. TCL- Transaction Control Language

Each language has different operations' statements. Now let’s discuss it in short:

DDL: Data Definition Language statements.

Data Definition Language (DDL) is a standard used for commands that define the different structures in a database. DDL statements create, modify, and even remove database objects such as the tables, indexes, and users. Common DDL statements are CREATE, ALTER, and DROP.

Following are the various operations for the DDL command statements:

• CREATE - To create objects in the database
• ALTER - Alters the structure of the database
• DROP - Delete objects from the database
• TRUNCATE - Remove all records from a table, including all spaces allocated for the records are removed
• COMMENT - Add comments to the data dictionary

DML: Data Manipulation Language (DML) statements are used for managing the data within the schema objects.

Following are the different operations for DML Command Statement:

• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the space for the records remain
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

DCL: Data Control Language

Following are the operations for DCL Command Statement:

• GRANT - gives users access privileges to database
• REVOKE - withdraw access privileges given with the GRANT command

TCL : Data Control Language statements

Following are the operations for TCL Command Statement:

• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction to which you can later roll back
• ROLLBACK - restore database to original since the last COMMIT
• SET TRANSACTION - Change transaction options like what rollback segment to use

Thus Databases are versatile in many ways and one can use them effectively, with proper knowledge and knowing the various details of it.