Data and Database Fundamentals

Data and Database Fundamentals

• Data is essential for an enterprise's operation and decision-making.
• A database is a container that stores data.
• A warehouse is a commercial building for storing goods, but in the context of data, a data warehouse is a relational database designed for query and analysis rather than transaction processing.

Big Data and Data Warehousing

• Big Data refers to data that is too large to be processed on a computer.
• Characteristics of Big Data include:
  • Volume
  • Velocity
  • Variety
• A data warehouse (DWH) is a relational database designed for query and analysis, not transaction processing.
• Data warehouse environment includes:
  • ETL (extraction, transformation, and loading)
  • Statistical analysis
  • Reporting
  • Data mining capabilities

Data Warehouse Characteristics

• Characteristics of a data warehouse include:
  • Subject-oriented
  • Integrated
  • Non-volatile
  • Time-variant
• Subject-oriented means it can answer questions like "Who was our best customer for this item last year?" or "Who is likely to be our best customer next year?"
• Integrated means it is constructed by combining multiple, heterogeneous data sources.
• Non-volatile means that once entered into the DWH, data should not change.
• Time-variant means that the data in the DWH is time-dependent.

Data Mining and Business Intelligence

• Data mining is the process of extracting usable data from a larger set of data.
• Data warehouse implementation is the process of implementing a data warehouse using tools like Microsoft SQL Server.
• Business intelligence (BI) is a technology infrastructure for gaining maximum information from available data.
• BI is used to improve business processes and involves:
  • Gathering data about customers, products, and sales
  • Analyzing data using OLAP tools
  • Sharing insights and data

OLAP and Business Intelligence

• OLAP (Online Analytical Processing) is a software that facilitates multidimensional analysis.
• Characteristics of OLAP include:
  • Historical data
  • Infrequent updates
  • Integrated data
  • Analytical queries
• Types of OLAP include:
  • MOLAP (Multidimensional OLAP)
  • ROLAP (Relational OLAP)
  • HOLAP (Hybrid OLAP)
• OLTP (Online Transactional Processing) is the source data for OLAP and is characterized by:
  • Operational database
  • Frequent updates and queries
  • Normalization and ACID
  • Accessing individual tuples

Data Modeling

• A data model visually represents data and is used to create a database.
• Types of data models include:
  • Conceptual data modeling
  • Logical data modeling
  • Physical data modeling
• Conceptual data modeling gives an idea to the functional and technical team and is created by gathering business requirements from various sources.
• Logical data modeling is the actual implementation of a conceptual data model and includes:
  • Entities and relationships among them
  • Attributes for each entity
  • Primary key for each entity
  • Foreign keys
  • Normalization occurs at this level
• Physical data modeling represents how the model will be built in the database and includes:
  • Database performance
  • Indexing strategy
  • Physical storage