• Show understanding of the features provided by a Database Management System (DBMS) that address the issues of a file based approach

  • Including

    • data management, including maintaining a data dictionary

    • data modelling

    • logical schema

    • data integrity

    • data security, including backup procedures and the use of access rights to individuals / groups of users

• Show understanding of how software tools found within a DBMS are used in practice

  • Including the use and purpose of:

    • developer interface

    • query processor

Introduction

Databases can be large and have many users. If lots of different users access the same files on a database at the same time, problems can occur for the operating system. This problem can be overcome by using a 'Database Management System' (DBMS). This is a piece of software, which sits between the actual database of data and the applications that use the data.

The Data Definition Language (DDL)

The Data Definition Language (DDL) is software used by the database designer to create and maintain the database. Using this software, tables of data can be defined, including defining the data, the data types and validation rules required.

It is also used to set-up database security. It can be used to set up each authorised user with a login and password and also to define what parts of the database each user will be able to access. You don't want a company's nurse accessing sensitive financial information on a database just as you don't want the company's accountant having access to everyone's health records! Each authorised user can see different parts of the same database. We say that each person has a 'view' of the database and setting up views for different users is an important part of setting up a database. The other important security feature that is provided is the ability to back-up the database and recover the data in the event of a serious problem.

The Data Manipulation Language (DML)

The DML software is software that is commonly used by the users of a database to access and update data. You can write programs that add, delete, modify or retrieve data, for example, using queries.

Separation of data from applications

We have already seen that the DBMS software sits between the actual database of data and the applications that run it. The data is separated from the applications that use it. (This wasn't always the case. At one time, the data and the programs that used the data were closely intertwined). Separation is desirable because:

• applications can be designed or modified without having to worry about the design of the database.

• the underlying database design can be modified without worrying about the applications that use it.

• different applications can be developed by different companies or individuals that make use of a single database.

The Database Administrator (DBA)

The Database Administrator is the job title given to the person, whose job it is to look after and manage the data in an organisation's database. They make sure that the structure of the data is appropriate to the needs of the users and that everything works as it should. They also make sure that the data is secured properly, by controlling who can access it and making sure it is backed-up properly.

Introduction

The DBMS is a complicated piece of software. It can provide many functions. Underlying it all are schema, that describe the structures of the database and for which all the other components manage/control, etc.

Principal features of a DBMS.

The following list is not exhaustive but will depend on the DBMS being used. I have tried to include the ones that are commonly found in most DBMSs. Do not memorise more than a few of these.

• The DBMS includes software to allow the creation of tables, and links between tables. This includes defining what pieces of information (fields) will be stored, their data types, validation rules and properties. This information is stored in a 'data dictionary', which is just a place to store metadata (data about data).

• The DBMS stores information about primary keys (the unique field in each database), foreign keys (which fields will be used to link tables together) and secondary keys (which fields will be used to set up extra search indexes, to speed up searching).

• The DBMS includes software which ensure that the data integrity is maintained. If a record in Table 1 refers to a record in Table 2, you don't want to be able to delete the record in Table 2. If you do, the record in Table 1 will then be referring to a record that doesn't exist. The data integrity of the system will not be maintained. What should happen to maintain data integrity is that the record in Table 1 is deleted (or amended) and then the record in Table 2 is deleted.

• The DBMS includes software to allow the setting up of users' accounts. This includes giving each user a login and password, defining user groups and putting users in appropriate groups.

• The DBMS includes software for setting up the access rights and views for each group of users that has been defined.

• The DBMS includes software to automatically back-up the database.

• The DBMS includes software to create data input forms. These can be used to enter in records and to view records in the database. Wizards can be used to set-up the forms and then you can switch into a design view to customise them.

• The DBMS includes software that allows queries to be designed and run. This allows data to be interrogated.

• Reports can be set-up. This allows the results of queries to be formatted 'professionally' with a title, a data, meaningful headings, column widths set-up correctly, justification of results properly formatted, the addition of logos and so on.

• The DBMS includes software that allows macros to be designed and run, so that many often-repeated jobs can be automated.

• The DBMS includes software that allows Switchboards and navigation methods to be set up. Large databases may have many forms and reports and after a while, it might get too complicated to find the form, query or report you are looking for. A switchboard is simply a welcome screen that you see when you open a database. It has a few buttons on, which take you to other menus and sub-menus. The idea is that you group on one menu similar jobs and provide links which open the forms and reports that carry out these jobs.

• The DBMS includes software that allow system maintenance. For example, databases can 'grow' as temporary files are used and the data can get defragmented over time. Tools that deal with these kinds of problems are often provided.

Introduction

A database can become very complicated very quickly. With many tables, many pieces of information, many queries and many people wanting to access the data, it becomes necessary to have a way of managing the database and everything to do with it. We call the software that manages a database the Database Management System, or DBMS.

Access Rights, Views and the role of the DBMS.

Consider a relational database in a company. It is one database of related tables that serves the entire business. However, you do not want everyone having access to all the data in the system at all times from all computers. It would be a breach of the Data Protection Act for one thing! You would not want your personal details available to anyone who felt like looking them up for another. In addition, Information in companies, especially financial information, is valuable and therefore needs to be restricted. You want to be able to limit who can access what, at what times and from where. For example:

• The receptionist should be able to see who works for the company and his or her telephone extension but not personal details of each employee.

• The personnel manager should be able to view, add, delete or amend an employee's details, but not be able to read the sales figures for a company.

• A salesman should be able to view all orders, create new ones and view his own sales figures, but not view other people's sales figures, or the total sales figures.

• The senior management should have the right to view any salesman's sales figures.

The role of the DBMS

Restricting access to data is done through the DBMS (See DDL in the next section). This is a piece of software that, amongst other things, provides an interface for the database administrator to allocate 'views' and 'rights' to individuals or groups of individuals. There are a number of things an administrator could do to a user or group of users using the DBMS.

• A user or group could only be allowed to 'view' certain records but not others.

• A user or group could be allowed to both view certain records and modify them but not others.

• A user could be allowed to delete certain kinds of records but not others.

• A user could be allowed to execute certain programs but not others.

Setting up groups and making an individual account holder a member of groups

Typically, a Database Administrator will define a set of groups, each with their own rights. For example, if the organisation were a school, then there might be a group called Students, another called Teachers, another called Finance Department, another called Senior Teachers, another called Special Needs Department and so on. Each of these groups will be set up so that they are able to access only those parts of the data that they need to do their job and nothing else. When an account for a new user is created, they will be given a user ID and a password but they will also be added to one or more of the groups that have been set up. The result of this is that the individual account holder inherits the view of data and any rights to access, view, delete, amend or add to that data that the groups they are now members of have been given.

When a user correctly logs on using the User ID and Password, their rights and views are accessed by the DBMS. They will only be able to get to the views of data that they have been allocated with and will only be able to use the rights they have been given.

Security

It is possible for individual files to have extra security, for example by being password-protected. The user will then have to correctly enter a password to access those files. This is useful to prevent casual access to a computer that has been left unattended but not correctly logged out. As an additional security method, users can be logged out automatically after a period of inactivity.

Backing-up data

Databases and the data they hold are very valuable. They take a long time to design and the data in them may have been collected over many years. It is important therefore to protect the data. This is done by backing it up regularly and keeping the back-ups safe. Typically, full back-ups of the whole system and data are done periodically, perhaps once a week, and incremental back-ups are done at least once a day and often many times a day. An incremental back-up is one where the only files that are backed up are the ones that have been added or changed since the last back-up was done.

The DBMS can be used to automatically schedule when the back-ups are done in accordance with an organisation's written back-up policy. Back-ups are typically done to a magnetic tape device, although backing-up off-site to cloud storage is becoming increasingly more common. The only job left for the Database Administrator is to check periodically that the back-ups are being correctly done. They should, for example, restore a complete back-up on a separate computer system once a month, to check that the back-up system is correctly working.

Back-up policy

All data is at risk at all times. There are many reasons why this is so. A hacker or disgruntled employee may delete data, there could be a fire, data might get corrupted by software or an upgrade, a piece of equipment like a hard drive might fail, there might be an earthquake or other natural disaster, someone might steal a computer with data on and so on. To ensure that data can be recovered, it is backed-up regularly. However, a policy is written down to make it crystal clear;

• whose job it is to do the back-ups

• how often the back-ups will be made and when it will be done

• how each back-up is to be made

• how each back-up will be labelled

• where back-ups will be stored

• how the back-up system will be tested

Back-ups are usually done on a rolling basis. For example, in the first week of a four week back-up plan, an 'incremental back-up' might be made every night on a back-up tape called Monday1, Tuesday1, Wednesday1, and Thursday1. An incremental back-up resaves only those files that have changed since the last time a back-up was made. On the Friday, a 'full back-up' is made of the whole system' data on a tape called Friday1.

Then the above is repeated for the next week on tapes called Monday2, Tuesday2, Wednesday2, Thursday2 and Friday2, so you now have 10 tapes). The same is done for week three and week four (so you have 20 tapes now). Then at the start of the new four week period, the tapes are over-written and the whole process restarts.

The above policy might be fine for a small organisation. For larger ones, you would probably keep back-ups going back many months or even years. The reason why so many back-ups are made is that it might actually take a few weeks or months to detect that there was a problem with the back-up system. If you used just one tape and backed up a computer every day on that one tape, it would be a real problem if you discovered, for example, that the back-up device hadn't been working correctly for a few weeks or a back-up tape was faulty.

Locking

Databases occasionally need to be 'locked'. This is important when multiple users potentially have access to the same file at the same time. For example, if a file is being updated by User A, you don't want User B accessing the file until User A has finished with it. Reserving a file is known as 'locking' and ensures that the integrity of the file contents is not compromised. An example of a file needing to be locked is in a real time booking system. You wouldn’t want two different booking agents who book cinema tickets booking the same seat at the same time! This will lead to overbooking. One of them must have the right to modify the file and to book tickets. The other agent should have the right to look at the current seat availability, but not the right to book seats, until the file has been updated and released as a result of any bookings taken by the first booking agent.

More on DBMS, Languages & Query Processor

If you have used Access, or any other main relational database software, then you have used a DBMS. In Access, the kinds of things you will have done are varied. You may have used the special window to design tables, or you may have designed them using SQL. You might have entered data directly into the tables in ‘data sheet view’. You could have spent some time designing forms and entered the data in that way. You may have used QBE to design a search for information or again used SQL. Whichever way you used, you probably presented the findings using a report. All of these functions are provided by the DBMS.

In general, a DBMS is a piece of software that lets you create, manipulate and maintain a database. It lets you:

• Set up tables of data about data (technically called 'meta-data'. You may also know it as the 'data dictionary'.)

• Create forms to enter data.

• Create accounts and rights for users and groups of users.

• Do queries on data.

• Present data in various report formats.

• Provide a programming language to let you do extra-clever things.

• Provide you with the ability to set up different 'views' of data.

Above all, the DBMS provides users with a ‘conceptual view of data’. In other words, it provides the user with a user interface that they can understand and relate to, for example, a picture of tables of data related to each other (an E-R diagram) or a table of data showing the name of each piece of data, their data type, the validation rules and so on. Data is not stored like this on disk! In fact, the data might be stored in indexed files, linked lists or sequential files, for example. This is the ‘physical view of the data’, which humans cannot understand. To make use of the data on disk they need it to be interpreted and shown in a form they can understand. This is what the DBMS software does. It provides a user interface (users also being the database developers).

The scripting language (most commonly SQL) is split into two main parts: DDL and the DML. Technically, querying is a separate element, but often grouped with DML. Much more on these can be found in section 8.3

It is the job of the query processor to transform the user queries into a series of low level instructions (which acts like an interpreter).

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 fulfil the request. The means to fulfil 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 optimiser. Here the statement is analysed 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 optimiser 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.

You can find out more about these processes here.

Data Definition Language (DDL)

The Data Definition Language (DDL) is one part of the DBMS. It is used by the designer of a database rather than a user. The DDL is used to set up the structure of a database as well as for maintaining the database. For example, using the DDL, they can set up tables of data about data (names of attributes, data types, validation rules etc). The data about data is often called ‘meta-data’. The DDL is also used to set up the security systems in the database. This is done using the DDL by describing the whole database (the ‘schema’) and then describing data in parts of the database (the ‘sub-schema’). Once this has been done, the designer can control access to each of the sub-schema. For example, if a receptionist needs to know each employee’s department, extension and job title, then a sub-schema will be defined with these data items in it. The receptionist is then given access to that sub-schema (she is allowed to view that data on her screen) but not other sub-schema. That means she cannot, for example, access how much an employee earns, or the contents of their personnel file. Using the DDL, then, different users of a database can be allowed to access certain sub-schema but not others. Users can be given different ‘views’ of the same data in a database.

Data Manipulation Language (DML)

The DML is another important part of the DBMS. It is commonly used by the users of a database to access and update data. This language allows users to actually work on the data in your database. They could, for example, write programs that add, delete, modify or retrieve data! The DML is a very high level language. This ensures that minimal programming skills are necessary to actually use it. You will see and get some experience of a DML when you use SQL.

Introduction

Databases must be designed. DBMS's often have software that allow you to design the database structure before it is implemented.

Data modelling

Data modelling is a 'high level' implementation design of the database. This is achieved using techniques such as Entity-Relationship Diagrams and Jackson Structured Programming (JSP) diagrams. When we say 'high level' what we mean is that it gives the big picture without the detail. So for example, E-R diagrams would show you what tables you need and the relationships between them, but not the data that was in each table or information about queries, reports, forms and so on. A typical E-R diagram looks like this:

Once you have the high level E-R diagram, you can use it to start designing each individual table in the database model (see further below).

Schema

Schema describe the underlying structures. More formally, the database schema of a database is its structure described in a formal language supported by the database management system (DBMS).

Logical schema is an actual detailed specification and design of your database. Again, this is part of the software tools in a DBMS. Using the DBMS tools, you can set up:

• what tables you need to have in your database;

• how the tables are related to each other and

• what queries you might need to carry out, etc.

Physical schema describe how the data will be stored on a physical drive (e.g. several files, etc.)

Conceptual schema describe how the data is seen and accessed by any applications making use of the DBMS. The conceptual schema describes the database structure of the whole database for the community of users. This schema hides information about the physical storage structures and focuses on describing data types, entities, relationships, etc. This logical level comes between the user level and physical storage view.

Using the software tools in a DBMS

Your teacher should ask you to design and set-up a simple database so that you can get experience using the developer tools. You will probably use Access to do this. You should typically follow the following steps:

• use the developer interface to specify what tables you want

• define the relationships between the tables

• set-up referential integrity between tables

• design the data dictionary, including specifying the data in each table, the data types and the validation rules

• design the forms for entering data

• add test data to each table

• design the queries you need and test them using the query tools

• design the reports that you need using the report tools

• create any automated routines you need e.g. the autoexec macro when the database opens

• design a switchboard.

There are plenty of YouTube tutorials to help you get experience of using these tools. Try typing in 'Access database beginner' into YouTube's search engine.

There are not a great many useful videos on DBMS, as many go into the basics of databases, rather than focus specifically on the software running these.