The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
+15 votes

The following relations are used to store data about students, courses, enrollment of students in courses and teachers of courses. Attributes for primary key in each relation are marked by ‘*’.

Students (rollno*, sname, saddr)
courses (cno*, cname)
enroll(rollno*, cno*, grade)
teach(tno*, tname, cao*)

(cno is course number cname is course name, tno is teacher number, tname is teacher name, sname is student name, etc.)

Write a SQL query for retrieving roll number and name of students who got A grade in at least one course taught by teacher names Ramesh for the above relational database.

asked in Databases by Veteran (59.5k points)
edited by | 669 views

Self doubt.  What will be the query when we change the at least to  all  i mean" Write  query for retrieving roll number and name of students who got A grade in all the  courses taught by teacher named Ramesh for the above relational database."

2 Answers

+13 votes

23) select student.rollno, student.sname

From student natural join enroll on student.rollno=enroll.rollno

Where enroll.grade='A' AND enroll.cno in (select cno from teach where tname='Ramesh')

24) In teach relation cno(non prime attribute) does not depend upon any super key

So, split them like:

teach1 (tno,tnaem)

teach2 (tno,cno)

answered by Active (3.3k points)
edited by

Is on student.rollno=enroll.rollno required?

I guess natural join itself does the work?

yes natural join itself does the work.. and instead of using sub query for teach table we could have performed natural join with teach table also right because sub query is always a toll on database so better not to use it..

We should use distinct after select ..
"In teach relation cno(non prime attribute) does not depend upon any super key"
Cno is Primary Key as mentioned in the question, how can it be "non-prime" attribute.
this database is in BCNF.
It is not in BCNF, not even 2NF because at teach relation tname, which is a non prime attribute, partial dependent on primary key(tno,cno). I think there is no issue with cno attribute.

@ arjun sir, please confirm this answer. I feel teach relation is not in 2NF due to existence of partial dependency. I think given FD should be removed and tno, cao -> tname should be added as new fd for it to satisfy 3NF.

+5 votes

Select student.rollno,student.sname
From Student Natural Join Enroll
where enroll.grade='A' AND Enroll.cno in (Select from teach where tname='Ramesh')

Select student.rollno,student.sname
From Student , Enroll , Teach
where  Student. rollno. = Enroll.rollno. AND Enroll.grade='A' AND tname='Ramesh')

Students (rollno*, sname, saddr)  with rollno →sname, saddr is in BCNF
courses (cno*, cname) with cno→ cname is in BCNF
enroll(rollno*, cno*, grade) with rollno →  grade,cno and cno →  grade, rollno. is in BCNF
teach(tno*, tname, cao*) here Cao tno → tname, cao and  cao → tname,tno is in BCNF
answered by Boss (17.5k points)
edited by

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

39,848 questions
46,815 answers
59,066 users