Difference between DBMS and RDBMS Although DBMS and RDBMS both are used to store information in physical database but there are some remarkable differences between them. The main differences between DBMS and RDBMS are given below: No. DBMS RDBMS 1) DBMS applications store data as file. RDBMS applications store data in a tabular form. 2) In DBMS, data is generally stored in either a hierarchical form or a navigational form. In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables. 3) Normalization is not present in DBMS. Normalization is present in RDBMS. 4) DBMS does not apply any security with regards to data manipulation. RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property. 5) DBMS uses file system to store data, so there will be no relation between the tables. in RDBMS, data values are stored in the form of tables, so arelationship between these data values will be stored in the form of a table as well. 6) DBMS has to provide some uniform methods to access the stored information. RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information. 7) DBMS does not support distributed database. RDBMS supports distributed database. 8) DBMS is meant to be for small organization and deal with small data. it supports single user. RDBMS is designed to handle large amount of data. it supportsmultiple users. 9) Examples of DBMS are file systems, xml etc. Example of RDBMS are mysql, postgre, sql server, oracle etc. After observing the differences between DBMS and RDBMS, you can say that RDBMS is an extension of DBMS. There are many software products in the market today who are compatible for both DBMS and RDBMS. Means today a RDBMS application is DBMS application and vice-versa. Next TopicSQL Syntax ← prevnext →

---

---

difference between Database and Database management system?

Generally if  anyone asked about the difference between Database and DBMS then everyone says that both are same but there is huge difference between them are as:-

1.Both are part of the Database system.
2.DBMS contains 
        a) Query Processing software.
        b)storage Management software.
    Examples :-MySQL, MS SQL Server, Oracle
3.Databse contains
      a) Data
      b)schema(structure of the table ).
4.Both are interdependent.

--------------------------------
Database is a collection of interrelated data. 
Database management system is a software which can be used to manage the data by storing it on to the data base and by retrieving it from the data base.  
And  DBMS is a collection of interrelated data and some set of programs to access the data.
There are 3 types of Database Management Systems.
Relational DataBase Management Systems(RDBMS):   
It is a software system, which can be used to represents data in the form of tables. RDBMS will use SQL2 as a Queries  language.
Object Oriented DataBase Management Systems(OODBMS):  
It is a software system, which can be used to represent the data in the form of objects. This DBMS will use OQL as a Query language.
Object Relational DataBase Management Systems(ORDBMS):  
It is a DBMS which will represents some part of the data in the form of tables and some other part of the data in the form of objects. This management system will use SQL3 as a Query Language, it is a combination of  SQL2 and OQL.
 
How a query could be executed when we send a query to Database?
When we send an SQL Query from SQL prompt to the DataBaseEngine, then Database Engine will take the following steps.
Query Tokenization:  
This phase will take SQL query as an input and devide into stream of tokens.
Query Parsing:   

This phase will take stream of tokens as an input, with them it tried to construct a query tree. If query parser constructs query tree successfully then it was an indication that no grammatical mistakes in the taken SQL query. Otherwise there are some syntactical errors in the taken SQL query.
Query Optimization:   
This phase will take query tree as an input and performs number of query optimization mechanisms to reduce execution time and memory utilization.
Query Execution:  
This phase will take optimized query as an input and executes that SQL query by using interpreters internally as a result we will get some output on the SQL prompt.

How to test – Database Testing Process

The general test process for DB testing is not very different from any other application. The following are the steps:

Step #1) Prepare the environment
Step #2) Run a test
Step #3) Check test result
Step #4) Validate according to the expected results
Step #5) Report the findings to the respective stakeholders

Usually SQL queries are used to develop the tests. The most commonly used command is the “Select”.

Select * from <tablename> where <condition>

Apart from select, SQL has 3 important types of commands:

1. DDL : Data definition language
2. DML: Data manipulation language
3. DCL: data control language

Let us see the syntax for the most commonly used statements.

Data Definition language: Uses CREATE, ALTER, RENAME, DROP and TRUNCATE to handle tables (and indexes).

• Create table: Create table<tablename> (field1 datatype(field size) ,……………..fieldn datatype(field size))
• Delete entire table: Drop table <tablename>. – this command cannot be rolled back

Data Manipulation language: Includes statements to add, update and delete records.

• To insert a row into a DB: INSERT INTO <table name> (field1, field2, field3)  VALUES  (‘val1’, ‘val2’…’valn’);
• Delete specific row/rows from a table: DELETE FROM TABLENAME WHERE <required condition>.
• Update rows: UPDATE <tablename> SET field1 = ‘updated value’ WHERE field2 = ‘N’;

Data control language: Deals with giving the authorization to users for manipulation and access to the data. Grant and Revoke are the two statements used.

Grant syntax:
Grant select/update
On <table name>
To <user id1, user id2…useridn>;

Revoke syntax:
Revokeselect/update
on <table name>
from<user id1, user id2…useridn>;

What to test – different components

1) Transactions:

When testing transactions it is important to make sure that they satisfy the ACID properties.

The following are the statements commonly used:

• BEGIN TRANSACTION TRANSACTION#
• END TRANSACTION TRANSACTION#

Rollback statement ensures that the database lies in a consistent state.

• ROLLBACK TRANSACTION#

After these statements are executed, use a select to make sure if the changes have been reflected.

• SELECT * FROM TABLENAME <tables which involve the transactions>

2) Database schema:

Database schema is nothing but a formal definition of the how the data is going to be organized into a DB. To test it:

------------

• Identify the requirements based on which the database operates. Sample requirements:
  • Primary keys to be created before any other fields are created.
  • Foreign keys should be completely indexed for easy retrieval and searching.
  • Field names starting or ending with certain characters.
  • Fields with a constraint that certain values can or cannot be inserted.
• Use one of the following ways according to the relevance:
  • SQL Query DESC<table name> to validate the schema.
  • Regular expressions for validating the names of the individual fields and their values
  • Tools like SchemaCrawler

3) Trigger:

When a certain event takes places on a certain table, a piece of code (a trigger) can be auto-instructed to be executed.

For example, a new student joined a school. The student is taking 2 classes; math and science. The student is added to the “student table”.  A trigger could be adding the student to the corresponding subject tables once he is added to the student table.

The common method to test is to execute SQL query embedded in the trigger independently first and record the result. Follow this up with executing the trigger as a whole. Compare the results.

These are tested during both the black box and white box testing phases.

• White box testing:  Stubs and drivers are to insert or update or delete data that would result in the trigger being invoked. The basic idea is to just test the DB alone even before the integration with the front end (UI) is made.
• Black box testing:

a) Since the UI and DB integration is now available; we can insert/delete/update data from the front end in a way that the trigger gets invoked. Following that select statements can be used to retrieve the DB data to see if the trigger was successful in performing the intended operation.

b) Second way to test this is to directly load the data that would invoke the trigger and see if it works as intended.

4) Stored Procedures:

Stored procedures are more or less similar to user defined functions. These can be invoked by a call procedure/execute procedure statements and the output is usually in the form of result sets.

These are stored in the RDBMS and are available for applications.

These are also tested during:

• White box testing: Stubs are used to invoke the stored procedures and then the results are validated against the expected values.
• Black box testing: Perform an operation from the frontend(UI) of the application and check for the execution of the stored procedure and its results.

5. Field constraints – Default value, unique value and foreign key:

• Perform a front end operation which overruns the database object condition
• Validate the results with a SQL Query.

Checking the default value for a certain field is quite simple. It is a part of business rule validation. You can do it manually or you can use tools like QTP to do so. Manually, you can perform an action that will add a value other than the default value into the field from the front end and see if it results in an error.

The following is a sample VBScript code:

1	<i>Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

2	<i>Set newregexp = new RegExp</i>

3	<i>newregexp.Pattern = “<Default value as required by the business requirements>”</i>

4	<i>newregexp.Ignorecase = True</i>

5	<i>newregexp.Global = True</i>

6	<i>VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)</i>

7	<i>End Function</i>

8	<i>Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

The result to the above code is true if the default value exists or false if it doesn’t.

Checking the unique value can be done exactly the way we did for the default values. Try entering values from the UI that will violate this rule and see if an error gets displayed.

Automation VB script code can be:

1	<i>Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

2	<i>Set newregexp = new RegExp</i>

3	<i>newregexp.Pattern = “<Unique value as required by the business requirements>”</i>

4	<i>newregexp.Ignorecase = True</i>

5	<i>newregexp.Global = True</i>

6	<i>VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)</i>

7	<i>End Function</i>

8	<i>Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)</i>

For the foreign key constraint validation use data loads that directly input data that violates the constraint and see if the application restricts the same or not. Along with the back end data load, perform the front end UI operations too in a way that are going to violate the constraints and see if the relevant error is displayed.

Conclusion:

With all these features, factors and processes to test on a database, there is an increasing demand on the tester to be technically strong with the key DB concepts. Despite some of negative beliefs that the DB testing creates new bottlenecks and is a lot of additional expenditure – this is a realm of testing that is gaining obvious attention and demand.

I hope this article has helped to focus on why that is so and also has provided you with the basic details of what goes into testing a database.

Please let us know your feedback and also share your personal experiences if you have done this before. Your comments and feedback is important to us and our readers to learn from your experience.