1 1 Join operations are both commutative and

1. (1) Join operations are both commutative and the associative it is established. Describe each law's impact on optimization. (2) Why join optimization is important to left-deep tree Explain in detai.

(3) Among the join tree for the four relations, pipelining can be applied Show one example of missing. (4) Three Heuristic rules used in a typical Query optimization Explain what it is.

2. Using Linear hashing, draw the results changed after the following insertions. Insert 63, 41, 73, 137, 18, 34, 66, 130 ; h 1 Level = 0, N = 4 h 0 Next = 0 PRIMARY PAGES 000 00 64* 44* 001 01 9* 25* 5* 010 10 10* 011 11 31* 15* 7* 3*

3. Then calculate the I/O cost for each join algorithm. And this cost Calculate the minimum number of Buffer pages required to remain intact. 단, M(the number of pages in R) = 1, 000, N(the number of pages in S) = 500, B(the number of buffer pages) = 10. (1) Page Nested Loop Join (2) Block Nested Loop Join (3) Sort Merge Join (4) Hash Join

4. Whether each of the following schedules is "recoverable", "cascade-less", or "strict" explain the reasons for this. However, here, R read, W write, C is Commit. 1) S 1 : R 1(X); W 1(X); R 1(Y); W 1(Y); C 1; R 2(X); W 2(X); C 2; 2) S 2 : R 1(X); W 1(X); R 1(Y); W 1(Y); R 2(X); C 1; W 2(X); C 2; 3) S 3 : R 1(X); W 1(X); R 1(Y); W 1(Y); R 2(X); W 2(X); C 2; C 1; 4) S 4 : R 1(X); W 1(X); R 1(Y); R 2(X); W 1(Y); C 1; W 2(X); C 2; 5) S 5 : R 1(X); W 1(X); R 1(Y); R 2(X); W 1(Y); W 2(X); C 1; C 2;

5. The consistency conditions for the following transactions are as follows: Before performing If the values of A and B are zero, respectively, one of the values of A and B after execution should be zero. T 1 Read(A) Read(B) if (A = 0) then B = B + 1 Write(B) T 2 Read(B) Read(A) if (B = 0)then A = A + 1 Write(A) (1) Does a non-conflict serializable schedule exist from above? If yes Write an example. If no, explain in detail why. (2) Does the conflict serializable schedule exist from above? If yes Write an example. If no, explain in detail why.

6. Serial sequentially performed from the following transaction T 1 and T 2, in order of (T 1 → T 2) How many schedules exist both conflict-equivalent schedules? T 1 : R(A); A=A+2; W(A); R(B); B=B*3; W(B); T 2 : R(B); B=B*2; W(B); R(A); A=A+3; W(A);

7. Can the next schedule be established by applying Two Phase Locking? If not, explain why. If so, show the results applied. T 1 : T 2 : R(A); W(A); R(C); W(A); R(D); W(D);

8. If the following transaction T 1 and T 2 comply with two phase locking, respectively, Answer yes/no whether you always create a recoverable schedule. If yes, explain why, and show an example of a schedule that violates no. T 1 : R(A); W(A); R(B); W(B); T 2 : R(A); W(A);

9. Then rewrite each SQL query to a more efficient query. 1) No index is available SELECT DISTINCT(*) FROM Emp E 2) Index is available on ‘age’ SELECT E. eid FROM Emp E WHERE 2*E. age > 40 3) Index is available on ‘age’ SELECT MIN(E. age) FROM Emp E WHERE E. age=20 OR E. age=30 4) ‘did’ is a foreign key that refers to Dept SELECT E. eid FROM Emp E, Dept D WHERE E. did=D. did