Databases and Information Systems Overview

Database Fundamentals and Types

  • Database Definition: A collection of related data that can be stored, sorted, organized, and queried.
  • Core Advantages:
    • Efficient management of large volumes of data.
    • Seamless information sharing across organizations.
    • Promotion of data integrity, ensuring information remains accurate, consistent, and reliable.
  • Flat Databases:
    • Stored as simple lists or single tables.
    • Prone to data redundancy, data inconsistency, inappropriate data, and incomplete data.
  • Relational Databases:
    • Organize data into two-dimensional tables (relations) linked by shared common fields.
    • Common relationship types: one-to-many, one-to-one, and many-to-many.
  • Object-Oriented Databases:
    • Store data in objects containing both data attributes and the procedural methods to manipulate them.
    • Better suited for unstructured, complex data formats like Binary Large Objects (BLOBs), supporting faster data retrieval.
  • Multidimensional Databases:
    • Structure data in a cube format across multiple dimensions, featuring measure attributes and feature attributes.
    • Provide customized views to analyze information across different business angles and process analytical queries faster than pure relational databases.

Multidimensional database cube format

  • NoSQL Databases:
    • Designed to scale rapidly and process modern, unstructured, dynamic web-created data.
    • Prioritize rapid access and performance over strict data consistency.

Relational Database Components and Data Management

  • Database Management System (DBMS): System software that enables users to store, define, modify, query, and output data.
  • Data Hierarchy:
    • Field: A single category of data (column).
    • Record: A collection of related fields representing a single entity (row).
    • Table: A collection of related records.
  • Field Attributes:
    • Data Types: Short Text, Long Text, Number, Large Number, Currency, Date/Time, Yes/No, Calculated, OLE Object, Hyperlink.
    • Field Properties: Define field parameters such as Field Size, Default Value, and Caption.
  • Database Keys and Integrity:
    • Primary Key: A unique identifier field for each record in a table.
    • Foreign Key: A common field that links to a primary key in another table.
    • Referential Integrity: Rules enforcing that a foreign key value must match an existing primary key in the related table.

Relational database tables linked by primary and foreign keys

  • Data Dictionary & Metadata: An organized repository documenting database structure, fields, and descriptions (metadata).
  • Data Validation Techniques:
    • Range Check: Confirms data falls within specified boundaries.
    • Field Constraint: Enforces specific formatting or entry rules.
    • Completeness Check: Ensures required fields are not left blank.
    • Consistency Check: Compares entered data with other fields to identify logical contradictions.
    • Alphabetic/Numeric Checks: Ensures correct character types are entered.
  • Data Operations:
    • Input Forms: Streamline and control how data is entered.
    • Sorting & Filtering: Reorder data or temporarily display matching subsets.
    • Queries: Questions posed using database query languages to extract specific data.
    • Reports: Formatted, printable summaries of extracted data.

Data Warehousing and Data Mining

  • Data Warehouse: A centralized, large-scale repository aggregating data from internal business databases, external repositories, and clickstream logs.
  • Data Mart: A specialized subsection or slice of a data warehouse focused on a single business unit or department.
  • Data Mining: Analytical exploration of massive datasets to discover hidden trends and patterns.
  • Data Mining Techniques:
    • Anomaly Detection: Pinpoints outliers to expose problems or emergent opportunities.
    • Association / Affinity Grouping: Determines which items or data points occur together.
    • Classification: Applies predefined class definitions to categorize unclassified data.
    • Clustering: Automatically groups similar data without predefined categories.
    • Estimation: Assigns score values to records based on defined criteria.
    • Visualization: Formats data through charts and dashboards for intuitive managerial interpretation.

Business Information Systems

  • Information System: A comprehensive software solution used to acquire, process, store, and deliver business information.
  • Transaction-Processing System (TPS):
    • Tracks daily business operational transactions.
    • Real-time Processing: Immediately updates records to reflect current states.
    • Batch Processing: Groups non-time-sensitive transactions for bulk processing at scheduled intervals.
  • Management Information System (MIS):
    • Assists operational managers by converting transaction data into regular reporting.
    • Produces detail reports, summary reports, and exception reports.
  • Decision Support System (DSS):
    • Combines historical internal data, external sources, management models via a model management system, and user insights to solve semi-structured and unstructured problems.
  • Business Intelligence (BI): Tools and practices enabling executives to evaluate large operational datasets to make strategic business decisions.
  • Enterprise Resource Planning (ERP): Comprehensive software suite unifying information flows across all functional business departments.
  • Knowledge-Based Systems: Intelligent platforms supporting complex human decision-making using Artificial Intelligence (AI), Natural Language Processing (NLP), and fuzzy logic.