Multiuser databases enable many people to access the data and manipulate it at t
ID: 3558231 • Letter: M
Question
Multiuser databases enable many people to access the data and manipulate it at the same time. Describe the different locking techniques to protect the data and why data locking is necessary. If locking did not exist, could multiuser databases exist? Why or why not?
Which data integrity problems are introduced by multiple users using the same database? Explain why these problems exist and how to protect against them.
Duplicate records can become troublesome in a database. How does the risk of duplicate records increase in multiuser databases?
Data is a valuable asset to most companies. Explain the different backup procedures for databases and explain when you would use them.
Describe the role of a database administrator (DBA). Make sure to include detail about transactions used by DBAs and locks that require DBA intervention.
Explanation / Answer
Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.
a. Avoids deadlock conditions
b. Avoids clashes in capturing the resources
Types of locks:
a. Read Operations: Select
b. Write Operations: Insert, Update and Delete
In general, multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.
2)Integrity Problems
There are some basic integrity problems that can result if transaction isolation isn't enforced. Three of these problems are common to many databases:
Dirty reads
A dirty read occurs when a database allows a transaction to read data that has been changed by another transaction but hasn't been committed yet. The changes made by the transaction may be rolled back, so the data read may turn out to be incorrect. Many databases allow dirty reads to avoid the contention caused by read locks.
Nonrepeatable reads
A nonrepeatable read occurs as a result of changes made by another transaction. A transaction makes a query based on a particular condition. After the data has been returned to the first transaction, but before the first transaction is complete, another transaction changes the data so that some of the previously retrieved data no longer satisfies the selection condition. If the query was repeated in the same transaction, it would return a different set of results, so any changes made on the basis of the original results may no longer be valid. Data that was read once may return different results if it's read again later in the same transaction.
Phantom reads
A phantom read also occurs as a result of changes made by another transaction. One transaction makes a query based on a particular condition. After the data has been returned to the first transaction, but before the first transaction is complete, another transaction inserts new rows into the database that would have been selected by the first transaction. If the transaction is making changes based on the assumption that the only rows that satisfied the condition were returned, a phantom read could result in improper data. Although all the data read by the first query is returned for the second query, additional data also should have been returned, so any changes made on the basis of the original results may no longer be valid.
As multiple users access the same data, there is always the possibility that one user's changes to a specific piece of data will be unwittingly overwritten by another user's changes. If this situation occurs, the accuracy of the information in the database is corrupted, which can render the data useless or, even worse, misleading. At the same time, the techniques used to prevent this type of loss can dramatically reduce the performance of an application system, as users wait for other users to complete their work before continuing. we can't solve this type of performance problem by increasing the resources available to an application because it's caused by the traffic visiting a piece of data, not by any lack of horsepower in the system that's handling the data.
If the problems raised by concurrent access aren't properly handled by the underlying database software, developers may find themselves suffering in a number of ways. They will have to create their own customized solutions to these problems in their software, which will consume valuable development time. They will frequently find themselves adding code during the late stages of development and testing to work around the underlying deficiencies in the database, which can alter the design of the application. Worst of all, they may find themselves changing the optimal design of their data structures to compensate for weaknesses in the capabilities of the underlying database.
There is only one way to deal successfully with the issues raised by concurrent data access. The database that provides the access must implement strategies to transparently overcome the potential problems posed by concurrent access. Fortunately, Oracle has excellent methods for handling concurrent access.
3)Duplicate data can cause confusion that might cost you money, trust or goodwill. For example, a duplicate record might result in a perishable delivery to the wrong address. Access can help you avoid duplicate data by providing ways to reuse data, and help prevent duplicate data input by requiring that new values be unique. One of the primary reasons for using a relational database such as Access is to avoid duplicate data, which can cause confusion and problems. Sometimes if you create a database by gathering data from existing systems, say a set of Excel workbooks, you may end up with duplicate data that needs to be resolved before you start using the database. Moreover, if certain design precautions are not taken (or are impractical for some reason), a database can sometimes acquire duplicate values, especially when multiple users enter data simultaneously.
duplicate records results in money loss,ineffective direct marketing, administration overhead and preventable shipping errors.
4)ypes of backups available in SQL Server:
Complete: This creates a complete stand alone image of the database. This backup is self dependent and can be restored to either the same or a new database on same or other server.
Differential: This backs up only the modified contents since the last backup. They do not provide much flexibility.
Transaction log: This backs up all transaction logs since the previous transaction log backup or the complete transaction log backup if there has not been one in past.
Files and Filegroups backup: This option is suitable when time constraints are high and one cannot afford to perform a complete database backup. It also needs transaction logs backup to take place to make it worth choosing this option. After restoring file backup, apply transaction logs to roll the file contents forward to make it consistent with the database..
4)The database administrator performs a critical role within an organization and is an important and key role in
Database Management Systems.
The major responsibility of a database administrator is to handle the process of developing the database
and maintaining the database of an organization. The database administrator is responsible for defining
the internal layout of the database and ensuring the internal layout optimizes system performance.
The database administrator has full access over all type of important data of an organization.
The database administrator decides what data will be stored in the database and how to organize
data in database so that it can be access easily on requirement or need of an organization.
To design the database of an organization, the database administrator must have a meeting
with users and determine their requirements.
The database administrator is also responsible for preparing documentation,
including recording the procedures, standards, guidelines, and data descriptions
necessary for the efficient and continuing use of the database environment. Documents
should include materials to help end users, database application programmers, the operation
staff, and all personnel connected with the database management system.
The database administrator is responsible for monitoring the database environment, such
as seeing that the database is meeting performance standards, making sure the accuracy,
integrity, and security of data are maintained.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.