IT auditing chapter 8: Data Structures and CAATTS for Data Extraction

Flat-file Structures:

this model describes an environment in which individual data files are not integrated with other files. Data processing is performed by standalone applications rather than integrated systems

Flat-file Structures: Sequential structure

• all records are stored in an orderly way based on their primary keys

• applications start at the beginning of the file and process each record in sequence

• approach is not efficient when only a small portion of file being processed

• does not permit accessing a record directly

Flat-file Structures: Indexed structure

• contains an additional and separate index file that is linked to the actual data file

• the index contains the numeric value of physical disk storage location

• locating a record is accomplished by searching the index

Flat-file Structures: Hashing structure

employs an algorithm that converts the primary key of a record directly into a storage address

advantage: fast access speed

disadvantage: inefficient use of storage

Flat-file Structures: Pointer structures

• a data structuring approach where one record stores the address (pointer) of related record

• pointers provide connections between records and may be used to link records between files

Physical Address Pointer

contains the actual disk storage location which allows direct access to the record.

advantage: access speed

disadvantage: if related record moves, pointer must be changed. if pointer is lost or destroyed, the record it references is also lost

Relative Address Pointer

contains relative position of a record in the file which must be manipulated to convert to physical address

Logical Key Pointer

contains primary key of related record. Key value is converted by hashing to physical address

Relational Database Structure

• based on the indexed sequential file structure

• uses an index in conjunction with a sequential file organization

• multiple indexes can be used to create a cross reference (called an inverted list)

Entity

anything about which the organization wishes to capture data

Data model

blueprint for the physical database

Entity relationship diagram

graphic representation of a data model

Occurence

used to describe number of instances or records pertaining to a specific entity

Attributes

data elements that define an entity

Association

• represented by a line connecting two entities based on business relationship

• described by a verb, such as ships, request, or receives

Cardinality

degree of association between to entities

• Zero or one (0,1)

• One and only one (1,1)

• Zero or many (0,M)

• One or many (1,M)

Database Anomalies: update anomaly

a modification on an attribute must be made in each of the rows in which the attribute appears. Results from data redundancy in an unnormalized table

Database Anomalies: Insertion anomaly

a new item cannot be added to the table until at least one entity uses a particular attribute item

Database Anomalies: deletion anomaly

if an attribute item used by only one entity is deleted, all information about that attribute item is lost.

Repeating Groups

the existence of multiple values for a particular attribute in a specific record

Partial Dependency

• occurs when one or more non-key attributes are dependent on (defined by) only party of the primary key, rather than the whole key

• this can occur only in tables that have composite (two or more attribute) primary keys

Transitive Dependency

occurs in a table where non-key attributes are dependent on another non-key attribute and independent of the table’s primary key

Normalizing tables

identifies and removes dependencies resulting in tables in which:

• all non-key (data) attributes in table are dependent on primary key

• all non-key attributes are independent of other no-key attributes

To be free of anomalies…

tables must be normalized to the third normal form (3NF)

• first normal form (1NF): free of repeating group data

• second normal form (2NF): free of partial dependencies

• third normal form (3NF): free of transitive dependencies

Disadvantages of EAM: operational efficiency

decrease performance from a user’s point of view, especially if testing is extensive

Disadvantages of EAM: Verifying EAM integrity

may not be a viable audit technique in environments with a high level of program maintenance

Embedded Audit Module (EAM)

specially programmed module embedded in a host application to capture predetermined transaction types for subsequent analysis

Generalized Audit Software (GAS)

• mostly used CAATTS for IS auditing

• allows to access electronically coded data files and perform various operations on their contents

Advantages of GAS

• GAS languages are easy to learn and use

• many GAS products can be used on both mainframe and PC systems

• Auditors can perform their tests independently

• GAS can be used to audit data stored in most file structures and formats

Potential problems that may arise when using GAS

• auditor must sometimes rely on IT personnel to produce files/data

• risk that data integrity is compromised by extraction procedures

• auditors skilled in programming better prepare to avoid these pitfalls