Information and Communication Technology

Databases

Using a database to enter and organise information

People have been collecting and organising data for a very long time.

Thousands of clay tablets still exist from Mesopotamia, dating back over 5000 years and recording things such as trade transactions and storage of goods.

The problems with clay tablets, and all the subsequent systems based on papyrus sheets, paper, parchment, etc. is that they are bulky and that it takes a relatively long time to find a record once it has been stored.


That all changed when computers arrived. Storage became digital, search times dropped to seconds rather than minutes or hours, and the development of user-friendly software meant that using a database could become an everyday task for anybody with a PC.

This page looks at some of the commoner aspects of using a database.

Entering data

Using a database requires you to collect and enter data

Using a database is regarded by examiners as requiring a higher level of skill than using many other software packages. So, for the IGCSE practical examination, and probably for other practical examinations as well, it is likely that you will be given a database to work with. It will have already been partially completed and the examination questions will ask you to perform a number of tasks with it.

If you are creating your own database however, you will need to be able to collect and enter data.

The commonest way is to type the data in using a keyboard, but nearly all modern databases allow the import of data from external sources as well. The details differ between databases but they will all accept data from:

If you are typing in the data you will usually do it on a form or a table.

A table looks like a spreadsheet table and you can enter data into any cell.

A form is often a customised input screen and may vary in appearance. Usually, forms are created for specific purposes and will only display selected fields.

Data types

Modern databases can store and work with a lot of different types of data.

For the IGCSE, you are required to know about: logical / Boolean, alphanumeric / text, numeric (real and integer), and date types.

Logical / Boolean. This data type may be represented by a single binary digit (a bit), 1 or 0. However, it rarely means the numbers 1 or 0 and should not be used for that purpose unless the 1 and 0 are alternative choices. More commonly it will mean Yes or No, True or False, or some other pair of values that must be one or the other.

Alphanumeric / text. Sometimes called 'string'. This data type holds any character, including letters, numbers and symbols. The amount of characters that can be held may be limited to 255, as this allows the size of the data to be held in a single byte. Some databases allow up to 65,535 charcters, with the size being held in two bytes.

Numeric. Numbers may be integers (whole numbers) or real (numbers with decimal places). There are a lot of variations on number, including fixed decimal, floating point, and small or large integers. Fortunately, for the IGCSE practical examination, you will only have to deal with reals and integers. i.e. those with decimal places and those that are whole numbers.

Date. This also includes time and date-time. Although dates and times look as though they are alphanumeric, they can be converted into numeric values that will allow calculations to be performed. e.g. to find the number of days between two dates, or to add a month to a date.

There are a lot of date and time formats but these are just for presentation. 1st December 2011 will be held as the same number as 01/12/2011 or 01 Dec 11. The database simply presents that number in different formats when you view the date.

If you are using a database outside of the IGCSE specification, you may find other data types useful. Most databases can use data types such as currency, hyperlinks and OLE objects. OLE(object linking and embedding) can include things such as images, sound files, documents, spreadsheet charts and bits of other databases.

Database structure

There are a lot of different types of database, but for IGCSE ICT you only need to know about two of them, flat file and relational. Both of these, and some other types, are based on tables of data which look like spreadsheet tables. In fact, databases are able to use spreadsheet tables as data tables and spreadsheets can be used to build databases. It's really just a matter of how the data is organised and what can be done with it.

Flat file databases are held in a single table of data, while relational databases can have a number of tables linked together.

Most modern databases are relational. e.g. MS Access, LibreOffice Base, but there are several common applications such as address books or diaries which are based on a flat file system.

A data table is organised into rows and columns, like a spreadsheet. Each row holds a single record and each column holds a field.

A record holds a set of related data. e.g. a person's name, address, date of birth, etc. A field holds data for one of those items. e.g. date of birth. When answering examination questions It is important to distinguish between the field name and the field content. e.g. The field named 'date of birth' will hold a date, not the words date of birth. Common mistakes are giving content when asked for a name or a name when asked for the content.

In order to be searchable, and therefore more useful than a spreadsheet table, at least one of the fields must be a key field.

The key field holds the primary key for each record. The primary key must be a unique identifier for that record and many databases create a default autonumber field to ensure that a primary key exists.

It is allowable to have more than one key field for each record, in which case, only one of them needs to hold a unique primary key.

Relational databases often have more than one table. Links (relationships) are formed between tables by sharing fields. e.g. in a database about cars, the vehicle registration field would be the key field in a table holding details of individual cars. The same vehicle registration field could be included in a table holding details of car owners. The field would form the link that allows an owner to be connected to the car that they own.

Validation

There is a well known saying in computing, Garbage in, garbage out. (GIGO). If you want to avoid the garbage out part, you need to ensure that you don't make mistakes when entering data into your database.

One way of helping with this is to use validation.

Validation is the use of software to check that data meets pre-set criteria when it is entered into a database. Note that although it will reject data that does not fit the criteria, it does not ensure that the data is actually correct.

Making sure that data is correct would need the process of verification, where the data input is independently checked against the original data source.

For the IGCSE, you need to know about four validation methods; range check, presence check, type check, length check.

Range check. This ensures that an entry falls between two boundaries. These may be numerical, e.g. between 2 and 8, or alphanumerical. e.g. between abr and hgy. The boundaries may be made inclusive or exclusive. e.g. between 2 and 8 OR between and including 2 and 8.

If only one end of the range is specified, it becomes a limit check instead.


Presence check. This check simply ensures that something is entered in the field before a record can be stored. i.e. an empty field is not allowed if a presence check is enforced.


Type check. This checks what sort of data is allowed. It is usually set up when a field is first created, since most databases require that the field be given a data type to start with. You need to be able to set up fields with logical / Boolean, alphanumeric / text, numeric (real and integer), and date types.


Length check. This checks how many characters a piece of data consists of. As with type checks, this is often set up when a field is first created, but most databases have default field sizes so the length check could be imposed afterwards.

Sorting and searching

Once you have a database with some records in it, you will need to be able to sort and search the data.

Sorting simply means putting the data in order. This might be numerical, date, or alphabetical, depending on the field that you use for the sort process. Data can also be sorted to be ascending or descending in value. i.e. 0 - 9 or 9 - 0.

Data is often sorted automatically using the key field, but you are allowed to use any field or combination of fields that you want.

Sorting using a single field is faster than having multiple fields, but for the size of database that you are likley to use at IGCSE level the difference will not be noticable. If you choose to sort using more than one field you must choose the order in which the sort will occur. e.g. for an address book, you might sort on family name, followed by first name. This would first group people by family and then put them in alphabetical order of first name.

Searching involves finding specific records within the database.

With a simple flat file database such as an address book, this is a simple process and could be done by sorting on the field you are interested in, e.g. name, and then looking down the list to find the correct record.

For relational databases the process is more complex. The data is usually spread over several tables, so just sorting one of them and looking down the list would not find all the information for that record. In addition, for any type of database, as the number of records increase, the task of finding what you want gets more difficult and time consuming.

For the practical examination you need to know how to; search on a single criterion, search with multiple criteria, and search within results.

The database that you are working with at IGCSE level will probably use Structured Query Language (SQL) to run searches.

SQL queries take the general form of SELECT field(s) FROM table WHERE condition.

Fortunately any database that you use for an IGCSE practical examination is likely to have a search / query wizard built in to it. You just need to follow the wizard and the SQL will be written and run by the software. You will of course need to get plenty of practice in using the wizard before taking the examination.

A single criterion search just looks for a single condition in the WHERE part of the SQL. e.g. searching for records where the name field holds the word Smith.

A multiple criteria search involves looking for more than one condition in the WHERE part of the SQL. e.g. searching for records where the name field holds the word Smith and where the age field holds a number greater than 16.

Searching within results is a process where a search produces a set of records which are then narrowed down further by a second search which only applies to that set of records rather than the whole database. In most cases the desired result can be achieved by one multiple criteria search. Where several criteria must be looked at, it is often better to take things one stage at a time as this will help avoid mistakes when setting up the search.

The condition part of a query statement is often just the value or word that you are looking for. e.g. age > 16, name = Smith, Date of Birth < 12/12/ 2000

The <. >, and = symbols are known as relational operators. Other ones are /= (not equal to) <=(less than or equal to) >= (greater than or equal to).

Search conditions may also include logical operators, OR, AND, NOR (neither OR, meaning neither of the two choices), NAND (not AND, meaning not both of the two choices) and several others, You are unlikely to need anything other than AND, OR in a practical examination but no list is given in the specification, so you may come across NOR, NAND.

Although searching may sound a bit complex, you have probably already done something similar when using advanced search features on a search engine.

Database output

Once you have completed your sorting and searching, you will need to output the results. For a practical examination this will usually mean a report or a mail merge.

A report is a document produced by the database, usually laid out in rows and columns. The report can show all of the search results or just a selection from them. The report format can be customised with different fonts, colours, etc. in a similar way to a word processed document.

Reports are usually created by a wizard, you just have to select the layout and what is to be included and then do some customisation if necessary.

In an examination, customisation may be specifically asked for, or it may be something that you need to think about for yourself under the heading of making the report fit for purpose. The default layout is unlikely to get full marks.

When creating a report in an examination, pay careful attention to the question. Common mistakes are:


A mailmerge or form letter may be produced by the database or by a combination of a database and a word processor.

The process is similar for both cases. Which one you use will depend on the facilities offered by the database you are using and the task you are trying to perform.

Mailmerges are usually created by a wizard, you just have to select the layout and what is to be included and then do some customisation if necessary.

Mailmerges are a little more complex than reports as you need to think about the content and writing style of the text that goes into the form letter as well as what fields to include and whereabouts in the letter they need to go.

When creating a mailmerge in an examination, pay careful attention to the question. Common mistakes are:

ContactPrivacyLycett-King.comForumICT indexLearning ObjectivesHome
HomeLearning ObjectivesICT indexForumLycett-King.comPrivacyContact