Database Management Systems (DBMS)

A database management system (DBMS) is the software that creates a database, manages it and controls access to it. Users and programs never reach the stored data directly: everything goes through the DBMS.

Databases come in many sizes, from a simple system made for one person to the central database of a large organisation. Some of the facilities a DBMS provides are only needed in a large organisation, where they are controlled by a database administrator.

In a file-based approach, each program keeps its own files, so the same data is stored many times, copies get out of step, and every program depends on how its files are laid out. A DBMS deals with these problems:

A DBMS also gives developers and users software tools: a developer interface for building the database and its forms and reports, and a query processor for finding data.

A database is not just a collection of data: it is built according to the rules of a theoretical model. This idea was proposed by ANSI (the American National Standards Institute) in the 1970s, in its three-level model. The three levels are the external level, the conceptual level and the internal level.

• The internal level is the lowest level. Its internal schema describes how the data is physically stored, here on disk. This level is controlled by the database management system (DBMS) software. Only the programmers who wrote the DBMS know the storage structure, and the DBMS copes with any change of storage medium.
• The conceptual level holds a single, overall view of the whole database. It is controlled by the database administrator (DBA), who has access to the DBMS. Its conceptual schema describes how the data is organised as a user or programmer sees it. It is also called the logical schema.
• The external level holds the individual user views and programmer views. Each view has an external schema that describes which parts of the database it can reach. One view can be used by several programs.

Views are an important security tool for the DBA. Each user, or group of users, can be given access rights that control what they are allowed to do through their view. For example, a user may be allowed to read data but not to change it, or may only be able to reach a few of the tables in the database.

Data modelling means planning what data a database will store and how the items of data are related, before the database is built. The model is usually drawn as an entity-relationship (E-R) diagram, which shows the tables (entities) and the relationships between them.

The result of data modelling is the logical schema: the overall design of the database, showing its tables, their fields and the relationships between the tables. It is the conceptual schema of the three-level model. The logical schema describes the data and its structure, but not how the data is stored on disk, so it does not depend on any particular DBMS. It is then used to design the physical structure (the internal schema).

An important feature of a DBMS is the data dictionary. It is part of the database, but it is hidden from everyone except the DBA. It holds metadata: data about the data. This includes the definitions of all the tables, fields and so on, and also details of how the physical storage is organised.

Keeping the data dictionary up to date is part of the data management that a DBMS provides.

Whatever the size of the database, one way to create it is to use the special-purpose language SQL, which is covered in 8.3. Most DBMSs also offer other ways. The DBMS provides software tools through a developer interface. These let a developer create tables and define their fields (attributes) and data types without writing SQL.

The DBMS also lets a programmer build a user interface for the database. Another feature is the ability to create a report, which presents output in a formatted way. A programmer can give users access to queries and reports from the user interface.

A query is the way to extract and manipulate data in a database. The DBMS provides a query processor that lets a query be created and then processed (run).

The DBA is responsible for setting up the user and programmer views, and for giving each one suitable, specific access rights. Access rights can be given to individuals or to groups of users. For example, some accounts can read data, some can read and write data, and others cannot read a table at all.

As with every system, regular backups are essential. The DBA is responsible for backing up the stored data.

Data integrity means the data in the database is accurate, complete and consistent. This is a key concern. One possible problem is a transaction that starts but is stopped by a system problem before it finishes, which would leave the database in an undefined state. A DBMS has a built-in feature that stops this happening: a transaction is either completed in full or not done at all.

Several DBMS features improve performance. The most important is the ability to create an index for a table, which is needed when a table has many fields and many records (tuples). An index is a second table linked to one field. It holds the values of that field, each with a pointer to the matching record in the original table. Searching the index is much quicker than searching the whole table.

Searching on a primary key gives a single matching record. For example, searching the sports club Member table (in 8.3) for member number 132099 gives the record for Abdel Patel. But databases are often searched on other values, for example to find a member by name.

Searching a large number of records can be slow, and an index speeds this up. Behind the scenes, extra information is stored so records can be found as quickly as possible. All primary key fields are indexed automatically. If records will often be found using some other field, that field should be set up as a secondary key, which is indexed. A surname is a very common secondary key in a table about people.