IT audit chapter 8 (focus on 338-344, 350-370)

What do the relational features restrict, project, and join mean?

1)Restrict—Extracts specified rows from a specified table. 2) Project—Extracts specified attributes (columns) from a table to create a virtual table. 3) Join—Builds a new physical table from two tables consisting of all concatenated pairs of rows, one from each table

Explain the three types of anomalies associated with database tables that have not been normalized. (#4)

1. Update anomaly- the result of data redundancy. If a data element is stored in more than one place, it must be updated in all places. If this does not happen, the data are inconsistent. 2) insertion anomaly occurs when too much data are stored together -when vendor information is stored only with specific inventory items. Until items are purchased from a given vendor, the vendor cannot be added to the database. 3) Deletion anomaly - the opposite of the insertion anomaly—if a vendor supplies only one item and the firm discontinues that item, all information on the vendor is lost.

What are the four characteristics of properly designed relational database tables?

1.All attribute values in any column must be of the same class. 2. Each column in a given table must be uniquely named. 3. Tables must conform to the rules of normalization. [ free from structural dependencies including repeating groups, partial dependencies, and transitive dependencies] 4. The value of at least one attribute in each occurrence (row) must be unique. This attribute is the primary key. The values of the other (nonkey) attributes in the row need not be unique.

Explain the risk associated with using GAS to access complex file structures

The auditor must sometimes rely on computer services personnel to produce a flat file from the complex file structures. There is a risk that data integrity will be compromised by the procedures used to create the flat file. For example, if the auditor's objective is to confirm accounts receivable, certain fraudulent accounts in the complex structure may be intentionally omitted from the flat-file copy that is created. The sample of confirmations drawn from the flat file may therefore be unreliable. Auditors skilled in programming languages may avoid this potential pitfall by writing their own data extraction routines.

How can a poorly designed database result in unintentional loss of critical records?

The deletion anomaly may cause records to be deleted unintentionally and may occur for some time before the problem is noticed. A deletion anomaly occurs when an item in one file is legitimately deleted. The problem occurs when this file is linked to another file, which may also have a record deleted, due to its link. If the second record should not be deleted, then an update anomaly has occurred.

Discuss the accounting implications of the update, insertion, and deletion anomalies associated with improperly normalized tables.

The insertion and update anomalies would create record keeping and operational problems for the firm. However, flawed databases design that prevents the insertion of records, or requires the user to perform excessive updates, would attract attention quickly. The presence of the deletion anomaly is less conspicuous, but potentially more serious from an accounting perspective. Because the deletion anomaly may go undetected, the user may be unaware of the loss of important data until it is too late. This anomaly can result in the unintentional loss of critical accounting records and the destruction of the audit trail.

How is a user view different from a database table?

User views are derived database tables. A single table may contribute data to several different views. On the other hand, simple views may be constructed from a single table.

What is a user view?

A user view is the set of data that a particular user sees. Examples of user views are computer screens for entering or viewing data, management reports, or source documents, such as an invoice.

In a relational database environment, certain accounting records (for example, journals, subsidiary ledgers, and event general ledger accounts) may not exist. How is this possible?

Database accounting systems are transaction-based rather than account-based. The focus is on capturing important details of transactions that may be lost when they are forced into the structure of traditional accounting records. The transaction tables are then to be used to reconstruct traditional accounting records, such as Accounts Receivable and Accounts Payable.

Explain why auditors should be familiar with the principle of data normalization.

Database normalization is a technical matter that is usually the responsibility of systems professionals. However, the subject has implications for internal control that make it the concern of auditors also. For example, the update anomaly can generate conflicting and obsolete data values; the insertion anomaly can result in unrecorded transactions and incomplete audit trails; and the deletion anomaly can cause the loss of accounting records and the destruction of audit trails. Although 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.

Many to many relationship

For each occurrence of record types X and Y, zero, one, or many occurrences exist of record type Y and X, respectively. An example would be a student-professor relationship. Each student has multiple professors each semester, and each professor has multiple students each semester.

One to one relationship

For every occurrence in record type X, either zero or one occurrence exists of record type Y. An example would be that, for every student, only one social security number exists.

One to many relationship

For every occurrence in record type X, zero, one, or many occurrences exist of record type Y. An example would be buyers of assigned seating at concerts. Each potential buyer would leave the sales box office with zero, one, or many seats