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 [databasea structured collection of records or data stored in a computer system ], the record structure must be decided to make best use of the memory[memoryused to store data ] and backing store [backing storea 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 [validationchecking 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 fielda 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 [datainformation 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 [databasea 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 [informationdata 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 [databasea 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 forma 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.
Includes fields for registration number, make, model, date first registered (with boxes marked DD, MM, YY for date, month and year), price in pounds (£), and if it was taxed (options are Y for yes and N for no)

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 [datainformation 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[applicationapplications 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-accessa 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 [informationdata 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 [applicationapplications serve a specific purpose, eg Microsoft Word is used for word processing ] such as Microsoft Access use query [queryfinding 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”
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s