The database management system (DBMS) is a software application that sits between the data in the database and the applications that an organisation uses to access that data. Its key roles are:
Database integrity ensures that data entered into the database is accurate, valid, and consistent. Any applicable integrity constraints and data validation rules must be satisfied before permitting a change to the database. Integrity can be compromised if unintended changes take place by accident or by malicious individuals.
Three basic types of database integrity constraints are:
Some changes to a database might not make sense. For example, if you delete songs from your music library, but they are still referred to in a playlist... the playlist will not be able to locate the songs to play them. Or, in a school, there might be information about which pupils are taking which exams. If someone deleted an exam subject and there were pupils entered for it, this would make it impossible to do the entry processing. DBMSs allow the database designer to enforce referential integrity which prevents inconsistent changes from being carried out.
There is a wide range of security controls employed to protect databases against compromises of their confidentiality, integrity and availability. Security risks to database systems include:
In order to ensure data integrity, only one copy of the data is held in a database. It is necessary, therefore, for the DBMS to control access to specified data within the database at any given time. Multiple, different applications must be able to access the same database simultaneously, but not edit the same data at the same time (whilst one user/application edits a record, other users/applications will be restricted to read-only access to that record.)
The DBMS is also responsible for maintaining the different external views of the database available to different users (see notes from Unit 5.1 – Database Concept). Different people need different subsets to work on. They must all, however, be able to see the one and only up-to-date copy of the data, so that there are no inconsistencies. The DBMS must:
There is a set of rules that databases should always be subjected to so that inconsistent changes are not made. These are known as the ACID rules. They refer to transactions (actions that bring about a change to the data).
Atomic – a change is either completely performed or not at all. It must not be possible to abandon a transaction and save it in a half completed state. For example, in a bank, if one account is debited, another must be credited or the money just disappears.
Consistent – a transaction must take a database from one consistent state to another. Again, in a banking situation, the total amount of debits in the complete system must equal the total credits.
Isolated – a transaction should not be visible to any other transaction or user until it is irrevocably committed. Imagine if several theatre booking offices had access to a particular seat in a particular theatre on a particular day, all at the same time: double bookings could easily occur. It is necessary to lock a record while a transaction is taking place and not release it until it has been committed.
Durable – once a change has been made, it must not be lost due to any subsequent failure
DBMSs allow the creation of applications. The DBMS makes sure that any applications related are not able to damage the underlying data. Applications can be created by the language provided or often by using a visual interface. This allows the designer to create objects such as:
It is often possible to create the database objects using a wizard. This asks questions about what is required and then constructs the component automatically.
These are the essential data stores of the database. The fields can be created manually or a wizard can suggest fields for you. It is important to make the right decisions about which data goes into which table. Also, data types have to be chosen so that they can help in the validation of data input and ensure the database performs in the way intended.
These are the interface between the user and the underlying application. They can accept data as well as display it. They can have controls added, such as buttons and combo boxes, in order to provide interaction and validation capability.
These are used to extract a subset of the data. They can be applied to more than one table in order to provide the user with exactly the right data to work on. Queries can be constructed visually as in the screenshot on the next page. The tables and fields are inserted where required. The conditions can be added by filling in boxes. Alternatively, SQL can be used to construct a query (see notes from – Database Concepts).
These are printed lists of selected data from a database. They can be set to present data in all sorts of ways. They can be based on tables or queries in order to select exactly the data that is needed.
These are units of program code. Most DBMSs have a programming language built in so that a developer can have total control over the performance of the applications. For example, Microsoft® Access has the language VBA (Visual Basic® for Applications) built into it. Here is a short example of VBA code.