# Recent questions and answers in Databases

1
Consider a relational database containing the following schemas. $\begin{array}{c} \text{Catalogue} \end{array}$ ... (cost) FROM Catalogue WHERE pno = P4' GROUP BY pno) ; The number of rows returned by the above SQL query is $4$ $5$ $0$ $2$
2
If following sequence of keys are inserted in a $B+$ tree with $K(=3)$ pointers: $8,5,1,7,3,12,9,6$ Which of the following shall be correct $B+$ tree?
3
Which of the following concurrency control protocols ensure both conflict serializability and freedom from deadlock? 2-phase locking Time-stamp ordering I only II only Both I and II Neither I nor II
4
The relation book (title,price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? select title from book as B where (select count(*) from book as T where T.price ... four most expensive books Title of the fifth most inexpensive book Title of the fifth most expensive book Titles of the five most expensive books
5
In a database system, unique timestamps are assigned to each transaction using Lamport's logical clock. Let $TS(T_{1})$ and $TS(T_{2})$ be the timestamps of transactions $T_{1}$ and $T_{2}$ respectively. Besides, $T_{1}$ holds a lock on the ... , but not starvation-free. The database system is starvation-free, but not deadlock-free. The database system is neither deadlock-free nor starvation-free.
6
A $B^+$ - tree index is to be built on the Name attribute of the relation STUDENT. Assume that all the student names are of length $8$ bytes, disk blocks are of size $512$ bytes, and index pointers are of size $4$ bytes. Given the scenario, what would be the best choice of the degree (i.e. number of pointers per node) of the $B^+$ - tree? $16$ $42$ $43$ $44$
7
Consider a relational table $r$ with sufficient number of records, having attributes $A_1, A_2, \dots ,A_n$ and let $1 \leq p \leq n$. Two queries $Q1$ and $Q2$ are given below. $Q1: \pi_{A_1, \dots ,A_p} \left(\sigma_{A_p=c}\left(r\right)\right)$ ... for both queries Hashing will outperform ordered indexing on $Q1$, but not on $Q2$ Hashing will outperform ordered indexing on $Q2$, but not on $Q1$
8
A database table $T_1$ has $2000$ records and occupies $80$ disk blocks. Another table $T_2$ has $400$ records and occupies $20$ disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records ... in the outer loop, the reduction in number of block accesses required for reading the data will be $0$ $30400$ $38400$ $798400$
9
What does the double arrow in options 2 & 4 mean ?
10
Which of the following statements are true about weak entity sets 1. a weak entity set cannot have a primary key 2. a weak entity set must have a local attribute in primary key 3.a weak entity must borrow an attribute from another entity set to form a primary key (a) 1 and 2 (b) 2 and 3 (c) 3 only (d) None
11
is conservative 2PL is recoverable schedule ?
12
A table T1 in a relational database has the following rows and columns: $\begin{array}{|c|c|c|} \hline \text {Roll no. } & \text {Marks} \\\hline 1& 10 \\\hline 2 & 20 \\\hline3 & 30 \\\hline 4 & \text{NULL}\\\hline \end{array}$ The following sequence of SQL ... Update T1 set marks = marks + 5 Select avg(marks) from T1 What is the output of the select statement? $18.75$ $20$ $25$ $\text{Null}$
13
Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts 6000, 7000, 8000 ... , Plan 1 executes faster than Plan 2 for all databases For x = 9000, Plan I executes slower than Plan 2 for all databases
14
If every non-key attribute is functionally dependent on the primary key then the relation will be in   A. 1NF                          B. 2NF                        C. 3NF                             D. 4NF I think the answer should be 2NF, but in the key it is given 3NF
15
Consider a relational schema $S=(U,V,W,X,Y,Z)$ on which the following functional dependencies hold: $(U \rightarrow V, VW \rightarrow X, Y \rightarrow W, X \rightarrow U)$ Which are the candidate keys among following options? $UY, VY$ $UY, VY, XY$ $UYZ, VYZ, VWZ$ $UYZ, VYZ, XYZ$
16
1 vote
17
Q Consider a relation R(a, b, c) ordered on a non-key attribute b, and an index is maintained on attribute c. To evaluate the following expression, which is a better order of operation? σ(b= abc'∧c=12)(R) A. First filter tuples using index on c and then ... First search tuples for the given value of b and then use index on c C. The order of operation does not matter D. Insufficient data to answer
1 vote
18
A candidate key should have unique values and a chosen CK is called primary key. Null is also unique. Why (C) is not answer?
19
According to me it should be – “Retrieve the names of all students with a lower rank, than all students with age < 18 ”
20
Consider a database that has the relation schemas EMP(EmpId, EmpName, DeptId), and DEPT(DeptName, DeptId). Note that the DeptId can be permitted to be NULL in the relation EMP. Consider the following queries on the database expressed in tuple relational calculus. {$t$ | $\exists$u $\in$ ... v[DeptId]))} Which of the above queries are safe? I and II only I and III only II and III only I, II and III
21
For the relation instances A and B, A/B is the largest relation instance Q such that Q B ⊆ A. Consider A has exactly two fields x and y and B has just one field y with the same domain as in A. Division operation A/B is defined as the set of all x values (in the form of unary tuples) such that for every y value ... (πx(B) A) - B) (C) πx(A) - πx((πx(A) B) - A) (D) πx(A) - πx((πx(B) A) - A)
22
23
Why is the maximum number of tuples in full outer join equal to m*n, where m is the number of attributes in one relation and n is the attribute count in other ? Can someone give an example to illustrate this ?
1 vote
24
Consider relation R' and S' have n' and m' tuples, respectively. Choose the best matching between List-I (Expression) and List-II (Maximum number of tuple): Soln. According to me Answer should be Option C. 1.R union S = m+n (easy nothing to say) 2. Say Relations are like:- R(A,B,C) s(C,D) ... D 1 2 3 3 4 2 3 3 3 5 4 3 3 3 6 Now in R natual join S = m * n So option C should suffice isn't it ?
25
Suppose we have a relation R(ABCD) where AB is candidate key. Now if there is a FD B -> AD, then will this FD satisfy the condition of 3NF ? I mean condition of X->Y where X is super key or Y is prime attribute as here only A is prime attribute on RHS. I know we can split the above FD as B->A and B->D and here B->A satisfies the condition and B->D doesn't Any help would be appreciated.
1 vote
26
WHICH OF THE FOLLOWING IS CORRECT IF RELATION R IS IN 3NF 1. NO NON PRIME ATTRIBUTE OF R IS TRANSITIVELY DEPENDENT ON KEY(SUPER KEY,PRIMARY KEY,CANDIDATE KEY). 2.PRIME ATTRIBUTE OF R CAN BE TRANSITIVELY DEPENDENT ON KEY(SUPER KEY,PRIMARY KEY,CANDIDATE KEY). 3. A relation R is ... of R. 4.A relation R is in 3NF, if every non-prime attribute of R, is fully functionally dependent on every key of R.
27
State whether the following statements are TRUE or FALSE: A relation $r$ with schema $(X, Y)$ satisfies the function dependency $X \rightarrow Y$, The tuples $\langle 1, 2\rangle$ and $\langle 2, 2 \rangle$ can both be in $r$ simultaneously.
28
(a) Suppose you are given an empty B+- tree where each node (leaf and internal) can store up to 5 key values. Suppose values 1, 2,.....10 are inserted, in order, into the tree. Show the tree pictorially after 6 insertions, and after all 10 insertions Do NOT ... . Then what approximately is the average number of keys in each leaf level node. in the normal case, and with the insertion as in (b).
29
In a relational database there are three relations: Customers = C (C Name) Shops = S (S Name) Buys = B (C Name, S Name) Then the Relational Algebra expression ( $\Pi$ is the projection operator). $C-\Pi _{C Name}((C \times S)-B)$ returns the names of ... . Customers who buy from at least two shops. Customers who buy from all shops. Customers who do not buy buy anything at all. None of the above.
30
In a certain operating system, deadlock prevention is attemped using the following scheme. Each process is assigned a unique timestamp, and is restarted with the same timestamp if killed. Let $P_h$ be the process holding a resource $R, P_r$ be a ... deadlock-free, but starvation-free The scheme is neither deadlock-free nor starvation-free The scheme is both deadlock-free and starvation-free
31
Which of the following scenarios may lead to an irrecoverable error in a database system? A transaction writes a data item after it is read by an uncommitted transaction A transaction reads a data item after it is read by an uncommitted transaction A transaction ... item after it is written by a committed transaction A transaction reads a data item after it is written by an uncommitted transaction
32
Consider the following relational schema: $\text{Suppliers}(\underline{\text{sid:integer}},\text{ sname:string, city:string, street:string})$ $\text{Parts}(\underline{\text{pid:integer}}, \text{ pname:string, color:string})$ ... the names of all suppliers who have supplied only non-blue part. Find the names of all suppliers who have not supplied only blue parts.
33
Minimum no of table for this ER diagram
34
In the context of concurrency control, a given pair of operations in a schedule is called conflict schedule if At least one of the operations is write operation Both the operations are performed on the same data item Both the operations are performed by different transactions Both the operations are performed on different ... $(b)$ and $(c)$ only $(a)$, $(c)$ and $(d)$ only $(c)$ and $(d)$ only
1 vote
35
Match $\text{List I}$ with $\text{List II}$ Choose the correct answer from the options given below: $A-II, B-IV, C-III, D-I$ $A-IV, B-I, C-II, D-III$ $A-II, B-I, C-IV, D-III$ $A-III, B-IV, C-I, D-II$
36
Consider the following table structures related to a university for the below question. EMPLOYEE NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10), PRIMARY KEY (EID), FOREIGN KEY (HODEID) ... 20,30,40); SELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO IN(20,30,40); SELECT DISTINCT EMPID FROM PROJECTWORK WHERE PROJNO=20,30,40;
37
Consider the following table structures related to a university for the below question. EMPLOYEE NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10), PRIMARY KEY (EID), FOREIGN KEY (HODEID) REFERENCES ... $II$ are false Statement $I$ is correct but Statement $II$ is false Statement $I$ is incorrect but Statement $II$ is true
Consider the following table structures related to a university for the below question. EMPLOYEE NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10), PRIMARY KEY (EID), FOREIGN KEY (HODEID) REFERENCES EMPLOYEE (EID), ... EID Choose the correct answer from the options given below: $a$ and $b$ only $a$ and $c$ only $b$ only $c$ only
Consider the following table structures related to a university for the below question. EMPLOYEE NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10), PRIMARY KEY (EID), FOREIGN KEY (HODEID) REFERENCES EMPLOYEE (EID), FOREIGN ... correct answer from the options given below: $a$ and $b$ only $a$ and $c$ only $b$ and $c$ only $a$ only
Consider the following table structures related to a university for the below question. EMPLOYEE NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10), PRIMARY KEY (EID), FOREIGN KEY (HODEID) REFERENCES EMPLOYEE (EID), ... ) Choose the correct answer from the options given below: $a$ and $b$ only $a$ only $b$ and $c$ only $c$ only