Database Assignment: PL/SQL, T-SQL, Concurrency

This homework assignment focuses on database concepts, including PL/SQL for banking transactions, T-SQL for scheduling jobs, and concurrency control mechanisms. It requires students to analyze schedules for serializability, apply locking protocols, and understand deadlock prevention and detection. The assignment includes practical exercises involving transaction management and concurrency control techniques.

Assignment
Rubric Assessment Score
Lab Assessment ( Weight =10%)
The purpose of the lab is clearly identified and stated and lab report illustrates an accurate and thorough
understanding of concepts underlying the lab. /3
Professional looking and accurate representation of the data in tables and/or graphs. Graphs and tables
are labeled and titled. /3
The relationship between the variables is discussed and trends/patterns logically analyzed. Predictions
are made about what might happen if part of the lab were changed or how the design could be changed. /3
Summary describes the skills learned, the information learned and some future applications to real life
situations. /3
Grade: /12
Q1. Use PL/SQL in a high-level host language to do the following banking transaction. The
program will continue till the user will accept -1. Program will accept account number like 1, 2,
3 and debit amount from the user and check the balance is available or not. If available then
update the bal field. If account is not available then display appropriate message. Use the
following table to write the above program. [3 Marks]
Q2. Use T-SQL to create a schedule named NightlyJobs. Jobs that use this schedule execute
every day when the time on the server is 01:00. [2 Marks]
1 | P a g e

Note: To answer Q3 and Q4, you can create tables with dummy data and use
insert/update/delete operations to test the given scenario.
Q3.Consider the following classes of schedules: serializable, conflict-serializable, view
serializable, recoverable, avoids-cascading-aborts, and strict. For each of the following
schedules, state which of the preceding classes it belongs to. If you cannot decide whether a
schedule belongs in a certain class based on the listed actions, explain briefly. The actions are
listed in the order they are scheduled and prefixed with the transaction name. If a commit or
abort is not shown, the schedule is incomplete; assume that abort or commit must follow all the
listed actions. [2 Marks]
1. T1: W(X), T2: R(Y), T1: R(Y), T2: R(X)
2. T1: R(X), T1: R(Y), T1: W(X), T2: R(Y), T3: W(Y), T1: W(X), T2: R(Y)
Q4. Consider the following sequences of actions, listed in the order in which they are submitted
to the DBMS:
Sequence S1: T1:R(X), T2:W(X), T2:W(Y), T3:W(Y), T1:W(Y), T1: Commit, T2:Commit,
T3:Commit
Sequence S2: T1:R(X), T2:W(Y), T2:W(X), T3:W(Y), T1:W(Y), T1:Commit, T2:Commit,
T3:Commit
For each sequence and for each of the following concurrency control mechanisms, describe how
the concurrency control mechanism handles the sequence.
Assume that the timestamp of transaction Ti is i.
For lock-based concurrency control mechanism, add lock and unlock requests to the above
sequence of actions as per the locking protocol. If a transaction is blocked, assume that all of its
actions are queued until it is resumed; the DBMS continues with the next action (according to the
listed sequence) of an unblocked transaction.
1. Strict 2PL with timestamps used for deadlock prevention. [1 Mark for S1 and 0.5 for S2]
2 | P a g e

2. Strict 2PL with deadlock detection. Show the waits-for graph if a deadlock cycle develops.
[1 Mark for S1 and 0.5 for S2]
Use Wait-Die policy to answer the above question.
3 | P a g e