Database Classification and Systems

Single vs. Multi-User Databases

  • Single-user databases are mostly textbook definitions and rarely exist today.
  • Multi-user databases support simultaneous access by two or more users; most modern databases fall into this category (e.g., SQL Server, Oracle, MySQL).

Centralized Database

  • Data is kept in one physical location, simplifying administration and ensuring data consistency.
  • Single point of failure; can be problematic for remote locations with slow internet or can overload the server.

Distributed Database

  • Data is kept in multiple physical locations for faster local access; some systems replicate data across nodes for redundancy.
  • More challenging to configure and maintain, but beneficial when data needs to be close to the end-user (e.g., Google search database).

Transactional vs. Data Warehouses

  • Transactional Databases:
    • Used for real-time data processing for day-to-day operations (OLTP).
    • Designed and optimized to record data quickly while maintaining data integrity.
  • Data Warehouse:
    • Captures day-to-day transactions and contains additional analyzed information (OLAP).
    • Designed to perform data analysis questions.

Options to a Paper-Based System: Limitations of Paper Systems

  • Physical storage increases over time.
  • Each visit requires physical file retrieval.
  • Large files for frequent visitors.
  • Difficult to ask cross-patient questions.
  • Limited privacy.
  • Risks include damage, misplacement, and lack of backups.

Desktop Productivity Tools

  • MS Word:
    • Stores text, not information, making retrieval difficult.
    • Primarily addresses physical storage limitations.
  • Excel:
    • Mimics a database, storing data in cells.
    • Lacks data validation, risking inconsistency.
    • Difficult to consolidate data for analysis without careful design.

Customized Software

  • Guarantees consistent data recording and report generation.
  • Problem: Development cost and limitations in program functionality.

DBMS System

  • Procedures and standards are written and enforced.
  • Roles:
    • System Administrator.
    • Database Administrator.
    • Analysts.
    • Database Designer.
    • Programmers.
    • End Users.

Database Models

  • Relational Database:
    • Stores everything in tables (e.g., MySQL).
    • Offers flexible retrieval mechanisms.
  • Key-Value Stores:
    • Each record is freeform; given a key, the system returns the value (e.g., MongoDB).