Concept of Database Management 8th Edition Solutions
Concepts of Database Management Eighth 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 feature 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 © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 2
Objectives (continued) • 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 © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 3
Introduction • 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 © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 4
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 © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 5
Update and Retrieve Data (continued) FIGURE 7 -1: Adding a new item to the TAL Distributors database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 6
Update and Retrieve Data (continued) FIGURE 7 -2: Changing the price of an item in the TAL Distributors database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 7
Update and Retrieve Data (continued) FIGURE 7 -3: Retrieving a balance amount from the TAL Distributors database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 8
Provide Catalog Services • Metadata: data about data • Stores metadata and makes it accessible to users • Enterprise DBMSs often have a data dictionary (a super catalog) © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 9
Support Concurrent Update • Ensures accuracy when several users update database at the same time • Manages complex scenarios for updates • Concurrent update: multiple users make updates to the same database at the same time © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 10
The Concurrent Update Problem FIGURE 7 -4: Ryan updates the database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 11
The Concurrent Update Problem (continued) FIGURE 7 -5: Elena updates the database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 12
The Concurrent Update Problem (continued) FIGURE 7 -6: Ryan's and Elena's updates to the database result in a lost update © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 13
The Concurrent Update Problem (continued) FIGURE 7 -6: Ryan's and Elena's updates to the database result in a lost update (continued) © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 14
Avoiding the Lost Update Problem • Batch processing – All updates done through a special program – Problem: data becomes out of date – Does not work in situations that require data to be current © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 15
Avoiding the Lost Update Problem (continued) FIGURE 7 -7: Delaying updates to the TAL Distributors database to avoid the lost update problem © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 16
Two-Phase Locking • Locking: deny other users access to data while one user's updates are being processed • Transaction: set of steps completed by a DBMS to accomplish a single user task • Two-phase locking solves lost update problem – Growing phase: DBMS locks more rows and releases none of the locks – Shrinking phase: DBMS releases all the locks and acquires no new locks © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 17
Two-Phase Locking (continued) FIGURE 7 -8: The DBMS uses a locking scheme to apply Ryan's and Elena's updates to the database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 18
Two-Phase Locking (continued) FIGURE 7 -8: The DBMS uses a locking scheme to apply Ryan's and Elena's updates to the database (continued) © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 19
Two-Phase Locking (continued) FIGURE 7 -8: The DBMS uses a locking scheme to apply Ryan's and Elena's updates to the database (continued) © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 20
Deadlock • Deadlock or deadly embrace – Two users hold a lock and require a lock on the resource that the other already has – To minimize occurrence, make sure all programs lock records in the same order whenever possible • Managing deadlocks – DBMS detects and breaks any deadlock – DBMS chooses one user to be the victim © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 21
Deadlock (continued) FIGURE 7 -9: Two users experiencing deadlock © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 22
Locking on PC-Based DBMSs • Usually more limited than locking facilities on enterprise DBMSs • Programs can lock an entire table or an individual row within a table, but only one or the other • Programs can release any or all of the locks they currently hold • Programs can inquire whether a given row or table is locked © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 23
Timestamping • DBMS assigns each database update a unique time (timestamp) when the update started • Advantages – Avoids need to lock rows – Eliminates processing time needed to apply and release locks and to detect and resolve deadlocks • Disadvantages – Additional disk and memory space – Extra processing time © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 24
Recover Data • Recovery: returning database to a correct state from an incorrect state • Simplest recovery involves using backups – Backup or save: copy of database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 25
Journaling • Journaling: maintaining a journal or log of all updates – Log is available even if database is destroyed • Information kept in log for each transaction: – – – Transaction ID Date and time of each update Before image After image Start of a transaction Successful completion (commit) of a transaction © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 26
Journaling (continued) FIGURE 7 -10: Four sample transactions © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 27
Journaling (continued) FIGURE 7 -11: Sample log in which all four transactions commit normally © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 28
Forward Recovery • DBA executes a DBMS recovery program • Recovery program applies after images of committed transactions from log to database • Improving performance of the recovery program – Apply the last after image of a record © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 29
Forward Recovery (continued) FIGURE 7 -12: Forward recovery © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 30
Backward Recovery • Database not in a valid state – Transactions stopped in midstream – Incorrect transactions • Backward recovery or rollback – Undo problem transactions – Apply before images from log to undo their updates © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 31
Backward Recovery (continued) FIGURE 7 -13: Backward recovery © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 32
Recovery on PC-Based DBMSs • Sophisticated recovery features not available on PC -based DBMSs • Regularly make backup copies using DBMS – Use most recent backup for recovery • Systems with large number of updates between backups – Recovery features not supplied by DBMS need to be included in application programs © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 33
Provide Security Services • Security: prevention of unauthorized access, either intentional or accidental, to a database • Most common security features used by DBMSs: – – Encryption Authentication Authorizations Views © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 34
Encryption • Encryption: converts data to a format indecipherable to another program and stores it in an encrypted format • Encryption process is transparent to a legitimate user • Decrypting: reversing the encryption • In Access, encrypt a database with a password © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 35
Authentication • Authentication: techniques for identifying the person attempting to access the DBMS • Password: string of characters assigned by DBA to a user that must be entered for access • Biometrics: identify users by physical characteristics such as fingerprints, voiceprints, handwritten signatures, and facial characteristics • Smart cards: small plastic cards with built-in circuits containing processing logic to identify the cardholder © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 36
Authentication (continued) • Database password: string of characters assigned to database that users must enter for accessing the database FIGURE 7 -14: Assigning a database password to the TAL Distributors database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 37
Authorizations • DBA can use authorization rules to specify which users have what type of access to which data • Permissions: specify what kind of access the user has to objects in the database • Workgroups: groups of users © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 38
Views • View: snapshot of certain data in the database at a given moment in time • Can be used for security purposes © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 39
Privacy • Privacy: right of individuals to have certain information about them kept confidential • Laws and regulations dictate some privacy rules • Companies institute additional privacy rules © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 40
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 © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 41
Provide Data Integrity Features (continued) • Four ways of handling integrity constraints: 1. Constraint is ignored 2. Responsibility for constraint enforcement placed on users 3. Responsibility for constraint enforcement placed on programmers 4. Responsibility for constraint enforcement placed on DBMS © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 42
Provide Data Integrity Features (continued) FIGURE 7 -16: Example of integrity constraints in Access © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 43
Support Data Independence • Data independence: can change database structure without needing to change programs that access the database • Types of changes: – – Adding a field Changing a field property (such as length) Creating an index Adding or changing a relationship © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 44
Adding a Field • Don't need to change any program except those programs using the new field • SQL SELECT * FROM command will present an extra field – Solution: list the required fields in an SQL SELECT command instead of using * © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 45
Changing the Length of a Field • Generally, don't need to change programs • Need to change the program if: – Certain portion of screen or report is set aside for the field and the space cannot fit the new length © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 46
Creating an Index • To create an index, enter a simple SQL command or select a few options • Most DBMSs use the new index automatically • For some DBMSs, need to make minor changes in already existing programs © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 47
Adding or Changing a Relationship • Trickiest of all • May need to restructure database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 48
Support Data Replication • Replicated: duplicated • Manage multiple copies of same data in multiple locations • Maintained for performance or other reasons • Ease of access and portability • Replicas: copies • Synchronization: DBMS exchanges all updated data between master database and a replica © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 49
Support Data Replication (continued) FIGURE 7 -18: DBMS synchronizes two databases in a replica set © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 50
Provide Utility Services • Utility services assist in general database maintenance • Change database structure • Add new indexes and delete indexes • Use services available from operating system • Export and import data • Support for easy-to-use edit and query capabilities, screen generators, report generators, etc. © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 51
Provide Utility Services (continued) • Support for procedural and nonprocedural languages – Procedural language: must tell computer precisely how a given task is to be accomplished – Nonprocedural language: describe task you want computer to accomplish • Easy-to-use menu-driven or switchboard-driven interface © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 52
Summary • DBMS allows users to update and retrieve data in a database without needing to know how data is structured on disk or manipulated • DBMS must store metadata (data about the data) and make this data accessible to users • DBMS must support concurrent update • Locking denies access by other users to data while DBMS processes one user's updates • During deadlock and deadly embrace, two or more users are waiting for the other user to release a lock before they can proceed © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 53
Summary (continued) • In timestamping, DBMS processes updates to a database in timestamp order • DBMS must provide methods to recover a database in the event the database is damaged • DBMSs provide facilities for periodically making a backup copy of the database • Enterprise DBMSs maintain a log or journal of all database updates since the last backup; log is used in recovery process © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 54
Summary (continued) • DBMSs provide security features (encryption, authentication, authorizations, and views) to prevent unauthorized access to a database • DBMS must follow rules or integrity constraints (key integrity constraints and data integrity constraints) so that it updates data accurately and consistently • DBMS must support data independence • DBMS must have facility to handle data replication • DBMS must provide utility services that assist in general maintenance of a database © 2015 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part, except for use as permitted in a license distributed with a certain product or service or otherwise on a password-protected website for classroom use. 55
Concept of Database Management 8th Edition Solutions
Source: https://slidetodoc.com/concepts-of-database-management-eighth-edition-chapter-7/