Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
exam practice for database, good to practice
Typology: Exams
Uploaded on 04/21/2024
1 / 10
This page cannot be seen from the preview
Don't miss anything!
Name:
Question Points Extra credit Score
1 27 0
2 18 0
3 34 0
4 21 0
Total: 100
This page would have been blank if it weren’t for this sentence.
(b) Consider the following relation and functional dependencies.
SupremeCourt(Docket, Appellant, Respondent, oral arGument, Opinion author, appoInted by, Party) OI! P RO! I G! ARO D! G GA! D
i. [10 points] Write the lossless decomposition of the SupremeCourt relation into BCNF, by resolving the constraints that violate BCNF (if any), in the order that the FDs are given (start from the first FD, and move downwards). Stop decomposing as soon as there are no more violations.
Solution: DARGOIP decomposes into DARGOI and OIP, based on the first FD. DARGOI decomposes into DARGO and ROI, based on the second FD. Final lossless-join decomposition: DARGO, ROI, OIP
ii. [3 points] Is this decomposition dependency-preserving?
Solution: Yes. For every FD, all attributes in the FD appear in a single table in the decomposition.
Consider indexing the following key values using an extendible hash table. Their corre- sponding hash value is already computed. Assume each bucket can hold at most 2 data items. Assume an initial state of the hash table as shown in the picture.
n h(n) A 1001 B 1111 C 1101 D 1011 E 1000 F 0101 G 1010
Figure 1: Hash function.
0" 1"
1"
Global depth 1"
1"
Local depth
Figure 2: Initial state of the hash table.
(a) [8 points] Suppose the keys are to be inserted in ascending alphabetical order, ie. A, B, C, ..., G. The hash table is initially empty, as shown in the figure above. Draw the hash table (including the directory) after the insertion of keys A, B, and C. Show the keys themselves in the buckets, not the hash value. Be sure to indicate the global and local depths.
00" 01"
2"
1"
A" C"
2"
10" 11" (^) B"
2"
Assume the following conjunctive queries:
q 1 (x) : R(x, y), R(y, u) q 2 (x) : R(x, u), R(x, x) q 3 (x) : R(x, u), R(v, u), R(u, y) q 4 (x) : R(x, y), R(y, x)
(a) [9 points] Is q 1 ✓ q 3? If the answer is yes, provide a proof; if the answer is no, give a database instance I on which q 1 (I) 6 ✓ q 3 (I).
(a) Yes
Solution: Homomorphism f : q 3! q 1 f (x) = x, f (u) = y, f (v) = x, f (y) = u.
(b) [9 points] Is q 1 ✓ q 4? If the answer is yes, provide a proof; if the answer is no, give a database instance I on which q 1 (I) 6 ✓ q 4 (I).
(b) No
Solution: q 4 is not satisfied on the canonical database of q 1 : x y y u
(c) [16 points] Let q and q^0 be unions of two conjunctive queries as follows: q = q 1 [ q 2 and q^0 = q 3 [ q 4. Is q contained in q^0 (q ✓ q^0 )? Show proof of your response.
(c) Yes
Solution: As shown in (a), q 1 ✓ q 3 , therefore q 1 ✓ q 3 [ q 4 ) q 1 ✓ q^0. Additionally, there is a homomorphism f : q 4! q 2 f (x) = x, f (y) = x. Therefore, q 2 ✓ q 4 ) q 2 ✓ q 3 [ q 4 ) q 2 ✓ q^0. Since q 1 ✓ q^0 and q 2 ✓ q^0 , q 1 [ q 2 ✓ q^0 ) q ✓ q^0.
(c) [5 points] Mark the correct answer to the following questions.
i. For any schedules S 1 and S 2 , if S 1 and S 2 are conflict serializable, then S 1 and S 2 are conflict equivalent. 2 True ⇥ False
ii. Schedule 2 from part (b) cannot be produced by 2PL. ⇥ True 2 False
iii. An IX lock is compatible with an IS lock, i.e. if a transaction holds an IS lock on an object, then another transaction can take an IX lock on the same object. ⇥ True 2 False
iv. In timestamp-based concurrency control, if a transaction gets aborted, it will be restarted with a new timestamp. ⇥ True 2 False
v. Some conflict-serializable schedules are not view serializable. 2 True ⇥ False
