360 likes | 566 Vues
Concepts of Database Management, Fifth Edition. Chapter 7: DBMS Functions. Objectives. Introduce the functions, or services, provided by a DBMS Describe how a DBMS handles updating and retrieving data Examine the catalog features of a DBMS
E N D
Concepts of Database Management, Fifth Edition Chapter 7: DBMS Functions
Objectives • Introduce the functions, or services, provided by a DBMS • Describe how a DBMS handles updating and retrieving data • Examine the catalog features of a DBMS • Illustrate the concurrent update problem and describe how a DBMS handles this problem • Explain the data recovery process in a database environment Concepts of Database Management, 5th Edition
Objectives • Describe the security services provided by a DBMS • Examine the data integrity features provided by a DBMS • Discuss the extent to which a DBMS achieves data independence • Define and describe data replication • Present the utility services provided by a DBMS Concepts of Database Management, 5th Edition
Nine Functions of a DBMS • Update and retrieve data • Provide catalog services • Support concurrent update • Recover data • Provide security services • Provide data integrity features • Support data independence • Support data replication • Provide utility services Concepts of Database Management, 5th Edition
Update and Retrieve Data • Fundamental capability of a DBMS • Users don’t need to know how data is stored or manipulated • Users add, change, and delete records during updates • Users view and manipulate data during retrieval Concepts of Database Management, 5th Edition
Figure 7.1: Adding Record Concepts of Database Management, 5th Edition
Figure 7.2:Updating Record Concepts of Database Management, 5th Edition
Figure 7.3: Retrieving Data Concepts of Database Management, 5th Edition
Provide Catalog Services • Stores data about data • Contains descriptions of database components • Often hidden from users • Used by database administrators and programmers • Data dictionary in larger DBMSs Concepts of Database Management, 5th Edition
Support Concurrent Update • Ensures accuracy when several users update database at same time • Manages complex scenarios for updates Concepts of Database Management, 5th Edition
Figure 7.4: Ryan Updates the Database Before update DBMS reads data from database into RAM for Ryan Ryan changes data in RAM DBMS updates database with Ryan’s change Concepts of Database Management, 5th Edition
Figure 7.5: Elena Updates the Database After Ryan’s update and before Elena’s DBMS reads database data into RAM for Elena Elena changes data in RAM DBMS updates database with Elena’s change Concepts of Database Management, 5th Edition
Figure 7.6a: Lost Update Database before updates DBMS reads database data into RAM for Ryan DBMS reads database data into RAM for Elena Ryan changes data in RAM Concepts of Database Management, 5th Edition
Figure 7.6b: Lost Update (con’t.) Elena updates data in RAM DBMS updates database with Ryan’s change DBMS updates database with Elena’s change; Ryan’s update is lost! Concepts of Database Management, 5th Edition
Avoiding Lost Updates • Prohibit shared update • Use batch processing • Implement locking scheme Concepts of Database Management, 5th Edition
Figure 7.7: Delaying Updates Concepts of Database Management, 5th Edition
Figure 7.8a: Locking Scheme Database before updates DBMS reads database data into RAM for Ryan and locks record Elena requests same record and request fails Ryan changes data in RAM; Elena’s request for same record again fails Concepts of Database Management, 5th Edition
Figure 7.8b: Locking Scheme (con’t.) DBMS updates database with Ryan’s change; Elena’s request for same record again fails DBMS unlocks record; DBMS reads database data into RAM for Elena and locks record Elena changes data in RAM Concepts of Database Management, 5th Edition
Figure 7.8b: Locking Scheme (con’t.) DBMS updates database with Elena’s change DBMS unlocks record Concepts of Database Management, 5th Edition
Locking Schemes • Two-Phase Locking • Locks are held until required updates completed • Deadlock • Occurs when two users hold a lock and require a lock on the resource that the other already has • DBMS chooses method to break deadlock • One user becomes ‘victim’ • Locking on PC-Based DBMSs • Table or row locked, not both • Usually more limited than locking facilities on mainframe DBMSs Concepts of Database Management, 5th Edition
Locking Schemes (con’t.) • Timestamping • DBMS assigns each database update a unique time when the update started • Avoids the need to lock rows • Eliminates processing time needed to apply and release locks • Helps detect and resolve deadlocks Concepts of Database Management, 5th Edition
Figure 7.9: Deadlock Concepts of Database Management, 5th Edition
Recovery • Mechanism for recovering damaged database • Recovery: return of database to correct state • Simplest recovery involves using backups • Other recovery methods: • Journaling • Forward recovery • Backward recovery • PC-based Concepts of Database Management, 5th Edition
Figure 7.12: Forward Recovery Concepts of Database Management, 5th Edition
Figure 7.13:Backward Recovery Concepts of Database Management, 5th Edition
Provide Security Services • Prevention of unauthorized access • Encryption - converts data to indecipherable form • Authentication - identification of DBMS user, often with passwords or biometrics • Authorizations - rules to specify data available to certain users • Views • Privacy Concepts of Database Management, 5th Edition
Provide Data Integrity Features • Rules followed to ensure data is accurately and consistently updated • Key integrity • Foreign key and primary key constraints • Data integrity • Data type • Legal values • Format Concepts of Database Management, 5th Edition
Figure 7.21: Integrity Constraints in a DBMS Concepts of Database Management, 5th Edition
Support Data Independence • Programs must be independent of database structure • Considerations • Adding a field • Changing length of field • Creating an index • Adding or changing a relationship Concepts of Database Management, 5th Edition
Support Data Replication • Manage multiple copies of same data in multiple locations • Maintained for performance or other reasons • Ease of access and portability Concepts of Database Management, 5th Edition
Figure 7.22:Replicas From Master Database Concepts of Database Management, 5th Edition
Figure 7.23:DBMS Synchronizes Databases Concepts of Database Management, 5th Edition
Provide Utility Services • Assist in general database maintenance • Permit changes to database structure • Permit addition and deletion of indexes • Provide access to operating system services • Support for queries, screen generators, and report generators Concepts of Database Management, 5th Edition
Provide Utility Services (con’t.) • Provide support for embedded procedures • Procedural • Nonprocedural • Provide easy-to-use, menu-driven interface Concepts of Database Management, 5th Edition
Summary • A DBMS provides update and data retrieval functions • DBMS catalog service • Support concurrent update • Prevent shared update, locking systems, timestamp updates • Recovery services allow a database to overcome unexpected failures Concepts of Database Management, 5th Edition
Summary • Security Services ensure users don’t have access to data they are not authorized to view, update or delete • Data replication services allow you to maintain copies of the data closer to where you use it or to provide for disaster recovery • Your DBMS will provide a set of utility services, such as reporting and access to operating system services Concepts of Database Management, 5th Edition