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

Q. With regard to the expressive power of the formal relational query languages, which of the following statements is true?

View solution

Q. 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’);

View solution

Q. Consider the following database schedule with two transactions, T1 and T2.

S = r2(X); r1(X); r2(Y); w1(X); r1(Y); w2(X); a1; a2;

where ri(Z) denotes a read operation by transaction Ti on a variable Z, wi(Z) denotes a write operation by Ti on a variable Z and ai denotes an abort by transaction Ti . Which one of the following statements about the above schedule is TRUE?

View solution

Q. Consider the following two phase locking protocol. Suppose a transaction T accesses (for read or write operations), a certain set of objects {O1,...,Ok}. This is done in the following manner: Step 1. T acquires exclusive locks to O1, . . . , Ok in increasing order of their addresses. Step 2. The required operations are performed. Step 3. All locks are released. This protocol will

View solution

Q. Which one of the following statements about normal forms is FALSE?

View solution

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

View solution

Q. The employee information in a company is stored in the relation
Employee (name, sex, salary, deptName)
(name is primary key )
Consider the following SQL query

select deptName
from Employee
where sex = 'M'
group by deptName
having avg (salary) > (select avg (salary) from Employee)

It returns the names of the department in which

View solution

Q. Two transactions T₁ and T₂ are given as follows:

T₁: r₁ (A); w₁(A); r₁ (B); w₁(B)

Find the no. of conflict serializable schedules that can be formed over T₁ and T₂.

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. Which of the following command is used to delete a table in SQL?

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

View solution

Q. How many serial schedules are possible using n-transactions?

View solution

Q. From the following instance of a relation scheme R (A, B, C), we can conclude that :
A B C
1 1 1
1 1 0
2 3 2
2 3 2

View solution

Q. Table A
Id Name Age
----------------
12 Arun 60
15 Shreya 24
99 Rohit 11

Table B
Id Name Age
----------------
15 Shreya 24
25 Hari 40
98 Rohit 20
99 Rohit 11

Table C
Id Phone Area
-----------------
10 2200 02
99 2100 01
Consider the above tables A, B and C. How many tuples does the result of the following SQL query contains?
SELECT A.id
FROM A
WHERE A.age > ALL (SELECT B.age FROM B
WHERE B. name = "arun")

View solution

Q. Which of the following schedule are not conflict serializable?

View solution

Q. Consider the table employee(empId, name, department, salary) and the two queries Q1 ,Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?
Q1 : Select e.empId
From employee e
Where not exists
(Select * From employee s where s.department = “5” and
s.salary >=e.salary)
Q2 : Select e.empId
From employee e
Where e.salary > Any
(Select distinct salary From employee s Where s.department = “5”)

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. The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.

A C
-----
2 4
3 4
4 3
5 2
7 2
9 5
6 4

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:

View solution