Week 10 saw us move away from the more technical SQL and Indexing topics to ‘Database administration, Transaction management and Concurrency Control’.
Responsibilities of DBA:
- Control of redundancy
- Use of standards for modelling
- Maintaining data dictionary
- Ensuring sufficient normalization
- Managing sharing of data between multiple applications
- Control of Integrity Constraints
- Defining appropriate integrity constraints
- Ensuring applications meet with the impose constraints
- Ensuring applications cannot subvert constraints
- Control of central data security
- Provision relevant user views
- Issuing passwords
- reviewing physical security
- design and monitoring of back up procedures
Also important duties are:
- Query Optimization
- Clusters, Indexes
Manoj then continued into the nature transactions, crashes and concurrency control. A quick summary below:
- Transactions
- A single or number of processes involving database accesses that constitutes one single logical action (i.e. bank transfer). Atomic (indivisible, can’t separate processes), Durable (upon completion, stored permanently).
- Transactions can be committed or aborted (rollback, utilizing before-image log)
- Checkpoints are periodic (usually 15-20mins) batch writes of transactions to disk.
- Soft Crashes
- I.e. Power failure, where system resets but no physical damage.
- After soft crash, secondary memory data is loaded, then transaction log is referred to and transaction missing from secondary memory are either:
- Redone (REDO), if the transaction was completed and can be re-completed this will be done
- Undone (UNDO) if the transaction was not completed (committed) in the log then it must be undone (transactions are atomic).
