5.2.3
Data entry - validation and verification
| Revision points: |
|
Database designers want to help the user of the database make as few mistakes as possible when they enter data into the database. There are two methods you need to learn for the examination and one method that you should use in your GCSE 1b coursework.
VALIDATION:
This type of data entry check does NOT check that the data entered is actually correct. It only checks that it is VALID data, in other words it COULD be correct.
There are several different types of validation checks which you can use in your 1b coursework database project.
Range checks:
These are used with numerical fields to limit the range of numbers a user can enter. For example:
- Imagine if you were making a database for a user to store details of yachts that are sold in a second-hand boatyard and you know that they never sell anything longer than 20 metres and less than 13 metres.
- It would make sense to make a range-check validation rule for the 'LENGTH' field so that if an entry was made that was outside this range then a warning message would pop up and the entry would not be accepted.
- A typical range-check validation rule might be: BETWEEN 13 AND 20
- This could also be entered as: >=13 AND <=20
Text entry checks:
These are used with alphanumeric (text) fields to limit the type of entry that can be made (remember, a 'text' field can accept text or numbers but will always sort alphabetically). For example:
- Imagine if you were making a database for a user to store details of yachts that are sold in a second-hand boatyard and you know that masts are only ever made out of wood, aluminium or carbon-fibre.
- It would make sense therefore to make a text-entry validation rule for the 'MAST_MATERIAL' field so that only these three values could be entered and they will always be spelt correctly.
- A typical text entry validation rule might be: "WOOD" OR "ALUMUNIUM" OR "CARBON FIBRE"
| Typical validation rules | |||
| Rule | What is being checked | Valid data | Invalid data |
| >20 | If a numerical entry is greater than 20 | 21 | 20 |
| >=20 | If a numerical entry is greater than OR equal to 20 | 20 | 19 |
| BETWEEN 20 AND 30 | If a numerical entry is greater than OR equal to 20 AND less than OR equal to 30 | 25 | 31 |
| >=20 AND <=30 | If a numerical entry greater than OR equal to 20 AND less than OR equal to 30 | ||
| "RED" OR "BLUE" | If a text entry is RED or BLUE | BLUE | LIGHT BLUE |
VERIFICATION:
This
type of data entry check is used to try and check if the
data is actually correct.
The simplest verification check is where data is entered twice, possibly by two different operators. The two entries are then compared against each other and a warning given if there is no match.
Data entries can also be verified against data that is known to be correct. For example if you log onto the KLB School network then your username is verified as being allowed to access the network then your password entry is verified by comparing it with the one given to you when your account was created.