Database Systems Basics


A transaction is a group of queries that must be either completed or rejected as a whole.

  • when processing transactions, DBMS must:
    • ensure transactions are processed completely or not at all
    • prevent conflicts between concurrent transactions
    • ensure transaction results are never lost

Architecture

Database architecture describes the internal components and the relationships between components.

  • components:
    • query processor
      • interprets queries, creates a plan to modify the database or retrieve data, and returns query results to the application
      • performs query optimization to ensure the most efficient instructions are executed
    • storage manager
      • translates the query processor instructions into low-level file-system commands that modify or retrieve data
      • uses indexes to quickly locate data
    • log
      • is a file containing a complete record of all inserts, updates and deletes processed by the database
      • transaction manager writes to log before applying changes to the database
    • catalog
      • aka data dictionary
      • is a directory of tables, columns, indexes, and other database objects
      • used to process and execute queries

Metadata is data about the database, such as column names and the number of rows in each table.

Products

A relational database stores data in tables, columns, and rows.

  • similar to a spreadsheet
  • most database systems are relational
  • all data in a column has the same format
  • all data in a row represents a single object
  • all relational database systems support Structured Query Language (SQL)
    • uses statements that read and write data, create and delete tables, and administer the database system
  • ideal for databases that require an accurate record of every transaction
  • not ideal for big data
    • massive volumes of poorly structured or incomplete data
    • thus non-relational systems were created optimized for big data
      • called NoSQL
  • db-engines.com ranks systems