Database Management

Back to Topic 3

We have seen how databases are complex things and need to be supervised with a great deal of care by somebody who is familiar with the way databases work.  That job falls to a department called database administration which in turn is managed by the database administrator (DBA).  The key tasks for a DBA are:

• Design of the database.
• Maintenance of the database, including investigation of problems, for example, if a report takes a long time to generate;
• Altering the database structure if needed;
• Keeping users informed of changes to the database structure if that affects them, for example, if a format of a field is changed.
• Maintaining the data dictionary;
• Implementing access rights for different levels of user;
• Allocating passwords;
• Security of information on the database;
• Ensuring that the database is backed up.

The database dictionary is the database about the database.  It contains:

• the tables and columns that form the database structure;
• the field names;
• characteristics of fields, such as data type and length;
• restrictions on the value of fields,  For example the monthly pay would have a maximum value of, say, £10 000.  An attempt to put £100 000 in would lead to an error message ("This company does NOT employ premiership footballers").
• Meanings of field names that might not be immediately obvious, for example "through by light under dwelling" (this might appear on an estate- agent's database in some parts of Yorkshire.  "Through by light" means that the house doesn't have a back door, but has a back window);
• Relationships between tables;
• Which programs can access the data;
• Which programs can read the data on a read only basis;
• Which programs can alter the data.

The database management system carries out these functions:

• Data storage, retrieval, and update;
• Creation and maintenance of the data dictionary;
• Management of the sharing of the database by applications;
• Back-up and recovery in case of failure;
• Security.

Queries

Databases use queries to extract information from the database:

• Produce a table from the data from a number of different tables;
• Select the fields that appear in the answer tables;
• Specify the criteria for searching;
• Saving the query so that it can be run whenever wanted;
• Use the answer table as the basis of the report.

Queries can be of several different types:

• Simple select queries in which data is displayed according to certain criteria, for example, customers whose accounts have not been paid at the end of last month;
• Action queries which process the data in some way, for example updating the accounts that have been paid;
• Queries that delete data from the database.

The picture above shows how a query can be assembled and the screenshot below shows the table produced when the query is run.

Whichever way a query is put together, the query is translated into statements of a special database called Structured Query Language (SQL).  This is a database manipulation language that was originally devised for old mainframe computers, but many PC can be configured to run SQL.

Client Server Database

Most databases are run on networks, with the server running DBMS server software, and the terminals running DBMS client software.  A car dealer may well have a terminal that can query the manufacturer's central database.  It would be pointless for the whole central database to be downloaded onto the dealer's machine; the transmission would take a long time, then the dealer's machine would have to do the search. 

The dealer's machine acts as the client, while the database is held on a server, hence client server database.  (In a non-client server system, the whole database would have to be on that machine.)

The advantages of client server databases are:

• Expensive resources on a central server can be made available to a large number of users;

• client workstations can update the data, rather than having to send instructions to the database administration department to update the database;

• data are kept consistent on the central server, rather than there being several versions on different machines;

• database processing is done only on the server.  The results are assembled by the server;

• communication time between client and server are reduced as only the query and the results are transmitted;

• relevant programs and report formats are held on the work stations for different departments.

Question 3  The ICT manager for a large organisation is tasked with writing a job-specification for a database manager.

(a) Describe three key tasks that would be included.

(b) The database is a client-server database.  Describe two advantages of this type of database structure.  (AQA past question) ANSWER

Now try the Topic Quiz