Q1 15pts Below is a log of a transactional database.
LOG
<Start T1>
<T1, c, 12>
<Start T3>
<T1, B, 16>
<Start T2>
<T2, A, 10>
<Commit T1>
<Abort T2>
<Start T4>
<T4, B, 19>
<Commit T3>
<T4, C, 22>
crash
a. Suppose this was an UNDO-log. What are the values of A, B, and C after recovery? Write “NA” if that value is unknowable from this log.
b. Suppose this was a REDO-log. What are the values of A, B, and C after recovery? Write “NA” if that value is unknowable from this log.
Q2 10pts Use 2 phase locking (2PL) to enforce a serializable schedule of two interleaved transactions shown below (use read, write, lock, unlock):
T1 = READ(A,t); t := t*3; WRITE(A,t); READ(B,t); t := t*2; WRITE(B,t); T2 = READ(B,t); t := t+4; WRITE(B,t);
| Tx | Action | t | Mem A | Mem B |
|---|---|---|---|---|
| T1 | lock1(A) | – | 7 | 0 |
| … |
Q3: 10pts MapReduce on Databases is the magic behind BigData processing. One important note is that there is usually lots of metadata in the tuple that was not included on the board exercises. In this problem, metadata of a tuple is indicated as a sub-tuple. For example: v = (A, Key1, Kansas, 6) means that this tuple has a primary key 1, and lives in table A. We can access it with v.key = ‘Key1’ and v.tbl = TblA, and v.state = ‘Kansas’ and v.x = 6.
Relations A and B have the same schema with three columns (tbl, key, state, x). Write map and reduce code to perform an inner join between relation A and B on the key-columns.
Server1 Server2 Server3 (1, (A, 1, Kansas, 6)) (4, (B, 4, Washington, 12)) (3, (A, 3, Idaho, 4)) (2, (A, 2, Indiana, 11)) (3, (B, 3, New York, 29)) (2, (B, 2, Illinois, 20)) (1, (B, 1, Vermont, 3))
public void map(key k, tuple v):
public void reduce(key k, list v):
Q4: 10pts Say there are 3 mappers over three servers and 1 reducer. For the data and code in Q3 show the actual values that are:
a. output from each the 3 mappers
b. input to the reducer
c. output from the reducer
Write the data, be verbose.
Q5: 15pts SQL Injection attacks are an enormous problem in Web application development: https://en.wikipedia.org/wiki/SQL_injection
Read up on it and its details.
Now, say I have some PHP code that looks like this:
$uname = $_POST['username'];
$passwd = $_POST['password'];
$sql = “SELECT id FROM users WHERE username='" . $uname . "' AND password='" . $passwd . "'";
$result = mysql_query($db, $sql)
from a Web form that looks like this:
a. Explain why this code is vulnerable to SQL injection attacks.
b. Design a SQL injection attack that drops the user table.
c. Fix this vulnerable PHP code so that it is no longer susceptible to SQL injection.
Q6: 1pt Extra Credit – Give Relation A and Relation B from Q3 appropriate names. What does this data represent?