AIS Exam #2 (Ch.8,9,10,11)

Logical Model

Pictorial representations of users’ information needs.

Aspects of Logical Design

Logical Data Modeling & Logical Process Modeling

Conceptual Data Modeling

AKA Logical Data Modeling or Semantic Data Modeling
The analyst attempts to develop a conceptually accurate representation of a real-world business scenario. The goal is to capture the semantics, or meaning, of reality in a model.

Crow’s Foot Notation

Crow Foot - “Many”

Optional Participation - “O”

Mandatory Participation - “|”

Four Symbols in a DFD Model

Process - Circle

Arrow - Flows of Data

Open-ended Rectangle - Data Store

Square Box - External Entity/Agent

Data Flow Diagram

Useful for modeling and understanding processes and the flow of data relative to processes in a system.

Context Diagram

Highest level of DFD, treats the entire information system as one process and shows the inputs to the system from external and internal entities, and the outputs from the system to external and internal entities.

Who uses context diagrams?

Intended to be used by high-level managers such as controllers and the chief information office (CIO). Understanding the system at a very general level -- what inputs are going into the system and who is providing those inputs, what the outputs of the system are, and who receives the outputs.

Level 0 DFD

Shows the major processes and the data stores used to store data. The reason for calling it a “Level 0” DFD is because the processes shown are labeled 1.0, 2.0…

Level 1 DFD

Provides details about what exactly is involved in a particular Level 0 DFD process. Not all processes would have the process execution recorded in a table. Ex. The “assign professionals” process. Checking the skills of professionals, checking their availability, and actually making the assignments are the three sub-processes involved. (From textbook)

Data Store Accessed

Arrows originating from a data store.

Data Store Updated

Arrows going to a data store. Updated means that either new data is being added or existing data is being changed. Possible to have a double-headed arrow.

Guidelines for “Good” DFD

1. Determine the scope of the system.

2. Identify the processes involved and the relationship (sequencing) between the processes.

3. Identify the data stores (tables) that will be accessed and/or updated.

4. Label all DFD elements.

5. To the extent possible, avoid overlapping data flow lines.

Significant Business Event

Business activities that management wants to plan, execute, and evaluate.

Categories of Business Events

Economic and Non-Economic

Economic Event

Sales and purchases, increases or decreases in resources such as inventory, cash, etc. (journal entry)

Non-Economic Event

Issuing a sales quotation, taking a sales order, purchase order; no journal entry.

Commitment Event

Events in which the enterprise enters into agreements expected to result in future economic events (example of significant non-economic events).

3 Types of Relationships in the REA Framework

1. Stock-Flow Relationships

2. Duality Relationships

3. Participation or Control Relationships

Stock-Flow Relationships

Relationship between events and resources. Either “inflow” or “outflow” relationships.

Duality Relationship

Relationship between events. Each “increment” event has a corresponding “decrement” event.

Participation/Control Relationship

Relationships between events and agents. External agents “participate” in events, while internal agents “control” events.

Difference between Business Events (Business Processes) and Information Processes

Business processes create new information, where information processes do not.

Information Process

Do not create new information. Aimed at simply recording, updating, or extracting existing information.

Types of Information Processes

Recording, Maintenance, and Reporting

Recording Process DFD

Needed to record all aspects of each business event identified in the EER diagram.

Rules for Construction Recording Process DFD’s

1. Each event entity on the EER diagram will have a Level 0 DFD.

2. The agents associated with each event should be shown as external entity/agent symbols providing inputs to or getting output from the event process.

3. All resource and agent entities associated with the event entity should be shown as data stores being accessed and/or updated by the event process.

4. A data store should be shown for the event itself, with an arrow going to the data store indicating that it is being added to.

Maintenance Process DFD

A maintenance process DFD is needed for each resource and agent entity on the EER diagram: add, update, and delete.

Reporting Process DFD

One or more reporting process DFDs will be needed for each event, resource, and agent on the EER diagram. By "reporting process" we simply mean the extraction of information from one or more data stores to generate either routine or non-routine reports.

Conclusion Materialization

The process of periodically generating reports that summarize the results of ongoing business events.

Conversion Rules for Creating Relational Tables (Mandatory)

1. Collapse 1:1 relationships into a single table.

2. For 1:M relationships, post the PK of the table on the “one” side to the table on the “many” side.

3. For M:M relationships, create a new composite key table posting the PK of each participating table to the new table.

Conversion Rules for Creating Relational Tables (Optional)

1. Treat the “optional” side of the relationship as a mandatory “many” relationship for the purpose of conversion.

2. Apply the same conversion rules for mandatory relationships stated above.

General Rule of Thumb for Determining the Number of Relational Tables

Number of Primary Classes + Number of Many-to-Many Relationships

Process for Creating Relational Tables

1. Look for mandatory 1:1

2. Look for minimums of zero and change to many in head.

3. Determine Number of Relational Tables Needed

Database Normalization

Seeks to ensure that there are no significant data anomalies in a database design. In essence, we are seeking to ensure that the tables in a relational database conform to sound relational database design principles.

“Normal Form”

Means that a table conforms to certain rules aimed at testing the integrity of the table structure.

Data Anomalies

A table that is not in third normal form has one or more design flaws that can result in anomalies in the data.

Three Types of Data Anomalies

Insert, Delete, and Update

Insert Anomaly

The inability to insert or add data to a table because of a design problem.

Delete Anomaly

Means that deleting certain data from a table inadvertently results in the loss of other related data which the user would like to retain.

Update Anomaly

A change in a data item must be performed in multiple locations rather than just one, again because of a design flaw in the database structure.

Functional Dependency Notion

Means that all non-key attributes in a table are functionally dependent on the PK.

First Normal Form (1NF)

The first stage in the normalization process. It describes a relation depicted in tabular format, with no repeating groups and a primary key identified. All nonkey attributes in the relation are dependent on the primary key.

Second Normal Form (2NF)

The second stage in the normalization process, in which a relation is in 1NF and there are no partial dependencies (dependencies in only part of the primary key).

Third Normal Form (3NF)

A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies.

Transitive Dependency

A condition in which an attribute is dependent on another attribute that is not part of the primary key. Can potentially cause insert, update, and delete anomalies in a 2NF relation.

Steps to Implement the Design into a Relational Database System

1. Create Tables

2. Designate the PK

3. Establish Relationships Between Tables

4. Create Forms for Event Recording Processes

5. Create Forms for Maintenance Processes for Each Resource and Agent Entity

6. Create Queries and Reports for All Information Processes

Database Form

A user-friendly graphical interface to one or more tables. Have a number of fields and possibly one or more command buttons or other objects. Used for recording, maintaining, and perhaps reporting information processes.

Controls

Mechanisms or procedures designed to prevent, detect, or correct errors and irregularities.

Procedure

A series of steps undertaken to accomplish a particular tasks.

Errors

Unintentional mistakes arising mainly from invalid data entry.

Irregularities

Intentional alterations or misstatements of data.

Risk

The likelihood that an information system will experience an error or irregularity.

Exposure

The dollar amount of loss that could occur if the risk is realized.

Exposure: Erroneous Record Keeping

Outcome: The recording of financial transactions contrary to established accounting policies. The errors may involve timing, value, or classification problems.

Exposure: Unacceptable Accounting

Outcome: The establishment or implementation of accounting policies which are not generally accepted or are inappropriate to the circumstances.

Exposure: Business Interruption

This may include anything from a temporary suspension of operations to a permanent termination of the enterprise.

Exposure: Erroneous Management Decisions

Outcome: Are objectionable in themselves, but may also lead to other exposures. May arise due to misleading information, lack of information, or errors in judgement.

Exposure: Fraud & Embezzlement

Perpetrated at different levels - against management or by management. Misappropriation of funds is only one ramification of fraud. Deliberately misinforming management or investors is also fraudulent.

Exposure: Statutory Sanctions

Any set of the penalties, which may be brought by judicial or regulatory authorities having jurisdiction over an organization's operations.

Exposure: Excessive Costs

Include any expense of the business, which could be readily avoided. A related exposure is also a loss of revenues to which the organization if fairly entitled.

Exposure: Loss or Destruction of Assets

Refers to the unintentional loss of physical assets, monies, claims to monies, or information assets.

Exposure: Competitive Advantage

Relates to any inability of an organization to effectively remain abreast of the demands of the marketplace or to respond effectively to competitive challenges.

Reasonable Assurance

The concept that recognizes that controls should only be implemented where the benefits from the control exceed the costs of implementation.

Control Categories

Preventative, Detective, Corrective

Preventative Control

Aims to prevent the occurrence of errors or unwanted acts. Tend to halt processing once an error has occurred or an exception condition has been identified (segregation of duties, passwords)

Detective Control

Aim to detect errors or unwanted acts once they have occurred. Internal and external auditors, rely on human intervention and follow up on corrective controls for ultimate resolution.

Corrective Control

Assist both the investigation and correction of errors or unwanted acts after they have been detected. (backup processes, discrepancy reports)

Risks in Computer-Based Systems

Errors in Data, Irregularities in Data, Loss of Data, Natural Disaster, Computer/Cyber Crime

Errors in Data

Most common risk; data being incorrectly input, updated, or deleted; unintentional mistakes arising mainly from invalid data entry.

Irregularities

Intentional alterations or misstatements of data, management fraud misleads investors or creditors, concealment of theft or misappropriation of assets (defalcation), incorrect number of hours worked in a payroll data entry.

Defalcation

Intentional alteration or misstatements of data in systems could be performed by employees, often to conceal theft or misappropriation of assets.

Loss of Data

An error condition in information systems in which information is destroyed by failures or neglect in storage, transmission, or processing. Organization needs preventative measures and recovery procedures.

Computer Crime

Includes any intentional act of involving unauthorized access to, use of, alteration of, or destruction of, computer hardware. Most cases go unreported.

Sarbanes-Oxley Act 2002

Set into place the PCAOB established requirements for proper financial record keeping for public companies and penalties of as much as 25 years in prison for noncompliance.

Public Company Accounting Oversight Board

PCAOB

SOX Title I

Extends broad powers to the PCAOB to regulate audits and auditors of public companies. Describes the Board’s responsibility, structure, and authority.

SOX Title II

Auditor Independence.
-Creates standards for external auditor independence
-Goal is to reduce conflicts of interest
-Auditor approval, audit partner rotation, auditor reporting requirements

SOX Title III

Corporate Responsibility: topics include
1. Public company audit committees
2. Corporate responsibility for financial reports
3. Improper influence on conduct of audits
4. Forfeiture of certain bonuses and profits

SOX Title IV

Enhanced financial disclosures; topics include:
1. disclosures in periodic reports
2. enhanced conflict-of-interest provisions
3. disclosures of transactions involving management & principal stockholders
4. management assessment of IC
5. certain exemptions
6. code of ethics for senior financial officers
7. disclosure of audit committee financial expert
8. enhanced review of periodic disclosures by issuers

Section 404 of SOX

Requires that managers document and assess the effectiveness of all internal control processes that can impact financial reporting.

Committee of Sponsoring Organizations

COSO

COSO Internal Control Framework

Provides suitable criteria against which management may evaluate and report on effectiveness of its internal controls for financial reporting.

Components of COSO Internal Control Framework

1. Control Environment
2. Risk Assessment
3. Control Activities
4. Information and Communication
5. Monitoring

Control Environment

The foundation for all the other elements of internal control, setting the overall tone for the organization. Managements philosophy, entity's organizational structure.

Management’s Risk Assessment

The identification, analysis, and management of risks relevant to the preparation of financial statements that are fairly presented in conformity with GAAP.

Control Activities

The policies and procedures that help ensure that management's directives are carried out.

Information & Communication

The internal control system must capture and communicate all pertinent information both down and up the organization, as well as communicate information to appropriate external parties.

Monitoring

Identifies and fixes; management periodically assessing the quality of the internal control performance.

General Control Procedures

Also referred to as entity-level control procedures; Control procedures that affect all information systems and sub systems within the organization.

Application Control Procedures

Controls designed to prevent or detect errors within each information system or sub system.

Drawback of COSO Control Framework

It does not explicitly recognize and prioritize information system controls.

Control Objectives for Information and Related Technology

COBIT

COBIT Framework

Explicitly focuses on information technology controls, oriented towards management and "good practices" most recent version is COBIT 5 or COBIT 2019

Validity Test

The test that ensures that a value entered into a field matches an accepted value for that field.

Enforcing Referential Integrity

Input Control procedure that can only be performed in database systems.

Dynamic Backup

What backup method involves maintaining a log file of transaction activity occurring since the last back up.

What are the domains of COBIT?

1. Monitoring

2. Acquisition and Implementation

3. Planning and Organization

4. Delivery and Support

Public Key Encryption

The most secure form of data transmission over the internet.

Conceptual Data Modeling

Another name for Logical Data Modeling