What is a database?
The database is an organized collection of data. Databases store the data and help in managing the data efficiently. It supports many operations such as update data, deletes data, modifies, view data. Some examples of databases are a hospital patient’s database, telephone directory, and social networking sites data, etc.
What is a data warehouse?
A data warehouse is a database consisting of historical data ranging from 5-10 years old data. A data warehouse is populated from multiple heterogeneous sources. It is a subject oriented, time-variant, involatile and integrated database. The architecture of the data warehouse comprises of 3 tier: data sources, data warehouse, and Front End user layer.
What is OLTP?
The online databases responsible for transactions and query processing are online transaction processing databases. The databases used in day- to- day transactions such as marketing, purchasing, payroll, registration are OLTP systems.
Let us compare each of these.
Comparison between Data warehouse and OLTP
| Data warehouse | OLTP | |
| System orientation | It is a market-oriented database used by management for the decision making process. | It is a customer-oriented database used by employees. |
| Data | Data warehouse contains historical data which is aggregated and summarized. | The data present in the transactional database is current data and too detailed. |
| Database Design | Data warehouse follows star and snow flake schema model for designing the database. | The database follows the entity-relationship(ER) database, model |
| Data Access | The data warehouse has read-only access with complex queries. It does not support data access for day-to-day operations | The OLTP database is accessible via simple queries. It is used for short transactions. OLTP requires mechanisms such as concurrency and recovery control. |
| Data View | The information contained in the data warehouse comes from multiple data sources. It spans multiple database schemas. It requires multidimensional data view. | The OTLP focuses mainly on current data. |
| Data Operations | Data warehouse requires a lot of scans | OLTP follows indexing and hashing on the primary key |
| Database size | The size of DW is more than terabytes of data | The size of OLTP ranges from few gigabytes to hundreds of gigabytes |
| Database records accessed | About millions of records can be accessed at one time. | About tens or hundreds of records can be accessed at one time. |
| Database Function | DW is used for information processing and analysis | OLTP is used for operational processing and transaction processing. |
| Data Query | DW information can be processed by complex queries | OLTP database operations can be accessed by simple queries |
Difference between data warehouse and database
| Data warehouse | Database | |
| Function | Type of database used for analytical processing | Collection of objects used for data retrieval, modification, and data access. |
| Data | Data in DW comes from a variety of data sources heterogeneous in nature. | Data in database comes from different sources such as online transactions, registration, sales, purchasing, etc. |
| Types | Data warehouses are generally enterprise data warehouses. It is an OLAP present on top of the OLTP database. Data Marts are subsets of data warehouses | Databases are of many types such as OLAP, OLTP, XML, CSV and Excel spreadsheets and flat files. |
| Data Storage | DW stores data from multiple applications | Single database stores data of the single application |
| Service Level Agreement | DW SLA’s are flexible as occasional downtime is expected. Data refreshing takes places depending on the size of data from 24 hours- weeks to months. | Databases offer 99.99% uptime. Data refreshing takes place in real time to serve the needs of the organization. |
| Data Organization | Data is organized to encourage analysis and reporting. The data is stored in the deformalized form with less of tables and simple structure | OLTP Database encourages efficient storage and processing. Therefore the data is normalized so that duplicate data is not present |
| Reporting | DW allows in-depth analysis such as descriptive, diagnostic analysis. DW needs deep drilling down, aggregation and summarization techniques of data. | The database helps in simple reporting such as monthly reporting etc. It cannot perform an in-depth analysis. The database generates static reports. |
| Data Processing | Analytical processing may require several minutes to run | Databases which are OTLP require sub-second response time. |
| Requirements | DW works on large datasets for aggregating data so it is heavy. It requires high processing CPU and larger disk bandwidth. | OLTP databases perform single point transactions thus are comparatively lighter than DW queries |
Similarities between Database and Data warehouse
- Both the database and data warehouse is used for storing data. These are data storage systems.
- Generally, the data warehouse bottom tier is a relational database system. Databases are also relational database system. Relational DB systems consist of rows and columns and a large amount of data.
- The DW and databases support multi-user access. A single instance of database and data warehouse can be accessed by many users at a time.
- Both DW and database require queries for accessing the data. The Data warehouse can be accessed using complex queries while OLTP database can be accessed by simpler queries.
- The database and data warehouse servers can be present on the company premise or on the cloud.
- A data warehouse is also a database.
Examples of database and data warehouse
- Data warehouse: Data warehouse is a relational
database for query analysis rather than transactional processing. The top 3
data warehouses are:
- TERADATA: It contains more than 30 years of historical data. It is robust, scalable, hybrid storage capacity and analytics.
- Amazon Web Services: Amazon stores data storage tools in its cloud service platform such as Amazon Redshift, Elastic Map Reduce.
- ORACLE: Oracle 12 c is a high-performance data warehousing tool.
- Database: Types of the database are:
- Text database: Data organized in a text file in the form of rows and column.
- Microsoft Excel Spreadsheet: It is a desktop database program for a single user for storing data and retrieving data.
- RDBMS: Relational databases such as Oracle, SQL, and MySQL. They support multiple users.
- Object-Oriented Databases: It follows classes and objects for data storage. These databases follow OOPS (Object Oriented Concepts) for data storage, retrieval, manipulation.
- Database Management Systems: The popular
Database management systems are:
- MySql Database: MySQL was founded in 1995 and acquired by Sun Microsystems in 2008.
- MS –Access: It is a computer-based database developed by Microsoft.
- Oracle: It is developed by Oracle Corporation and it is a relational database system.
- NOSQL: It is not the only SQL and it is non-relational database system.
⇓ Subscribe Us ⇓
If you are not regular reader of this website then highly recommends you to Sign up for our free email newsletter!! Sign up just providing your email address below:
Happy Testing!!!