Concepts of Database Management, Fifth Edition

1. Concepts of Database Management, Fifth Edition Chapter 7: DBMS Functions

2. 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

3. 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

4. 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

5. 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

6. Figure 7.1: Adding Record Concepts of Database Management, 5th Edition

7. Figure 7.2:Updating Record Concepts of Database Management, 5th Edition

8. Figure 7.3: Retrieving Data Concepts of Database Management, 5th Edition

9. 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

10. Support Concurrent Update • Ensures accuracy when several users update database at same time • Manages complex scenarios for updates Concepts of Database Management, 5th Edition

11. 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

12. 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

13. 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

14. 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

15. Avoiding Lost Updates • Prohibit shared update • Use batch processing • Implement locking scheme Concepts of Database Management, 5th Edition

16. Figure 7.7: Delaying Updates Concepts of Database Management, 5th Edition

17. 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

18. 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

19. Figure 7.8b: Locking Scheme (con’t.) DBMS updates database with Elena’s change DBMS unlocks record Concepts of Database Management, 5th Edition

20. 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

21. 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

22. Figure 7.9: Deadlock Concepts of Database Management, 5th Edition

23. 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

24. Figure 7.12: Forward Recovery Concepts of Database Management, 5th Edition

25. Figure 7.13:Backward Recovery Concepts of Database Management, 5th Edition

26. 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

27. 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

28. Figure 7.21: Integrity Constraints in a DBMS Concepts of Database Management, 5th Edition

29. 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

30. 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

31. Figure 7.22:Replicas From Master Database Concepts of Database Management, 5th Edition

32. Figure 7.23:DBMS Synchronizes Databases Concepts of Database Management, 5th Edition

33. 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

34. 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

35. 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

36. 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