sql代写|Assignment代写 - COMP9311 Assignment

Please make sure that you always use notations consistent with lecture notes. Different notations will not be accepted. The deadline for assignment 2 is: Wed 18, Nov 5:00 pm Question 1 (15 marks) Consider a relation 𝑅(𝐴,𝐵, 𝐶,𝐷, 𝐸, 𝐺, 𝐻,𝐼,𝐽) and its FD set 𝐹 = {𝐴 → 𝐵𝐶, 𝐶𝐷 → 𝐴𝐸, 𝐸 → 𝐶𝐻𝐼, 𝐻 → 𝐽}. 1) Check if 𝐴 → 𝐼 ∈ F+ . (3 marks) 2) Find a candidate key for 𝑅. (3 marks) 3) Determine the highest normal form of 𝑅 with respect to 𝐹. Justify your answer. (3 marks) 4) Find a minimal cover 𝐹𝑚 for 𝐹. (3 marks) 5) Decompose into a set of 3NF relations if it is not in 3NF step by step. Make sure your decomposition is dependency-preserving and lossless-join. (3 marks) Question 2 (10 marks) Consider the schedule below. Here, R(*) and W(*) stand for ‘Read’ and ‘Write’, respectively. , , and represent four transactions and tirepresents a time slot. Time t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 R(A) R(C) W(A) W(C) R(A) W(A) R(B) W(B) R(B) R(C) R(A) W(C) W(B) W(A) R(C) W(C) R(B) W(B) Each transaction begins at the time slot of its first Read and commits right after its last Write (same time slot). Regarding the following questions, give and justify your answers. 1) Is the transaction schedule conflict serializable? Give the precedence graph to justify your answer. (4 marks) 2) Give a serial schedule of these four transactions (there can be more than 12 time slots). (3 marks) 3) Lock the transactions and according to the simple locking scheme. You only need to consider the order of the operations, not the timestamps. (3 marks) Assignment Submission • Students must submit an electronic copy of their answers to the above questions to the course website in Moodle. • Only .doc or .pdf file is accepted. The file name should be ass2_studentID.doc or ass2_studentID.pdf (e.g., ass2_z5100000.doc or ass2_z5100000.pdf). Note: 1. For any problems in submissions, please email to comp9311unsw@gmail.com 2. All submissions will be checked for plagiarism. 3. We do not accept e-mail submissions. The university regards plagiarism as a form of academic misconduct and has very strict rules regarding plagiarism. For UNSW policies, penalties, and information to help avoid plagiarism, please see: https://student.unsw.edu.au/plagiarism as well as the guidelines in the online ELISE tutorials for all new UNSW students: https://subjectguides.library.unsw.edu.au/elise Late Submission Penalty 20% of the value of the submission will be deducted for each day (24 hours). Submissions with more than five days late will not be marked.