### SET 2

Question 1

**Consider the relation scheme R = (E, F, G, H, I, J, K, L, M, N) and the set of functional dependencies
**

{{E,F}-> {G}

{F}-> {I, J}

{E,H}->{K,L}

{K}->{M}

{L}->{N} }

on R. What is the key for R?

A : {E.F}

B : {E.F,H}

C : {E.F,H,K,L}

D : {E}

Answer Discuss it!

.

Correct answer is :B

Solution :

R(EFGHI,JKLMN)

F = {

EF->G

F->IJ

EH->KL

K->M

L->N

}

(EF)^{+} = EFGIJ, E&F + = Together functionally derive GIJ and if we observe given FDs, H can’t be determined by any other attributes. So H must be part of all the (candidate) keys. H along with E determines K and L, K & L functionally determine M and N respectively.

(EFH) ^{+} = EFGIJHKLMN

EFH is the only candidate for key.

Question 2

**Given the following statements: **

S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL

S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.

CREATE TABLE S (

a INTEGER,

d INTEGER,

e INTEGER,

PRIMARY KEY (d),

FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?

A : S1 is TRUE and S2 is a FALSE

B : Both S1 and S2 are TRUE

C : S1 is FALSE and S2 is a TRUE

D : Both S1 and S2 are FALSE

Answer Discuss it!

.

Correct answer is :D

Solution :

Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.

Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field a of R. The field a in table R doesnt uniquely identify a row in table R.

Question 3

**Consider the following four schedules due to three transactions (indicted by the subscript) using read and write on a data item x, denoted r (x) and w (x) respectively. Which one of them is conflict serializable?**

A : r1 (x) ; r2 (x) ; w1 (x) ; r3(x) ; w2 (x)

B : r2 (x) ; r1 (x) ; w2 (x) ; r3(x) ; w1 (x)

C : r3 (x) ; r2 (x) ; r1 (x) ; w2(x) ; w1 (x)

D : r2 (x) ; w2 (x) ; r3 (x) ; r1(x) ; w1 (x)

Answer Discuss it!

.

Correct answer is :D

Solution :

If there is a cycle in precedence graph, then the schedule is not conflict serializable.

Question 4

**Given the following two statements:**

S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF

S2 : AB->C, D -> E, E->C is a minimal cover for the set of functional dependencies AB->C, D -> E, AB -> E,E -> C Which one of the following is CORRECT?

A : S1 is TRUE and S2 is FALSE.

B : Both S1 and S2 are TRUE.

C : S1 is FALSE and S2 is TRUE.

D : Both S1 and S2 are FALSE.

Answer Discuss it!

.

Correct answer is :A

Question 5

**Given the following schema: **

employees(emp-id, first-name, last-name, hire-date, dept-id, salary)

departments(dept-id, dept-name, manager-id, location-id)

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL>SELECT last-name, hire-date

FROM employees

WHERE (dept-id, hire-date) IN

(SELECT dept-id, MAX(hire-date)

FROM employees JOIN departments USING(dept-id)

WHERE location-id = 1700

GROUP BY dept-id);

What is the outcome?

A : It executes but does not give the correct result.

B : It executes and gives the correct result.

C : It generates an error because of pairwise comparison.

D : It generates an error because the GROUP BY clause cannot be used with table joins in a sub-query.

Answer Discuss it!

.

Correct answer is :B

Question 6

**The maximum number of super keys for the relation schema R (E, F, G, H) with E as the key is __________.**

Answer Discuss it!

.

Correct answer is :8

Solution :

The maximum number of super keys for the relation schema R(E,F,G,H) with E as the key is 2^{3} = 8 as any subset of non key attributes along with key attribute will form the super key of R.

As we have 3 nonkey all (F, G and H) so subsets will be 2^{3}

Question 7

**Given an instance of the STUDENTS relation as shown below For (StudentName, StudentAge) to be a key for this instance, the value X should NOT be equal to____________**

Answer Discuss it!

.

Correct answer is :19

Solution :

For (Student Name, student age) to be a key for given instance of STUDENTS relation, the pair value should not get repeated in any two tuples p and q (uniqueness in forced by the definition of key)

Output :-

Shankar age should not b 19

Shankar 19

Question 8

**Consider the following schedule S of transactions T1, T2, T3, T4 **

Which one of the following statements is CORRECT?

A : S is conflict-serializable but not recoverable

B : S is conflict-serializable but is recoverable

C : S is both conflict-serializable and recoverable

D : S is neither conflict-serializable nor is it recoverable

Answer Discuss it!

.

Correct answer is :C

Solution :

According to the precedence graph In the schedule S of transactions T1 ,T2 ,T3 and T4 for each pair of transaction Ti and Tj , such that Tj reads a data item previously written by Ti the commit operation of Tj appears after the commit operation of Ti hence the schedule is recoverable schedule.

Question 9

**Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method. There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved for intermediate results. Assuming size(r(R)) < size(s(S)), the join will have fewer number of disk block accesses if
**

A : relation r(R) is in the outer loop.

B : relation s(S) is in the outer loop.

C : join selection factor between r(R) and s(S) is more than 0.5.

D : join selection factor between r(R) and s(S) is less than 0.5.

Answer Discuss it!

.

Correct answer is :A

Solution :

A join between r(R) and s (S) using nested loop method will be as follows.

For each tuple r in R do

For each tuple s in S do

If r and s satisfy the join condition then output the tuple

Cost estimations for the above loop:
– b(R) and b(S) number of blocks in R and in S
– Each block of outer relation is read once
– Inner relation is read once for each block of outer relation
Summing up : IO= b(R)+b(R)*b(S) total IO operations
this will be lowest when R is in outer loop.

Question 10

**SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below: **

Select * from R where a in (select S. a from S)

A : Select R. * from R, S where R. a=S. a

B : Select distinct R. * from R,S where R. a=S. a

C : Select R. * from R, (select distinct a from S) as S1 where R. a=S1.a

D : Select R. * from R, S where R.a = S. a and is unique R

Answer Discuss it!

.

Correct answer is :C

Question 11

**What is the optimized version of the relation algebra expression, where A1, A2 are sets of attributes in with A1 ⊂ A2 and F1, F2 are Boolean expressions based on the attributes in r?**

A : π_{A1} ( ∂ (F1 ∧ F2 ) (r) )

B : π_{A1} ( ∂ (F1 ∨ F2 ) (r) )

C : π_{A2} ( ∂ (F1 ∧ F2 ) (r) )

D : π_{A2} ( ∂ (F1 ∨ F2 ) (r) )

Answer Discuss it!

.

Correct answer is :A

Question 12

**A prime attribute of a relation scheme R is an attribute that appears**

A : in all candidate keys of R.

B : in some candidate key of R.

C : in a foreign keys of R.

D : only in the primary key of R.

Answer Discuss it!

.

Correct answer is :B

Solution :

A prime attribute or key attribute of a relation scheme R is an attribute that appears in any of the candidate key of R, remaining attributes are known as non-prime or non-key tribute

Question 13

**Consider the transactions T1, T2, and T3 and the schedules S1 and S2 given below.**

T1 : r1 (X) ; r1 (z) ; w1 (X) ; w1 (z)

T2 : r2 (X) ; r2 (z) ; w2 (z)

T3 : r3 (X) ; r3 (X) ; w3 (Y)

S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)

S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)

Which one of the following statements about the schedules is TRUE?

A : Only S1 is conflict-serializable

B : Only S2 is conflict-serializable.

C : Both S1 and S2 are conflict-serializable.

D : Neither S1 nor S2 is conflict-serializable.

Answer Discuss it!

.

Correct answer is :A

Solution :

Precedence graph for 1 2 S &S are as follows

Only S1 is conflict serializable.

Question 14

**Consider the relational schema given below, where eId of the relation dependentis a foreign key referring to empId of the relation employee. Assume that every employee has at least one associated dependent in the dependent relation.**

employee (__empId__, empName, empAge)

dependent (__depId, eId,__ depName, depAge)

Consider the following relational algebra query

πempId(employee) - π empId( employee <> empId=eID) ∧ (emp Age <= depAge) dependent )

The above query evaluates to the set of empIds of employees whose age is greater than that of

A : some dependent.

B : all dependents.

C : some of his/her dependents.

D : all of his/her dependent

Answer Discuss it!

.

Correct answer is :D

Solution :

Part A of the above given relational algebra query will give the set of empIds of those employees whose age is less than or equal to the age of some of his/her dependents. Now when set of empIds of all employees minus set of empIds obtained from part A is done, then we get the set of empIds of employees whose age is greater than that of all of his/her dependents.

Question 15

**Consider the following relational schema: **

Employee (empId, empName, empDept )

Customer (custId,custName, salesRepId, rating)

SalesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName

FROM employee E

WHERE NOT EXISTS (SELECT custId

FROM customer C

WHERE C. salesRepId = E. empId

AND C. rating < > ‘GOOD’)

A : Names of all the employees with at least one of their customers having a ‘GOOD’ rating.

B : Names of all the employees with at most one of their customers having a ‘GOOD’ rating.

C : Names of all the employees with none of their customers having a ‘GOOD’ rating.

D : Names of all the employees with all their customers having a ‘GOOD’ rating.

Answer Discuss it!

.

Correct answer is :D

Solution :

The outer query will return the value (names of employees) for a tuple in relation E, only if inner query for that tuple will return no tuple (usage of NOT EXISTS).

The inner query will run for every tuple of outer query. It selects cust-id for an employee e, if rating of customer is NOT good. Such an employee should not be selected in the output of outer query.

So the query will return the names of all those employees whose all customers have GOOD rating.

Question 1

{{E,F}-> {G}

{F}-> {I, J}

{E,H}->{K,L}

{K}->{M}

{L}->{N} }

on R. What is the key for R?

.

Correct answer is :B

Solution :

R(EFGHI,JKLMN)

F = {

EF->G

F->IJ

EH->KL

K->M

L->N

}

(EF)

^{+}= EFGIJ, E&F + = Together functionally derive GIJ and if we observe given FDs, H can’t be determined by any other attributes. So H must be part of all the (candidate) keys. H along with E determines K and L, K & L functionally determine M and N respectively.

(EFH)

^{+}= EFGIJHKLMN

EFH is the only candidate for key.

Question 2

S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL

S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition.

CREATE TABLE S (

a INTEGER,

d INTEGER,

e INTEGER,

PRIMARY KEY (d),

FOREIGN KEY (a) references R)

Which one of the following statements is CORRECT?

.

Correct answer is :D

Solution :

Check assertions are not sufficient to replace foreign key. Foreign key declaration may have cascade delete which is not possible by just check insertion.

Foreign key in one table should uniquely identifies a row of other table. In above table definition, table S has a foreign key that refers to field a of R. The field a in table R doesnt uniquely identify a row in table R.

Question 3

.

Correct answer is :D

Solution :

If there is a cycle in precedence graph, then the schedule is not conflict serializable.

Question 4

S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF

S2 : AB->C, D -> E, E->C is a minimal cover for the set of functional dependencies AB->C, D -> E, AB -> E,E -> C Which one of the following is CORRECT?

.

Correct answer is :A

Question 5

employees(emp-id, first-name, last-name, hire-date, dept-id, salary)

departments(dept-id, dept-name, manager-id, location-id)

You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query:

SQL>SELECT last-name, hire-date

FROM employees

WHERE (dept-id, hire-date) IN

(SELECT dept-id, MAX(hire-date)

FROM employees JOIN departments USING(dept-id)

WHERE location-id = 1700

GROUP BY dept-id);

What is the outcome?

.

Correct answer is :B

Question 6

.

Correct answer is :8

Solution :

The maximum number of super keys for the relation schema R(E,F,G,H) with E as the key is 2

^{3}= 8 as any subset of non key attributes along with key attribute will form the super key of R.

As we have 3 nonkey all (F, G and H) so subsets will be 2

^{3}

Question 7

.

Correct answer is :19

Solution :

For (Student Name, student age) to be a key for given instance of STUDENTS relation, the pair value should not get repeated in any two tuples p and q (uniqueness in forced by the definition of key)

Output :-

Shankar age should not b 19

Shankar 19

Question 8

Which one of the following statements is CORRECT?

.

Correct answer is :C

Solution :

According to the precedence graph In the schedule S of transactions T1 ,T2 ,T3 and T4 for each pair of transaction Ti and Tj , such that Tj reads a data item previously written by Ti the commit operation of Tj appears after the commit operation of Ti hence the schedule is recoverable schedule.

Question 9

.

Correct answer is :A

Solution :

A join between r(R) and s (S) using nested loop method will be as follows.

For each tuple r in R do

For each tuple s in S do

If r and s satisfy the join condition then output the tuple

Cost estimations for the above loop: – b(R) and b(S) number of blocks in R and in S – Each block of outer relation is read once – Inner relation is read once for each block of outer relation Summing up : IO= b(R)+b(R)*b(S) total IO operations this will be lowest when R is in outer loop.

Question 10

Select * from R where a in (select S. a from S)

.

Correct answer is :C

Question 11

.

Correct answer is :A

Question 12

.

Correct answer is :B

Solution :

A prime attribute or key attribute of a relation scheme R is an attribute that appears in any of the candidate key of R, remaining attributes are known as non-prime or non-key tribute

Question 13

T1 : r1 (X) ; r1 (z) ; w1 (X) ; w1 (z)

T2 : r2 (X) ; r2 (z) ; w2 (z)

T3 : r3 (X) ; r3 (X) ; w3 (Y)

S1: r1(X); r3(Y); r3(X); r2(Y); r2(Z); w3(Y); w2(Z); r1(Z); w1(X); w1(Z)

S2: r1(X); r3(Y); r2(Y); r3(X); r1(Z); r2(Z); w3(Y); w1(X); w2(Z); w1(Z)

Which one of the following statements about the schedules is TRUE?

.

Correct answer is :A

Solution :

Precedence graph for 1 2 S &S are as follows

Only S1 is conflict serializable.

Question 14

employee (

__empId__, empName, empAge)

dependent (

__depId, eId,__depName, depAge)

Consider the following relational algebra query

πempId(employee) - π empId( employee <> empId=eID) ∧ (emp Age <= depAge) dependent )

The above query evaluates to the set of empIds of employees whose age is greater than that of

.

Correct answer is :D

Solution :

Part A of the above given relational algebra query will give the set of empIds of those employees whose age is less than or equal to the age of some of his/her dependents. Now when set of empIds of all employees minus set of empIds obtained from part A is done, then we get the set of empIds of employees whose age is greater than that of all of his/her dependents.

Question 15

Employee (empId, empName, empDept )

Customer (custId,custName, salesRepId, rating)

SalesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return?

SELECT empName

FROM employee E

WHERE NOT EXISTS (SELECT custId

FROM customer C

WHERE C. salesRepId = E. empId

AND C. rating < > ‘GOOD’)

.

Correct answer is :D

Solution :

The outer query will return the value (names of employees) for a tuple in relation E, only if inner query for that tuple will return no tuple (usage of NOT EXISTS).

The inner query will run for every tuple of outer query. It selects cust-id for an employee e, if rating of customer is NOT good. Such an employee should not be selected in the output of outer query.

So the query will return the names of all those employees whose all customers have GOOD rating.