Question

Hello There,

This is discussion Question

For Advanced Database Systems

Question:

(a) Please define what a “trigger” is. If your answer is from the textbook, please state which page of the textbook from which you got the definition. If your answer is not from the textbook, please also include the complete reference.

(b) Please define what “PL/SQL” is. If your answer is from the textbook, please state which page of the textbook from which you got the definition. If your answer is not from the textbook, please also include the complete reference.

(c) Please define what a “transaction” is. If your answer is from the textbook, please state which page of the textbook from which you got the definition. If your answer is not from the textbook, please also include the complete reference.

(d) Please define “concurrency control” in database operations. If your answer is from the textbook, please state which page of the textbook from which you got the definition. If your answer is not from the textbook, please also include the complete reference.

Thank you

Answer 1

Trigger(chapter 5, page - 180 of book Database System Concepts” by Abraham Silberschatz and S Sudarshanpdf)

1.A trigger is special stored procedure that is run when specific actions occur within a database.

2.riggers can be defined to run instead of or after  "Data Manipulation Language" actions such as """INSERT, UPDATE, and DELETE.""".

3.Triggers help the database designer ensure certain actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data.

PL/SQL

1.PL/SQL is an extension of Structured Query Language (SQL) that is used in Oracle.

2. PL/SQL == "Procedural Language extensions to SQL".

3. PL/SQL means instructing the compiler 'what to do' through SQL and 'how to do' through its procedural way.

4.It combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries.

Transaction( chapter 14, page - 627 of book Database System Concepts” by Abraham Silberschatz and S Sudarshanpdf)

1.A transaction is a collection of operations that performs a single logical function in a database application.

2.Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.

3.Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the
database.

Concurrency control(chapter 15, page - 661 of book Database System Concepts” by Abraham Silberschatz and S Sudarshanpdf)

1. Concurrency control is the procedure in DBMS for managing simultaneous operations without conflicting with each another.

Lock-Based Protocols:--

1.A lock is a mechanism to control concurrent access to a data item.

2.Data items can be locked in two modes :--

a.exclusive (X) mode. Data item can be both read as well as written. X-lock is requested using lock-X instruction.

b.shared (S) mode. Data item can only be read. S-lock is requested using lock-S instruction..

3.Lock requests are made to concurrency-control manager. Transaction can proceed only after request is granted.

4.A locking protocol is a set of rules followed by all transactions while requesting and releasing locks. Locking protocols restrict the set of possible schedules.

5.Starvation is also possible if concurrency control manager is badly designed. For example::--

i..A transaction may be waiting for an X-lock on an item, while a sequence of other transactions request and are granted an S-lock on the same item.

ii.--The same transaction is repeatedly rolled back due to deadlocks.

The Two The Two-Phase Locking Protocol Phase Locking Protocol

1.This is a protocol which ensures conflict-serializable schedules

2.Phase 1: Growing Phase:--

i. transaction may obtain locks

ii. transaction may not release locks.

3.Phase 2: Shrinking Phase:--

i.! transaction may release locks

ii. transaction may not obtain locks

4.The protocol assures serializability. It can be proved that the transactions can be serialized in the order of their lock points (i.e. the point where a transaction acquired its final lock).

5.Cascading roll-back is possible under two-phase locking. To avoid this, follow a modified protocol called strict two-phase locking. Here a transaction must hold all its exclusive locks till it commits/aborts.

6.Rigorous two-phase locking is even stricter: here all locks are held till commit/abort. In this protocol transactions can be serialized in the order in which they commit.

Timestamp-Based Protocols

1.Each transaction is issued a timestamp when it enters the system. If an old transaction Ti has time-stamp TS(Ti), a new transaction Tj is assigned time-stamp TS(Tj) such that TS(Ti).

2.The protocol manages concurrent execution such that the timestamps determine the serializability order.

3. In order to assure such behavior, the protocol maintains for each data Q two timestamp values:---

i.W-timestamp(Q) is the largest time-stamp of any transaction that executed write(Q) successfully.

ii.R-timestamp(Q) is the largest time-stamp of any transaction that executed read(Q) successfully.

4.The timestamp ordering protocol ensures that any conflicting read and write operations are executed in timestamp order.

Validation-Based Protocols

1.Each transaction Ti has 3 timestamps:--

i.Start(Ti) : the time when Ti started its execution

ii.Validation(Ti): the time when Ti entered its validation phase.

iii.Finish(Ti) : the time when Ti finished its write phase.

2.Serializability order is determined by timestamp given at validation time, to increase concurrency. Thus TS(Ti) is given the value of Validation(Ti)..

3.This protocol is useful and gives greater degree of concurrency if probability of conflicts is low. That is because the serializability order is not pre-decided and relatively less transactions will have to be rolled back..

4.If for all Ti with TS (Ti) < TS (Tj) either one of the following condition holds:---

i.finish(Ti) < start(Tj)

ii.start(Tj) < finish(Ti) < validation(Tj) and the set of data items written by Ti does not intersect with the set of data items read by Tj.

5.Justification: Either first condition is satisfied, and there is no overlapped execution, or second condition is satisfied and:--

i.the writes of Tj do not affect reads of Ti since they occur after Ti has finished its reads.

ii.the writes of Ti do not affect reads of Tj since Tj does not read any item written by Ti.