Q. 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

View solution

Q. Consider the following relation

Cinema (theater, address, capacity)

Which of the following options will be needed at the end of the SQL query

SELECT P1. address
FROM Cinema P1

Such that it always finds the addresses of theaters with maximum capacity?

View solution

Q. R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?

View solution

Q. 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. Given the following four queries:

Query1: select student from enrolled where
student in (select student from paid)
Query2: select student from paid where
student in (select student from enrolled)
Query3: select E.student from enrolled E, paid P
where E.student = P.student
Query4: select student from paid where exists
(select * from enrolled where enrolled.student
= paid.student)

Which one of the following statements is correct?

View solution

Q. Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional depen­dencies are known to hold: AB → CD, DE → P, C → E, P → C and B → G. The relational schema R is

View solution

Q. Suppose a database schedule S involves transactions T1, ....Tn. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?

View solution

Q. Which of the following scenarios may lead to an irrecoverable error in a database system ?

View solution

Q. Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many candidate keys does the relation R have?

View solution

Q. Which of the following statement is/are incorrect?

A: A schedule following strict two phase locking protocol is conflict serializable as well as recoverable.
B: Checkpoint in schedules are inserted to ensure recoverability.

View solution

Q. An index is clustered, if

View solution

Q. Which level of locking provides the highest degree of concurrency in a relational data base?

View solution

Q. A relational database contains two tables student and department in which student table has columns roll_no, name and dept_id and department table has columns dept_id and dept_name. The following insert statements were executed successfully to populate the empty tables:

Insert into department values (1, 'Mathematics')
Insert into department values (2, 'Physics')
Insert into student values (l, 'Navin', 1)
Insert into student values (2, 'Mukesh', 2)
Insert into student values (3, 'Gita', 1)

How many rows and columns will be retrieved by the following SQL statement?

Select * from student, department

View solution

Q. Which of the following is wrong?
The goal of concurrent execution is

View solution

Q. Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)
(sid,pid are primary keys)
Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema?

View solution

Q. Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, M} 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?

View solution

Q. Consider the following relational schema:

Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)

Consider the following relational query on the above database:

SELECT S.sname
FROM Suppliers S
WHERE S.sid NOT IN (SELECT C.sid
FROM Catalog C
WHERE C.pid NOT IN (SELECT P.pid
FROM Parts P
WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

View solution

Q. Consider a schema R(A,B,C,D) and functional dependencies A->B and C->D. Then the decomposition of R into R1(AB) and R2(CD) is

View solution

Q. A relational schema for a train reservation database is given below. Passenger (pid, pname, age) Reservation (pid, class, tid)

Table: Passenger
pid pname age
-----------------
0 Sachin 65
1 Rahul 66
2 Sourav 67
3 Anil 69

Table : Reservation
pid class tid
---------------
0 AC 8200
1 AC 8201
2 SC 8201
5 AC 8203
1 SC 8204
3 AC 8202
What pids are returned by the following SQL query for the above instance of the tables?
SELECT pid
FROM Reservation ,
WHERE class ‘AC’ AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger. pid = Reservation.pid)

View solution

Q. Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?

View solution

Q. Consider the following three table to store student enrollements in different courses.

Student(EnrollNo, Name)
Course(CourseID, Name)
EnrollMents(EnrollNo, CourseID)
(EnrollNo,CourseID are primary keys)

What does the following query do?

SELECT S.Name
FROM Student S, Course C, Enrollments E
WHERE S.EnrollNo = E.EnrollNo AND
C.Name = "DBMS" AND
E.CourseID = C.CourseID AND
S.EnrollNo IN
(SELECT S2.EnrollNo
FROM Student S2, Course C2, Enrollments E2
WHERE S2.EnrollNo = E2.EnrollNo AND
E2.CourseID = C2.CourseID
C2.Name = "OS")

View solution