BA 371 Business Systems Design - DB Administration

• Database processing environmnt:
• 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 system administration (OS-like).
• Kroenke: "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 (discussed and practiced in BA372).
• Triggers: stored procedures that execute on signal (discussed and practiced in BA372).

• Atomic transaction control:
• Commit and rollback: undo the former transactions because failure of the current transaction (will practice in BA372).
• 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., separate updates on the same record must be queued.
• 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 synchronized transactions.
• Disadvantage: paralyzing transaction wait queues when a lock does not get released.
• DIY: makes you very powerful; you better know what you're doing.

• Read and/or write locks.
• Deadlock.
• Isolation levels and problem types.

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

• Protection from system failures (backup 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.