Question

Consider the following relation. The Primary key is Rollno, Isbn, Student(RollNo, Name, Branch), Book(Isbn, Title, Author, Publisher) Issue(Rollno, Isbn, te_of_issue). Write the query in Relational algebra and SQL of the followingi) List the Roll Number and Name of All CSE Branch Student. ii) Find the name of students who have issued a book of publication ‘BPB’. iii) List the title and author of all books which are issued by a student name started with a.

Answer 1

Answer:

Create table student

[roll no. int , Name]

Answer 2

Query in Relational algebra and SQL

(i) Relational Algebra:

π <Roll no; Name> (σ <Branch = CSE> (student))

SQL Query:

select Roll no, Name from student where Branch = "CSE";

(ii) Relational Algebra:

π  <Name> (σ (Publisher = "BPB" (Student ∞ Issue ∞ Book))

SQL Query:

select s.name from student s inner join issue i on s.rollno = i.rollno inner join book b on i.isbn = b.isbn where b.publisher = "BPB";

(iii) Relational Algebra:

π <title,author> (σ <name started with a> (student ∞ Issue ∞ Book))

SQL Query:

select b.title, b.author from book b inner join issue on b.isbn = i.isbn inner join student on s.rollno = i.rollno where name like A%;

hence, this is the Query in Relational algebra and SQL