ACC564; Excel Problem 8.3
NAME
22-Feb-11

Member # "Flight Date
DDMMYY" Plane Used Takeoff Time Landing Time
1234 311109 G 0625 0846
4111 011109 C 0849 1023
1210 011109 P 0342 0542
0023 011109 X 0159 1243
012A 011109 P 1229 1532
0999 011109 L 1551 1387
*G = Glider; C = Cessna; P = Piper Cub; L = Lear Jet

a. The following describes one or more input edit controls that could be included in the program for detecting possible errors for each of the five data fields:
1) Member #: The facts state that the Member numbers are numbered sequentially and that there are less than 2000 members total.
a) Field check - to determine that the proper type of character is entered in the field. In this case, only numeric characters allowed.
b) Limit check - to determine that the numeric amount entered does not exceed a predetermined numeric value, e.g. 2000 in this case.
c) Range check - to determine that upper and lower limits are valid, otherwise similar to the limit check, e.g. 0001 - 2399 in this case.
d) Size check - to ensure that input data will fit into the assigned field, e.g. no more than 4 characters in this case. 2) Flight date: The facts state that the format is DDMMYY
a) Field check - to determine that the proper type of character is entered in the field. In this case, only numeric characters allowed.
b) Size check - to ensure that input data will fit into the assigned field, e.g. no more than 6 characters in this case.
c) Check digit verification - There are only so many valid combinations of days and months in the year, for example, November has 30 days, so a day higher than 30 would be invalid 3) Plane used: The facts show that there are only 4 planes presently used, identified by the, at this time, unique first letter of the type of plane.
a) Field check - to determine that the proper type of character is entered in the field. In this case, only alphanumeric characters are valid.
b) Size check - to ensure that input data will fit into the assigned field, e.g. no more than 1 character in this case. 4) Takeoff Time: The facts state that the planes are rented by the hour and all must be returned the same day.
Per review of the input, it appears that the time is tracked by military time, which uses a 24 hour clock beginning at 0000 at midnight to 2399 at 11:59pm.
a) Field check - to determine that the proper type of character is entered in the field. In this case, only numeric characters allowed.
b) Limit check - to determine that the numeric amount entered does not exceed a predetermined numeric value, e.g. 2359 in this case.
c) Range check - to determine that upper and lower limits are valid, otherwise similar to the limit check, e.g. 0001 - 2359 in this case.
d) Size check - to ensure that input data will fit into the assigned field, e.g. no more than 4 characters in this case. 5)Landing Time: Same as Takeoff Time discussed above, in addition to the following: a - d as show above for the Takeoff time
e) Reasonableness test - to determine the correctness of the logical relationship between two data items. In this case the relationship between the takeoff time and the landing time, and the flight date. The entire flight should occur on the same date within the hours eligible for one day, or 0001 to 2399.

b. Identify and describe any errors in the records - The fields with errors are highlighted in orange above
1) There is a member number with an alpha character when the facts state that the member number should be between 0001 and 1999
2) The first line indicates 311109 in the flight date field which has a format of DDMMYY. There are only 30 days in November, so this appears to be an input error.
3) The fourth line of data indicates a plane that is not in the key. The input for type of plane is "X" when the only valid entries are "G, C, P and L."
4) The last line shows a landing time that appears to be invalid. Assuming they are using military time, there would be no such time as "1387". In addition if the landing time is in the 1:00 hour, then the flight would have landed prior to the stated takeoff time, which suggests 3:51 pm.

c. After the data was entered as shown in the problem, the following controls were added to prevent the input of erroneous data:
1) Click on fields in the Member#, Flight Date and Plane Used fields to see input messages
2) The only valid numbers for the Member# is set at between 0001 and 1999, in accordance with the number of possible members.
3) There is a validity check on the Landing time field to check that it is prior to the corresponding entry in the Takeoff Time field
4) Only numeric numbers are valid in the Takeoff Time and Landing Time fields