1
GATE CSE 2009
+2
-0.6
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)

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?
A
The schema is in BCNF
B
The schema is in 3NF but not in BCNF
C
The schema is in 2NF but not in 3NF
D
The schema is not in 2NF
2
GATE CSE 2008
+2
-0.6

Consider The Following Relational Scheme

Enrolment (school-id, sch-roll-no, erollno, examname)
ExamResult (Erollno, examname, marks)

Consider the following tuple relational calculus query

{ t | ∃E ∈ Enrolment t = E.school-id ∧
| { x | x ∈ ExamResult B.school-id =
t ∧ ( ∃B ∈ ExamResult B.erollno =
x.erollno ∧ B.examname = x.examname ∧
B.marks > 35 } | ÷ |
{ x | x ∈ Enrolment ∧ x.school-id = t }
| * 100 > 35 }
If a student needs to score more than 35 marks to pass an exam what does the query return?
A
The empty set
B
Schools with more than 35% of it's student enrolled in some exam or the other
C
Schools with a pass percentage above 35% over all exams taken together
D
Schools with a pass percentage above 35% over each exam.
3
GATE CSE 2008
+2
-0.6

Consider The Following Relational Scheme

Enrolment (school-id, sch-roll-no, erollno, examname)
ExamResult (Erollno, examname, marks)

What does the following SQL query output?
SELECT sch-name, COUNT (*)
FROM School C, Enrolment E,
ExamResult R
WHERE E.school-id = C.school-id
AND E.examname = R.examname
AND E.erollno = R.erollno
AND R.marks = 100 AND S.school-id IN
(SELECT school-id
FROM student
GROUP BY school-id
HAVING COUNT (*) > 200)
GROUP BY school-id;
A
for each school with more than 200 students appearing in exams, the name of the school and the number of 100s scored by its students
B
for each school with more than 200 students in it, the name of the school and the number of 100s scored by its students
C
for each school with more than 200 students in it, the name of the school and the number of its students scoring 100 in at least one exam
D
nothing; the query has a syntax error
4
GATE CSE 2007
+2
-0.6
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");
A
Q1 is the correct query.
B
Q2 is the correct query.
C
Both Q1 and Q2 produce the same answer.
D
Neither Q1 nor Q2 is the correct query.
GATE CSE Subjects
Theory of Computation
Operating Systems
Algorithms
Digital Logic
Database Management System
Data Structures
Computer Networks
Software Engineering
Compiler Design
Web Technologies
General Aptitude
Discrete Mathematics
Programming Languages
Computer Organization
EXAM MAP
Joint Entrance Examination