The main objective of a database management system is to store, retrieve, and process data. In order to do this in a safe and efficient manner, several major components work together to achieve this aim. They are:
Databases
Databases mainly consist of sets of related tables, which house data, all centered on a central topic or purpose. For instance an Accounting database would house tables all related to storing and processing account transactions.
In addition to tables, databases contain other objects, such as indexes, to aid in retrieving and storing data within the database.
A DBMS (Data Base Management System) can manage several databases.
Security
Access to a particular database is granted through the DBMS security module. The DBA (Database Administrator) creates user IDs for people and applications that wish to access a DBMS databases. These user ID’s then have special permissions to access one or more databases. The security can become quite granular. For instance it can be set up so that users only have access to specific tables or views. In addition you can restrict whether a user can read, write or delete data.
The data dictionary holds a description of each database. The data dictionary contains entries for all tables, fields, data types, indexes, and so on. Every object defined is included in an entry.
The data dictionary is also SQL database. This makes it really easy to query and an excellent resource when you need to explore and understand the structure of a database.
You could write a query to list all the tables in a database, or to list all the indexes defined for a particular table. Many database management system tools, rely on the data dictionary to provide information about a database.
Database Management System Storage Engine
The storage Engine manages how data is stored in the database.
It is responsible for management of all aspects of ACID (Atomic, Concurrency, Isolation, and Durability).
The engine manages create, read, update, and delete (CRUD) data operations from the computer’s hard drives. Storage engines are quite sophisticated and utilize special indexing structures to speed up data retrieval. Most modern database engines are capable of caching data in high speed RAM (Random Access Memory). Reading data from RAM is thousands of times faster than from a hard drive. This obviously speeds up repeated queries.
Query Processor
The query processor executes the SQL statements is receives. The process has two major jobs: to validate the statement and to then find an optimal means to fulfill the request. The means to fulfill the request is called a query plan.
The processor validates the statement by checking that the syntax is correct. It does so by checking the SQL statement’s grammar and also comparing tables and columns within the statement to those found in the data dictionary.
Once a statement is validated it is passed along to the query optimizer. Here the statement is analyzed to find the best way to take advantage of the storage engine to execute the request. For instance, if the processor is given a query to select an employee by a specific employee ID, the optimizer will look to see whether there is an index defined on employee ID, if so, it will use that to look up the employee rather than resorting to doing a reading the table from start to finish.
Once a statement is executed, the processor collects the results and passes them back to the requester. If the requester is a program, the result are available in the form of a dataset. Datasets are typically displayed on a screen in grid or table form.
Thoughts on Database Management Systems
Of the five items discussed my two favorites are the data dictionary and the query processor. I feel the data dictionary is one of the most underutilized resources in a database. There is so much, you as a DBA can learn and do with a data dictionary. In the past I’ve written generalized queries, that wouldn’t be possible without the data dictionary, such as dumping all duplicate records from all tables in a database.
Also, the query processor, especially the optimizer is most fascinating! You can learn a lot about how the optimizer works by looking at a query’s plan. Given a SQL statement, most modern databases will output the plan, list of tables, indexes, and operations, they’ll use to access the data. By studying a query plan, I’ve been able to decrease a query’s cost by one hundred thousand fold. I did this by noticing that the query was fully scanning a table rather than using an index. All I needed to do was create the missing index.
Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.
Leave a Reply