Data Extraction & File Structures

• Data extraction is performed during computer audits to obtain accounting data from the client's accounting system.
• Data extraction is useful for performing tests of controls and substantive testing.
• Substantive testing includes verifying inventory valuation, confirming balances, and searching for unrecorded liabilities.

Data Structures

• Organization refers to the physical arrangement of records and files, which can be sequential or random.
• Access method is the technique to locate records and files, which can be direct or sequential.
• Index contains the physical location of records.
• Common file processing operations include retrieving, inserting, updating, reading, finding, scanning, and deleting records.

Flat File Structures

• Sequential structure stores all records in contiguous storage spaces in a specified sequence, using a key field.
• Sequential files are simple and easy to process because applications read from the beginning in sequence.
• Sequential method is inefficient if processing only a small portion of the file because it does not permit direct access to records.
• Sequential file processing is efficient for operations like reading and finding the next record, but inefficient for retrieving, inserting, scanning, and deleting.
• Indexed structure uses a separate index file in addition to the data file.
• Index file contains the physical address in the data file of each indexed record.
• Indexed random files are created without regard to physical proximity to related records.
• The physical organization of the index file itself may be either sequential or random.
• Random indexes are easier to maintain while sequential indexes are more difficult.
• Compared to sequential files, indexed random files enable rapid searches due to the processing of individual records and efficient disk usage.
• Indexed random file processing is efficient for retrieving, inserting, updating, and deleting records, but inefficient for reading a file

Indexed Sequential Access Method (ISAM)

• Suitable for large files and routine batch processing, allowing a moderate degree of individual record processing.
• Used for files across cylinders, using a number of indexes with summarized content.
• Access time for a single record is slower than indexed sequential or indexed random.
• Does not perform record insertions efficiently, requiring physical relocation of all records beyond a certain point.
• Has three physical components: indexes, prime data storage area, and overflow area.
• Requires searching the index, prime data area, and overflow area, slowing down access time.
• Integrating overflow records into the prime data area and reconstructing indexes reorganizes ISAM files.
• ISAM is very efficient for reading, finding the next record, and scanning a file.
• ISAM is moderately efficient for retrieving a record by key and inefficient for inserting and deleting records.

Hashing Structure

• Converts the primary key into a physical record storage address using an algorithm.
• Hashing structure does not require a separate index.
• Hashing's advantage is access speed, but its disadvantage is inefficient storage usage.
• Different keys may create the same address in hashing.
• Hashing is efficient for retrieving, inserting, updating, and scanning a file, but inefficient for reading a file, finding the next record and deleting a record.

Point Structure

• Stores the address (pointer) of a related record in a field with each data record.
• Records are stored randomly, and pointers provide connections between records.
• Pointers may also provide links of records between files.
• Types of pointers:
  • Physical address: actual disk storage location
    • Advantage: Access speed
    • Disadvantage: if related record moves, pointer must be changed and without logical reference, a pointer could be lost causing referenced record to be lost
  • Relative address: relative position in the file
    • Must be manipulated to convert to physical address
  • Logical address: primary key of related record
    • Key value is converted by hashing to physical address
• Point structures are efficient for record retrieval, insertion, updating, and scanning a file, but inefficient for reading, finding the next record, and deleting.

Database Structures

• Hierarchical & network structures use explicit linkages between records to establish relationships.
• Relational structure uses implicit linkages between records using foreign keys/primary keys.
• In relational databases, "tables" consist of rows and columns.
• Relational records: "Foreign keys" in one record establishes relationships to related records in other files.
• User views are based on data a particular user needs to achieve their assigned tasks.
• Single user views, or views without user input, leads to problems in meeting the diverse needs of the enterprise.
• Current trend: capture data in sufficient detail and diversity to sustain multiple user views.
• Consolidation entails user views MUST be consolidated into a single "logical view" or schema, and the data in the logical view MUST be normalized.

Data Normalization

• Creating views involves designing output reports, documents, and input screens which helps understand the relationships among the data.
• Then apply normalization principles to the conceptual user views to design the database tables.
• Data normalization is critical to the success of DBMS through Effective design in grouping data
• Un-normalized data suffers from insertion, deletion and update anomalies.
• Tables with one or more of these anomalies are less than 3NF.
• Normalization process must eliminate the 3 anomalies if all non-key attributes are dependent on the primary key and There are no partial dependencies (on part of the primary key) and There are no transitive dependencies; non-key attributes are not dependent on other non-key attributes.
• "Split" tables are linked via embedded "foreign keys".
• Most auditors will never be responsible for normalizing an organization's databases, they should have an understanding of the process and be able to determine whether a table is properly normalized.
• In order to extract data from tables to perform audit procedures, the auditor first needs to know how the data are structured.

Embedded Audit Module (EAM)

• Identify important transactions live while they are being processed and extract them.
• For example, errors, fraud, and compliance (SAS 78, SAS 94, SAS 99/S-OX).
• Operational efficiency may decrease and verifying EAM integrity can be extensive.
• EAM status: increasing need, demand, and usage of COA/EAM/CA.

Generalized Audit Software (GAS)

• The most widely used CAATT is used for footing, balancing, selecting, and reporting detail data and selecting stratified statistical samples from data files.
• GAS formats results into audit reports (auto work papers!), printing confirmations, screening / filtering data, comparing multiple files for differences
• Recalculating values in data
• GAS software is easy to use and requires little computer background.
• Many GAS products are platform independent and works on mainframes and PCs.
• Auditors can perform tests independently of IT staff because GAS can be used to audit the data currently being stored in most file structures and formats.
• Simple structures, complex structures raise auditing issues.
• The auditor must sometime rely on IT personnel to produce files/data with risks that data integrity is compromised by extraction procedures.
• Auditors skilled in programming are better prepared to avoid these pitfalls. ACL is a proprietary version of GAS and a leader in the industry.
• Designed as an auditor-friendly meta-language which has Access to data generally easy with ODBC interface.