diff options
| -rw-r--r-- | assignments/3/README.md | 14 | ||||
| -rw-r--r-- | doc/unit-8.md | 109 |
2 files changed, 122 insertions, 1 deletions
diff --git a/assignments/3/README.md b/assignments/3/README.md index e09cfbb..0590027 100644 --- a/assignments/3/README.md +++ b/assignments/3/README.md @@ -69,21 +69,33 @@ Suggest an appropriate recovery technique that a database administrator could us **A network disconnection occurs while a user is entering a transaction at an ATM bank machine.** -Rollback the transaction. +Perform a `Backward Recovery` i.e. rollback the transaction. **A disk drive fails during regular operations while a clerk is entering data about newly registered students.** +If disk mirroring is enabled then one of the secondary nodes can be promoted to become the new primary. +If mirroring is not enabled, then replacing the drive and restoring from the most recent backup can +help to restore service. The failed drive can be analyzed to see what bin/transaction logs can be +recovered so that they can be replayed against the new primary from the time of the last backup. + **The registration office at a university entered an incorrect amount for a student tuition payment. The error was discovered by the financial services department several weeks later.** +Apply a compensating transaction to correct the error. + **The database administrator of a financial institution performed a full database backup, but forgot to activate the journalizing facility. Afterwards, data entry clerks at the financial institution entered transactions for two hours before the database became corrupt. It is discovered that the journalizing facility of the database has not been activated since the backup was made.** +Restore the database to the most recent database backup and apply the after images from the database change log. + ## Question 3 Suggest the most appropriate security measures for each of the situations described below. **The Western Union bank uses an electronic funds transfer (EFT) system to transmit sensitive financial data between its branches all over the world.** + **A fighter jet simulation company has set up an off-site computer-based training centre for the F35. The company wishes to restrict access to the site to authorized employees. Since each employee's use of the centre is occasional, it does not wish to provide the employees with keys to access the centre.** + **A golf club uses a simple password system to protect its database. The club has created a new Web site to allow both its members and its employees to use the new Web-based system to access and update information. The club finds that it needs a more comprehensive security system to grant different privileges (such as read-only versus create or update) to different users.** + **A training centre at the Northern Technical University has experienced considerable difficulty with unauthorized users who access files and databases by appropriating passwords from legitimate users.** ## Question 4 diff --git a/doc/unit-8.md b/doc/unit-8.md index ce9eb25..3a27e48 100644 --- a/doc/unit-8.md +++ b/doc/unit-8.md @@ -25,3 +25,112 @@ When you have completed this section, you should be able to * discuss the types of database failures and the process for disaster recovery; * outline different mechanisms used for concurrency control; * list and discuss the core functions supported by repository engines. + +> Database administration: a technical function that is responsible for physical database design with technical issues, +such as security enforcement, database performance and backup and recovery. + +* analzye and design database +* select dbms and related tools +* installing and upgrading the dbms +* tuning database performance +* improving database query processing performance +* managing data security, privacy and integrity +* performing data backup and recovery + +> Open source DBMS: Free DBMS source code software that provides the core functionality of an SQL-compliant DBMS. + + +> Database security: Protection of database data against accidental or intentional loss, destruction, or misuse. + +Threats: + +* accidental loss: human error, software/hardward caused breaches +* theft and fraud: +* loss of privacy or confidentiality: +* loss of data integrity: +* loss of availability: + +> Authorization rules: controls incorporated in a data management system that restrict access to data and also restrict the actions that people may take when they access data. + +Sarbanes-Oxley (SOX) + +1. IT change management +2. Logical access to data +3. IT operations + +> Database recovery: mechanisms for restoring a database quickly and accurately after loss or damage. + +Basic recovery facilities: + +1. Backup facilities: which provide periodic backup copies of portions of or the entire database. +2. Journalizing facilities: which maintain an audit trail of transactions and database changes. +3. A checkpoint facility: by which the DBMS periodically suspends all processing and synchronizes its files and journals to establish a recovery point. +4. A recovery manager: which allows the DBMS to restore the database to a correct condition and restart processing transactions. + +> Backup facility: A DBMS COPY utility that produces a backup copy of an entire database or a subset of a database. + +> Journalizing facility: An audit trail of transactions and database changes. + +In the event of a failure, a consistent database state can be reestablished using the info +in the journals with the most recent backup. + +1. transaction log: contains a record of the essential data for each transaction that is processed. +2. database change log: contains before and after images of records that have been modified by transactions. + +> Transaction: A discrete unit of work that must be completely processed or not processed at all within a computer system. + +> Checkpoint facility: A facility by which a DBMS periodically refuses to accept any new transaction. The system is in a quiet state, and the database and transaction logs are synchronized. + +> Recovery Manager: A module of a DBMS that restores the database to a correct condition when a failure occurs and then resumes processing user questions. + + +Recover and restart: + +1. Disk Mirroring: Primary + replicas. Promote replica to primary if primary has outage. +2. Restore/Rerun: reprocess day's transactions against the backup copy of the database or portion of the database. + +ACID Transactions + +1. Atomic: process all of the transaction or nothing. +2. Consistent: any db constrants that were true before the trx must be true after the trx. +3. Isolated: changes to db are not revealed to users until trx is committed. +4. Durable: changes are permanent. + +> Transaction boundaries: The logical beginning and end of a transaction. + +Backward Recovery (rollback) + +The backout, or undo, of unwanted changes to a database. Before images of the records that have +been changed are applied to the database, and the database is returned to an earlier state. +Rollback is used to reverse the changes made by transaction that have been aborted, or terminated abnormally. + +Forward Recovery + +A technique that starts with an earlier copy of a database then plays the `after images` of +good transactions. + +1. Time consuming logic of reprocessing each transaction does not have to be repeated. +2. Only th emost recent after images need to be applied. + +Types of failure: + +1. Aborted transactions: a transaction in progress that terminates abnormally. +2. Incorrect data: db is updated with incorrect, but valid, data. + +Responses to failure: + +1. Aborted transaction: + * rollback + * rollforward/return transactions to state just prior to abort +2. Incorrect data + * Rollback + * Reprocess transaction without inaccurate data updates + * Compenstating transactions +3. System failure + * switch to duplicate database + * rollback + * restart from checkpoint +4. Database destruction + * Promote replica to primary + * roll forward + * reprocess transactions |