Chapter 9 Notes for Module 7

What is an information system? What is its purpose?

An information system is a system that

• provides the conditions for data collection, storage, and retrieval
• facilitates the transformation of data into information
• provides management of both data and information.

An information system is composed of hardware, software (DBMS and applications), the database(s), procedures, and people.

Good decisions are generally based on good information. Ultimately, the purpose of an information system is to facilitate good decision making by making relevant and timely information available to the decision makers.

How do systems analysis and systems development fit into a discussion about information systems?

Both systems analysis and systems development constitute part of the Systems Development Life Cycle, or SDLC. Systems analysis, phase II of the SDLC, establishes the need for and the extent of an information system by

• Establishing end-user requirements.
• Evaluating the existing system.
• Developing a logical systems design.

Systems development, based on the detailed systems design found in phase III of the SDLC, yields the information system. The detailed system specifications are established during the systems design phase, in which the designer completes the design of all required system processes.

What does the acronym SDLC mean, and what does an SDLC portray?

SDLC is the acronym that is used to label the System Development Life Cycle. The SDLC traces the history of a information system from its inception to its obsolescence. The SDLC is composed of six phases: planning, analysis, detailed system, design, implementation and maintenance.

What does the acronym DBLC mean, and what does a DBLC portray?

DBLC is the acronym that is used to label the Database Life Cycle. The DBLC traces the history of a database system from its inception to its obsolescence. Since the database constitutes the core of an information system, the DBLC is concurrent to the SDLC. The DBLC is composed of six phases: initial study, design, implementation and loading, testing and evaluation, operation, and maintenance and evolution.

What is the minimal data rule in conceptual design? Why is it important?

The minimal data rule specifies that all the data defined in the data model are actually required to fit present and expected future data requirements. This rule may be phrased as All that is needed is there, and all that is there is needed.

What are business rules? Why are they important to a database designer?

Business rules are narrative descriptions of the business policies, procedures, or principles that are derived from a detailed description of operations. Business rules are particularly valuable to database designers, because they help define:

• Entities
• Attributes
• Relationships (1:1, 1:M, M:N, expressed through connectivities and cardinalities)
• Constraints

To develop an accurate data model, the database designer must have a thorough and complete understanding of the organization’s data requirements. The business rules are very important to the designer because they enable the designer to fully understand how the business works and what role is played by data within company operations.

What is the data dictionary’s function in database design?

A good data dictionary provides a precise description of the characteristics of all the entities and attributes found within the database. The data dictionary thus makes it easier to check for the existence of synonyms and homonyms, to check whether all attributes exist to support required reports, to verify appropriate relationship representations, and so on. The data dictionary’s contents are both developed and used during the six DBLC phases:

DATABASE INITIAL STUDY

The basic data dictionary components are developed as the entities and attributes are defined during this phase.

DATABASE DESIGN

The data dictionary contents are used to verify the database design components: entities, attributes, and their relationships. The designer also uses the data dictionary to check the database  design for homonyms and synonyms and verifies that the entities and attributes will support all required query and report requirements.

IMPLEMENTATION AND LOADING

The DBMS’s data dictionary helps to resolve any remaining attribute definition inconsistencies.

TESTING AND EVALUATION

If problems develop during this phase, the data dictionary contents may be used to help restructure the basic design components to make sure that they support all required operations.

OPERATION

If the database design still yields (the almost inevitable) operational glitches, the data dictionary may be used as a quality control device to ensure that operational modifications to the database do not conflict with existing components.

MAINTENANCE AND EVOLUTION

As users face inevitable changes in information needs, the database may be modified to support those needs. Perhaps entities, attributes, and relationships must be added, or relationships must be changed. If new database components are fit into the design, their introduction may produce conflict with existing components. The data dictionary turns out to be a very useful tool to check whether a suggested change invites conflicts within the database design and, if so, how such conflicts may be resolved.

What factors are important in a DBMS software selection?

The selection of DBMS software is critical to the information system’s smooth operation. Consequently, the advantages and disadvantages of the proposed DBMS software should be carefully studied. To avoid false expectations, the end user must be made aware of the limitations of both the DBMS and the database.

Although the factors affecting the purchasing decision vary from company to company, some of the most common are:

• Cost. Purchase, maintenance, operational, license, installation, training, and conversion costs.
• DBMS features and tools. Some database software includes a variety of tools that facilitate the application development task. For example, the availability of query by example (QBE), screen painters, report generators, application generators, data dictionaries, and so on, helps to create a more pleasant work environment for both the end user and the application programmer. Database administrator facilities, query facilities, ease of use, performance, security, concurrency control, transaction processing, and third-party support also influence DBMS software selection.
• Underlying model. Hierarchical, network, relational, object/relational, or object.
• Portability. Across platforms, systems, and languages.
• DBMS hardware requirements. Processor(s), RAM, disk space, and so on.

What three levels of backup may be used in database recovery management? Briefly describe what each of those three backup levels does.

A full backup of the database creates a backup copy of all database objects in their entirety.

A differential backup of the database creates a backup of only those database objects that have changed since the last full backup.

A transaction log backup does not create a backup of database objects, but makes a backup of the log of changes that have been applied to the database objects since the last backup.

Chapter 10 Notes for Module 7

What is meant by the following statement: a transaction is a logical unit of work.

A transaction is a logical unit of work that must be entirely completed of aborted; no intermediate states are accepted. In other words, a transaction, composed of several database requests, is treated by the DBMS as a unit of work in which all transaction steps must be fully completed if the transaction is to be accepted by the DBMS.

Acceptance of an incomplete transaction will yield an inconsistent database state. To avoid such a state, the DBMS ensures that all of a transaction’s database operations are completed before they are committed to the database. For example, a credit sale requires a minimum of three database operations:

1. An invoice is created for the sold product.
2. The product’s inventory quantity on hand is reduced.
3. The customer accounts payable balance is increased by the amount listed on the invoice.

If only parts 1 and 2 are completed, the database will be left in an inconsistent state. Unless all three parts (1, 2, and 3) are completed, the entire sales transaction is canceled.

What is a consistent database state, and how is it achieved?

A consistent database state is one in which all data integrity constraints are satisfied. To achieve a consistent database state, a transaction must take the database from one consistent state to another.

The DBMS does not guarantee that the semantic meaning of the transaction truly represents the real-world event. What are the possible consequences of that limitation? Give an example.

The database is designed to verify the syntactic accuracy of the database commands given by the user to be executed by the DBMS. The DBMS will check that the database exists, that the referenced attributes exist in the selected tables, that the attribute data types are correct, and so on. Unfortunately, the DBMS is not designed to guarantee that the syntactically correct transaction accurately represents the real‑world event.

For example, if the end user sells 10 units of product 100179 (Crystal Vases), the DBMS cannot detect errors such as the operator entering 10 units of product 100197 (Crystal Glasses). The DBMS will execute the transaction, and the database will end up in a technically consistent state but in a real‑world inconsistent state because the wrong product was updated.

What are the five transaction properties and what do they mean?

The five transaction properties are:

Atomicity – requires that all parts of a transaction must be completed or the transaction is aborted. This property ensures that the database will remain in a consistent state.

Consistency – Indicates the permanence of the database consistent state.

Isolation – means that the data required by an executing transaction cannot be accessed by any other transaction until the first transaction finishes. This property ensures data consistency for concurrently executing transactions.

Durability – indicates that the database will be in a permanent consistent state after the execution of a transaction. In other words, once a consistent state is reached, it cannot be lost.

Serializability – means that a series of concurrent transactions will yield the same result as if they were executed one after another.

All five transaction properties work together to make sure that a database maintains data integrity and consistency for either a single-user or a multi-user DBMS.

What does serializability of transactions mean?

Serializability of transactions means that a series of concurrent transactions will yield the same result as if they were executed one after another

What is a transaction log, and what is its function?

The transaction log is a special DBMS table that contains a description of all the database transactions executed by the DBMS. The database transaction log plays a crucial role in maintaining database concurrency control and integrity.

The information stored in the log is used by the DBMS to recover the database after a transaction is aborted or after a system failure. The transaction log is usually stored in a different hard disk or in a different media (tape) to prevent the failure caused by a media error.

What is a scheduler, what does it do, and why is its activity important to concurrency control?

The scheduler is the DBMS component that establishes the order in which concurrent database operations are executed. The scheduler interleaves the execution of the database operations (belonging to several concurrent transactions) to ensure the serializability of transactions. In other words, the scheduler guarantees that the execution of concurrent transactions will yield the same result as though the transactions were executed one after another. The scheduler is important because it is the DBMS component that will ensure transaction serializability. In other words, the scheduler allows the concurrent execution of transactions, giving end users the impression that they are the DBMS’s only users.

What is a lock, and how, in general, does it work?

A lock is a mechanism used in concurrency control to guarantee the exclusive use of a data element to the transaction that owns the lock. For example, if the data element X is currently locked by transaction T1, transaction T2 will not have access to the data element X until T1 releases its lock.

Generally speaking, a data item can be in only two states: locked (being used by some transaction) or unlocked (not in use by any transaction). To access a data element X, a transaction T1 first must request a lock to the DBMS. If the data element is not in use, the DBMS will lock X to be used by T1 exclusively. No other transaction will have access to X while T1 is executed.

What are the different levels of lock granularity?

Lock granularity refers to the size of the database object that a single lock is placed upon.  Lock granularity can be:

•       Database-level, meaning the entire database is locked by one lock.
•       Table-level, meaning a table is locked by one lock.
•       Page-level, meaning a diskpage is locked by one lock.
•       Row-level, meaning one row is locked by one lock.
•       Field-level, meaning one field in one row is locked by one lock.

Why might a page-level lock be preferred over a field-level lock?

Smaller lock granualarity improves the concurrency of the database by reducing contention to lock database objects.  However, smaller lock granularity also means that more locks must be maintained and managed by the DBMS, requiring more processing overhead and system resources for lock management.  Concurrency demands and system resource usage must be balanced to ensure the best overall transaction performance.  In some circumstances, page-level locks, which require fewer system resources, may produce better overall performance than field-level locks, which require more system resources.

What is concurrency control, and what is its objective?

Concurrency control is the activity of coordinating the simultaneous execution of transactions in a multiprocessing or multi‑user database management system. The objective of concurrency control is to ensure the serializability of transactions in a multi‑user database management system. (The DBMS’s scheduler is in charge of maintaining concurrency control.)

Because it helps to guarantee data integrity and consistency in a database system, concurrency control is one of the most critical activities performed by a DBMS. If concurrency control is not maintained, three serious problems may be caused by concurrent transaction execution: lost updates, uncommitted data, and inconsistent retrievals.

What is an exclusive lock, and under what circumstances is it granted?

An exclusive lock is one of two lock types used to enforce concurrency control.  (A lock can have three states: unlocked, shared (read) lock, and exclusive (write) lock. The “shared” and “exclusive” labels indicate the nature of the lock.)

An exclusive lock exists when access to a data item is specifically reserved for the transaction that locked the object. The exclusive lock must be used when a potential for conflict exists, e.g., when one or more transactions must update (WRITE) a data item. Therefore, an exclusive lock is issued only when a transaction must WRITE (update) a data item and no locks are currently held on that data item by any other transaction.

To understand the reasons for having an exclusive lock, look at its counterpart, the shared lock. Shared locks are appropriate when concurrent transactions are granted READ access on the basis of a common lock, because concurrent transactions based on a READ cannot produce a conflict.

A shared lock is issued when a transaction must read data from the database and no exclusive locks are held on the data to be read.

What is a deadlock, and how can it be avoided? What are some strategies for dealing with deadlocks?

Base your discussion on Chapter 10’s Section 10.3.4, Deadlocks. Start by pointing out that, although locks prevent serious data inconsistencies, their use may lead to two major problems:

1. The transaction schedule dictated by the locking requirements may not be serializable, thus causing data integrity and consistency problems.
2. The schedule may create deadlocks. Database deadlocks are the equivalent of a traffic gridlock in a big city and are caused by two transactions waiting for each other to unlock data.

Refer to Table 10.13 on page 473 of the text.

In a real world DBMS, many more transactions can be executed simultaneously, thereby increasing the probability of generating deadlocks. Note that deadlocks are possible only if one of the transactions wants to obtain an exclusive lock on a data item; no deadlock condition can exist among shared locks.

Three basic techniques exist to control deadlocks:

Deadlock Prevention

A transaction requesting a new lock is aborted if there is a possibility that a deadlock may occur. If the transaction is aborted, all the changes made by this transaction are rolled back and all locks are released. The transaction is then re-scheduled for execution. Deadlock prevention works because it avoids the conditions that lead to deadlocking.

Deadlock Detection

The DBMS periodically tests the database for deadlocks. If a deadlock is found, one of the transactions (the “victim”) is aborted (rolled back and rescheduled) and the other transaction continues. Note particularly the discussion in Section 10.4.1, Wait/Die and Wound/Wait Schemes.

Deadlock Avoidance

The transaction must obtain all the locks it needs before it can be executed. This technique avoids rollback of conflicting transactions by requiring that locks be obtained in succession. However, the serial lock assignment required in deadlock avoidance increases the response times.

The best deadlock control method depends on the database environment. For example, if the probability of deadlocks is low, deadlock detection is recommended. However, if the probability of deadlocks is high, deadlock prevention is recommended. If response time is not high on the system priority list, deadlock avoidance may be employed.

What are some disadvantages of time-stamping methods for concurrency control?

The disadvantages are: 1) each value stored in the database requires two additional time stamp fields – one for the last time the field was read and one for the last time it was updated, 2) increased memory and processing overhead requirements, and 3) many transactions may have to be stopped, rescheduled, and restamped.

Why might it take a long time to complete transactions when an optimistic approach to concurrency control is used?

Because the optimistic approach makes the assumption that conflict from concurrent transactions is unlikely, it does nothing to avoid conflicts or control the conflicts.  The only test for conflict occurs during the validation phase.  If a conflict is detected, then the entire transaction restarts.  In an environment with few conflicts from concurrency, this type of single checking scheme works well.  In an environment where conflicts are common, a transaction may have to be restarted numerous times before it can be written to the database.

What are the three types of database critical events that can trigger the database recovery process? Give some examples for each one.

Backup and recovery functions constitute a very important component of today’s DBMSs. Some DBMSs provide functions that allow the database administrator to perform and schedule automatic database backups to permanent secondary storage devices, such as disks or tapes.

Critical events include:

• Hardware/software failures. hard disk media failure, a bad capacitor on a motherboard, or a failing memory bank. Other causes of errors under this category include application program or operating system errors that cause data to be overwritten, deleted, or lost.
• Human-caused incidents. This type of event can be categorized as unintentional or intentional.

An unintentional failure is caused by carelessness by end-users. Such errors include deleting the wrong rows from a table, pressing the wrong key on the keyboard, or shutting down the main database server by accident.

Intentional events are of a more severe nature and normally indicate that the company data are at serious risk. Under this category are security threats caused by hackers trying to gain unauthorized access to data resources and virus attacks caused by disgruntled employees trying to compromise the database operation and damage the company.

• Natural disasters. This category includes fires, earthquakes, floods, and power failures.

What are the four ANSI transaction isolation levels? What type of reads does each level allow?

The four ANSI transaction isolation levels are 1) read uncommitted, 2) read committed, 3) repeatable read, and 4) Serializable.  These levels allow different “questionable” reads.  A read is questionable if it can produce inconsistent results.  Read uncommitted isolation will allow dirty reads, non-repeatable reads and phantom reads.  Read committed isolation will allow non-repeatable reads and phantom reads.  Repeatable read isolation will allow phantom reads.  Serializable does not allow any questionable reads