CSC 553 Chapter 9

9.52 Summarize the DBA's responsibilities for managing the DBMS.

(1) Generate database application performance reports (2) Investigate user performance complaints (3) Assess need for changes in database structure or application design (4) Modify database structure (5) Evaluate and implement new DBMS features (6) Tune the DBMS

9.3 List seven important DBA tasks.

(1) Managing database structure (2) Controlling concurrent processing (3) Managing processing rights and responsibilities (4) Developing database security (5) Providing for database recovery (6) Managing the DBMS (7) Maintaining the data repository

9.1 Briefly describe five difficult problems for organizations that create and use multiuser databases.

(1) Multiuser databases are complicated to design and develop because they support many overlapping user views. (2) Requirements change over time, and those changes necessitate other changes to the database structure. Such structure changes must be carefully planned and controlled so that a change made for one group does not cause problems for another. (3) When users process a database concurrently, special controls are needed to ensure that the actions of one user do not inappropriately influence the results for another. (4) Processing rights and responsibilities need to be defined and enforced. (5) Effective backup and recovery plans, techniques, and procedures are essential and complicated.

9.43 List two elements of a database security plan.

(1) Procedures for both preventing and detecting security problems should be developed. (2) Procedures should be developed for actions to be taken in case of a security breach.

9.38 Explain the relationships among USER, GROUP, PERMISSION, and OBJECT for a generic database security system.

A USER can be assigned to one or more GROUPs (also called ROLEs), and a GROUP can have one or more USERs. Both USERs and GROUPSs have many PERMISSIONs. OBJECTs (used in a generic, not an OOP sense) have many PERMISSIONs assigned to them. Each PERMISSION pertains to one USER or GROUP and one OBJECT.

9.29 Explain the term cursor.

A cursor is a pointer into a set of rows.

9.53 What is a data repository? A passive data repository? An active data repository?

A data repository is a collection of metadata about databases, database applications, Web pages, users, and other application components. An active data repository is part of the system development process in such a way that metadata is created automatically as the system components are created. A passive data repository is filled only when someone takes the time to generate the needed metadata and place it in the repository.

9.54 Explain why a data repository is important. What is likely to happen if one is not available?

A data repository is a key resource for maintaining the database and its applications and for planning changes and extensions to them. Without one, maintenance is likely to be costly, slow, and very risky.

9.55 Define distributed database.

A distributed database is a database that: (1) Has been split into sections called partitions, and has the partitions stored on different computers, or (2) Has copies of the database called replicas stored on different computers, or (3) Has been both partitioned and replicated.

9.13 Explain the difference between an exclusive lock and a shared lock.

An exclusive lock locks the item from access of any type. No other transaction can read or change the data. A shared lock locks the item from change but not from read. That is, other transactions can read the item as long as they do not attempt to alter it.

9.42 With regard to security, what actions should the DBA take on user accounts and passwords?

All accounts within the DBMS should be protected by strong passwords. Such passwords have at least eight characters; and contain upper- and lowercase letters, numbers, and special unprintable key combinations (certain Alt + key combinations).

9.21 Explain the meaning of the expression ACID transaction.

An ACID transaction is one that is atomic, consistent, isolated, and durable. An atomic transaction is one in which either all of the database actions occur or none of them do. A durable transaction is one for which all committed changes are permanent. The DBMS will not lose or remove such changes, even in the case of failure. If the transaction is durable, the DBMS will provide facilities to recover the changes of all committed actions when necessary. The terms consistent and isolated are not as definitive as the terms atomic and durable.

9.45 What is an SQL injection attack and how can it be prevented?

An SQL injection attack occurs when some form of SQL is included as data when a user enters data into a form. Any time user input is used to modify an SQL statement, that input must be carefully edited to ensure that only valid input has been received and that no additional SQL syntax has been entered.

9.8 Define an atomic transaction and explain why atomicity is important.

An atomic transaction is a series of actions to be taken on the database so that either all of them are performed successfully or none of them are performed at all, in which case the database remains unchanged. Such a transaction is sometimes called a Logical Unit of Work because it is performed as a unit.

9.67 What is an object-relational database?

An object-relational database is a relational database that has the full functionality of a relational database, but can also handle some object data.

9.56 Explain one way to partition a database that has three tables: T1, T2, and T3.

Assume we have three database servers - S1, S2 and S3. Partition the database by putting one table on each server: • Server S1 holds table T1 • Server S2 holds table T2 • Server S3 holds table T3

9.57 Explain one way to replicate a database that has three tables: T1, T2, and T3.

Assume we have three database servers - S1, S2 and S3. Replicate the database by putting all three tables on each server: • Server S1 holds table T1, T2 and T3 • Server S2 holds table T1, T2 and T3 • Server S3 holds table T1, T2 and T3

9.20 Explain the use of SQL BEGIN TRANSACTION, COMMITTRANSACTION, and ROLLBACK TRANSACTION statements. Why does MySQL also use the SQL START TRANSACTION statement?

BEGIN TRANSACTION defines the beginning or starting point of a transaction. COMMIT TRANSACTION marks the successful completion of the transaction. ROLLBACK TRANACTION backs out any change made by the transaction. MySQL uses the keyword BEGIN as part of BEGIN...END block structures, and thus uses START TRANSACATION to avoid confusion about the meaning of the keyword BEGIN.

9.31 What is the advantage of using different types of cursors?

Because cursors require considerable memory, having many cursors open at the same time for, say, a thousand concurrent transactions can consume considerable memory and CPU time. One way to reduce cursor overhead is to define reduced-capability cursors and use them when a full-capability cursor is not needed.

9.51 What is the advantage of taking frequent checkpoints of a database?

Checkpoints are inexpensive operations, and it is feasible to take three or four (or more) per hour. In this way, no more than 15 or 20 minutes of processing needs to be recovered.

9.5 What is configuration control? Why is it necessary?

Configuration Control is a process that allows users to register their needs for changes to procedures and policies, the entire database community can discuss the impacts of the changes, and a global decision can be made whether or not to implement proposed changes.

9.44 Describe the advantages and disadvantages of DBMS-provided and application-provided security.

DBMS-provided - Advantages: Easier to implement, it will be done regardless of the source of data changes and activities, probably more consistent. Disadvantages: May not suffice for particular needs. Works best for vertical security. Application-provided - Advantages: Can be tailored to unique requirements. Can provide horizontal security. Disadvantages: May be done poorly or inconsistently, must be programmed and maintained, may not be as robust.

9.40 What should be done with unused DBMS features and functions?

Database features and functions that are not required by the applications should be removed or disabled from the production DBMS.

9.17 What is deadlock? How can it be avoided? How can it be resolved once it occurs?

Deadlock occurs when User1 locks a resource needed by User2 and User2 locks a resource needed by User1. Each is waiting for a resource that the other person has locked. One way of avoiding a deadlock is to allow users to issue all lock requests at one time. Users must lock all the resources they want at once. When deadlock occurs, the normal solution is to rollback one of the transactions to remove its changes from the database.

9.34 Explain keyset cursors. Give an example of their use.

Dynamic changes of any type and from any source are visible. When the cursor is opened, a primary key value is saved for each row in the recordset. Use a keyset cursor when you need to see updates from any source and your own inserts, but not inserts from other sources.

9.7 Explain the trade-off that exists in concurrency control.

High level of concurrency control is easier for the DBMS to administer but causes poor throughput. A low level of concurrency control is more difficult to administer for the DBMS, but provides better throughput.

9.61 Explain what problems can occur in a distributed database that is partitioned but notreplicated.

If a distributed database is partitioned by not replicated, then problems will occur only if a transaction updates data that spans two or more of the distributed partitions. The transaction must be initiated on all the servers, but must be allowed to commit on any one computer only if it can be committed on all the servers.

9.60 What solution is used to prevent the problems in Review Question 9.59?

If more than one computer can update a replicated database, then the associated problems can be prevented by using distributed two-phase locking.

9.59 If more than one computer can update a replicated database, what three problems canoccur?

If more than one computer can update a replicated database, then: • There may be inconsistent updates, and • One computer may delete a record that another computer is updating, and • Changes may be made that violate uniqueness constraints.

9.58 Explain what must be done when fully replicating a database but allowing only onecomputer to process updates.

If only one computer accepts updates, the copies of the updates must be periodically sent to the servers holding the other replicas. The challenges are: • To make sure that only logically consistent copies of the database are distributed, and • To make sure the locations using the replicas understand that the replicas may not be completely updated.

9.48 Why is it important to write to the log before changing the database values?

If the system crashes between the time a transaction is logged and the time it is applied, at worst there is a record of an unapplied transaction. If, on the other hand, the transactions were to be applied before they were logged, it would be possible (but undesirable) to change the database but have no record of the change. If this happened, an unwary user might reenter an already completed transaction.

9.4 Summarize the DBA's responsibilities for managing database structure.

Participate in Database and Application Development • Assist in requirements stage and data model creation • Play an active role in database design and creation Facilitate Changes to Database Structure • Seek community-wide solutions • Assess impact on all users • Provide configuration control forum • Be prepared for problems after changes are made • Maintain documentation

9.36 What happens if you do not declare the transaction isolation level and the cursor type to the DBMS? Is this good or bad?

If you do not specify the isolation level of a transaction or do not specify the type of cursors you open, the DBMS will use a default level and type. These defaults may be perfect for your application, but they also may be terrible. Thus, even though these issues can be ignored, the consequences of them cannot be avoided.

9.49 Describe the rollback process. Under what conditions should it be used?

In a rollback, the current database and the transaction log are used. Before images of all uncommitted transactions are placed back on the database and any failed transaction is restarted. A rollback is used when a transaction fails or a system failure occurs that does not damage the active database.

9.47 Define rollback and rollforward.

In a rollback, we undo changes made by erroneous or partially processed transactions by undoing the changes they have made in the database. We apply before images to the changed database data. Then, the valid transactions that were in process at the time of the failure are restarted. In a rollforward, the database is restored using the saved data, and all valid transactions since the save are reapplied. We apply after images to the restored database data.

9.50 Describe the rollforward process. Under what conditions should it be used?

In a rollforward, the saved copy of the database and the transaction log are used. First, the database is restored from the saved copy. Next, after-images of all committed transactions are placed back on the database and all failed transactions are restarted. A rollforward is used when a failure has occurred that renders the database unusable.

9.16 In general, how should the boundaries of a transaction be defined?

In general, the boundaries of a transaction should correspond to the definition of the database view it is processing. Following the two-phase strategy, the rows of each relation in the view are locked as needed. Changes are made, but the data are not committed to the database until all of the view has been processed. At this point, changes are made in the actual database, and all locks are released.

9.19 Explain the benefits of marking transaction boundaries, declaring lock characteristics, and letting the DBMS place locks.

In this way, if the locking behavior needs to be changed, the application need not be rewritten to place locks in different locations in the transaction. Instead, only the lock declaration need be changed.

9.64 In general terms, explain why relational databases are difficult to use for object persistence.

It is difficult to use a relational database for object persistence because object structures are generally more complicated than a table row. Typically several rows of several tables are required to store the data about the object. This results in the need for special structures (essentially mini-databases) in the relational database just to hold the object data.

9.6 Explain the meaning of the word inappropriately in the phrase "one user's work does not inappropriately influence another user's work."

It means that any overlap of work results is anticipated and is in accordance with the requirements. In some applications, the work of two users is supposed to be completely isolated from each other, such as with patient records processing for two different patients in a hospital. In other applications, two users' work need not be so isolated — someone who wants the latest stock price won't mind if the price is updated as the list is being produced — as long as they know that it may happen.

9.11 Explain the difference between an explicit and an implicit lock.

Locks placed by the DBMS are called implicit locks; those placed by command are called explicit locks.

9.41 Explain how to protect the computer that runs the DBMS.

No one other than authorized DBA personnel should be allowed to work at the keyboard of the computer that runs the DBMS. That computer should be physically secured behind locked doors, and access to that facility should be controlled. Visits to the DBMS computer room should be recorded in a log.

9.65 What does OODBMS stand for, and what is its purpose?

OODBMS stands for object-oriented DBMS, and its purpose is to provide a DBMS that stores object data and supported object persistence.

9.66 According to this chapter, why were OODBMSs not successful?

OODBMSs were not successful because by the time they were introduced too much data was already stored in relational DBMS formats, and organizations did not want to have to convert this data to an OODBMS format just to be able to use an OODBMS.

9.63 Explain the meaning of the term object persistence.

Object persistence means storing the values of the properties of an object.

9.37 Explain the necessity of defining processing rights and responsibilities. How are such responsibilities enforced, and what is the role of SQL DCL in enforcing them?

Processing rights and responsibilities are necessary to bring order to the processing of the database, which is a shared resource. While rights can be enforced by the DBMS and application programs, responsibilities must be documented and understood by users. The upholding of responsibilities cannot be automated. It's a matter of user training and behavior.

9.26 Explain the read committed isolation level. Give an example of its use.

Read committed isolation level allows nonrepeatable reads and phantom reads to occur, but dirty reads are disallowed. Use this if it is not important that you read a record that may have been inserted or changed by another concurrently running transaction.

9.25 Explain the read uncommitted isolation level. Give an example of its use.

Read uncommitted isolation level allows dirty reads, nonrepeatable reads and phantom reads to occur. This essentially means no isolation level. Use this when the tables you are processing will not be used by any other concurrent transaction

9.27 Explain the repeatable read isolation level. Give an example of its use.

Repeatable read isolation level allows phantom reads, but disallows both dirty reads and nonrepeatable reads. Use this isolation level when it is OK to read records that have been inserted by a concurrently running uncommitted transaction.

9.62 What organizations should consider using a distributed database?

Replicated, read-only databases present few problems, but distributed databases should only be used by organizations that have: • Experienced database teams • A substantial budget for the project • A significant amount of time for the project • Data communications expertise

9.46 Explain how a database could be recovered via reprocessing. Why is this generally not feasible?

Reprocessing means to redo all events exactly like they were done the first time. For example, if several transactions from an ATM were lost, reprocessing would mean going back to the ATM and performing the same transactions in the same order. First, reprocessing transactions takes the same amount of time as processing them in the first place. If the computer is heavily scheduled, the system may never catch up. Second, when transactions are processed concurrently, events are asynchronous. Slight variations in human activity, such as a user inserting a floppy disk more slowly or a user reading an electronic mail message before responding to an application prompt, may change the order of the execution of concurrent transactions.

9.28 Explain the serializable isolation level. Give an example of its use.

Serializable isolation level will not allow any dirty reads, nonrepeatable reads or phantom reads to occur. Use this isolation level when it is important that you never read data that is being used by a concurrent transaction.

9.22 Describe statement-level consistency.

Statement-level consistency means that each statement independently processes rows consistently, but that changes from other users to these rows might be allowed during the interval between two SQL statements

9.24 What is the purpose of transaction isolation levels?

The 1992 ANSI SQL standard defines four transaction isolation levels, which specify which of these problems are allowed to occur. The goal is for the application programmer to be able to declare the type of isolation level he or she wants and then to have the DBMS manage locks so as to achieve that level of isolation.

9.39 Should the DBA assume a firewall when planning security?

The DBA should plan security assuming that the firewall has been breached. The DBMS, the database, and all applications should be secure even if the firewall fails.

9.10 Give an example, other than the one in this text, of the lost update problem.

The lost update problem occurs when two transactions attempt to update the same data resource simultaneously. Because each transaction copies the record into its own work area, each can make changes to its own work area, then rewrite their work area to the database. The second update will destroy the first update, thus creating a lost update for the first transaction

9.32 Explain forward only cursors. Give an example of their use.

The simplest cursor is the forward only cursor. With it, the application can only move forward through the recordset. Changes made by other cursors in this transaction and by other transactions will be visible only if they occur in rows ahead of the cursor. They would be used when processing a report sequentially and you use a forward only cursor when you are sequentially reading a recordset.

9.12 What is lock granularity?

The size of a lock is referred to as the lock granularity. Locks with large granularity are easy for the DBMS to administer, but frequently cause conflicts. Locks with small granularity are difficult to administer (there are many more details for the DBMS to track and check), but conflicts are less common.

9.2 Explain the difference between a database administrator and a data administrator.

The term data administrator refers to a function that applies to an entire organization. The term database administration refers to a function that is specific to a particular database, including the applications that process that database.

9.30 Explain why a transaction may have many cursors. Also, how is it possible that a transaction may have more than one cursor on a given table?

The transaction may need to process several tables at one time. A cursor can be opened on two different views of a table (these are SQL views, not application views), in which case there will be two cursors open on the same table for that transaction. Furthermore, there are some transactions in which the logic requires that two cursors process the same table.

9.15 How does releasing all locks at the end of the transaction relate to two-phase locking?

This strategy is more restrictive than two-phase locking requires, but it is easier to implement.

9.23 Describe transaction-level consistency. What disadvantage can exist with it?

Transaction-level consistency means that all rows impacted by any of the SQL statements in a transaction are protected from changes during the entire transaction. The disadvantage is that this is more restrictive setting, and may result in less throughput.

9.14 Explain two-phased locking.

Transactions are allowed to obtain locks as necessary, but once the first lock is released, no other lock can be obtained. Transactions thus have a growing phase, in which the locks are obtained, and a shrinking phase, in which the locks are released.

9.9 Explain the difference between concurrent transactions and simultaneous transactions. How many CPUs are required for simultaneous transactions?

When two transactions are being processed against a database at the same time, they are termed concurrent transactions. With concurrent transactions, two or more users access the database using a single CPU on the database server. The CPU executes some instructions from one, then executes some from the other, switching back-and-forth between them. The actions may appear simultaneous to the two users. For transactions to be processed simultaneously, two or more CPUs are required. With modern server computers, such processing is possible.

9.35 Explain dynamic cursors. Give an example of their use.

With a dynamic cursor, changes in row order are not visible. If the isolation level is dirty read, then uncommitted updates and deletions are visible; otherwise only committed updates and deletions are visible. Use a dynamic curser when you need to see all changes being made. Use with dirty read isolation level to see uncommitted changes, otherwise only committed changes will be seen.

9.33 Explain static cursors. Give an example of their use.

With a static cursor, the application sees the data as it was at the time the cursor was opened. Changes made by this cursor are visible. Changes from other sources are not visible. Backward and forward scrolling are allowed. Use this when processing a recordset and you need to see any changes you have made but not changes other users have made.

9.18 Explain the difference between optimistic and pessimistic locking.

With optimistic locking, the assumption is made that no conflict will occur. Data are read, the transaction is processed, updates are issued, and then a check is made to see if conflict occurred. If not, the transaction is finished. If so, the transaction is repeated until it processes with no conflict. With pessimistic locking, the assumption is made that conflict will occur. First, locks are issued, the transaction is processed, and then the locks are freed.