20T3 COMP9311 Sample Solution
Q1:
1. False, the CREATE TYPE statement allows to create a new type.

2. False, because in SQL AS is only to rename the attribute.

3. True, 2NF requires that every nonprime attribute is fully dependent on every candidate
key. BCNF requires that every attribute must be fully dependent on every key.

4. False, the primary key is an attribute or a set of attributes that uniquely identify a
specific instance of an entity. Every entity in the data model must have a primary key
whose values uniquely identify instances of the entity.

5. False, A lossless and dependency-preserving decomposition into 3NF is always
possible.

6. False, OS file system can improve portability and scalability not speed.

7. True, SQL cannot control sequences of database operations

8. False, Hash index is not suitable for range check, it is suitable for specific value query

9. False, ISAM does not store data

10. False, optimistic control is a good option if there is not much interaction between
transactions.















Q2:
(a):

(b):




Q3:
(a):
(1):
Reduce right side.
F’={BD->C, BD->H, BC->H, BC->I, EI->H, H->A, H->B, I->E, EJ->I}
Reduce left side.
BD->C,
B
+
={B}; thus B->C is not inferred by F’.
Hence, BD->C cannot be replaced by D->C.
D
+
={D}; thus D->C is not inferred by F’.
Hence, BD->C cannot be replaced by B->C.
EI->H,
E
+
={E}; thus E->H is not inferred by F’.
Hence, EI->H cannot be replaced by E->H.
I
+
= {E, H, I}; thus I->H is inferred by F’.
Hence, EI->H can be replaced by I->H.
Iteratively reduce left side, then we can get:
F’’ = {BD->C, BD->H, BC->H, BC->I, I->H, H->A, H->B, I->E, EJ->I}
Remove redundant FDs.
BD
+
|F’’ – {BD->C} = {A, B, D, H}; thus BD->C is not inferred by F’’– {BD->C}. That is, BD->C is not
redundant.
BD
+
|F’’ – {BD->H} = {A, B, C, D, E, H, I}; thus BD->H is redundant.
Thus, we can remove BD->H from F’’ and get F’’’.
F’’’= {BD->C, BC->H, BC->I, I->H, H->A, H->B, I->E, EJ->I}
BC
+
|F’’’ – {BC->H} = {A, B, C, E, H, I}; thus BC->H is redundant.
Thus, we can remove BC->H from F’’ and get F’’’’.
F’’’’= {BD->C, BC->I, I->H, H->A, H->B, I->E, EJ->I}
Iteratively, we can get Fmin
Thus, Fmin = {BD->C, BC->I, I->H, H->A, H->B, I->E, EJ->I}.

(2):
Find a super key X.
Let X:={BCDEGJHI},
Try to remove B, {CDEGJHI}
+
= {A,B,C,D,E,G,H,I,J}
Thus, X:= {CDEGJHI}
Try to remove C, {DEGJHI}
+
= {A,B,C,D,E,G,H,I,J}
Thus, X:= {DEGJHI}
Try to remove D, {EGJHI}
+
= {A,B,E,G,H,I,J}
Thus, D cannot be removed.
Try to remove E, {DGJHI}
+
= {A,B,C,D,E,G,H,I,J}
Thus, X:= {DGJHI}
Try to remove G, {DJHI}
+
= {A,B,C,D,E,H,I,J}
Thus, G cannot be removed.
Try to remove J, {DGHI}
+
= {A,B,C,D,E,H,I}
Thus, J cannot be removed.
Try to remove H, {DGJI}
+
= {A,B,C,D,E,G,H,I,J}
Thus, X:= {DGJI}
Try to remove I, {DGJ}
+
= {D,G,J}
Thus, I cannot be removed.
So {DGJI} is a candidate key and add to T.

Find another super key X.
Let X:= {BCDEGJH},
Try to remove B, {CDEGJH}
+
= {A,B,C,D,E,G,H,I,J}
Thus, B can be removed.
Try to remove C, {DEGJH}
+
= {A,B,C,D,E,G,H,I,J}
Thus, C can be removed.
Try to remove D, {EGJH}
+
= {A,B,E,G,H,I,J}
Thus, D cannot be removed.
Try to remove E, {DGJH}
+
= {A,B,C,D,E,G,H,I,J}
Thus, E can be removed.
Also, we can find that G,J,H cannot be removed.
So {DGJH} is a candidate key and add to T.

Find another super key X.
Let X:= {BCDEGJ},
Try to remove B, {CDEGJ}
+
= {A,B,C,D,E,G,H,I,J}
Thus, B can be removed.
Try to remove C, {DEGJ}
+
= {A,B,C,D,E,G,H,I,J}
Thus, C can be removed.
Also, we can find that D,E,G,J cannot be removed.
So {DEGJ} is a candidate key and add to T.

Find another super key X.
Let X:= {BCDGJ},
Try to remove B, {CDGJ}
+
= {C,D,G,J}
Thus, B cannot be removed.
Try to remove C, {BDGJ}
+
= {A,B,C,D,E,G,H,I,J}
Thus, C can be removed.
Also, we can find that D,G,J cannot be removed.
So {BDGJ} is a candidate key and add to T.

Cannot find any other super keys.
So, candidate keys are {BDGJ}, {DEGJ}, {DGJH}, {DGJI}.




(3):
No.

A B C D E G H I J
R1 a a a a b b a b b
R2 b b b b a a a a a

A B C D E G H I J
R1 a a a a b b a b b
R2 a a b b a a a a a

No row is entirely made up by “a” value, so the decomposition is not lossless join.


(4):
1NF, since H is a non-prime attribute, while it is partially functionally dependent on EI.
BCNF:
Consider BD->CH, BD is not a superkey, split R into R1(B,D,C,H) and R2(A,B,D,E,G,I,J)
Consider BC->H, BC is not a superkey, split R1 into R11(B,C,H) and R12(B,D,C)
Consider I->E, I is not a superkey in R2, split R2 into R21(E,I) and R22(A,B,D,G,I,J)

(b):
(1):
0 = ? ?
1 = {}( 0 ÷ ( {})
= {} ? 1

(2):
0 = {}(( ? ? ) ÷ {}( Restaurant) )
1 = {} ? {}
Resut = 0 ∪ 1

(3):
0 = {,,}( ? )
= {, ()}(0)









Q4:
(a):
(1):


(2):
There is a dead lock.

(b):
(1):

(2):
Yes. T2-T3-T1-T4














Q5:
(a):
(1):
buffer size = 4,
Query stream: p1, p2, p3, p4, p5, p1, p2, p3, p4

Since MRU results in the least number of page faults, it outperforms the other buffer updating
policies.
(2):
buffer size = 4,
Query stream: p1, p2, p3, p4, p5, p2, p6, p3, p4

Since FIFO results in the least number of page faults, it outperforms the other buffer updating
policies.

(b):
(1):
2-core: {v0, v1, v2, v3, v4, v5, v6, v7, v8, v9}
(2):
3-core: {v2, v3, v4, v7, v8, v9}
(3):
(3,2)-core: {v2, v3, v4, v9}