Application Controls for Information Systems

1. Chapter 6 Application Controls EECS4482 2017

2. “Drive thy business, let not that drive thee.” - Benjamin Franklin EECS4482 2017

3. Application Controls • Application controlspertain to specific information systems. • Application controls occur at the input, processing, output and storage phases of transactions. EECS4482 2017

4. Application Control Objectives • To ensure that authorized transactions are processed completely, accurately and on a timely basis. • Also ensure that information produced is accurate, complete, relevant and available only to authorized parties. • Also ensure that duties are assigned to deter irregular or fraudulent transactions and to facilitate error detection. EECS4482 2017

5. Control Matrix EECS4482 2017

6. Types of Application Controls • Authorization limit and approval • Input controls • Computer processing controls • Segregation of duties • Output controls • Management or independent controls • Access controls EECS4482 2017

7. Application Controls • Operate on the foundation of general controls. • If general controls are weak, application controls are compromised. • You should describe each control in specifics, including who, what, frequency and how, including the subject and object. EECS4482 2017

8. Input Controls • Carefully structured source documents and input screens • Batch control totalsthat help prevent the loss of transactions and the erroneous posting of transaction data • The use of Batch control logs in the batch control section • Amount control total totals the values in an amount or quantity field • Hash total totals the values in an identification field • Record count totals the number of source documents (transactions) in a batch • Transaction numbering EECS4482 2017

9. Batch Total Example • Mail room totals payments received. • Payment list and cheques brought to accounts receivable. • Accounts receivable (AR) system generates total after posting. • AR clerk compares 2 totals. EECS4482 2017

10. Offsetting Errors • If a $100 cheque falls through the crack on its way to AR but another $100 cheque is posted to AR as $200, the 2 loss of the cheque will not be detected. • Hash total can detect this, i.e., instead of or in addition to the amount total, the account number totals are also compared. EECS4482 2017

11. Definition and Purpose of Edit Tests • Edit Checks (programmed checks)are most often validation routines built into application software • The purpose of edit tests is to examine selected fields of input data and to reject those transactions whose data fields do not meet the pre-established standards of data quality EECS4482 2017

12. Examples of Edit Checks (Programmed Checks) • Validity Check(e.g., M = male, F = female) • Limit Check(e.g., hours worked do not exceed 40 hours) • Reasonableness Check(e.g., increase in salary is reasonable compared to base salary) • Field Check(e.g., numbers do not appear in fields reserved for words) • Sequence Check(e.g., successive input data are in some prescribed order) • Range Check (e.g., particular fields fall within specified ranges - pay rates for hourly employees in a firm should fall between $8 and $20) EECS4482 2017

13. Examples of Edit Check • Sign Check (e.g., the quantity ordered cannot be negative.) • Check digit (e.g., the last digit of SIN is a product of the first 8) • Missing data check, to make sure all required fields are filled in. EECS4482 2017

14. Other Input Controls • User friendly screen • Retrieval of data based on initial input • Automatic cursor movement • Gap detection • Duplicate detection • Drop down menu • Exception reporting and review EECS4482 2017

15. Objectives of Processing Controls • Processing Controlshelp ensure that data are processed accurately and completely, that no unauthorized transactions are included, that the proper files and programs are included, and that all transactions can be easily traced • Categories of processing controls include Manual Cross-checks, ProcessingLogic Checks, Batch totals,Run-to-Run Controls,File and Program Checks, accounting for transaction numbering. EECS4482 2017

16. Examples of Processing Controls • Manual Cross-Checks- include checking the work of another employee, reconciliations and acknowledgments. This is a management or independent control. • Processing Logic Checks- many of the programmed edit checks, such as sequence checks and reasonableness checks (e.g., payroll records) used in the input stage, may also be employed during processing. EECS4482 2017

17. Run-to-Run Control • System processing check to ensure that transaction batches are passed completely from one program to another. EECS4482 2017

18. Segregation of Duties • Separates incompatible duties that can expose an organization to a higher degree of fraud, improper practice or unchecked errors than necessary. • Enforced by organization charts, job descriptions, procedures and access controls. EECS4482 2017

19. Output Controls • Outputs should be complete and reliable and should be distributed to the proper recipients • Types of output controls are: • validating processing results • regulating the distribution and use of printed output • Accounting for completeness of output EECS4482 2017

20. Validating/Reviewing Processing Results • Activity (or proof account)listings document processing activity and reflect changes made to master files • Because of the high volume of transactions, large companies may elect to reviewexception reportsthat highlight material changes in master files. This is a management or independent control. EECS4482 2017

21. Management Review • Increasingly important as more transactions are processed with general authorization. • Organizations are moving towards exception reporting for expedience. • A trail of management review is needed and follow-up should be done on overdue reviews. EECS4482 2017

22. Independent Review • Critical work should be reviewed by an independent person, e.g., bank reconciliation. • Review can be performed before or after processing. • Can replace in some cases joint authorization when the latter is not practical, e.g., cheque signing. EECS4482 2017

23. Independent Review • Critical work should be reviewed by an independent person, e.g., bank reconciliation. • Review can be performed before or after processing. • Can replace in some cases joint authorization when the latter is not practical, e.g., cheques normally signed by 2 people are signed by one because of extended absence, in this case, the president should review documentation even after the fact. EECS4482 2017

24. Database Controls • Access control over the database tables, DBMS and other components, defining who can read and write. • Normalization • Documentation • Controls to prevent concurrent update by locking the record to be updated. EECS4482 2017

25. Concurrent Update Anomaly • Beginning loan balance = $1,000 • Process A reads it to process a loan increase of $200. • Process B reads the same beginning balance of $1,000 to process a loan repayment of $300. • When Process A finishes, it puts the revised balance back as $1,200. EECS4482 2017

26. Concurrent Update Anomaly • When Process B finishes, it puts the revised balance back as $700. • The actual new balance should be $1,000 + 200 – 300, or $900. • The problem is that two independent processes perform update on the same account balance. EECS4482 2017

27. Field Lock • To prevent this, a process that intends to update a field should inform the DBMS by setting a flag. The BDMS then locks the field from being read by other processes which also intend to update based on that field until the first update finishes. EECS4482 2017

28. Deadlock • When inter-dependent transactions lock multiple records, the system can come to a halt. • This is because each of these transactions has to wait for the others to complete before accessing the required fields, but no transaction on the list can complete because fields locked by other transactions. EECS4482 2017

29. Deadlock Example • I enter an ATM transaction to transfer $1,000 from a joint checking account to a joint savings account. • Before this transaction, the checking account and savings account balances are $5,000 and $3,000. • At the same time, my wife uses eBanking to transfer $2,000 from our joint savings account to our joint checking account. EECS4482 2017

30. Deadlock Example • My transaction starts first, so it locks the checking account balance after having read it; but before it reads the savings account balance, my wife’s transaction reads the savings account balance and locks it. • Now my transaction cannot read the savings account balance and my wife’s transaction cannot read the checking account balance, so neither transactions can progress. EECS4482 2017

31. Database Controls • Controls to resolve deadlock by identifying it and removing all but 1 locks based on usually first come first serve. • Rollback procedures to prevent data loss in the event of incomplete transaction processing. • Synchronization of database versions in a distributed environment, including time synchronization. EECS4482 2017

32. Foreign Key A field in a table which is the primary key in another table, e.g., customer number in the sales order table. Foreign key must not be blank. DBMS should enforce this and it is called referential integrity. EECS4482 2017 32

33. Database Normalization Rules for database normalization based on set theory. A database should be normalized to be efficient and less prone to errors, containing less data redundancy. Failure to normalize results in anomalies/errors that otherwise might occur when adding, changing, or deleting data stored in the database. There are 6 normal forms, but the first 3 are generally considered sufficient. To function properly, a database should obtain the third normal form. Normal forms are inclusive, which means that each higher normal form includes all lower normal forms. That is, a table in 3NF is in 1NF and in 2NF. EECS4482 2017 33

34. Unnormalized Table Table contains repeating groups Sales order line items repeat No primary key EECS4482 2017 34

35. Relation in First Normal Form (1NF) A table is in first normal form (1NF) if it doesn’t contain repeating groups. Rows are now key dependent, uniquely identified by a primary key. The primary key for the table is a combination of SO_Number and Item_Number. A primary key formed by the combination of two or more columns is called a composite primary key. EECS4482 2017 35

36. Problems with First Normal Form (1NF) Includes the following functional dependencies: Item_Number functionally determines Item_Name. Therefore, item names are repeated several times. This data redundancy should be eliminated. Cust_Code functionally determines Cust_Name. The combination of SO_Number and Item_Number together functionally determine Item_Name, Qty_Ordered, Cust_Code, and Cust_Name. These functional dependencies cause several problems called update anomalies. EECS4482 2017 36

37. Two steps to get from 1NF to 2NF Create a new table for each subset of the table that is partially dependent on a part of the composite primary key. This step results in two new tables, one with SO_Number as its primary key (SALES_ORDERS table) and another with Item_Number as its primary key (INVENTORY_ITEMS table). Place each of the non-key attributes that are dependent on a part of the composite primary key into the table that now has a primary key that is the field on which the non-key attribute is partially dependent. The Item_Name field is partially dependent on the Item_Number field portion of the composite primary key, so it would be moved into the new INVENTORY_ITEMS table. EECS4482 2017 37

38. Relations in Second Normal Form (2NF) To obtain second normal form, partial dependencies must be eliminated by creating new tables. EECS4482 2017 38

39. Third Normal Form (3NF) To obtain third normal form, transitive dependencies must be eliminated. A transitive dependency exists when a non-key field depends on another non-key field which in turn depends on the key. For example, customer name depends on customer code which depends on sales a order number. A table is in third normal form if it is in second normal form and transitive dependencies are eliminated. EECS4482 2017 39

40. Relations in Third Normal Form (3NF) Customer information moved to “Customers” table EECS4482 2017 40

41. Summarized Rules for Normalization • A 1NF table has a primary key. • In a 2NF table, every non-key field depends on the entire primary key. So there is no partial dependency, which could cause data redundancy. • In a 3NF table, every non-key field depends on nothing but the primary key, so there is no transitive dependency and as a result data redundancy is gone. EECS4482 2017

42. Review Questions • What is the difference between redundant data check and referential integrity • check? EECS4482 2017

43. Review Questions • 2. What is the difference between batch total and hash total? • 3. Describe an example of what can go wrong if concurrent update is allowed. . EECS4482 2017

44. Review Questions • 4. Describe a technique that can be used as a general control and an application control. • 5. Which risk do edit checks mainly address? EECS4482 2017

45. MC Question • Which risk does database normalization reduce? A. Concurrent update B. Obsolete data C. Data redundancy D. Data incompleteness E. Data leakage EECS4482 2017