Database miscellaneous Easy Questions and Answers | Page - 2

Database miscellaneous

Consider the following database table named water_schemes:

The number of tuples returned by the following SQL query is ________.
with total (name, capacity) as
select district_name, sum (capacity)
from water_schemes
group by district_name
with total_avg (capacity) as
select avg (capacity)
from total
select name
from total, total_avg
where total.capacity ≠ total_avg.capacity

1. 2
1. 3
1. 4
1. 5

View Hint View Answer Discuss in Forum

First we should perform group by group. We get ajmer 1
bikaner 3
charu 2
dungargarh 1
Now we should perform total capacity we will get 100. Now perform avg. capacity on total 100 / 4 = 25. bikaner = 40 which is greater than 25 charu is 30 which is also greater than 25. Hence charu and bikaner will be printed therefore answer is 2 tuples.

Correct Option: A

First we should perform group by group. We get ajmer 1
bikaner 3
charu 2
dungargarh 1
Now we should perform total capacity we will get 100. Now perform avg. capacity on total 100 / 4 = 25. bikaner = 40 which is greater than 25 charu is 30 which is also greater than 25. Hence charu and bikaner will be printed therefore answer is 2 tuples.

Consider a database that has the relation schema CR(StudentName. CourseName). An instance of the schema CR is as given below.

The following query is made on the database.
T₁ ← π_CourseName (σ_{StudentName= ‘SA’}(CR))
T₂ ← CR ÷ T₁
The number of rows in T₂ is _______.

1. 1
1. 2
1. 4
1. 15

View Hint View Answer Discuss in Forum

The given query is made on the database are:
1.
For T₁ Result: find out the course name for the student name is ‘SA’ i.e.

So, T₁ = {CA, CB, CC} The number of rows in T₁ is 3.
2. T₁ ← (CR ÷ T₁)

For T₂ Result: find out the student name for which every course name of T₁ are {CA, CB, CC} is:
So, T2 = {SA, SC, SD, SF} Hence, the number of rows in T2 is 4.

Correct Option: C

The given query is made on the database are:
1.
For T₁ Result: find out the course name for the student name is ‘SA’ i.e.

So, T₁ = {CA, CB, CC} The number of rows in T₁ is 3.
2. T₁ ← (CR ÷ T₁)

For T₂ Result: find out the student name for which every course name of T₁ are {CA, CB, CC} is:
So, T2 = {SA, SC, SD, SF} Hence, the number of rows in T2 is 4.

Suppose the adjacency relation of vertices in a graph is represented in a table Adj (X, Y). Which of the following queries cannot be expressed by a relational algebra expression of constant length?

1. List of all vertices adjacent to a given vertex
1. List of all vertices which have self loops
1. List of all vertices which belong to cycles of less than three vertices
1. List of all vertices reachable from a given vertex

View Hint View Answer Discuss in Forum

(A) ⇒ This is simple select query query.
(B) ⇒ This is simple query we need to check X=Y in where clause.
(C) ⇒ Cycle < 3. Means cycle of length 1 & 2. Cycle of length 1 is easy., Same as self loop. Cycle of length 2 is is also not too hard to compute. Though it'll be little complex, will need to do like (X,Y) & (Y, X) both present & X! = Y,. We can do this with constant RA query.
(D) ⇒ This is most hard part. Here we need to find closure of vertices. This willl need kind of loop. If the graph is like skewed tree, our query must loop for O(N) Times. We can't do with constant length query here.

Correct Option: D

(A) ⇒ This is simple select query query.
(B) ⇒ This is simple query we need to check X=Y in where clause.
(C) ⇒ Cycle < 3. Means cycle of length 1 & 2. Cycle of length 1 is easy., Same as self loop. Cycle of length 2 is is also not too hard to compute. Though it'll be little complex, will need to do like (X,Y) & (Y, X) both present & X! = Y,. We can do this with constant RA query.
(D) ⇒ This is most hard part. Here we need to find closure of vertices. This willl need kind of loop. If the graph is like skewed tree, our query must loop for O(N) Times. We can't do with constant length query here.

Which of the following relational calculus expressions is not safe?

1. { t | ∃u ∈ R₁(u[A]) ∧ ̚ ∃s ∈ R₂ (t[A] = s[A]) }
1. { t | ∀u ∈ R₁(u[A]) = "x" ⇒ ∃s ∈ R₂ (t[A] s[A] ∧ s[A] = u[A])) }
1. { t | ̚(t ∈ R₁) }
1. { t | ∃u ∈ R₁(t[A]) = u[A] = u[A] ∧ ∃s ∈ R₂ (t[A] = s[A])) }

View Hint View Answer Discuss in Forum

{t | ̚ (t ∈ R)}
This option contradicts itself. We want a tuple t and t itself is in r₁ and we want other tuples than t by using negation so it’s not safe.

Correct Option: C

{t | ̚ (t ∈ R)}
This option contradicts itself. We want a tuple t and t itself is in r₁ and we want other tuples than t by using negation so it’s not safe.

Consider a relation geq which represents “greater than or equal to”, that is, (x, y) ∈ geq only if y ≥ x.
create table geq
(lb integer not null
ub integer not null
primary key lb foreign key (ub) references geq on delete cascade)
Which of the following is possible if a tuple (x, y) is deleted?

1. A tupel (z, w) with z > y is deleted
1. A tuple (z, w) with z > x is deleted
1. A tuple (z, w) with w < x is deleted
1. The deletion of (x, y) is prohibited

View Hint View Answer Discuss in Forum

When the tuple (x, y) is deleted, consider the tuple (z, w). Now, in tupel (z, w), z is the lower bound and hence > X. and w is the upper bound and hence > Y.
→ (X, Y) will be easily deleted.
Therefore, Y > X

Correct Option: C

When the tuple (x, y) is deleted, consider the tuple (z, w). Now, in tupel (z, w), z is the lower bound and hence > X. and w is the upper bound and hence > Y.
→ (X, Y) will be easily deleted.
Therefore, Y > X