Definition: DBMS

(DataBase Management System) Software used to create and maintain a database. A DBMS accepts requests from an application and instructs the operating system to write to and read from the database. The most popular type of database structure today is "relational" (see below). An essential software system, IT departments may employ different database management systems; for example, one for back-office processing and another for ad hoc queries.

Major DBMS vendors are Oracle, IBM and Microsoft. Popular open source databases are MySQL for large projects and SQLite for smaller ones.

A Note on Terminology
Because a database is created by a DBMS, the terms DBMS and database are used interchangeably. Although a database can be a single data set (single file), it is generally two or more data sets related in some manner. However, a single file with no relationships that is not created by a DBMS is often called a database as well. Essentially, any storage of data may be called a database. See file.

The Features
A DBMS interfaces with applications written in traditional programming languages (C/C++, Java, etc.), and it may provide its own programming language. Following are the features of a DBMS and why it is essential software in the IT world.

Data Independence
Because requirements change, a DBMS lets new data structures be easily added to an information system. Without a DBMS, programmers reserve space in their programs for a file format that has a fixed structure. If a new field is added and 20 applications access the file, all 20 have to be changed even if only one uses the new data.

When a DBMS is used, applications access data by field name (column name); for example, "customer name and balance due." Newly added fields have no impact on existing applications unless they process the new data. See buffer.

Indexes and Interactive Query
Indexing is an essential feature of a DBMS. One or more fields within a record (columns within a row) are defined as key fields (account no., name, etc.). The DBMS creates and maintains these indexes in order to provide immediate access (see index).

The DBMS may support the SQL query language, and it may provide its own query language that uses some form of English syntax. See SQL, query language and report writer.

Data Security
With passwords, the DBMS prevents unauthorized access to the data but gives authorized access at different levels. For example, some users may be able to view salaries in an employee database while others may view only work history and medical data. See database security.

Data Integrity
The DBMS can ensure that no more than one user can update the same record at the same time. It can keep duplicate records out of the database; for example, no two customers with the same account number can be entered. See "intelligent database" below.

Interactive Access Has No Audit Trail
The DBMS may allow users to create a simple database (single data set) from the keyboard and interactively update it. However, interactive operation does not automatically generate an audit trail, which if required by regulations, must be designed into all applications that enter and update the data.

Database Design
A business information system is made up of subjects (customers, employees, products) and transactions (orders, payments, updates). Database design defines the data relationships; for example, customers to orders by account number, vendors to products by part number. The DBMS maintains the linkage between these records.

Hierarchical, Network & Relational
Most every DBMS today is relational, but the hierarchical and network DBMS were the precursors.

Hierarchical DBMS

Hierarchical databases link records like an organization chart, and a record type can be owned by only one owner. Hierarchical structures were widely used with early mainframes.

Network DBMS

Records can have multiple owners. In this example, orders are owned by customers and products, reflecting their relationship in business.

Relational DBMS

There is no physical linkage. The relationships are simulated by the DBMS. Common fields such as account number are used for matching, and such fields are usually indexed to speed up queries. The hierarchical and network DBMS may also provide a "virtual linkage" capability. See relational database.

Object Database

When hierarchical, network and relational structures are too restrictive, an object database can handle complex data structures. Object databases handle many-to-many relationships.

Intelligent Database
A DBMS may provide some data validation; for example, rejecting invalid dates or alphabetic data entered into money fields. But most validation is left up to the application programs.

Intelligent databases provide more validation; for example, table lookups can reject bad spelling or incorrect codes. Common algorithms can also be added such as computing sales tax based on zip code.

When validation is performed in each application, one program may allow an item to be entered while another rejects it. Data integrity is better served when data validation is done in the DBMS. The mainframe DBMS was the first to become intelligent.

Database Terminology

Like everything else in the tech field, there are multiple ways of saying the same thing. For decades, field, record and file defined data structures until relational databases came along.

DBMS and OS Interaction

This diagram shows the interaction between the DBMS with other system and application software running in memory.