On Line Transaction Processing (OLTP) System in DBMS

On-Line Transaction Processing (OLTP) System refers to the system that manage transaction oriented applications. These systems are designed to support on-line transaction and process query quickly on the Internet. For example: POS (point of sale) system of any supermarket is a OLTP System. Every industry in today's world use OLTP system to record their transactional data. The main concern of OLTP systems is to enter, store and retrieve the data. They covers all day to day operations such as purchasing, manufacturing, payroll, accounting, etc.of an organization. Such systems have large numbers of user which conduct short transaction. It supports simple database query so the response time of any user action is very fast. The data acquired through an OLTP system is stored in commercial RDBMS, which can be used by an OLAP System for data analytics and other business intelligence operations. Some other examples of OLTP systems include order entry, retail sales, and financial transaction systems. Advantages of an OLTP System:

• OLTP Systems are user friendly and can be used by anyone having basic understanding
• It allows its user to perform operations like read, write and delete data quickly.
• It responds to its user actions immediately as it can process query very quickly.
• This systems are original source of the data.
• It helps to administrate and run fundamental business tasks
• It helps in widening customer base of an organization by simplifying individual processes

Challenges of an OLTP system:

• It allows multiple users to access and change the same data at the same time. So it requires concurrency control and recovery mechanism to avoid any unprecedented situations
• The data acquired through OLTP systems are not suitable for decision making. OLAP systems are used for the decision making or "what if" analysis.

Type of queries that an OLTP system can Process: An OLTP system is an online database modifying system. So it supports database query like INSERT, UPDATE and DELETE information from the database. Consider a POS system of a supermarket, Below are the sample queries that it can process -

• Retrieve the complete description of a particular product
• Filter all products related to any particular supplier
• Search for the record of any particular customer.
• List all products having price less than Rs 1000.

Type of queries that an OLTP system can not Process: An OLTP system supports simple database query like INSERT, UPDATE and DELETE only. It does not support complex query. Reconsider the POS system of the supermarket, Below are the sample queries that it can not process -

• How much discount should they offer on a particular product?
• Which product should be introduced to its customer ?

To read difference between an OLTP system and an OLAP system, Please refer the following article-

• Difference between an OLTP system and an OLAP System