Operational Vs Informational system : Operational Systems are those which are based on current data and thus supports current/day-to-day functioning of a business. Informational Systems on the other hand are based on historical information and thus supports complex data mining/analysis and decision making. The core difference lies in the way queries are run to process/analyze data. Simple, planned and real time queries are run on an operational system that acts on small data sets, where as in an informational system, huge complex queries are executed that acts on bulk amounts of data sets.
Operational database vs. Data warehouse : Operational database is the database that we use to implement an operational system and Data warehouse is one of the opted database design that we use to implement an information system. Operational database is less complex in design (such as relational based and in 3rd normal form) supporting storage and processing of current/operational data where as Data warehouses are designed to support effective storage and processing of large volumes of historical data. Operational databases are relational in most cases, where as Data warehouse follows database models such as star or snowflake schema (multidimensional data models).
Data warehouse vs. Data mart : Data warehouse comprises of a complete domain of data pertaining to an enterprise, where data marts are scaled down version of a data warehouse that is confined to a particular data domain (eg: sales). In other words, data marts are a subset of data warehouse.
OLTP vs. OLAP : OLTP or Online transaction Processing Systems refers to those Information Management systems that are based on operational databases and thus supports current business transactions such as sales order processing. OLAP or Online Analytical Processing systems are those which are based on data warehouses or similar solutions that are designed to support ad-hoc query analysis or data-mining based on historical data. OLTP supports current business functions and flows where OLAP supports decision making based on complex data analysis.
Ad hoc queries vs. Data Mining : Ad hoc queries are queries which are designed for a specific known purpose and cannot be dynamically altered to suit a different need. Data mining on the other hand allows us to create dynamic queries based on real time user/process inputs and thus produce data patterns which are not originally known to the business.
Star schema vs. Snow flake schema : Both Star and Snow flake schemas are data warehouse database design models that supports multidimensional data modeling. Star schema is the most simple data warehouse data model which consists of one or more fact tables in the middle and many dimensional tables connected to the fact table, thus creating the shape of a star. Snowflake data warehouse data model are similar to star schema, except that the dimensional tables are normalized. Snowflake schema is usually used when we convert an already normalized transactional database into a data warehouse, where all dimensional tables would be already normalized.