Chapter 3: Data Management Systems

1. Chapter 3:Data Management Systems IT Auditing & Assurance, 2e, Hall & Singleton

2. 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

3. 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

4. CENTRALIZED DATABASE SYSTEM • Figure 3.3 [p.98]Database Environment • DBMS • Users • Database administrator • Physical database IT Auditing & Assurance, 2e, Hall & Singleton

5. DBMS • Typical features • Program development • Backup and recovery • Database usage reporting • Database access IT Auditing & Assurance, 2e, Hall & Singleton

6. 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

7. 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

8. 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

9. 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

10. DATABASE MODELS • Hierarchical • Network • Relational IT Auditing & Assurance, 2e, Hall & Singleton

11. RELATIONAL MODEL: 2-dimensional IT Auditing & Assurance, 2e, Hall & Singleton

12. RELATIONAL MODEL - TERMS • TABLE = file • COLUMN = field • ROW = record IT Auditing & Assurance, 2e, Hall & Singleton

13. 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

14. RULE #2 • “Consistency” applies to columnar values – same class IT Auditing & Assurance, 2e, Hall & Singleton

15. RULE #3 • Column names are distinct • Example “cost” for sales price and unit cost columns IT Auditing & Assurance, 2e, Hall & Singleton

16. RULE #4 • Each row contains distinctively different data from all other rows • Requires use of “key field(s)” IT Auditing & Assurance, 2e, Hall & Singleton

17. RELATIONAL MODEL • Figure 3-13, p. 112 IT Auditing & Assurance, 2e, Hall & Singleton

18. 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” DEADLOCK!! • Deadlock Resolution IT Auditing & Assurance, 2e, Hall & Singleton

19. DATABASE IN DDP • Distributed database • Partitioned • Replicated • Concurrency control • Classified • Time-stamps IT Auditing & Assurance, 2e, Hall & Singleton

20. 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 • Mother’s maiden name • Data encryption • Biometric devices • Inference controls (query) • example (p. 123) IT Auditing & Assurance, 2e, Hall & Singleton

21. 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

22. 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

23. 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

24. Chapter 3:Data Management Systems IT Auditing & Assurance, 2e, Hall & Singleton