Data Structures and CAATTs

• Chapter 8 focuses on data structures and CAATTs (Computer-Assisted Audit Tools and Techniques) for data extraction.

Data Structure Fundamentals

• Data structure has two fundamental components: organization and access method.
• Organization refers to the arrangement of records on storage devices, either sequentially or randomly.
• Access method is the technique used to locate records and navigate through a database or file.

Flat File Structures

• In flat file structures, end users own their data files, rather than sharing them.
• Data files are structured, formatted, and arranged to suit the specific needs of the owner or primary user.

Sequential Structure

• All records are stored in contiguous storage spaces in a specified sequence, using a key field.
• Sequential files are simple and easy to process.
• The application starts at the beginning of the file and processes each record in sequence.

Indexed Random File

• Records are created without regard to physical proximity to other related records.
• The physical organization of the index itself may be either sequential (by key value) or random.

Virtual Storage Access Method (VSAM)

• VSAM is used for very large files that require routine batch processing and a moderate degree of individual record processing.
• VSAM has three physical components: indexes, prime data storage area, and overflow area.

Hashing Structure

• Hashing structure provides fast access but inefficient storage and collision issues.

Pointer Structure

• Pointer structure uses address pointers to link records, offering fast access.
• Potential issues arise if a record moves or a pointer is lost.

Types of Pointers

• Physical address pointer: Contains the actual disk storage location for direct access to the record.
• Relative address pointer: Contains the relative position of a record in the file which must be converted to a physical address.
• Logical key pointer: Contains the primary key of a related record, with the key value converted by hashing to the physical address.

Hierarchical & Network Database Structures

• These structures are early models using flat-file techniques and proprietary structures for process integration and data sharing.

Relational Database Structure Concepts

• Based on indexed sequential file structure, this approach facilitates direct and batch access to records and supports relational algebra functions.

Algebra Functions in Relational Databases

• Restrict: Extracts specified rows from a specified table.
• Project: Extracts specified attributes (columns) from a table to create a virtual table.
• Join: Builds a new physical table from two tables, consisting of all concatenated pairs of rows from each table.
• Concatenated means linked together in a series

Entity, Occurrence, and Attributes

• 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.
• Occurrence: Number of records that pertain to a specific entity.
• Attributes: Data elements of an entity.

Association and Cardinality

• Cardinality: Degree of association between two entities.
  • Zero or one (0,1)
  • One and only one (1,1)
  • Zero or many (0,M)
  • One or many (1,M)

Anomalies, Structural Dependencies, and Data Normalization

• Update anomaly: Results from data redundancy in an unnormalized table, requiring modifications across multiple rows.
• Insertion anomaly: Occurs when a new item cannot be added to the table due to a lack of association with the primary key.
• Deletion anomaly: Involves unintentional data loss when deleting an attribute item used by only one entity.

Designing Relational Databases

• Six phases of database design:
  • Identify entities
  • Construct a data model showing entity associations
  • Add primary keys and attributes to the model
  • Normalize the data model and add foreign keys
  • Construct the physical database
  • Prepare the user reviews

Embedded Audit Module

• Also known as continuous auditing, it identifies important transactions while they are being processed and extracts copies in real time.
• An EAM is a specially programmed module embedded in a host application to capture predetermined transaction types for subsequent analysis.

Generalized Audit Software (GAS)

• GAS allows auditors to access electronically coded data files and perform various operations on their contents.
• Common uses for GAS:
  • Footing and balancing files
  • Selecting and reporting file data
  • Selecting statistical samples
  • Formatting audit reports
  • Printing confirmations
  • Screening data items
  • Comparing files
  • Recalculating data fields

ACL Software

• ACL (Audit Command Language) is used to access data in various digital formats and test them comprehensively.
  • Data Definition: Allows the auditor to define characteristics of the source file such as record length, field names, data types, and field lengths.
  • Customizing a View: Allows auditors to customize the original view to better meet their audit needs.
  • Filtering Data: ACL provides options for filtering data using logical operators such as AND, OR, and NOT.
  • Stratifying Data: Allows the auditor to view the distribution of records by strata, such as sales price or unit cost.
  • Statistical Analysis: ACL offers sampling methods such as record sampling and monetary unit sampling (MUS).