WHAT ARE THE FUNCTIONS OR SERVICES PROVIDED BY DBMS? Only one 2-3 sentences

• 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 • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Update and Retrieve Data (1 of 4) • Fundamental capability of a DBMS • Users don’t need to know how data is physically structured on a storage medium or which processes the DBMS uses to manipulate the data • Users add, change, and delete existing records during updates Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Update and Retrieve Data (2 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Update and Retrieve Data (3 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Update and Retrieve Data (4 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Provide Catalog Services • Metadata: information about the data in a database • Includes table descriptions and field definitions • Catalog stores metadata and makes it accessible to users • Enterprise DBMSs often have a data dictionary • A super catalog Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Support Concurrent Update • DBMS must ensure accuracy when several users update database at the same time • Concurrent update: multiple users make updates to the same database at the same time Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Concurrent Update Problem (1 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Concurrent Update Problem (2 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Concurrent Update Problem (3 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. The Concurrent Update Problem (4 of 4) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Avoiding the Lost Update Problem (1 of 2) • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Avoiding the Lost Update Problem (2 of 2) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Two -Phase Locking (1 of 3) • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Two -Phase Locking (2 of 3) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Two -Phase Locking (3 of 3) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Deadlock (1 of 2) • 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 are in the same order whenever possible • Managing deadlocks • DBMS detects and breaks any deadlock • DBMS chooses one user to be the victim Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Deadlock (2 of 2) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Locking on PC -Based DBMSs • Usually more limited than locking facilities on enterprise DBMSs • Additional burden on the programmers who write the • programs that allow concurrent update • Fairly typical facilities • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Timestamping • DBMS assigns each database update a unique time (i.e., 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 storage and memory space • Extra processing time Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Recover Data • Recovery: returning database to a correct state from an incorrect state • Simplest recovery involves using backups • Backup or save: copy of database Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Journaling (1 of 3) • 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 entry • Successful completion (commit) of a transaction Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Journaling (2 of 3) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Journaling (3 of 3) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Forward Recovery (1 of 2) • DBA executes a DBMS recovery program • Recovery program applies after images of committed transactions from log to database • Improve the performance of the recovery program • First scan the log and then apply the last after image Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Forward Recovery (2 of 2) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Backward Recovery (1 of 2) • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Backward Recovery (2 of 2) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Recovery on PC -Based DBMSs • PC -based DBMSs generally do not offer sophisticated recovery features • Most provide users with a simple way to make backup copies and to recover the database later by copying the backup over the database • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 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 • Privacy Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Authentication (1 of 2) • Authentication refers to 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Authentication (2 of 2) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Views • Snapshots of certain data in the database at a given moment in time • Can be used for security purposes • Tables or fields to which the user does not have access in his or her view effectively do not exist for that user Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Privacy • Privacy: right of individuals to have certain information about them kept confidential • Laws and regulations dictate some privacy rules • Organizations institute additional privacy rules Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Provide Data Integrity Features (1 of 3) • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Provide Data Integrity Features (2 of 3) • Four ways of handling integrity constraints:

• Constraint is ignored • Responsibility for constraint enforcement placed on users • Responsibility for constraint enforcement placed on programmers • Responsibility for constraint enforcement placed on DBMS Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Provide Data Integrity Features (3 of 3) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 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 the field length • Creating an index • Adding or changing a relationship Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Adding a Field • No 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 * Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Changing the Length of a Field • Generally, no 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 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, you may need to make minor changes in already existing programs Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Adding or Changing a Relationship • Trickiest of all • May need to restructure database Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Support Data Replication (1 of 2) • Replicated data: duplicated data • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Support Data Replication (2 of 2) Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. 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. • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Summary (1 of 3) • 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, or deadly embrace, two or more users are waiting for the other user to release a lock before they can proceed Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Summary (2 of 3) • 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 • Enterprise DBMSs maintain a log or journal of all database updates since the last backup; log is used in recovery process • DBMSs provide security features (e.g., encryption , authentication, authorizations, and views) to prevent unauthorized access to a database Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part. Summary (3 of 3) • 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 Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.