Candidates should be able to:
- describe a database as a persistent organised store of data
- explain the use of data handling software to create, maintain and interrogate a database.
What is a database?
A database is a persistent, organised store of related data.
- A database is persistent because the data and structures are stored in secondary storage, even when the applications that use the data are no longer running.
- A database is organised because the data is stored in a very structured way, using tables, records and fields so that users and data handling applications can easily add, delete, edit, search and manipulate the data.
- A database is made up of related data because the individual items of data have a connection of some sort.
An address book, an encyclopaedia and a telephone directory are examples of paper-based manual databases. However, it is more common to talk about computerised databases. Computerised databases have several advantages over manual databases. These include:
- the ability for the data to be accessed by more than one person at the same time
- the ability to interrogate or query the data and view the resulting answers
- the ability for changes to the data to be made quickly available to all end users
- the reduction of errors in repetitive tasks due to the processing accuracy of data handling software
- the output of data in a range of different formats to suit user needs (e.g. graphs, reports, forms, etc.), either for viewing on screen or as print-outs
A computerised database is a collection of related data stored in one or more computerised files in a manner that can be accessed by users or computer programs.
Most computerised databases are operational databases, meaning that data going into the database is used in real time to support the ongoing activities of a business. A supermarket accounting system is an example: as items are sold, the inventory database is updated and the inventory information is made available to the sales staff.
Computers have the ability to store large amounts of data in a compact space and to process it speedily. Organisations of all sizes use databases to store, sort, interrogate and manage their data. Below are a few examples:
Hospital databases maintain details of patients, doctors and treatments.
The databases manage and co-ordinate admissions, consultations, treatments, staffing and stock control. Business use databases to keep track of sales, stock and staff, etc. and to analyse their own performance
Businesses use databases to keep track of sales, stock and staff etc. and to analyse their own performance.
Databases also help businesses to monitor trends in customers’ purchases. This helps businesses identify market opportunities.
Internet Search engines, such as Google, Bing, Yahoo, etc. all have powerful databases behind the scenes to collect the details of websites that are used in searches.
What is data handling software?
Any software designed to create, maintain and interrogate computerised databases is termed data handling software. Data handling software can therefore range from a simple program that creates and maintains a specific comma-delimited flat-file database through to sophisticated relational database management systems that can be used to create and manage a huge variety of database structures.
How is data management software used to create a database?
Database creation involves using software to define and build the structures to hold the data. In a database file the data is structured in a particular way.
- A single item of data is stored in a named FIELD
- A complete set of fields makes up a RECORD, the KEY FIELD contains data unique to that record
- All the records on one ENTITY are stored in a TABLE
- One or more tables then make up the database FILE
Database creation involves the following steps:
- Each field would be created, selecting a data type to match the data to be stored.
- An existing field is set as the key field or a field is created for this purpose.
- Once the complete set of fields have been created and any validation rules added, they are saved as a table.
- Data is then entered into the database fields, each complete set of fields forming a single record with a unique entry in the KEY FIELD.
For example, in a database of students;
- A TABLE would store all the data on all the students
- An individual RECORD would store the data on a single student
- Several FIELDS would store the data (attributes) of the student such as Student ID, Forename, Surname etc.
- A KEY FIELD such as ‘StudentID’ can store a unique number to identify that student.
This database FILE would contain just one table and is known as a flat-file database. There are a number of limitations to such databases and a relational database which contains multiple linked tables offers many advantages.
How is data management software used to maintain a database?
Database maintenance involves the following:
- adding (also referred to as inserting) new data records (for example, when a new member of staff joins a company or a new product is added to the stock in a warehouse).
- deleting existing data records (for example, when a member of staff leaves a company or a product discontinued from the stock in a warehouse).
- updating (editing) existing data items within existing data records (for example, when a member of staff changes their name or a product has a price change).
How is the data in a data files actually stored?
The data in a database can be physically stored in different ways, each offering particular advantages and disadvantages.
Serial data files
In a serial data file each record is stored in series, one after the other and there are no particular order to the records.
In this type of file structure the computer has to read through the data record-by-record until it finds the record that is needs to access. This makes accessing data from a serial file relatively slow.
If a record is deleted or edited then the complete altered file is re-written back to the storage medium which is relatively slow and may involve writing to a temporary file until the process is completed. The original file is then replaced by the altered temporary file.
Sequential data files
In a sequential data file the data the records are still in series but they are stored in order, using one of the records in the database. This makes if much easier to locate a particular record using an algorithm such as a binary sort.
An alternative an indexed sequential file. Here the position of each record is stored in an index which is a separate sub-file. This allows the computer to quickly access any record by looking it up in the index first and then going directly to the correct location.
- Serial data files are slow to access particular records within the file.
- Sequential data files allow faster access to particular records, either using the fact that the data is sorted or indexed.
How does data management software interrogate a database?
Database interrogation involves using the database management software to query (search) the database for information.
There are many reasons why users may wish to query data, including:
- To identify a group of records that share a certain attribute – e.g. a list of students with nut allergies, products from a particular supplier, etc.
- To calculate totals based on the information held in records – e.g. calculating the total value of the assets held by a company.
- To update the details of a specific record or group of records
The above list shows that queries are a means of producing information from data. This information is used by the decision makers in organisations to plan strategies and tactics. Databases usually allow users to create, save and then reuse queries.
A query design specifies which records the user is searching for and what fields to display out of those records. There are two types of query:
- Simple query – looking for data in one field only (for example – for example a user of a car showroom database could run a simple query to find out how may Toyota cars there are in stock).
- Complex query – looking for data in multiple fields (for example a user of a car showroom database could run a complex query to find all hatchback or saloon Toyota cars registered between August 2006 and July 2010 but not with 5 doors).
- Further information on queries.