Expert answer:Database assignment

Solved by verified expert:Please find the attached work document for the question. Writing are 2 points and choice questions are 1 points. Eight questions altogether. I have attached powerpoint slides so that it would be easy to answer.
database.docx

unit1_transactions_final_1_.pdf

unit2_concurrency_final_1_.pdf

unit3_sql_microsoft.pdf

Unformatted Attachment Preview

1.
In theory, which of the following database architectures would have a recovery with fewer steps
(potentially faster)?
A. Immediate Update Architecture
B. Deferred Update Architecture
2. A transaction updates a value A=10 to A=11. The transaction commits. There is
a power failure, the system re-boots and comes back online. The value of A is
11 when users are back on the system.
Has the DBMS correctly followed the ACID properties, why or why not?
3. For the diagram below, how could the tables be implemented if you wanted to
maximize the performance of a query that retrieved all of the aliens with all of
their attributes (show the tables)?
Format Example:
Emp( emp_id PK, emp_name, emp_salary)
4.
In most cases, data retrieval is faster
A. When retrieving data from disk
B. When retrieving data from memory
C. When retrieving data across the network
5.
Data pages from memory may be written out to disk prior to the data being committed if the system runs
out of space in memory even if the data on those pages are NOT committed.
True
False
6.
Examine the following snapshot of a transaction log. After the failure, during automatic recovery,
Transaction T8:
T1(s) T1(c) T2(s) T3(s) T4(s) T5(s) T5(c) (CHECKPOINT) T6(s) T7(s)
(CHECKPOINT) T8(s) T6(c ) T2(c) [FAILURE]
A. Is rolledback
B. Is rolled forward
C. Nothing needs to be done
7. In general, any transaction can have how many outcomes?
A database administrator (DBA) wanted to improve the performance of the recovery of his
system (recover faster). To accomplish this, the DBA would:
A. Configure the system to perform more frequent checkpoints.
8.
B. Configure the system to perform less frequent checkpoints.
Unit 1
Transaction Management
1
Unit 1 – Objectives
 Function
and importance of transactions.
 Properties of transactions.
 Recovery Control
– Some causes of database failure.
– Purpose of transaction log file.
– Purpose of checkpointing.
– How to recover following database failure.
2
DBMS – Review
Database Properties:
•Represent some aspect of the real world, often called a mini-world (but not the real world).
•Is a logical collection of data with some inherent meaning and relationships.
•Can be represented on many levels to different users.
•Have a self-describing nature.
•Must be designed properly to avoid anomalies, inconsistencies and unmanageable code.
•Are big business and big money.
Basic Database Environment
3
Introduction to Transaction Processing –
Some Concepts
 Single-User System:
– At most one user at a time can use the system.
 Multiuser System:
– Many users can access the system concurrently.
 Concurrency
– Interleaved processing:
» Concurrent execution of processes is interleaved in a single
CPU
– Parallel processing:
» Processes are concurrently executed in multiple CPUs.
4
What is a transaction?
•A transaction is a logical unit of work. It is defined by the business rules of the application.
•A transaction is usually a sequence of SQL operations executed against a database.
•A transaction usually includes one or more data modification operations which transform the database from one
consistent state (instance) to another.
•A transaction may have only 2 outcomes:
-Successful completion, actions are saved, “COMMITTED”.
-Unsuccessful completion, all actions are “undone’ or “ROLLED BACK”.
$
Savings
Checking
Consider a simple bank transfer from a savings account to a checking account. While there are many
important steps to completing this transaction, there are two that modify the database. One is a
withdrawal from savings, the other is a deposit to checking. You must assume that the database is in
a consistent state before the transaction. From the point of view of database consistency, it does not
matter whether the transaction succeeds or fails, only that it succeeds or fails completely. The 2
acceptable outcomes that leave the database in consistent states are:
1. The funds are subtracted from savings and added to checking.
2. Nothing changes.
5
What is a transaction?
•Transactions may have a single data modification or multiple.
•Inserts, deletes, updates are all considered data modification.
•Once a transaction “commits”, it can not be undone.
•When a transaction is “rolled back”, all modification must be undone.
•Transactions are the smallest unit of recoverable data.
•Most DBMS will not allow partial execution of a transaction.
Example:
SQL> delete from students where sect_nbr = “101”
RESULT:
-either all relevant rows are deleted (committed) or
-none of the rows are deleted.
It is the developers responsibility to understand the business rules of the
application and then to transform those business rules into transactions.
6
Transaction Implementation
Transaction implementation varies by DBMS but all have some mechanism to begin a transaction,
commit a transaction, and rollback a transaction (transaction control statements) . There usually is also
a mechanism to verify success or failure of every action within a transaction.
EXAMPLE (Microsoft / Sybase):
begin transaction
delete from students where sect_nbr = “101”
if @@error != 0
begin
rollback transaction
return
end
delete from grades where sect_nbr = “101”
if @@error != 0
begin
rollback transaction
return
end
commit transaction
7
Transaction Implementation
Example Oracle:
insert into emp values (new_id, new_name, new_job,
new_mgr, new_hiredate,new_sal, new_comm, new_dept_id);
DBMS_OUTPUT.PUT_LINE(‘Inserted ‘ || SQL%ROWCOUNT || ‘ Rows.’);
COMMIT; — TRANSACTION CONTROL
Exception
When OTHERS THEN
Rollback;
v_ErrorCode := SQLCODE;
v_ErrorMsg := substr(SQLERRM,1,200);
v_CurrentUser := USER;
DBMS_OUTPUT.PUT_LINE( TO_CHAR(SYSDATE) || v_CurrentUser || TO_CHAR(v_ErrorCode) || v_ErrorMsg);
8
Transaction Support in SQL
A
single SQL statement is always considered to
be atomic.
– Either the statement completes execution
without error or it fails and leaves the database
unchanged. We will see this later by example.
 With SQL, there is not always an explicit Begin
Transaction statement.
– Transaction initiation is done implicitly when
particular SQL statements are encountered.
 Every transaction must have an explicit end
statement, which is either a COMMIT or
ROLLBACK.
9
Properties of a Transaction
Consider the Workload characteristic of your system:
OLTP:
•High frequency inserts, updates, deletes.
•Usually single record being modified.
•Many small I/O requests.
•Risk of contention (locking) with multiple users.
DSS:
•Low frequency of data modification.
•Large number of inserts may be done at system startup or in batches at night.
•Primarily read-only.
•Heavy use of joins and selects.
•Ad hoc reporting.
•Low risk of locking.
•High CPU usage, smaller number of larger I/O requests.
Mixed:
•Combined high frequency modifications and read-only applications.
10
General Transaction Guidelines
Use short, well defined transaction
Break long transactions into several short ones if possible based on business rules.
Avoid updating or deleting large numbers of rows in a single transaction.
Do not put unnecessary logic inside a transaction.
Perform lookups outside of transactions.
Maximize concurrency
Minimize locking.
Design with throughput in mind
Avoid deferred updates.
Use stored procedures.
It is necessary to verify the success or failure of every SQL operation which modifies the database
within a transaction.
Return informational messages to the client in the event of failure.
11
Transaction Support
 Can
have one of two outcomes:
– Success – transaction commits and database reaches a
new consistent state.
– Failure – transaction aborts, and database must be
restored to consistent state before it started.
– Such a transaction is rolled back or undone.
 Committed
transaction cannot be aborted.
 Aborted transaction that is rolled back can be
restarted later.
12
State Transition Diagram for Transaction
13
Desirable Properties of Transactions
ACID properties:
 Atomicity:
 Consistency preservation:
 Isolation:
 Durability or permanency
14
Types of Failures
 System
crashes, resulting in loss of main
memory.
 Media failures, resulting in loss of parts of
secondary storage.
 Application software errors.
 Natural physical disasters.
 Carelessness or unintentional destruction of
data or facilities.
 Sabotage.
15
What could fail?
Why recovery is needed:
(What causes a Transaction to fail)
1. A computer failure (system crash):
A hardware or software error occurs in the computer system
during transaction execution. If the hardware crashes, the
contents of the computer’s internal memory may be lost.
2. A transaction or system error:
Some operation in the transaction may cause it to fail, such as
integer overflow or division by zero. Transaction failure
may also occur because of erroneous parameter values or
because of a logical programming error. In addition, the user
may interrupt the transaction during its execution.
16
Failure
Why recovery is needed (Contd.):
(What causes a Transaction to fail)
3. Local errors or exception conditions detected by the
transaction:
Certain conditions necessitate cancellation of the transaction. For
example, data for the transaction may not be found. A
condition, such as insufficient account balance in a banking
database, may cause a transaction, such as a fund
withdrawal from that account, to be canceled.
A programmed abort in the transaction causes it to fail.
4. Concurrency control enforcement:
The concurrency control method may decide to abort the
transaction, to be restarted later, because it violates
serializability or because several transactions are in a state
of deadlock.
17
Failure
Why recovery is needed (contd.):
(What causes a Transaction to fail)
5. Disk failure:
Some disk blocks may lose their data because of a read or write
malfunction or because of a disk read/write head crash. This
may happen during a read or a write operation of the
transaction.
6. Physical problems and catastrophes:
This refers to an endless list of problems that includes power or
air-conditioning failure, fire, theft, sabotage, overwriting
disks or tapes by mistake, and mounting of a wrong tape by
the operator.
18
Transactions and Database Recovery
Recovery is the process of restoring database to a
correct state in the event of a failure.
 Need
for Recovery Control
– Two types of storage: volatile (main memory) and
nonvolatile.
– Volatile storage does not survive system crashes.
– Stable storage represents information that has
been replicated in several nonvolatile storage
media with independent failure modes.
19
Data Modification Process
Cache
CPU
Transaction log
Disk
2
A= 10
Begin Transaction
.
.
.
A=A+ 1
.
.
.
Commit Transaction
Page 100
A= 10
1
1
2
CPU needs data item “A”. It looks in cache but does not find it. It locates it on page 100 on disk.
Page 100 is loaded into cache and read by the CPU.
Data Modification Process
4
Transaction log
Cache
(T1 Start) A=10 -> A= 11 (T1 Commit)
A= 11
Disk
5
CPU
3
Begin Transaction
.
.
.
A=A+ 1
.
.
.
Commit Transaction
Dirty data page
(T1 Start) A=10 -> A= 11 (T1 Commit)
A= 10
3 The CPU modifies “A” from 10 to 11.
4 The modification is written to the log in cache, to the data page in cache.
5 The transaction log from cache is written to disk (on commit)
Transactions and Recovery
 Transactions represent
basic unit of recovery.
 Recovery manager responsible for atomicity and
durability.
 If failure occurs between commit and database
buffers being flushed to secondary storage then,
to ensure durability, recovery manager has to
redo (rollforward) transaction’s updates.
 If
transaction had not committed at failure time,
recovery manager has to undo (rollback) any
effects of that transaction for atomicity.
22
Logging and Recovery
Types of Storage:
Actions in a transaction:
Volatile: Ram / cache memory.
•Reading / writing data items from disk or cache.
•Comparisons of data items.
•Arithmetic operations.
•SQL operations.
Nonvolatile: Disk, Tape, CD, …
Stable: Storage that can never be lost.
Recovery Strategies: The objective of recovery is to restore the database to a previous correct state. Usually as
close to the time of failure as possible but this may be application dependent. Recovery strategy must be planned
before deployment and is the responsibility of the developers and the DBA. The customer however must make
the final decision as to the acceptable loss.
Transaction Log
Each database has its own transaction log.
The transaction log is maintained in cache.
Changes to the log & data pages take place in cache first.
23
Recovery Facilities
 DBMS
should provide following facilities to
assist with recovery:
– Backup mechanism, which makes periodic
backup copies of database.
– Logging facilities, which keep track of current
state of transactions and database changes.
– Checkpoint facility, which enables updates to
database in progress to be made permanent.
– Recovery manager, which allows DBMS to
restore database to consistent state following a
failure.
24
Introduction to Transaction Processing
 For
recovery purposes, the system needs to keep
track of when the transaction starts, terminates,
modifcations, and commits or aborts.
READ AND WRITE OPERATIONS:


Basic unit of data transfer from the disk to the computer main memory
is one block. In general, a data item (what is read or written) will be
the field of some record in the database, although it may be a larger
unit such as a record or even a whole block.
read_item(X) command includes the following steps:
– Find the address of the disk block that contains item X.
– Copy that disk block into a buffer in main memory (if that disk
block is not already in some main memory buffer).
– Copy item X from the buffer to the program variable named X.
25
Introduction to Transaction Processing
READ AND WRITE OPERATIONS (contd.):
 write_item(X) command includes the following steps:
– Find the address of the disk block that contains item X.
– Copy that disk block into a buffer in main memory (if that disk
block is not already in some main memory buffer).
– Copy item X from the program variable named X into its
correct location in the buffer.
– Store the updated block from the buffer back to disk (either
immediately or at some later point in time).
26
Transaction and System Concepts
Commit Point of a Transaction:
 Definition a Commit Point:
– A transaction T reaches its commit point when all its
operations that access the database have been executed
successfully and the effect of all the transaction operations on
the database has been recorded in the log.
– Beyond the commit point, the transaction is said to be
committed, and its effect is assumed to be permanently
recorded in the database.
– The transaction then writes an entry [commit,T] into the log.

Roll Back of transactions:
– Needed for transactions that have a [start_transaction,T] entry
into the log but no commit entry [commit,T] into the log.
27
Log File
 Contains
information about all updates to
database:
– Transaction records.
– Checkpoint records.
 Often used for other purposes (for example,
auditing).
[T1start A= 10 -> A = 11, T2start, T1commit, T3start, T4start, T4commit, T5start ]
28
Log File

Transaction records contain:
– Transaction identifier.
– Type of log record, (transaction start, insert,
update, delete, abort, commit).
– Identifier of data item affected by database
action (insert, delete, and update operations).
– Before-image of data item.
– After-image of data item.
– Log management information.
29
Log File
 Log
file may be duplexed or triplexed.
 Log file sometimes split into two separate
random-access files.
 Potential bottleneck; critical in determining
overall performance.
30
Transaction and System Concepts

The System Log (cont):
– T in the following discussion refers to a unique transaction-id
that is generated automatically by the system and is used to
identify each transaction:
– Types of log record:
» [start_transaction,T]: Records that transaction T has started
execution.
» [write_item,T,X,old_value,new_value]: Records that
transaction T has changed the value of database item X from
old_value to new_value.
» [read_item,T,X]: Records that transaction T has read the value
of database item X.
» [commit,T]: Records that transaction T has completed
successfully, and affirms that its effect can be committed
(recorded permanently) to the database.
» [abort,T]: Records that transaction T has been aborted.
31
Transaction and System Concepts
Recovery – The Law
1. Any transaction that committed, must survive!.
2. Any transaction that made modifications but did not
commit, must go!
Recovery using log records:

If the system crashes, we can recover to a consistent database state by examining
the log.
1. Because the log contains a record of every write operation that changes the
value of some database item, it is possible to undo the effect of these write
operations of a transaction T by tracing backward through the log and resetting
all items changed by a write operation of T to their old values.
2. We can also redo the effect of the write operations of a transaction T by tracing
forward through the log and setting all items changed by a write operation of T
(that did not get done permanently) to their new_values.
32
Checkpointing
Checkpoint
Point of synchronization between database
and log file. All buffers are force-written to
secondary storage.
 Checkpoint
record is created containing
identifiers of all active transactions.
 When failure occurs, redo all transactions that
committed since the checkpoint and undo all
transactions active at time of crash.
33
Main Recovery Techniques

Two main recovery techniques:
– Deferred Update
– Immediate Update
Deferred Update:
Using the deferred update, all writes (changes to the data) are done on the log only until the
transaction is complete and a commit is written to the log. Then all changes are written to the
database. For recover, any transactions in the log which have not committed, need no action taken
since they were never written to the disk. For transactions which have committed, the DBMS must
ensure that their values were recorded in the database.
LOG
Database (DISK)
A = 1000
B = 2000
T1 Starts
T1 A = 950
T1 B = 2050
T1 COMMITTS
A = 950
B = 2050
34
Deferred Update Summary
 Updates
are not written to the database until
after a transaction has reached its commit point.
 If transaction fails before commit, it will not have
modified database and so no undoing of changes
required.
 May be necessary to redo updates of committed
transactions as their effect may not have reached
database.
35
Recovery cont…
Immediate Update
The immediate method allows the DBMS to make some updates to the database before the commit
point is reached or recorded in the log. The data modifications are still always written to the log
before the are written to the database. Recovery – Transactions that fail after making partial changes
to the database but don’t reach the commit point, must be rolled back or undone. Transactions that
have committed may or may not need to be redone.
LOG
Database(disk)
A = 1000
B = 2000
T1 STARTS
T1 (A, 1000 -> 950)
T1 (B, 2000 -> 2050)
A = 950
B = 2050
T1 COMMITTS
36
Immediate Update Summary






Updates may be applied to database as they occur.
May need to redo updates of committed transactions
following a failure.
May need to undo effects of transactions that had not
committed at time of failure.
Essential that log records are written before write to
database. …
Purchase answer to see full
attachment

How it works

1. Paste your instructions in the instructions box. You can also attach an instructions file
2. Select the writer category, deadline, education level and review the instructions 
3. Make a payment for the order to be assignment to a writer
4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades