Methods of capturing, selecting, managing and exchanging data
Introduction
This section is about capturing data that you want to enter into your database, selecting data that's in it and exchanging data with other systems.
Capturing data
The most common way to capture data to put into a database is to use a data capture form. If you were designing a student database, you could design a data capture form for students and print off or photocopy lots of copies. Each student you wanted to get into your database would then fill in a paper-based form and hand it to you. You would collect them together and then transfer the data from the paper-based data capture forms into the computerised database using a data input form in the database by sitting in front of the computer and typing the information in.
Don't get mixed up by the data capture form and the data input form. The data capture form is the paper-based form filled out using a pen or pencil. The data input form is the form you open up on the computer, which is part of the database, and you type in the information into the database from each data capture form using this data input form.
To make life easier and reduce the likelihood of errors, the data capture form is often designed in a very similar way to the data input form on the computer. As your eye moves from the data capture form to the data input form, it moves to and from the same place.
Selecting data
Data can be selected in a database using Structured Query Language (SQL). This is looked at in other articles in this section.
Managing data
There are a number of roles associated with managing a database.
The Database Administrator (DBA) is sometimes also known as the Database Manager. Apart from installation and set-up, they are responsible for upgrading, monitoring, maintenance, security and general administration of an organisation's database. The following is not an exhaustive list of all the typical responsibilities a DBA has, but does give an idea of what they do.
-
- Database software needs to be upgraded over time, for example, to protect it from any bugs discovered, to patch it against any security flaws, to ensure that it still works with other systems that have also been upgraded, to ensure it complies with any recent changes in legislation and to ensure that it can work with any new hardware and software.
- Databases use a lot of storage. The DBA is responsible for monitoring the storage, planning and implementing future increases and deleting records that will never be needed again. They are also responsible for archiving other records which aren't needed again at the moment but might need to be referred to in the future e.g. financial records by the tax authorities, test data for aviation parts by investigators looking into why a plane crashed.
- The DBA will have to give rights to users to access the database. This means creating logins and passwords for new users, setting up which parts of a database a user can see (called 'views of data') and what they can do with the parts of the database that they can see e.g. should they be allowed to add data, delete data amend data and so on? DBAs also have to delete users who no longer need access e.g. because they have left the company.
- The DBA needs to monitor the performance of the database and take any appropriate action e.g. if it becomes too slow.
- DBAs are responsible for ensuring that a proper back-up procedure has been written to cover all eventualities and is being followed, including testing back-ups periodically. They have to ensure that the data is safe, regardless of the event e.g. a fire, a malicious act by an employee or a hacker, a failure of equipment, a natural disaster and so on.
- DBAs have to ensure compliance with any licence agreements.
- DBAs should be able to generate reports from the data as required e.g. about usage, access and so on.
In addition to the above, a company with a database usually needs a Data Controller. Their job is to ensure that the company follows all the rules as laid down by the UK's Data Protection Act 1998 and the Data Commissioner, and if they are a public company, to ensure that the public has access to information as allowed under the Freedom of Information Act 2000.
Exchanging data
Copy and paste
Data can be exchanged between different systems in a number of ways. It might be possible to simply copy and paste data between applications. For example, if you wanted to use some data in Excel that was held in an Access database so that you could make use of Excel's well developed data analysis and charting features, then you could do this by simply copying the data in Access's datasheet view and then pasting it into an Excel worksheet.
Creating a connection
If you are using two applications in a package, and again, Access and Excel are the most common examples, you can make a connection between the two. Suppose you store data in an Access database but you use Excel to analyse the data, and you have to analyse the data frequently. Rather than copy and paste the data, or exporting the data, you can connect the packages so that selected data is automatically pulled from Access when it is needed in Excel. This can be achieved in Office by creating an Office Data Connection file, or .odc file. The main advantage of this approach is that you can easily refresh the Excel analysis should the data in the Access file change.
Importing and exporting data
If you wish to move data in tables from one application to another, you can simply export the data from the first application and import it into the second one using a format that both use. A common format available in most applications is the CSV file, or Comma-Separated Variable file. This is a standard file format that takes some tabular data that you want to export and puts each row in the table on a single line in a simple text file, and separates each piece of data on each line with a comma. This can then be easily imported into a database. There are often wizards that make the whole process very easy to do. CSV files are not the only format you can use but it is one of the most widely supported ones.