Formulate Relational Algebra for Queries STUDENT(#S_id, Sname, Class, Address,) ISSUE_RETURN( #S_Id, #B_id, Issue_Date, Return_Date, Dues) BOOK (#B_id, Title, Author, Publication) a.Find total number of books of Tata Mcgraw Hill Publication in the Library. b.Find the name of the student who paid the highest dues. c.Find the Return_Date of ‘Database System Concept’ book by student ‘Amar’ d.Find all the books issued by ‘Ravi’. e.Delete all records of student ‘Vinod’.

Relational Models

Three relational models are given for the entities Student, Issue_Return, and Book.

STUDENT

| S_id | Sname | Class | Address |

ISSUE_RETURN

| S_id | B_id | Issue_Date | Return_Date | Dues |

BOOK

| B_id | Title | Author | Publication |

Queries

a] Find the total number of books of Tata Mcgraw Hill Publication in the Library.

SQL - based Query -

SELECT COUNT (Publication)
FROM BOOK
WHERE Publication = "Tata Mcgraw Hill";

Relational Algebra - based Query -

π _{COUNT (publication)}

γ _{COUNT (publication)}

(σ _{(Publication = "Tata Mcgraw Hill")} (BOOK))

b] Find the name of the student who paid the highest dues.

SQL - based Query -

SELECT STUD.Sname, MAX(Dues)
FROM ISSUE_RETURN IR
INNER JOIN STUDENT STUD ON IR.S_id = STUD.S_id;

Relational Algebra - based Query -

π _{{STUDENT.Sname, MAX (Dues)}

γ _{MAX (Dues)}

(σ _{(STUDENT.S_id = ISSUE_RETURN.S_id)} (STUDENT × ISSUE_RETURN))

c] Find the Return_Date of ‘Database System Concept’ book by student ‘Amar’.

SQL - based Query -

SELECT IR.Return_Date
FROM ISSUE_RETURN IR
INNER JOIN STUDENT STUD ON IR.S_id = STUD.S_id
INNER JOIN BOOK B ON IR.B_id = B.B_id
WHERE STUD.Sname = "Aman" AND B.Title = "Database System Concept";

Relational Algebra - based Query -

π _{ISSUE_RETURN.Return_Date}

(σ _{(STUDENT.Sname = "Aman" ^ BOOK.Title = "Database System Concept")} (STUDENT × ISSUE_RETURN × BOOK))

d] Find all the books issued by ‘Ravi’.

SQL - based Query -

SELECT B.B_ID, B.Title
FROM BOOK B
INNER JOIN ISSUE_RETURN IR ON B.B_id = IR.B_id
INNER JOIN STUDENT STUD ON IR.S_id = STUD.S_id
WHERE STUD.Sname = "Ravi";

Relational Algebra - based Query -

π _{(BOOK.B_id, BOOK.Title)}

(σ _{(STUDENT.Sname = "Ravi")} (STUDENT × ISSUE_RETURN × BOOK))

e] Delete all records of student ‘Vinod’.

SQL - based Query -

Delete STUD, IR
FROM STUDENT STUD
INNER JOIN ISSUE_RETURN IR ON STUD.S_id = IR.S_id
WHERE STUD.Sname = "Vinod";

Relational Algebra - based Query -

STUDENT ← STUDENT - (σ _{(Sname = "Vinod")} (STUDENT))