0
1.3kviews
Formulate Relational Algebra for Queries

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

2
46views

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))