Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

exam practice for database, Exams of Relational Database Management Systems (RDBMS)

exam practice for database, good to practice

Typology: Exams

2023/2024

Uploaded on 04/21/2024

unknown user
unknown user 🇺🇸

1 / 10

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
CMPSCI 645: Midterm Examination
(practice)
Name:
Question Points Extra credit Score
127 0
218 0
334 0
421 0
Tot a l : 100
The exam has 4 questions.
Write your name on the front page, and your initials on all other pages.
You can use a page of notes.
No laptops, or other mobile devices.
Please write clearly. We can’t give you credit for what we cannot read.
You have 1 hour and 15 minutes. Budget your time carefully.
pf3
pf4
pf5
pf8
pf9
pfa

Partial preview of the text

Download exam practice for database and more Exams Relational Database Management Systems (RDBMS) in PDF only on Docsity!

CMPSCI 645: Midterm Examination

(practice)

Name:

Question Points Extra credit Score

1 27 0

2 18 0

3 34 0

4 21 0

Total: 100

  • The exam has 4 questions.
  • Write your name on the front page, and your initials on all other pages.
  • You can use a page of notes.
  • No laptops, or other mobile devices.
  • Please write clearly. We can’t give you credit for what we cannot read.
  • You have 1 hour and 15 minutes. Budget your time carefully.

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.

  1. Indexing [18 points]

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"

  1. Conjunctive Queries [34 points]

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