GoalWrite validation rules in a field list and decide which test records are accepted or rejected.
New words
field list · a table that names each field in a database with its data type and validation ruledata type · the kind of data a field holds, such as text or numbervalidation rule · a rule that data must follow before the database accepts it
Explanation
When a database is designed, each field gets a data type and, where it helps, a validation rule. The designer writes them in a field list. A number field, such as age, can have a range check. A text field, such as a code, can have a length check and a format check. A name field can have a presence check so it is never empty. An ID field that is filled in automatically is the primary key, and it needs no rule from the user. Not every field needs every check. When a user enters a record, each field is checked. If any field breaks a rule, the record is rejected with a message. The user must then correct the data. Rules should match real life: an age of 7 to 16 is sensible for a children’s reading club.
User types a recordgoes to→Each field is checked against its rulepasses all rules, or breaks one→Stored, or rejected with a message
Worked examples
Field
Data type
Validation rule
Reader ID
automatic number
primary key (different for every record)
First name
text
presence check; length at most 20
Age
number
range check: 7 to 16
Code
text
length exactly 5; two capital letters then three digits
Books read
number
range check: 0 to 50
A field list for a reading club database. Every field has a data type. Most have a validation rule. The primary key is filled in automatically.
Record
Data typed (name, age, code, books)
Result
Rule that rejects it
A
Lena, 12, RC204, 14
accepted
none
B
(empty), 12, RC204, 14
rejected
presence check on first name
C
Omar, 6, RC118, 9
rejected
range check on age
D
Mia, 10, RC20, 5
rejected
length check on code
E
Ravi, 16, RC777, 51
rejected
range check on books read
F
Tom, 7, rc204, 0
rejected
format check on code
Checking records against the rules. Each record is checked field by field. The first record passes everything. The others each break one rule, and the database rejects them with a message for that rule.
Pseudocode
1INPUT age
2IF age >= 7 AND age <= 16 THEN
3 OUTPUT "Accepted"4ELSE
5 OUTPUT "Rejected"6ENDIF
Output
Accepted
The age rule as pseudocode. Both conditions must be true, so AND joins them. With 16 typed, the output is Accepted. With 6 or 17 it would be Rejected.
Class activity
Be the database (no computer). Write the field list on a card. A partner invents ten records for the reading club, some correct and some with mistakes, and reads them out. For each record you say accepted or rejected, and name the rule that rejected it. Keep a score sheet. Then swap: you invent records that should be rejected, one for each type of rule (presence, length, format, range). Remember that only invented names and numbers are used. If you have a computer or tablet, ask an adult to help you check one record with the Python age rule.
Practice
1
Which rule would reject each of these? (a) a reader with no first name; (b) age 17; (c) code AB12; (d) books read 60.
Problem
Rule that rejects it
(a) no first name
?
(b) age 17
?
(c) code AB12
?
(d) books read 60
?
(a) presence check; (b) range check (7 to 16); (c) length check (not 5 characters); (d) range check (0 to 50).
2
Is the record (Nodira, 16, RC999, 50) accepted? Check each field.
Yes. The first name is present and short, the age 16 is in the range 7 to 16, the code has 5 characters in the right format, and 50 books is in the range 0 to 50. All the rules are passed.
3
Why does Reader ID have no validation rule that the user must pass?
It is filled in by the database automatically, so every record gets a different number. It is the primary key, and the user never types it.
4
Write the age rule as Python, so that it prints Accepted for ages 7 to 16 and Rejected otherwise.
Model answer (lines are separated by /, and [indent] means four spaces at the start of the line): age = int(input("Age? ")) / if age >= 7 and age <= 16: / [indent] print("Accepted") / else: / [indent] print("Rejected")