DBMS Practice Test: Normalization and Transactions

Question 1 Multiple Choice (Single Answer)

In a particular schedule, transaction T2 reads a value previously written by transaction T1 and transaction T3 reads a value previously written by T2. Which of the following ensures the schedule to be cascade less schedule?

T1 commits before T2 and T2 commits before T3.
T1 commits after T2 and T2 commits before write operation of T3.
T1 commits before T2 and T2 commits before write operation of T3.
T1 commits at last and T2 and T3 can commit in any order.

Question 2 Multiple Choice (Single Answer)

Relation R with an associated set of functional dependencies, F is decomposed in to BCNF. The redundancy in the resulting set of relation is

zero
more than zero but less than that of an equivalent SNF decomposition
proportional to the size of $F^t$
indeterminate

Question 3 Multiple Choice (Single Answer)

For relation R(ABCDE), the given FD's are A$\rightarrow$BC, BC$\rightarrow$E and E$\rightarrow$DA, similarly S be a relation with attributes ABCDE and with FD's A$\rightarrow$BC, B$\rightarrow$E and E$\rightarrow$DA. Which among R and S are in BCNF?

R only
S only
Both of these
Neither of these

Question 4 Multiple Choice (Single Answer)

Consider the following:
$r(R):$r is a relation with attribute R
$s(S):$s is a relation with attribute S
$|x|:$denotes natural join operation
X: denotes Cartesian product operation
Then $r|xs=r \times s|:$ if

$R \cap S = R$
$R \cup S = S$
$R \cap S = \phi$`
none of these

Question 5 Multiple Choice (Single Answer)

With regard to the expressive power of the former relational query language, which of the following statements is true?

Relational algebra is more powerful than relational calculus.
Relational algebra has the same power as relational calculus.
Relational algebra has the same power as safe relational calculus.
None of the above

Question 6 Multiple Choice (Single Answer)

Set F of functional dependencies for relation schema R(A, B, C, D, E) is A$\rightarrow$BC, CD$\rightarrow$E, B$\rightarrow$D, E$\rightarrow$A.
Determine the number of candidate's keys for R.

2
3
4
5

Question 7 Multiple Choice (Single Answer)

Given R(A,B, C, D, E) and $ F = \{ B \rightarrow, AB \rightarrow C, CD \rightarrow E \} \text{ and } D = \{BE, ABDE\}$be decomposition.
What is the highest normal form of decomposition D?

1 NF
2 NF
3 NF
BCNF

Question 8 Multiple Choice (Single Answer)

Given R(A,B, C, D, E) and $ F = \{ B \rightarrow, AB \rightarrow C, CD \rightarrow E \} \text{ and } D = \{BE, ABDE\}$be decomposition.

What is the highest normal form of R?

1 NF
2 NF
3 NF
BCNF

Question 9 Multiple Choice (Single Answer)

Consider the following relation R (A, B, C, D, E, F) with FD set
A$\rightarrow$BCDEF
BC$\rightarrow$ADEF
B$\rightarrow$F
D$\rightarrow$E
The highest normal form achieved by R is

1 NF
2 NF
3NF
BNCF

Question 10 Multiple Choice (Single Answer)

Consider the schema R = (S, T, U, V) and dependencies S$\rightarrow$T, T$\rightarrow$U, U$\rightarrow$V and V$\rightarrow$S. Let R$R = \{ R_1 \text{ and } R_2 \}$be a decomposition such that $R_1 \cap R_2 = \phi$. The decomposition is

not in 2NF
in 2NF but not in 3NF
in 3NF but not in 2NF
in both 2NF and 3NF

Question 11 Multiple Choice (Single Answer)

Let r(R) and s(S) be relations and let $S \le R$, the division operation R/S can be expressed as

$\pi_{R-S}(R)$
$\pi_{R-S}(R)-\pi_{R-S}((\pi_{R-S}(r)\times S) - \pi_{R-S}S(r))$
$\pi_{R-S}(R)((\pi_{R-S}(r)\times S) - \pi_{R-S}S(r))$
$\pi_{R-S}(r)\times S:$

Question 12 Multiple Choice (Single Answer)

Consider the relation schema:
Parts (Pno, pname)
Supplier (Sid, Sname)
SP (sid, pno, qty)
The SQL query which display all the parts having more than one supplier is

select distinct p.pname from parts P, SP a, SP b where a.Pno = b.Pno and a.Sid = b.sid and a.Pno = P.pno
select distinct P.name from parts P, SP a, SP b where a.pno = b.pno and a.sid < > b.sid and P.Pno = a.Pno
cannot be determined
none of these

Question 13 Multiple Choice (Single Answer)

Consider a relation:
R(A, B, C, D) with FD
A$\rightarrow$BCD
BC$\rightarrow$AD
D$\rightarrow$B
The highest normal form achieved by R is

1 NF
2 NF
3 NF
BCNF

Question 14 Multiple Choice (Single Answer)

Suppose a schedule with two transactions $T_1 \& T_2$:


$T_1$	$T_2$
Read (A)
Write (A)
	Read (A) commit
Read (A) commit

The above schedule is

cascade less schedule
recoverable schedule
irrecoverable schedule
none of these

Question 15 Multiple Choice (Single Answer)

Consider the following schedule:
$R_1(A,B), R_2(B,C), R_3(B,C), V_1, V_2, V_3, W_1(A), W_3(C)$, where R stands for reads, W stands for write and V for validation. What will happen if above schedule is checked and is tested under optimistic concurrency control protocol?

$T_1$ roll will be backed.
$T_2$ roll will be blocked.
$T_3$ roll will be backed.
Successful completion will be there.

Question 16 Multiple Choice (Single Answer)

Let $R_1 \left(\underline{A}, B, C\right)$ and $R_2\left(\underline{D}, E \right) $ be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in $R_1$ referring to $R_2$. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances $r_1$ and $r_2$. Which of the following relational algebra expressions would necessarily produce an empty relation?

$\Pi_D (r_2) - \Pi_C (r_1)$
$\Pi_C (r_1) - \Pi_D (r_2)$
$\Pi_D \left(r_1 \bowtie_{C \neq D}r_2\right)$
$\Pi_C \left(r_1 \bowtie_{C = D}r_2\right)$

Question 17 Multiple Choice (Single Answer)

The total number of function dependencies (both trivial and non trivial) that can be formed over relation having degree = n are

$2^n$
$2^{2n}$
$n!$
$2+n!$

DBMS Practice Test: Normalization and Transactions

Questions