DBMS Functions

1
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

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

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

5
Update and Retrieve Data

• Fundamental capability of a DBMS
• Users dont need to know how data is stored or
  manipulated
• Users add, change, and delete records during
  updates
• Users view and manipulate data during retrieval

6
Adding RecordFigure 7.1
7
Updating RecordFigure 7.2
8
Retrieving DataFigure 7.3
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

10
Support Concurrent Update

• Ensures accuracy when several users update
  database at same time
• Manages complex scenarios for updates

11
Ryan Updates the Database Figure 7.4
Before update
DBMS reads data from database into RAM for Ryan
Ryan changes data in RAM
DBMS updates database with Ryans change
12
Elena Updates the Database Figure 7.5
After Ryans update and before Elenas
DBMS reads database data into RAM for Elena
Elena changes data in RAM
DBMS updates database with Elenas change
13
Lost Update Figure 7.6a
Database before updates
DBMS reads database data into RAM for Ryan
DBMS reads database data into RAM for Elena
Ryan changes data in RAM
14
Lost Update (cont.) Figure 7.6b
Elena updates data in RAM
DBMS updates database with Ryans change
DBMS updates database with Elenas change Ryans
update is lost!
15
Avoiding Lost Updates

• Prohibit shared update
• Use batch processing
• Implement locking scheme

16
Delaying Updates Figure 7.7
17
Locking Scheme Figure 7.8a
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 Elenas request for
same record again fails
18
Locking Scheme (cont.) Figure 7.8b
DBMS updates database with Ryans change Elenas
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
19
Locking Scheme (cont.) Figure 7.8b
DBMS updates database with Elenas change
DBMS unlocks record
20
Locking Schemes

• Two-Phase Locking
• Locks are held until required updates completed
• Deadlock
• Occurs when two users hold more than one lock at
  a time
• 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

21
Locking Schemes (cont.)

• 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

22
Deadlock Figure 7.9
23
Recovery

• Mechanism for recovering damaged database
• The return of database to correct state is called
  recovery
• Simplest recovery involves using backups
• Other recovery methods
• Journaling
• Forward recovery
• Backward recovery
• PC-based

24
Forward RecoveryFigure 7.12
25
Backward RecoveryFigure 7.13
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

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

28
Integrity Constraints in a DBMS Figure 7.21
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

30
Support Data Replication

• Manage multiple copies of same data in multiple
  locations
• Maintained for performance or other reasons
• Ease of access and portability

31
Replicas From Master DatabaseFigure 7.22
32
DBMS Synchronizes DatabasesFigure 7.23
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

34
Provide Utility Services (cont.)

• Provide support for embedded procedures
• Procedural
• Nonprocedural
• Provide easy-to-use, menu-driven interface