CS 5330/7330 Homework #7 2022

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CS 5330/7330

Homework #7

2022

1)  (20/15 points)  This figure show the status of five transactions . A checkpoint was registered in the logfile marking the last the time when pending updates in the database buffer were forced to disk. Later, a system crash occurred.

What recovery operation (nothing, redo, or undo) is needed for each transaction T1 .. T5. Assume immediate update.

2)  (20/20 points) Given R ( A int, B int, C int ). Consider this select statement:

Select R1.A, R1.B, R1.C from R R1 join R R2 on R1.A =  R2.B where R1.A mod 2 = 0 and R2.B = R2.C

a. Show the EXPLAIN PLAN for the table and statement

b. Identify one or more indexes that might improve performance.

c. Show the EXPLAIN PLAN and describe if you have made improvement in the execution.

3) (20/20 points) Consider these schedules involving 3 transactions:

S1:  r1(x); r2(y); r1(z); r3(z); r2(x); r1(y)
S2:  r1(x); w2(y); r1(z); r3(z); w2(x); r1(y)
S3:  r1(x); w2(y); r1(z); r3(z); w1(x); r2(y)
S4:  r1(x); r2(y); r1(z); r3(z); w1(x); w2(y)

For each schedule, draw the precedence graph and decide if the schedule is conflict-serializable.

4) (20/10 points) Fill in the blank

a. T/F:  ____ Every SQL statement must to logged because that information is used in the recovery process.

b. The ____ statement is one example of a statement that cannot be rollback’ed.

c. The ____ 2PL locking protocol guarantees deadlocks will not occur.

d. A ______ update is when a database updates after the transaction commits.

e. Reading uncommitted data is called a ____ read.

f. The MySQL storage engine ______ supports transactions.

g. T/F:  ____ Deadlock is easy to detect.

h. T/F:  ____ A rollback will always undo all of the operations that were tentatively performed inside of a transaction.

i. Relational DBMSs enforce the ____ property where NoSQLsystems enforce ____. (both answers are acronyms)

j. In a database, the __________ operation is used to force all modified buffers to disk.

5) (20/20 points) Given three simple tables:

Create table t1 ( a integer, b integer );
Create table t2 ( b integer, c integer );
Create table t3 ( c integer, d integer );
data is provided in 3 CSV files for importing, or 3 SQL “insert” files

a.  create the tables as defined above and import the data (10K rows) using LOAD DATA (or equivalent).  SQL insert statements are provided as an alternative.

Consider these three different but equivalent queries.

-- 1

select a from t1

where mod(a,5) = 0 and b in

( select b from t2

where c in ( SELECT c FROM t3 where mod(d,5) = 0 ));

-- 2

select t1.a from t1, t2, t3 where

t1.b = t2.b and t2.c = t3.c and mod(t1.a,5) = 0 and mod(t3.d,5) = 0;

-- 3

select t1.a from (t1 join t2 on t1.b = t2.b ) join t3 on t2.c = t3.c

where mod(t1.a,5) = 0 and mod(t3.d,5) = 0;

b.  Which one of the above select statements has the best execution performance?   Explain your decision, supported by facts, not just opinion.

6) (0/15 points) Consider an internet voting system where a voter must enter a PIN which is checked to ensure it is valid and not already used.  Two tables are used: PIN (number, if_voted) and VOTES (candidate, count).  Consider a new read-only transaction that outputs the entire vote tally table.  What is the appropriate transaction isolation level to ensure correctly reporting the voting results? Explain your decision and assumptions.

For example: