MTA 98-364 Theory: Databases and data capture

A database is a way of storing information in an organised, logical way. It's important to know when to use a database and be aware of its advantages.

Databases and data capture

A database is a way of storing information [information:data with context or meaning ] in an organised, logical way. It's important to know when to use a database and be aware of its advantages.

Record structure

Before setting up a database [database: a structured collection of records or data stored in a computer system ], the record structure must be decided to make best use of the memory[memory: used to store data ] and backing store [backing store: a computer's primary data store, ie the hard disk ], and to make searching and report creation easier.

For example, a car showroom wants to record details of the cars it sells. Before setting up the database, the following questions need to be answered:

1. What information is needed?
2. What validation [validation: checking data against a set of specified rules ] could there be?

With these questions answered, informed decisions can be made about the record structure. This is how it might begin:

Record structure

Field name	Field type	Format
Registration number	Alphanumeric	Up to 7 characters - the key field [key field: a unique identifier for a database record or table entry ]
Make	Alphanumeric	Up to 15 characters
Model	Alphanumeric	Up to 15 characters
Date first registered	Date	DDMMYY
Price	Currency	Up to 5 numbers
Taxed	Yes/No (Boolean)	1 character Y/N
...

When designing a database it is important to choose the correct field type. This ensures that the data [data: information without context, eg a list of students with numbers beside their names is data, when it's made clear that those numbers represent their placing in a 100 metre race, the data becomes information ] stored is usable and it makes validation easier. For example, if the price paid for goods was stored in a text field, then the database wouldn’t be able to add each individual figure to produce a total.

Key fields

A database should always contain a key field.

The key field is a unique identifier for each record.

The following are examples of key fields:

• car registration number
• National Insurance number
• your school's examination centre number
• your own examination candidate number

Storing data in tables

Databases store data in tables, a single database file can store many tables, queries and reports. In the example table below there are six columns (divided vertically) and four rows (divided horizontally), each column has a heading, eg Registration number.

 

Registration number	Make	Model	Date first registered	Price	Taxed
R623 PHM	Ford	Fiesta	010198	6800	Y
P887 LHW	Rover	200	010397	7500	Y
P812 WHJ	Peugeot	406	010996	7000	N

A database in which all the data is stored in a single table is known as a flat file database.

Relational Databases

A relational database [database: a structured collection of records or data stored in a computer system ] has more than one table and the tables are linked using key fields [key field:a unique identifier for a database record or table entry ]. For example, a library database could have three tables:

1. customer - when a customer joins the library a record is created. It stores their details such as their first name and surname and includes a unique Customer ID.
2. book - each book in the library has a record. It stores details about the book, such as the author and title and includes a unique book ID.
3. lending - when a customer borrows a book, the lending table stores the customer's unique ID and the book's unique ID in a record. The record could also include additional information [information: data with context or meaning ] such as when the book was borrowed and when it's due back.

The customer and book ID are both examples of key fields.

Advantages

• The book's details and the customer's details need only be entered into the database once.
• Because of this, mistakes are less likely to happen and if there were a mistake in a customer's record, for example, correcting it will correct the mistake database-wide.
• Duplication is avoided - this keeps the database's file size down.
• Details about books and customers are easily accessible using their unique IDs.
• Queries can be performed and reports generated, eg a list of books a customer has borrowed since joining the library.

Data capture

Before setting up a database [database: a structured collection of records or data stored in a computer system ] the data [data:information without context, eg a list of students with numbers beside their names is data, when it's made clear that those numbers represent their placing in a 100 metre race, the data becomes information ] must be collected. This can be done using a data capture form [data capture form: a method for collecting data ].

A data capture form is designed to collect specific data.

A form completed by a customer buying a car from a showroom is an example of a data capture form.

An example of a data capture form for a car showroom

Data capture forms often use boxes or a set amount of spaces and occasionally provide examples too. This is to make sure each field is completed correctly.

Data is often entered as a code in a database, for example in the above data capture form Y is used for Yes and N for No. Codes like this are used because they:

• are quicker to type in
• use less disk space
• are easy to validate
• make searching the database easier as data is entered in a standard format

Why use a database?

• Databases can store very large numbers of records efficiently (they take up little space).
• It is very quick and easy to find information [information:data with context or meaning ].
• It is easy to add new data [data: information without context, eg a list of students with numbers beside their names is data, when it's made clear that those numbers represent their placing in a 100 metre race, the data becomes information ] and to edit or delete old data.
• Data can be searched easily, eg 'find all Ford cars'.
• Data can be sorted easily, for example into 'date first registered' order.
• Data can be imported into other applications[application: applications serve a specific purpose, eg Microsoft Word is used for word processing ], for example a mail-merge letter to a customer saying that an MOT test is due.
• More than one person can access the same database at the same time - multi-access [multi-access: a system that can be used by several users simultaneously via a local area network (LAN) ].
• Security may be better than in paper files.

Database uses

When a database holds details about people, it's likely to include their first name, surname and their date of birth. In addition to this, specialist information [information: data with context or meaning ] is stored depending on the database's intended use.

• The police have details of all known criminals in a database, eg crimes they've comitted.
• Schools use a database to store details about their pupils, eg how many days they've been off school sick.
• A hospital will store details of all its patients in a database, eg a history of their health issues.
• The Government uses a database to store records of people's income tax payments.
• A database is used to keep track of all the drivers in central London who have (or haven't) paid the Congestion Charge.

In the case of the Congestion Charge database, if someone hasn't paid the congestion charge, a fine will be issued. The database would play an integral part in automating the process.

Information stored in the other databases listed above could be used in similar ways.

AND/OR/NOT queries

Applications [application: applications serve a specific purpose, eg Microsoft Word is used for word processing ] such as Microsoft Access use query [query: finding data in a database which has been specified by the user ] language to narrow down search results. A query can look at more than one field.

 

Registration number	Make	Model	Date first registered	Price	Taxed
R623 PHM	Ford	Fiesta	010198	6800	Y
P887 LHW	Rover	200	010397	7500	Y
X842 PLD	Ford	Mondeo	010196	8100	N
P812 WHJ	Peugeot	406	010996	7000	N
T419 PCP	Citroen	C4	010999	6000	Y

This is a list of common terms used in query language with example usage based on the table above:

1. AND - use to find all records where the make is a FordAND the price is less than 8000. This would return the Fiesta but not the Mondeo.
2. OR - use to find all records where the make is a Ford ORRover. This would only return the Ford Fiesta, the Ford Mondeo and the Rover 200.
3. NOT - use to find all records where the make is NOTCitroen. This would return all records except the Citroen C4.

In query language, the above examples would look similar to this:

1. [Make] = "Ford" AND [Price] < 8000
2. [Make] = "Ford" OR [Make] = "Rover"
3. [Make] = NOT "Citroen"