Database and DBMS Overview

Database

  • Definition: An organized collection of related data.

    • Examples: Bank account databases, payroll databases, Skyward, Amazon’s product database, hotel reservation databases, personal notes.

  • Importance:

    • Contain detailed information about organizations or applications.
    • Manage large data sets (big data).
    • Complexity ranges from simple text files or CSV to complex relational databases.

Types of Databases

  • Operational Databases:

    • Purpose: Collect, modify, and maintain data, serving as the backbone of companies.
    • Characteristics: Store dynamic data that changes consistently, providing up-to-date information.

  • Analytical Databases:

    • Purpose: Store and track historical and time-dependent data.
    • Importance: Helpful in tracking trends and making strategic business projections.
    • Characteristics: Static data represents a point-in-time snapshot, rarely changing.

Example Application Scenarios

  • Online Bookseller:

    1. Purposes/Services:
    • Sell and recommend books.
    1. Data to Maintain:
    • Information on books, customers, orders (pending, complete, canceled), order histories, trends, and preferences.
    1. Functionalities:
    • Insert and remove books, find books by author/title, analyze past order history.

  • Vaccination Appointment Scheduler:

    • Focus: Designing functionalities without implementation.
    • Considerations: Expected functionalities/services and types of data needed. Explore interrelations.

Needs for Data Management

  • Maintaining Real-World Entities: Proper storage of real-world information.
  • Persistent Large Datasets: Storage solutions that can handle big data efficiently.
  • Efficient Data Queries and Updates: Allow for updates and queries of large datasets without performance issues.
  • Modify Data Structure: Ability to add, update, or remove attributes as needed.
  • Concurrent Updates and Reliability: Support for multiple updates and recovery from failures.
  • Security and Integrity: Ensure data security and integrity while eliminating redundancy.

Early Attempts at the Relational Model

  • Early systems relied on file systems where the programmers visualized data based on physical storage.
  • Ted Codd introduced the relational model for databases which fundamentally changed data organization.

Database Management System (DBMS)

  • Definition: Software that creates, manages, maintains, and persists databases.
    • Functions:
    • Manage data, database engine, and schema.
    • Allow access, lock, and modification of data.
    • Define logical structure and support for concurrency, security, integrity, and administrative procedures.
    • Examples: MySQL, SQLite, MongoDB, PostgreSQL, Oracle, DB2, MS-SQL, Derby.

DBMS Properties

  • Queryable: Must allow questions to be asked and retrieve data.
  • Durable: Ensure data safety and long-term persistence.
  • Schema: Define data structure for storage.
  • No Redundancy: Minimize space use and prevent corruptibility.
  • Indexes and Optimization: Trade-offs between query speed and space efficiency.
  • Concurrency Management: Handle multiple transactions efficiently.

Key Roles in DB Environment

  • Database Administrator (DBA): Manages and tunes the database system.
  • DB Designers: Specify data structure or schema.
  • Application Developers: Create programs for data manipulation.
  • Data Analysts: Clean, correct, and integrate data.
  • DBMS Implementers: Build and maintain DBMS systems.

Common Features / Key Concepts

  • Data Models: Describe real-world data structure.
  • Schema: Definition of tables in a database.
  • Instance: Snapshot of current data in the database.
  • Data Definition Language (DDL): Defines schema.
  • Data Manipulation Language (DML): Affects data instance.
  • Data Independence: Physical and logical data independent of applications.
  • Transaction Properties: Supports atomicity, consistency, isolation, and durability.