1 / 21

Data and Database Administration

Data and Database Administration. Definitions. Data Administration: A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards.

delany
Download Presentation

Data and Database Administration

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data and Database Administration

  2. Definitions • Data Administration: A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards. • Database Administration: A technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery.

  3. Data Administration Functions • Data policies, procedures, standards. • Planning. • Data conflict (ownership) resolution. • Internal marketing of DA concepts. • Managing the data repository.

  4. Database Administration Functions • Selection of hardware and software. • Managing data security, privacy, and integrity. • Data backup and recovery. • Fig. 13-1 is a list of DA and DBA functions.

  5. Pg 492 Data Security • Data Security: Protection of the data against accidental or intentional loss, destruction, or misuse. • Possible locations of data security threats

  6. Threats to Data Security • Accidental losses attributable to: • Human error. • Software failure. • Hardware failure. • Theft and fraud. • Improper data access: • Loss of privacy (personal data). • Loss of confidentiality (corporate data). • Loss of data integrity. • Loss of availability (through, e.g. sabotage).

  7. Data Management Software Security Features • Views or subschemas. • Authorization rules. • Fig. 13-3 (right) , 4, 5. • User-defined procedures. E.g. trigger an authorization procedure which asks additional identification questions. • Encryption. • Authentication schemes. • Biometric devices.

  8. Backup and RecoveryBasic Recovery Facilities • Backup Facilities • Journalizing Facilities • Fig. 13-6 (right) • Transaction Log - time, records, input values. • Database Change Log - before & after images. • Checkpoint Facility • Restart point after a failure. • Recovery Manager Fig 13-6: Database audit trail

  9. Recovery and Restart Procedures • Switch - Mirrored databases. • Restore/Rerun - Reprocess transactions against the backup. • Transaction Integrity - Commit or abort all transaction changes. (trans boundaries) • Backward & forward recovery follow….

  10. Pg 502 Basic recovery techniques (a) Rollback Backward Recovery - Apply before images. Undo unwanted changes

  11. Pg 502 (b) Rollforward Forward Recovery - Apply after images (preferable to restore/rerun.)

  12. Pg 503 Types of Database Failure • Table. 13-1. • Aborted transactions. • Incorrect data. (update inaccurate) • System failure (database intact.) • Database destruction

  13. Concurrency Control • Concurrency Control - The process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multi-user environment. • Lost Update Problem - Fig. 13-8. • -- refer to html notes --

  14. Concurrency Control Techniques • Serializability - Finish one transaction before starting another. • Locking Mechanisms - Fig. 13-10. • Locking level: • Database • Table • Block or page • Record • Field

  15. Concurrency Control Techniques • Types of locks: • Shared lock - Read but not update permitted. Used when just reading to prevent another user from placing an exclusive lock on the record. • Exclusive lock - No access permitted. Used when preparing to update.

  16. Deadlock • Deadlock - Transactions wait for each other to release needed resources. Fig. 13-10, 11. • Deadlock prevention: • Lock all records required at the beginning of a transaction • Two-phase locking protocol • Growing phase. • Shrinking phase. • May be difficult to determine all needed resources in advance.

  17. Deadlock • Deadlock Resolution: • Allow deadlocks to occur. • Mechanisms for detecting and breaking them. • Resource usage matrix.

  18. Concurrency Control Techniques • Versioning: • Optimistic approach to concurrency control. • Assumption is that simultaneous updates will be infrequent. • Each transaction can attempt an update as it wishes. • The system will reject an update when it senses a conflict. • Fig. 13-12.

  19. Managing Data Quality • Data Steward - Liaisons between IT and business units. • Five Data Quality Issues: • Security policy and disaster recovery. • Personnel controls. • Physical access controls. • Maintenance controls (hardware & software.) • Data protection and privacy.

  20. Data Dictionaries and Repositories • Passive data dictionary. • Active data dictionary. (managed by DBMS) • System catalog. • all DB objects, including data dictionary and user access info • Information Repository (CASE tools). • Information Repository Dictionary System (IRDS)- Manages access- standards for dictionary storage and access.

  21. Performance tuning the database • Correct installation • Memory usage • I/O Contention • CPU Usage • Application tuning • examine SQL

More Related