代写COMP9311 24T2: Assignment 2代做Prolog

COMP9311 24T2:Assignment 2

Deadline: Sun 21:59:59 28th July

Note: Please make sure that you always use notations consistent with lecture notes. Different notations will not be accepted.

Question 1 (12 marks)

Consider a relation  R (A, B, C, D, E, G, H, I, J)  and its FD set  F  = {AJ → IBE, DEJ  → IH, E → CA, CG → DI, AG → B, ADI → EH}.

Regarding the following questions. Give and justify your answers if the question is specified.

1)  Check if  EG  → I. Justify your answer. (1 mark)

2)  Find all the candidate keys for R. (2 mark)

3)  Determine the highest normal form. of R with respect to F. Justify your answer. (2 marks)

4)  Find a minimal cover  Fm    for  F. (2 marks)

5)  Regarding F, does the decomposition R1 = {CGIE}, R2 = {ADEJH}, R3 = {GBEH} of R satisfy the lossless join property? Please justify your answer. (2 marks)

6)  Provide a step-by-step lossless decomposition of R into BCNF normal form. (3 marks)

Question 2 (8 marks)

Consider the schedule below. Here, R(*) and W(*) stand for ‘Read’ and ‘Write’, respectively. T1, T2, T3, T4 and T5 represent five transactions and ti  represents a timeslot.

t1     t2      t3      t4       t5      t6       t7      t8      t9    t10   t11   t12   t13    t14    t15    t16

T1

R(X)

R(Y)

W(Y)

W(X)

T2

R(A)

W(X)

T3

R(Z)

R(X)

W(Z)

W(X)

T4

R(Z)

W(A)

W(Z)

T5

W(Y)

R(Z)

W(Z)

Note:  Each  transaction  begins  at  the  time  slot  of  its  first  operation  and commits right after its last operation (same timeslot).

Regarding the following questions. Give and justify your answers.

1)  Assume a checkpoint is made between t5  and t6, what should be done to the five transactions when the crash happens between t12  and t13. (2 marks)

2)  Is the transaction schedule conflict serializable? Give the full precedence graph to justify your answer. (2 marks)

3)  Construct a schedule (which is different from above) of these five transactions which causes deadlock when using two-phase locking protocol. You should clearly indicate all the locks and the corresponding unlocks in your schedule. If no such schedule exists, explain why. (4 marks)

Question 3 (6 marks)

Consider the following query:

P1, P2, P3, P2, P4, P5, P6, P6, P3, P7, P2, P3.

(The user is trying to read page 1 from disk, then page 2, page 3, …) Assume there are 3 buffers in the buffer pool.

1)  Sketch the process of how blocks are replaced in the Least Recently Used (LRU) policy. (1.5 marks)

2)  Sketch the process of how blocks are replaced in the Most Recently Used (MRU) policy. (1.5 marks)

3)  Sketch the process  of how blocks  are  replaced  in the  First  In  First  Out (FIFO) policy. (1.5 marks)

4)  Among LRU, MRU and FIFO policies, which one performs better in the given query? Why? (1.5 marks)

Assignment Submission

•   You  are  required  to  submit  an  electronic  version  of  your  answers  via Moodle. While we accept handwritten submissions, please ensure they are scanned or photographed clearly to ensure legibility.

•   We only accept the .pdf format. Please name your files in the following format: ass2_zID.pdf (e.g., ass2_z5000000.pdf).

Note:

1.   If   you   have   problems   relating   to   your   submission,   please   email   to junhua.zhang@unsw.edu.au.

2.   If there are issues with Moodle, send your assignment to the above email with the subject title “ <zid> COMP9311 Ass2 Submission” .

Late Submission Penalty

•    5% of the total mark (26 marks) will be deducted for each additional day.

•    Submissions that are more than five days late will not be marked.

Plagiarism

The work you submit must be your own work. Submission of work partially or completely derived from any other person or jointly written with any other person is not permitted. The penalties for such an offence may include negative marks, automatic failure of the course and possibly other academic discipline.

All submissions will be checked for plagiarism. The university regards plagiarism as a form. of academic misconduct and has very strict rules. Not knowing the rules will not be considered a valid excuse when you are caught.

•    For UNSW policies, penalties, and information to help avoid plagiarism, please see:https://student.unsw.edu.au/plagiarism.

•    For guidelines in the online ELISE tutorials for all new UNSW students: https://subjectguides.library.unsw.edu.au/elise/plagiarism.