AC 589 Exam 2

query

retrieving data from a relational database

- part of having a data mindset, gain conceptual understanding of how relational database is used/structured

why study this in accounting?

flat file

self-contained file that is not integrated with other files

excel worksheet

example of flat file

databse

a collection of integrated files or tables

relational database

stores data in tables

unique

each table has a _______ primary key

foreign keys

tables are linked by _________ ________

primary key from another table

what are foreign keys

record

each row in a database table

field

each column is called a ______

primary keys

foreign keys

non-key attributes

fields are either of these 3

normalization

the process of structuring the database in a way that avoids common problems

storing the same data twice

inability to insert new data

inability to delete unwanted data

ex of common problems that normalization tries to avoid

redundancy

storing the same data twice

1. first normal form (flawed)

2. second normal form (less flawed)

3. third normal form (a good database)

3 levels of normalization

first normal form

information stored as a flat file

second normal form

every record tied to the PK and tables linked by FKs

third normal form

no transitive dependencies (field A determimes field B)

resource, event, agent

what are the three types of entities that data is organized around in REA method

entity relationship diagram (ERD)

created, and used to design the database

resource

every event is linked to one _______

two agents

every event is linked to two _________

internal - employee

external - customer or vendor

who are typical two agents

event

events are linked to the preceding _________

will not

typically, ____________ link resource to resource, agent to resource, or agent to agent

foreign key or a relationship table

all direct links between entities must be in the database as a _____ ______ or a _______ _______

cardinality; number

_______ of occurrences between two entities (can be 1 or many); used to determine how the entities are linked in the database

one-to-one

each record for entity A links to only one record for entity B, and vice versa

one-to-many

one of the two entities has a record that links to more than one record of the other entity

many-to-many

both entities have records that link to more than one record of the other entity

crows foot; 1

agent cardinality will typically be a _______ next to the event and a _______ next to the agent

crows foot; one

cash cardinality will typically be a _______ next to the event and a _______ next to cash

bank account

what is the primary key for cash

primary keys from the two entities

what does a relationship table include

in either, but not both

for one-to-one, the FK goes where?

remittance #

PK for cash receipt

check #

primary key for check disbursement

the table of the entity where each records links to only one record of the other entity; the table where the crows foot is

for one-to-many, the FK goes where?

in the relationship table

for many-to-many b/w resource and agent, where do quantities or amounts go

structured query language (SQL)

standardized language used to query relational databases

SELECT

used to identify which columns will be displayed; fields you need to see

FROM

used to identify the tables that will be queried

FROM statement

tables needed to access the column used in SELECT and WHERE must be included here

WHERE; rows

used to select which ______ to retrieve; conditions -> =,

sum

the total value for a column

minimum

the smallest value in a column

maximum

the largest value in a column

count

the number of observations in a column

AND

several conditions are linked using an ________ statement

average

the average for a column

when more than one table is included in the query (FROM)

when must there be a WHERE statement

table name.column name = table name.column name

how to link the tables in the WHERE statement

sum

minimum

maximum

count

average

5 mathematical operations

SELECT statement

where is the math operation located

GROUP BY; subtotals

used to identify which field is subject to a math operations; allows for _______

everything in the SELECT statement EXCEPT the math operattion

what do you put in the GROUP BY

ORDER BY

used to sort the answer in ascending or descending order

n tables = n - 1 linkage statements

n tables in FROM

how to determine how many tables should be linked in WHERE statement

1. math operation in SELECT

2. another, non-math field in SELECT

what two conditions must be present to have a GROUP BY

box

every ______ is a table

table

every many-to-many is a ________

foreign key

every 1:1 or 1:many is a _________

record customer order

approve credit

process shipping order

billing

cash collections

5 steps of revenue cycle procedures

sales order (apply field checks, limit tests, range tests, etc.)

record customer order documents

credit authorization - won't ship product until approve credit

approve credit documents

picking ticket, packing slip, bill of lading

process shipping order documents

picking ticket

document given to warehouse personnel and tells them what product to select for that customer

packing slip

what is being shipped, list everything that is in the package

bill of lading

goes with the carrier, everything we loaded in the shipment that you will be transporting

invoice

billing documents

remittance advice = all info related to customer who is paying you

cash collections documents

- sales orders are processed as received

- item shipped

- check and remittance received

revenue cycle processes

sales orders are processed as received

online credit check, check inventory availability and transmit stock release record

item shipped

inventory updated, account receivable created, invoice and remittance are created

check and remittance received

cash receipts are recorded and account. receivables are updated

- credit authorization

- data validation controls

- batch controls

revenue input controls.

audit tests for credit authorization

use test data to examine program decision rules for credit authorization

credit authoriization

credit check

data validation controls

missing data, alpha-numeric, range, limit, validity, and check digits

audit tests for validation controls

- if system development, and maintenance controls are good, less testing is needed

- use test data to examine program logic

batch controls

record counts, batch totals and hash totals

audit test for batch controls

reconcile transmittal records for batches to the batch control log

- access controls

- physical controls

- audit tests for both

process controls

access controls

- guards, alarms, safes

- paswords, firewalls

audit tests for access controls

- verify existence of firewall, data encryption and password controls

- test password controls by attempting to logon as an unauthorized user.

physical controls

- segregation of duties

- supervision and independent verification

- inventory custody from inventory recording

- cash receipts from AR recording and AR write-off authorization

revenue specific segregation of duties

audit test for physical controls

- review organizational structure

- review systems development and maintenance controls

output controls

- AR change report

- transaction log/listing

- error listing

reconcile AR to sales and cash receipts

sales - AR = cash receipts

AR change report

audit test for output controls

compare audit trail text files to output reports

- recording sales that didn't occur

- missing sales that did occur

- incorrect billing amounts

- failure to receive payments for a sale

- theft of inventory

- understate allowance for doubtful accounts

revenue cycle concerns

theft of inventory

what is a concern of both revenue and expenditure cycles

- review sales invoices for unusual trends and exceptions

- review sales invoice and shipping log files for missing and duplicate items

- review sales data and inventory for sale price accuracy

- test for unmatched records

substantive tests: completeness assertion

- scanning data to look for unusual transactions or account balances

- look to see sales that are highly suspicious

review sales invoices for unusual trends and exceptions by:

- looking for out-of-sequence records

- looking for gaps in sequence numbers

- looking for duplicates

review sales invoice and shipping log files for missing and duplicate items by:

merging sales data with inventory data and comparing sales price between the two

review sales data and inventory for sale price accuracy by:

Identifying inventory items in the sales data that are not in the inventory file

test for unmatched records by:

accounts receivable confirmation

substantive tests: existence assertion