The DBMS

Candidates should be able to:

• describe how a DBMS allows the separation of data from applications and why this is desirable
• describe the principal features of a DBMS and how they can be used to create customised data handling applications.

---

What is a DBMS?

A Database Management System (DBMS) is used to manage a database. It is an integrated suite of data handling software that controls and manages the organisation, storage, maintenance and access of data in a database.

---

How does a DBMS allow separation of data from applications?

The diagram opposite shows how a DBMS sits between the end user and the database file system. This separates the data from the applications that need to access it. A DBMS can therefore present users (and other application programs) with views of the data that are largely independent of the way that the data is physically stored in the database files.

By this means, a DBMS permits applications to be developed and changed as an organisation’s information requirements change. Also, new categories of data can be added to the database without disrupting the operation of existing applications.

By sitting between the user and the database, a DBMS makes it far easier and more efficient to manage and use databases since application programs that need to use the data do not have to include code to directly access the database. Instead, the programs send requests and instructions to the database via the DBMS.

---

What are the principal features of a DBMS?

The facilities offered by different DBMS can vary a great deal, depending on their level of sophistication. A DBMS should however have the following features:

• A set of tools for accessing/maintaining the database such as:
  • Table definition and creation – tables form the basic structure of a database and a DBMS manages their definition, creation and linking.
  • Form creation – forms, based on tables or queries, allow users to easily view, enter or edit data.
  • Query creation – queries allow users users to search the database across multiple tables.
  • Report creation – reports are used to summarise or present data from tables or queries in a form that can easily be printed out. The data can be in a tabular or graphical format.
• Applications are independent from the database:
  • Separating applications from the database means a database administrator (DBA) can set up different applications for different users (i.e. a desktop application, a mobile phone app and and a web application), all accessing the same data.
• Data integrity control:
  • Managing data integrity – data integrity refers to maintaining and assuring the accuracy, validity and consistency of data. In a DBMS this involves:
    • Elimination of data duplication – A DBMS enables applications to share an integrated database containing all the data needed by the applications. This, along with the use of linked tables and foreign fields, eliminates the need for organisations to store duplicate data (data redundancy).
    • Elimination of data inconsistency – because data duplication is eliminated, data inconsistency (different and conflicting versions of the same data in different places) is also eliminated.
    • Applying data validation checks – Data validation ensures that, when data is input or changed by users, the DBMS checks that the inputs/changes are valid (reasonable) and either reports or blocks invalid data.
• Controls access to data, including security and multiple user access:
  • Handling the security of the database – A DBA can use the DBMS to set the access rights and privileges (e.g. read, read-write, etc.) of any user to any data item or area of the database. These authorisation rights are checked by the DBMS whenever users try to access the database. This security reduces corruption of the existing data either accidently or maliciously.
  • Maintaining a log of database access and changes – Many DBMS record details of who accessed the database and when. Most DBMS also record what was changed by that user. This permits the rollback (undoing) of incorrect actions on the database.
  • Managing concurrency – A DBMS can manage concurrent (simultaneous) access to the database by multiple users. Once a particular record is accessed by a user it becomes locked to any other users until the first user has finished with it. This prevents the conflicts that would occur if two users tried to alter a record at the same time.
  • Database back-ups – Many DBMS have facilities for the automatic scheduled back-up of data and data structures. In the event of hardware failures or corruption of the database, a back-up can be used to restore an uncorrupted version of the database.

---

Why is it desirable to separate data from applications?

A prime advantage of modern databases is that the database and applications that use the data can be altered independently of each other.

Traditionally, applications were data dependent. This means that both the way in which the data was organised, and the way in which it was accessed, depended on the application (program) that accessed it. An understanding of how the data was stored and how it needed to be accessed had to be coded into the application.

It is undesirable for applications to be data-dependent for the following reasons:

• Different applications need different views of the same data, e.g. a desktop application and web application accessing the same data. When applications are data-dependent, they need their own slightly different copy of the data. This data duplication leads to wasted storage space and wasted time entering the same information in different places.
• When data is duplicated for different applications, this can also lead to data inconsistency. For example: if an employee’s address is updated in the personnel database file, but not on the payroll database file, this will lead to inconsistencies in the employee’s data. The elimination of data duplication would remove this problem.
• Ideally the person responsible for looking after the database, the database administrator (DBA), must have the freedom to change the physical storage of the database structures in response to changing business requirements without having to modify existing applications. When applications are data-dependent, changes made to the database can require major modifications the applications that access it.

---

How can the features of a DBMS be used to create customised data handling applications?

Because a DBMS sits between users and the actual database files, it is able to present different areas of an organisation with views of the data that are particular to their needs. There are several ways this can be done:

• Applications using different platforms – desktop applications, web applications or mobile phone apps can all access a database via the DBMS.
• Applications limiting access based on the user – users with a low level of access can be limited to read only access or restricted in what data they can view.
• Applications reporting on different aspects of the data – users can be limited to summary data (tables or charts) or allowed to export raw data from the database for further analysis.

Using these facilities, suites of applications can be created for different areas of an organisation. For example, the forms and reports developed for users in the Sales department of a business can differ from those developed for the Marketing department, even though the underlying database is the same.

Some DBMS include facilities for reports to be automatically generated on a regular schedule. For example, the directors of a company may want to see a summary of monthly sales. Rather than have an individual user query the database to produce this report every month, a template report (i.e. a report with links to the data) can be designed once and then, on a monthly basis, automatically populated by the DBMS and emailed to the directors.

Further Readings:

• The DBMS