3:
Data Chapter Management Systems
IT Auditing & Assurance, 2e, Hall & Singleton
DATA-FLAT FILES
e.g., Figure 3.1 [p.94] Disadvantages
Data storage Data updating Currency of information Task-data dependency (limited access) Data integration (limited inclusion)
Do not use accounting data to support decisions Manipulate existing data to suit unique needs Obtain additional private sets of data, incurring costs and operational problems
IT Auditing & Assurance, 2e, Hall & Singleton
DATA-DATABASE
e.g., Figure 3.2 [p.96] How database approach eliminates the five disadvantages of flat files
Data storage Data updates Currency of information Task-data dependency (limited access) Data integration (limited inclusion)
IT Auditing & Assurance, 2e, Hall & Singleton
CENTRALIZED DATABASE SYSTEM
Figure 3.3 [p.98] Database Environment
DBMS Users Database administrator Physical database
IT Auditing & Assurance, 2e, Hall & Singleton
DBMS
Typical features
Program development Backup and recovery Database usage reporting Database access
IT Auditing & Assurance, 2e, Hall & Singleton
DBMS
Data definition language (DDL) Views
Figure 3.4 [p.99] Internal / physical view Conceptual / logical view External / user view
IT Auditing & Assurance, 2e, Hall & Singleton
USERS
Formal access: application interfaces Data manipulation language (DML) DBMS operations: 7 steps [Figure 3.4] Informal access: query
Define query SQL is industry de facto standard query language Select, from, where commands Review Figure 3.5 [p.101] SQL process QBE
IT Auditing & Assurance, 2e, Hall & Singleton
DBA
DBA
Manages the database resources Table 3.1 [p.102]
Database planning Database design Database implementation Database operations & maintenance Change & growth
Data dictionary Interactions [Figure 3-6, p.103]
IT Auditing & Assurance, 2e, Hall & Singleton
PHYSICAL DATABASE
Data structures Data organization
Sequential Random
Data access methods Data hierarchy
Attribute/field Record
Associations
File Database Enterprise database
IT Auditing & Assurance, 2e, Hall & Singleton
DATABASE MODELS
Hierarchical Network Relational
IT Auditing & Assurance, 2e, Hall & Singleton
RELATIONAL MODEL: 2-dimensional
IT Auditing & Assurance, 2e, Hall & Singleton
RELATIONAL MODEL TERMS TABLE = file COLUMN = field ROW = record
IT Auditing & Assurance, 2e, Hall & Singleton
RULE #1
Entries in the table cells MUST be single-valued Cannot be null Cannot be multi-values Example
IT & Auditing Assurance, 2e, Hall & Singleton
RULE #2
Consistency applies to columnar values same class
IT Auditing & Assurance, 2e, Hall & Singleton
RULE #3
Column names are distinct Example cost for sales price and unit cost columns
IT Auditing & Assurance, 2e, Hall & Singleton
RULE #4
Each row contains distinctively different data from all other rows Requires use of key field(s)
IT Auditing & Assurance, 2e, Hall & Singleton
RELATIONAL MODEL
Figure 3-13, p. 112
IT Auditing & Assurance, 2e, Hall & Singleton
DATABASE IN DDP
Data concurrency problem
Deadlock (illustrated in Figure 3-17, p. 118)
Time 1: User 1 loads File A, User 2 loads File C User 3 loads File E Time 2: User 1 locks File A, User 2 locks File C, User 3 locks File E Time 3: User 1 tries to load File C wait User 2 tries to load File E wait Use 3 tries to load File A wait
DEADL OCK !!
Deadlock Resolution
IT Auditing & Assurance, 2e, Hall & Singleton
DATABASE IN DDP
Distributed database
Partitioned Replicated
Concurrency control
Classified Time-stamps
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING DBMS
Access controls
User views / subschema [see Figure 3-20, p.121]
Database authorization table [Table 3-3,
p.122]
User-defined procedures
Mothers maiden name
Data encryption Biometric devices Inference controls (query)
example (p. 123)
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING DBMS: Audit Procedures
OBJECTIVE: Verify that database access authority and privileges are granted to users in accordance with legitimate needs.
Tables and subschemas
Review policy and job descriptions Examine programmer authority tables for access to DDL Interview programmers and DBA
Appropriate access authority Biometric controls Inference controls Encryption controls
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING DBMS: Audit Procedures
OBJECTIVE: Verify that backup controls in place are effective in protecting data files from physical damage, loss, accidental erasure, and data corruption through system failures and program errors.
Backups Logs Checkpoint Recovery module
IT Auditing & Assurance, 2e, Hall & Singleton
CONTROLLING & AUDITING DBMS: Audit Procedures
OBJECTIVE: Verify that controls over the data resource are sufficient to preserve the integrity and physical security of the database.
IT Auditing & Assurance, 2e, Hall & Singleton
Chapter 3:
Data Management Systems
IT Auditing & Assurance, 2e, Hall & Singleton