Online Transaction Processing (OLTP) Systems

Subject Name : Data Warehousing And Data Mining Subject Code: MCA-3012 Subject Topic: Online Transcation Processing Dr. Mamta Tiwari Assistant Professor Department Of Computer Application UIET,CSJM University, Kanpur

Online Transaction Processing OLTP (Online Transaction Processing) is a data processing category that deals with numerous transactions performed by many users. The OLTP system is an online database system that processes day-to-day queries that usually involve inserting, updating, and deleting data.

What is OLTP OLTP, or online transactional processing, enables the real-time execution of large numbers of database transactions by large numbers of people, typically over the internet. A database transaction is a change, insertion, deletion, or query of data in a database. OLTP systems (and the database transactions they enable) drive many of the financial transactions we make every day, including online banking and ATM transactions, e-commerce and in-store purchases, and hotel and airline bookings, to name a very few. In each of these cases, the database transaction also remains as a record of the corresponding financial transaction. OLTP can also drive non-financial database exchanges, including password changes and text messages.

Characteristics of OLTP systems Process a large number of relatively simple transactions: Usually insertions, updates, and deletions to data, as well as simple data queries (for example, a balance check at an ATM). Enable multi-user access to the same data, while ensuring data integrity: OLTP systems rely on concurrency algorithms to ensure that no two users can change the same data at the same time and that all transactions are carried out in the proper order. This prevents people from using online reservation systems from double-booking the same room and protects holders of jointly held bank accounts from accidental overdrafts.

Characteristics of OLTP systems Emphasize very rapid processing, with response times measured in milliseconds: The effectiveness of an OLTP system is measured by the total number of transactions that can be carried out per second. Provide indexed data sets: These are used for rapid searching, retrieval, and querying.

Characteristics of OLTP systems Deals with transactions involving small amounts of data. Has indexed access to data (faster access). Supports a large number of users. Deals with frequent queries and updates. Response times are measured in milliseconds.

OLTP Architecture The architecture. The presentation tier (the UI). The application tier (data processing). The data tier (data storing and managing). Each of the three tiers is independent and has its own infrastructure, development, and updating intervals, and does not impact other tiers. OLTP system is based on a 3-tier

OLTP Examples Online banking Adding items to cart in web shops Booking a ticket Sending a text message Order entry ATM center Surveyors entering poll results Employees viewing and updating customers' details

Advantages of OLTP Single Platform. OLTP integrates all business analytical needs in a single platform. Database Consistency. OLTP uses a fully normalized schema to ensure database consistency. Security Restrictions. OLTP applies security restrictions on users and objects to protect sensitive data. Expands Customer Base. OLTP attracts new customers by simplifying and speeding up individual processes, making them user-friendly. Timely Transaction Modifications. OLTP is a good foundation for a stable business or organization because timely transaction modifications are convenient to end-users. Large Database Support. OLTP supports large-size databases. Atomicity. Atomicity ensures that all the steps of a transaction are completed. If a step fails or is skipped, the entire transaction fails.

Disadvantages of OLTP High Staff Dependency. Requires IT professionals and business staff to implement and maintain OLTP database systems. Sensitive Information. OLTP databases store all user data and account information, so a high-level security is required to protect the sensitive data. Risk of Data Loss. In OLTP systems, transactions are highly affected when facing a hardware failure. Server failures can lead to the loss of large amounts of data. Allows Concurrent Data Modifications. Multiple users can access and modify the same data concurrently, which can cause issues. Limited Number of Queries. OLTP supports a limited number of queries and updates.

Difference Between OLTP and Data warehouse One major difference between the types of system is that data warehouses are not usually in third normal form (3NF), a type of data normalization common in OLTP environments. Operational Database Management Systems also called as OLTP (Online Transactions Processing Databases), are used to manage dynamic data in real-time. Data Warehouse Systems serve users or knowledge workers in the purpose of data analysis and decision-making. Such systems can organize and present information in specific formats to accommodate the diverse needs of various users. These systems are called as Online-Analytical Processing (OLAP) Systems. Data Warehouse and the OLTP database are both relational databases. However, the goals of both these databases are different.

Difference Between OLTP and Data Warehouse Operational systems are usually concerned with current data. Data warehousing systems are usually concerned with historical data. Data within operational systems are mainly updated regularly according to need. Non-volatile, new data may be added regularly. Once Added rarely changed. It is designed for real-time business dealing and processes.It is designed for analysis of business measures by subject area, categories, and attributes. It is optimized for a simple set of transactions, generally adding or retrieving a single row at a time per table. It is optimized for extent loads and high, complex, unpredictable queries that access many rows per table.

References [1] https://www.tutorialspoint.com/dbms/dbms_overview.htm [2] https://www.studytonight.com/dbms/database-model.php [3] https://www.javatpoint.com/what-is-meta-data [4] https://phoenixnap.com/kb/oltp-database#ftoc-heading-7 [5] Data warehousing, Data Mining & OLAP by Alex Berson,Stephen J.Smith ,TMH