Transactional Databases vs Data Warehouses

This article discusses the differences between transactional databases and data warehouses, their advantages and disadvantages, and their architecture. It also covers the types of data warehousing and the operations involved. The article is relevant for students studying database management and data warehousing. Course code and college/university are not mentioned.

TRANSACTIONAL DATABASES VS DATA WAREHOUSES
Presented by:
Presented to:

Abstract
For a business to be able to carry out its normal activities and functions, it is necessary for the
raw data to be organized and processed to information. The moment raw data was been data has
been processed through an identified system and the yield is thus referred to as information.
The data of an organization plays a key component of a firm and thus its quality and integrity
have to be in their highest state. With an emphasis on the quality of data, it should also not have
redundancy and must be up to date and accurate. The current information system has several
non-requirements and procedural activities that range from the data cleansing process and also
data loading process once the information has been integrated and transformed into useful
information. Data about data or in some instances the metadata is the data that is initially needed
to be processed and it is most useful to the users and administrators in the efficient and effective
use of the data stores in the database system.
What is a transactional database?
A database is a collection of information form single or multiple sources that are organized
systematically and made available to the user when needed. They are generally storage unit for
various components of a firm such as sales, inventories, product catalogs and even personal data
of customers in the firm. The manipulation of the information in the database is what is termed
as a database transaction. The operational databases which are also referred to as the Online
Transactional Processing Database make updates to the database on real-time compared to other
databases that do it on batch processing and batch update (Abbott & Garcia, 2012).
Outline of Data Warehouse
The combined analytics for both the unstructured and structured data have become very essential
in the market and provide multiple facilities to the firm.
The leading architect in developing data warehouse states that “A data warehouse is a subject-
oriented, integrated, time-variant, and non-volatile collection of data in support of management's
decision-making process.” In order to identify the business patterns of operation and predicting
future expectations in a firm has given rise to the data mining techniques.

Data mining contains several appliances that are manipulated by the administrator and also the
users of the data warehouse. These tools are used in organizing, easy comprehension of the
system and also give decisional support to the firm. In the first growing business market, the data
warehouses give a firm a better edge since it is utilized as an instrumental mechanism in business
competitions. Most of the firms have invested huge amounts of capital and other resources in
developing and implementing a data warehouse. The data warehouse is a child to the operational
databases.
TRANSACTIONAL DATABASES
Reasons for using databases
It is seemingly difficult to work in a firm where you are not aware of the different personnel
within that firm. The personnel may include who you are working for, your co-workers. In order
to work effectively and efficiently, it is essential for the firm to store all the data ranging from
the personnel who work there and also the business entities that are in the firm. In order for the
data stored to be processed into information and so as to be used, it is necessary for the firm to
implement an information system. The key objectives of the information systems are basically to
collect the data, clean the data and store it for easy access, manipulation, and management of the
data.
The database management system
This is an advancement of the file-based database that as a collection of different applications
that stored data. The database management system consists of single software that stores the data
and allows easy access and manipulation of the information. The systems are built to enable real-
time updates and efficient in carrying out activities. The database management system has a
server that acts as the intermediary between the user of the system and the file database.
Types of Databases
Different types of databases can be accommodated by the Database management system. The
variation of the database is based on the location of the database, the number of users or the
extent to which it is used.
Number of users using the database

Single-user database- this is a database that is only accessed and used by one user at a time. The
database cannot handle more than one user a time.
Multi-user database- this type of database is able to handle more than one user at a time. If the
number of users in the multi-user database is less than 50 then the database is known as a
workgroup database. Larger firms that handle more than 50 users is referred to as an enterprise
database.
According to the location
Centralized- this is a database that has been physically placed at one location.
Distributed database- this type of database includes several databases that have been distributed
and not centralized.
The extent of use
This is the most common way of classifying the database by considering the usage of it.
Operation databases- in a firm there are several transactions and operations happening, this may
range for employees checking in, payment and purchases among other. The databases that are
designed to handle the day to day activities in a firm are referred to as the operational databases.
In some instances, it is also referred to as the production or transactional database.
Data warehouse- this is a kind of database where the information from different sources are
integrated and stored together over a period of time.
Transactional Databases
This is a database that its structure is suitable for handling productive systems that range from
websites, banks etc. They are quite popular for their ability to read and also write data into the
individual row in a short time span while still preserving the integrity of the data.
For firms that have not yet incorporated a separate analytic sector, it is easier and takes a short
time to install a transactional database which will act as a replica to the analytic stack. This will
prevent the analytical queries from clogging the system for the more critical business queries.
The only shortcoming of the transactional database is that they are originally designed for A

transaction is a small section within a program that contains several child tasks within it. The
transactions that happen in a transactional database have to maintain the ACID properties
(Atomicity, Consistency, Isolation, and Durability). These properties ensure that the data
integrity is maintained.
Atomicity- as the name suggests, it implies that all the transactions within a database should be
handled separately and as a single unit. That is all the transaction has to be fully committed or
not executed. There should be no transaction that is to be left partially committed.
Consistency- this implies that the data in the database should remain in its compatible state after
any transaction has been fully committed. There should be no transaction that can cause a
disadvantageous effect on the data in the database. This property has to be maintained before and
after any transaction takes place.
Isolation- in the cases where there are several transactions within a database being executed at
the same time, the isolation property states that each of the transactions should be treated as if it's
the only operation running in the database. The transactions are independent of the other thus
cannot cause any effect on the other transactions.
Durability- the database should be able to maintain its latest state even if there is system failure
or the system restarts. If a write transaction is fully committed and there is a new data entry in
the database, then the data should not change unless there is another fully committed write
operation after it. In the case where the write operation is not fully committed, then the previous
data in the database should retain its state.
Transactional Database Operations
There is more than one transaction occurring in the equivalent system database. These operations
are executed separately and independent for the database system. The core functions of a
transactional database are to ensure that there is data recovery in the instance that an operation is
not fully committed or there are system failures. The other core importance of the transactional
database is that the database should maintain its consistency regardless of the shortcoming
activities that may happen to it.

What are the Advantages and Disadvantages of Transactional Databases?
The transactional databases have the following strengths:
Maintaining a low latency
Considering that the transactional database was designed to run productions systems, it takes the
shortest time to carry out operations. When carrying out analytics on a transaction replica, it is
certain that it will likely be in sync with the master database.
Maintaining data integrity
The transaction database is designed with the ACID properties in mind. This will ensure that the
write operations to the database fully commit or are rolled back if not successful. This will
ensure the levels of integrity are at its peak in the case where a transaction has occurred.
This property makes is essential for firms or organizations that aim to maintain data integrity to
use the transactional databases (Ponniah, 2011).
Operational system monitoring
The use of the transactional database to provide a real-time operational overview is a brilliant
way of using it as an analytic tool. This is made possible since the replica produces small
latencies during its transaction. Imitation of the transactional database is in some instances the
best approach towards monitoring inventories, support workload or other operational systems as
a decision-making system based on the information it stores (Wixom & Watson, 2010).
PROBLEMS ASSOCIATED WITH TRANSACTIONAL DATABASES
There are several disadvantages of the transactional database although the advantages of the
transactional database overweigh the disadvantages. These include:
1. The lost update problem
This is the state at which there is access to the same type of data in a data cell and in the process,
the two transactions interleave and therefore making the data incorrect. This will cause the rise of
a problem when one transaction updates the data that was previously updated by the other
interleaved transaction.

2. Temporary update
This is the state in which the data in a data cell has been updated before the transaction has been
fully committed. One the transaction is rolled back and makes use of the already updated data,
the output of the transaction will be incorrect since it has manipulated the already updated data.
This situation is also referred to as the uncommitted dependency or the dirty read.
3. Inconsistent analysis
This case is seen when a transaction is doing several read operation on similar data and during
the process, another write transaction is fully committed and changes the data before the first
transaction is fully committed. This will lead to system failure since the transaction has used
different data in its midst as it was being executed. This leads to the production of the wrong
outputs.
4. Non-repeatable read
This situation is experienced if distinctive results are delivered for a similar sort of question
inside a similar transaction. This inconsistency is caused when an exchange more than once
recovers an information thing in the database as the while there is an exchange which is adjusting
the information thing, therefore, causing the distinction coming about to a non-repeatable read in
the transaction database
5. Phantom read
This is a situation in which a transaction executes a similar order of commands more than once
and the succeeding outcomes demonstrate a few modifications that were absent in the previous
set outcomes. This is made conceivable when there is a transaction that is including the extra
questions in the middle of the rehashing inquiries. It nearly has indistinguishable properties from
the non-coherent read. The thing that matters is that the changing column of results is because of
an erasure or addition process (Buckinx, Verstraeten & Van den Poel, 2010).

DATA WAREHOUSE ANALYSIS
Data Warehousing
This is the technique where data is collected from various sources are processed in order to offer
a meaningful insight into the organization.
Operations in data warehousing
Data warehousing deals with the collection of information from various sources that are then
combined into one database that is comprehensive. The following is an instance of data
warehousing taken from the business point of view. It can be considered that data warehouse
entails information of a customer from a certain organization, information from its website, those
gotten from mail lists and comment card of the same organization. Other information that may be
incorporated in the data warehouse of an organization may include all employee details and
much more information (Krishnan, 2013).
Introduction to Data Warehousing Types
There are three types of data warehouse, they include:
a) Data Mart
b) Enterprise Data Store
c) Operational Data Store
Data Mart: is a part of a data warehouse that is specifically designed and implemented for
specific divisions like finance or sales option. In some instances, the data can be sourced directly
from their sources. This mostly occurs when dealing with a data mart that is independent. Data
mart similarly to data warehousing will be designed for dimensional data modeling since they
relate to dimensional tables, to the users it is an added advantage since they can is visually
represented as a cube in several dimensions. Splicing the cubes according to the user's preference
is allowed in data warehousing.
Enterprise Data Warehouse: this is a warehouse that has a centralized structure. It offers a
means of representing the data and also the proper organization of the data in order to act as a

decision support system within the organization. It also allows the user of the system to access
the system and also classify the data according to their subject.
Operation Data Store: It is also known as ODS. This data is normally required when both
online transaction processing and data warehouse does not support the reporting needs of the
organization. In Operational Data Store, the system is refreshed according to real-time that
makes it convenient for storing information and records that are routinely based (Coronel, &
Morris, 2016).
The Architecture of data warehousing
This architecture is based on the system server of the Relational Database management that acts
as the central store in which informational data is kept. In the architecture of a data warehouse,
both processing data and operational data are distinct from data warehousing processing. This
central storage is somehow bound by many key components that are designed in making the
whole system manageable, accessible and functional by both the operational data that searches
for data in the warehouse and the end-user query and analysis tools (Watson, Ariyachandra &
Matyska, 2011).
A data warehouse normally has three-tier architecture. The three tiers of the data warehouse are
namely as follow:
a) Bottom Tier: This tier in the architecture symbolizes the database server that is found in
the data warehouse that is also referred to as the relational database system. At this point
most of the utilities and back-end tools are used to provide data into the bottom tier; these
tools perform the following actions that are refresh, extract, load and clean functions.
b) Middle Tier: In this tier is where the online analytical processing server lies that is an
extension of a relational database management system. The Relational Online Analytical
Processing is responsible for mapping some of the operations on MOLAP that is,
multidimensional Online Analytical processing data to a standard MOLAP model that
implements both operations and multidimensional data directly.
c) Top Tier: top tier represents the front-end layer of the client in the architecture which
holds the following tools:

i. Querying and reporting tools.
ii. Analysis and data mining tools (Berson & Smith, 2009).
PROBLEMS ASSOCIATED WITH DATA WAREHOUSING
These challenges include:
DATA QUALITY
The quality of the data is reduced due to the risk of error when the data is being integrated. Some
of the errors that may occur during the process include missing data and replicating data. This
error reduces the quality of the data and later result in poor analysis.
COMPREHENDING ANALYTICS
The utilization of intense analyzing apparatuses and subjective report can be utilized to make
future forecasts that will give the association a superior edge in the market. With the end goal to
enable this procedure to run adequately, the fashioners of the framework need to place this in
thought. This will require the client of the framework to know the sort of examination that will
be performed. Inability to do as such, it will prompt framework breakdown because of the failure
to play out the coveted investigation (Dagum, Singh & Dagum, 2011).
QUALITY ASSURANCE
The reports and charts form the analysis done form the data warehouse is used by the end user to
make assumptions and predictions of future outcomes. The quality of the reports should be high
in order to ensure that the information will not mislead the users. Maintaining the good quality
will also allow the smooth running of the other related operations.
PERFORMANCE
To ensure the effective and efficient use of the system, the architecture should be well structured.
The requirement and non-requirement of the system should be attained. A poor structure of the
system will cause a system failure since it will not be working as it was intended to.

DATA WAREHOUSE DESIGNING
Structured designs used for creating a data warehouse will lead to an effective system. Failure to
use a suitable design yields a poor system that will not satisfy the users wants thus leading to
system failure.
ACCEPTANCE BY THE USER
It is important for the users of a system to be incorporated into the development process. This
will ease the development process as the user will give his preference for the system. Failure to
include the user will lead to a system failure since the users will not use the system.
COST
The development and implementation of a proper system into a firm requires a lot of resources
input. This input includes labor and capital. The amount of capital that is mostly required to
develop an effective data warehouse is usually over helming for a firm. If the firm inputs less
resource to the data warehouse system it will lead to an ineffective system that does not meet the
user's requirements (Chaudhuri & Dayal, 2009).
CONCLUSIONS
The basic databases that included the hierarchical and network databases were invited in the
years 1960, and later in the year 1980’s the more effective database; object-oriented databases
were developed. The operational databases were later overtaken by the SQL engines in the years
after 1990. Based on the above report, it is visible that data warehouse is a collection of several
databases that are integrated together in order to provide decision support. A transactional
database is a database that its structure is suitable for handling productive systems that range
from websites, banks and that are able to roll back transaction that have not been fully
committed.
Majority of the firm today are incorporating the data warehousing. They are used to collect, and
store the organized data from their breaches and other components of the firm. It is essential for
employees of a firm need to understand the importance of the data warehouse and the added
facilities that come with it. They also give the firm a better edge in competing in the market. On

the other hand databases are also an important component in an organization. In an organization,
it makes it easier to manage the different types of data; this data may include employee details,
employee pay slip, and project management and also inventories. A firm that does not have a
database will waste a lot of time as the data will have to be input manually in the system. The
transactional databases enable the firm to organize its information in a predefined structure thus
making it easier for the firm to access and utilize the information.
The data in the database and the information system are not similar across different firm due to
the different natures of the firms. The system may vary between few megabytes or terabytes. For
example, there are systems that need to respond quickly and handle a large amount of data. The
firms are able to get aces to information quickly and when needed because of the use of
databases which store and organize the data. A collection of several programs that manages and
controls various functions carried out in the database is called a database management system
(DBMS).
Many companies and organizations use the information to get an analysis of what their
customers may want and prefer in the market or use the same information to make decisions and
business plan of the organization, in order to maximize on both their profit and sale. For this to
be achieved, something known as data mining is done which means searching for useful data
from the huge amount of data in the data warehouse.
For a database to be completely acceptable, it is important for the developers of the system to
incorporate the users during the process of developing the database. This makes it easy for the
developers to develop the system as the users give their opinions on their preferences.
A firm that is looking to have a better performance and an edge in a competitive market will
implement the data warehouse and a transactional database.

REFERENCES
Abbott, R. K., & Garcia-Molina, H. (2012). Scheduling real-time transactions: A performance
evaluation. ACM Transactions on Database Systems (TODS), 17(3), 513-560.
Berson, A., & Smith, S. J. (2009). Data warehousing, data mining, and OLAP. McGraw-Hill,
Inc..
Buckinx, W., Verstraeten, G., & Van den Poel, D. (2010). Predicting customer loyalty using the
internal transactional database. Expert systems with applications, 32(1), 125-134.
Chaudhuri, S., & Dayal, U. (2009). An overview of data warehousing and OLAP
technology. ACM Sigmod record, 26(1), 65-74.
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Dagum, P., Singh, T., & Dagum, L. (2011). U.S. Patent No. 7,917,463. Washington, DC: U.S.
Patent and Trademark Office.
Krishnan, K. (2013). Data warehousing in the age of big data. Newnes.
Ponniah, P. (2011). Data warehousing fundamentals for IT professionals. John Wiley & Sons.
Watson, H., Ariyachandra, T., & Matyska, R. J. (2011). Data warehousing stages of
growth. Information Systems Management, 18(3), 42-50.
Wixom, B. H., & Watson, H. J. (2010). An empirical investigation of the factors affecting data
warehousing success. MIS quarterly, 17-41.