Validating or Checking Data on Entry

In a database it is important that data or information is entered correctly. A computerised database gas the advantage that automatic checks can be created to stop incorrect data being entered into the database. This is a process that could not easily be done with a manual database.

Unique Identifiers

It is important in a database that any one record can be clearly distinguished from another. Often each record has one field that acts as a unique identifier for a record.

A simple validation check can be used to to ensure that a particular value appears in this field only once, no matter how many records are in the database.

 

 

What you have to do! - Unique Checks

How to make fields Unique! Click to Go Larger Screen

1. Watch Video 7 , open the appropriate database file and
2. Where appropriate, answer the following questions of Exercise 9 in the General Section of your PROGRESS Grids
3. Follow the steps shown in the video and change the validation property of the stock number field in the FOOTBALL KITS database file.
4. Create a new record and type the value 513 in the stock number field (this value is the same as that used to describe the Leeds United Home kit)
5. In the PAINTINGS database why would it be a very bad idea to apply unique validation to check the field painter surname? (Hint: Study the records to find the answer to this question )
6. Make the field catalogue number unique.
7. Add the following record - shown below - to the database. The catalogue number should be one of Y4352, B7661 or D4399. Your database should only allow one of these values to be accepted.
8. Download the MOVIES.fp7 database and place it in your Database folder.
   
   
   
   
9. In the MOVIES database, which field should be set to unique? Change the options of your chosen field to make it unique.

 

Record of Information to Add

Catalogue number	?	Seller Surname	Birch
Title	Dawn	Seller Forename	Jonas
Painter Surname	Helder	Phone no	768490
Painter Forename	Joos	Reserve	3250
Seller Code	C65	Type	Bronze

 

 

Mark off Exercise 9 in your Progress Grids

 

Validating Data: Range Check

A validation check that can be applied to numeric fields is a range check, to ensure that only numbers within a certain range can be entered into a field. Bear in mind that this does not necessarily mean that the data entered will be correct. However it will lie within sensible limits for the values entered into that field.

Think that in the following video, the stock level for the football team kits is:

1. Always greater than zero, and;
2. You cannot have a negative number of kits, otherwise it wouldn't make sense.
3. The owner also wants to restrict the number of kits to 100 .

 

 

 

What you have to do! - Validation: Range Check

Using the information from Video 7, open the appropriate database file and where appropriate, answer the following questions of Exercise 10 in the Credit Section of your PROGRESS Grids

1. Follow the steps shown in the video and change the validation property of the stock level field in the FOOTBALL KITS database file.
   1. Now change the stock level of stock number 221 to 103 (the current correct value is 9). The database should automatically display the message:
   2. Choose No and type the value -7 in the field. Again this should be rejected.
   3. Finally type the value 7 in the field. This value should be accepted. The problem is that the correct value should be 9. However it is impossible for the database to pick up this error because it lies within the range of possible values (i.e. between 0 and 100) that you set earlier.
2. Download the SCOTTISH FOOTBALL TEAMS.fp7 database file and place it in your Database folder.
   
   
   
   
3. In the SCOTTISH FOOTBALL TEAMS database file, several range checks can be applied. Use the table below to set suitable range checks to the matching field names.
4. Several clubs have pitch widths larger than the maximum allowed.
   1. Write down how many clubs this involves.
   2. Change the values of these clubs so that they are set to a maximum value of 75.
5. Set the range of Year allowed in the MOVIE database to between 1900 and 2010.
6. The reserve value in the PAINTINGS database should have a minimum value of £50. There is no real upper limit, so set this value to a very large number (say 99999999)

Set the ranges of the following fields in the SCOTTISH FOOTBALL TEAM database.

 

Field	Range
Founded	1850 to 2003
Ground Capacity	0 to 100,000
Pitch Length	110 to 120
Pitch Width	64 to 75

 

Mark off Exercise 10 in your Progress Grids

 

Validating Data: Restricted Choice

Fields in a database sometimes have a limited amount of different pieces of data that can be entered into them. For example, a person's title is limited to Mr, Mrs, Miss etc.

Automating a database to accept only one of a series of valid choices can stop errors and decrease the time it takes to enter data.

In the FOOTBALL TEAM KITS database file, the category can only be Adult or Junior. The owner would not someone to accidentally enter Child instead of Junior because search and sorts would not work correctly

 

What you have to do! - Restricted Choice Validation

How to add Restricted Choices to a Database! Click to Go Larger Screen

1. Watch Video 8..
2. Open the appropriate database file and where necessary answer the following questions of Exercise 11 in the Credit Section of your PROGRESS Grids;
3. Follow the steps in the video and change the category field to restricted the choice of data to Adult and Junior.
   • In browse mode Find any records whose category field is blank ( Simply type in an = sign) - 3 records should be found.
   • The Arsenal should be set to Junior: the other two should be set to Adult.
4. Use this experience to add Restricted Choice to the fields in the files.:
   1. In the SCOTTISH TEAMS database file change the division field so that only the options SPL, 1st, 2nd or 3rd can be chosen.
   2. Update the contents of this field in each record with the current division that each team competes in.
   3. In the MOVIES database, the Appraisal field should be change to a pop-up list called Appraisal with the values Excellent, Very good, Good and Fair.
   4. Create a pop-up menu (not a list) in the same database for the rating field called rating with the values 12, 15, U, PG and 18.
      1. Note that a 3rd restricted choice setting has been created here for the Category field. This has been set up using check boxes. This allows several different options to be included in one field for a particular record, which would normally be impossible.
      2. Try experimenting with different field format options for the rating field to see the difference it makes to the appearance of the database.
   5. In the PAINTING database file, write down a field name where the format should be set to a restricted choice. Give an explanation for your answer

 

Mark off Exercise 11 in your Progress Grids

 

 

 

What you should be able to do now!

• Explain why each record in a database file has to have a UNIQUE identifier and that values in this field are only entered once.

• Set up a field so that it validates or checks that values entered are unique.

• Explain what a RANGE check is and set up fields to only accepts values that fall between sensible set limits.

• Explain how a RESTRICTED CHOICE helps reduce errors when data is entered and set up a menu of choices and tie this to a field.

 

Continue to Section 6: Practice with Football Teams.