BA370 - Data Base Administration (Kroenke, Ch. 6)

• Database processing env. (p. 135):
• producers:
• Transaction processing
• Forecasting
• Data warehousing (ETL)
• Administration

• consumers:
• Reporting.
• Forecasting (model initialization).
• Data warehousing (ETL)
• Administration

• multiple, simultaneous  users (mostly all programs; some people).
• multiple, simultaneous requests.

• ==> need for internal system administration (OS-like).
• Kroenke (p. 13) "A database is a self-describing collection of related records."
• Metadata or data dictionary.
• Tables, their ownership, and access privileges.
• Processes accessing the data.
• Users.
• Integrity constraints.
• Stored procedures (ch. 6, p. 136; Oracle PL/SQL example in Fig. 6-2).
• Triggers: stored procedures that execute on signal (Oracle: before, after and instead of triggers).

• Atomic transaction control:
• Commit and rollback: undo the former transactions because failure of the current transaction (p. 138).
• Careful: (Oracle) rollback segments have a finite size.

• Concurrency control:
• Certain transactions can (logically) occur simultaneously; others cannot:
• Database reads can generally occur simultaneously
• Database writes may or may not; e.g. two different updates on the same record (Fig 6.5).
• Database writes can interfere with database reads:
• Dirty read: read of a record that has not yet been committed --> leads to a different value if the write gets rolled back.
• Inconsistent or nonrepeatable read: reread that occurs after a write.
• Phantom read: reread that finds new records inserted.

• General mechanism to enforce order: locking:
• Implicit locks: placed by DMBS.
• Explicit locks: placed by command.

• Advantages: full control of synchronizes transactions.
• Disadvantage: potentially enormous queues when a lock does get released.
• DIY: makes you very powerful;: you better know what you're doing.

• Read and/or write locks.
• Deadlock (fig. 6.7: buying pencils and paper or paper and pencils).
• Isolation levels and problem types: fig. 6.10.

• Database Security:
• Protection from the environment:
• Firewall.
• Disable unused stored procedures.
• Disable unneeded protocols.
  
• Separate out the DBMS server.
• Users talk to (your) apps and apps talk to DMBS.
• Define groups of users as roles.
• Grant permissions to roles.

• Protection from system failures (bacup and recovery)
• Regular system backup is a good thing:
• Hot vs. cold backups.
• Change log (Oracle: redo logs) --> rollforward (redo) / rollback
• Careful: redo logs have finite size (Oracle: revolve).

• Crash recovery plan:
• Documented crash recovery procedure.
• Crash recovery testing (have to give up your weekend, Thanksgiving and/or Xmas eve).

• Other DBA tasks:
• Application error diagnosis, testing, bug fixing.
• Periodic software updates.
• Continuous performance testing.
• process for DB schema changes.