Outline

• Information Integration
• Typical components of a DBMS
• Storage and Buffer Management
• Quiz
• Query Processing
• Transaction Processing
• Query Processor

Introduction

• Last day, we showed we finished up our description of what is a database.
• We then went over the history of database systems from the 1960s to the present
• This involved initially adding features in the 1960s to filesystems to make it easier to organize data into records and to search and retrieve those records.
• The idea of splitting data into table -- the relational model -- was developed in the 1970s, 1980s.
• Along with this came facilities for ensuring integrity while carrying out transactions processing.
• The 1990s brought automated logging and recovery techniques, active databases and triggers, and through the 2000s the ability to handle semi-structured data like HTML, XML, JSON, etc. were added and developed.
• Since the 2010s, we have seen more and more facilities for handling datasets of sizes in the TB, PB, EBs. These include NoSQL approaches.
• Today, we continue our overview DBMSs by beginning with a very common problem for companies using databases: Infromation Integration.

Information Integration

• One standard problem in building and maintaining databases is to join information contained in many related databases into a whole.
• This is called information integration.
• For example, a large company has many divisions, each of which has built its own database of products or employee records independently of the other divisions.
• This can easily happen especially if one division was originally a separate company that was acquired.
• Over the course of the history of a company several databases might have been used and some data might still only exists in some of the earlier system.
• As a result, one often needs to build a structure on top of existing databases to allow all the data to be accessed from one interface.
• Another approach to the information integration problem is to periodically copy the data from these legacy system into a central system known as a data warehouse.

Overview of a Database Management System

• Above is a diagram of the parts of a complete DBMS, boxes represent system components, double boxes represent in-memory data structures, and lines indicate control and data flow.
• We are going to briefly consider the parts of this model over the next couple slides.

Sources of Commands

• Starting at the top of the diagram, we see there are two distinct sources of commands to the DBMS:
  1. Conventional users and application programs that ask for data or modify data.
  2. A database administrator (DBA): a person or persons responsible for the structure or schema of the database.
• Let's consider the second kind of command first as it is simpler to trace through the diagram.

Data-Definition Language Commands

• Let's consider an example: A DBA for a registrar's database might decide that there should be a table or relation with columns for a student, a course the student has taken, and a grade for the student in that course.
• The DBA might also decide that the only allowable grades are A,B,C,D and F.
• This structure and constraint information is all part of the schema of the database.
• This info could be entered by the DBA in a Data Definition Language.
• To execute these commands, the DBA needs special authority over the database a typical user would not have to execute DDL commands.
• These commands would then be parsed by the DDL processor and passed to the execution engine, which then goes through the index/file/record manager to alter the metadata schema information for the database.

Quiz

Which of the following is true?

1. A database is a collection of tables.
2. The hierarchical model (used in IBMs IMS system) organizes data in databases into tree-like hierarchies.
3. NoSQL databases first appeared in the 1980s.

Overview of Query Processing

• The majority of interactions with the DBMS follow the path of the left hand side of the figure a few slides ago.
• A user or an application program initiates some actions using the data-manipulation language (DML)
• This command does not affect the schema of the database, but it may affect the content of the database.
• Alternatively, it might extract information from the database.
• For this reason DML statements are handled by two separate subsystems: the query subsystem and the transaction subsytem.

Answering a Query

• A query is parsed and optimized by a query compiler.
• This results in a query plan or a sequence of actions the DBMS should perform to answer the query.
• In turn, this is passed to the execution engine to actually calculate the results of the query.
• The execution engines issues a sequence of requests for small pieces of data, typically records or tuples of a relation to a resource manager.
• The resource manager knows about data files for holding relations, the format and size of records in those files, as well as index files which help finds elements of data files quickly
• The requests for data are passed to the buffer manager. The buffer manager's task is to bring appropriate portions of the data from secondary storage (disk) where it is kept permanently, to the main memory buffers. It does this in units of disk blocks.

Transaction Processing

• Queries and other DML actions are grouped into transactions.
• These are units of DB actions that must be executed atomically and in isolation from one another.
• We also require that transactions must be durable, meaning that the affect of any completed transaction must be preserved even if the systems fails.
• A transaction processor usually has two main parts:
  1. A concurrency-control manager (aka scheduler) responsible for assuring atomicity and isolation of transactions.
  2. A logging and recovery manager responsible for the durability of transactions.

Storage and Buffer Management

• Data in a database usually resides in secondary storage (disk/SSD).
• To perform useful operations on data, the data must be in main memory (RAM).
• It is the job of the DBMS' storage manager to control the placement of data on disk and control its movement between disk and main memory.
• The storage manager keeps track of the location of files on the disk and obtains block or blocks from a file based on requests from the buffer manager.
• The buffer manager is responsible for partitioning the main memory the DBMS has access to into buffers.
• Buffers are page-sized regions (a page is a standard number of bytes of main memory) into which disk blocks (a block is standard number of bytes that are read/written to the disk in one go) can be transferred.
• All DBMS components that need disk info will interact either directly or indirectly with the buffer manager.

DBMS Info Stored on Disk

The following DBMS info is persisted on disk and hence would involve requests through the buffer and storage manager for to access and manipulate:

1. Data: the contents of the database itself.
2. Metadata: the database schema that describes the structure of and constraints on the database.
3. Log Records: information about recent changes to the database; these are used to make ensure the durability of data stored in the database.
4. Statistics: information gathered and stored by the DBMS such as sizes of various relations, etc.
5. Indexes: data structures used to help speed up access to data in the database.

Transaction Processing

• Often one or more database operations are group together into what's called a transaction.
• For example, read an account balance, change its value, write an account balance.
• A transaction is a unit of work that must be executed atomically and in apparent isolation from other transactions in order to ensure the consistency of the data in the database.
• A DBMS should ensure that the work of a completed transaction is never lost (it is durable).
• The transaction manager accepts commands from a user/application. For example, begin transaction, end transaction, and various kinds of read/write commands.
• It also allows the user/application to set the expectation for how these commands must operate. For example, some transactions might be more or less strict about their atomicity requirements.

ACID Properties of a Transaction

Properly implemented transactions are often said to meet the "ACID" test where:

• "A" stands for "atomicity". Either all of the effects of the transaction are permanently recorded to the DB when the transaction completes or none of them are.
• "C" stands for "consistency". Given a database (DB) that meets all constraints and expectations, the results of a transaction should be a DB in which consistency constraints still hold and expectations about relationships of data are still valid.
• "I" stands for "isolation". Each transaction should appear to be executed as if no other transaction is executing at the same time.
• "D" stands for "durability". The effect of a transaction should never be lost once the transaction completes.

Tasks of the Transaction Processor

• Logging: To ensure durability, every change in the database is logged separately to disk. The log manager follows one of several policies to assure that no matter when a system failure (aka crash) occurs, a recovery manager will be able to examine the log of changes and restore the database to a consistent state.
• Concurrency Control: Transactions must appear to execute in isolation. On most systems, however, many transactions will be executing at the same time. The scheduler/concurrency control manager must assure that the individuals actions of multiple transactions are executed in such an order that the net effect is the same as if the transactions had operated one at a time in some order. Schedulers often do this by maintaining a lock table which has rows consisting of a transaction id and a database item (row, page, table, etc) that a transaction has the lock for. In order to schedule an operation on an item with the execution engine, the scheduler requires a transaction to first get a suitable lock on that item. By controlling, when locks are released, the scheduler can ensure isolation.
• Deadlock Resolution: As transactions compete for resources through the locks the scheduler grants, one can get into a situation where some group of transactions cannot proceed because each transaction in it needs a lock that the another transaction in the group has. The transaction manager then must intervene and decide to abort some transactions to allow others to proceed.

Query Processor

• The query processor has two main components:
  1. The query compiler: This translates the query into an internal form called a query plan, a sequence of operations to be performed on the data. Often these plans are a sequence of "relational algebra" operations. The query compiler has three major sub-parts:
     1. A query parser: This builds a tree data structure from the text (SQL) form of the query.
     2. A query preprocessor: This performs semantic checks on the tree (do all specified relations exists?, etc.). It might also do simple tree transformations to turn the parse tree into a tree of algebraic operators representing the initial query plan.
     3. A query optimizer: This transforms the initial query plan into the best available sequence of operations on the actual data. For example, this might involve choosing if there is more than one index available on the data which to use. It might involve choosing one of many algorithms to use based on table size statistics, etc.
  2. The execution engine: This is responsible for executing each of the steps of the chosen query plan. This component interacts with other components directly or through the buffers to manipulate the data. It also interacts with the scheduler to avoid accessing locked data, and with the log manager to make sure all the DB changes are properly logged.