Coursify

SQL & Relational Algebra Queries for a Library Issue System (Student–Book–Issue)

SQL & Relational Algebra Queries for a Library Issue System (Student–Book–Issue)

Verified Sources
Sep 12, 2026

We model a library system with three relations:

  • Student(RollNo, Name, Father_Name, Branch)
  • Book(ISBN, Title, Author, Publisher)
  • Issue(RollNo, ISBN, Date-of-Issue)

The join keys are:

  • RollNo in Student ↔ Issue
  • ISBN in Book ↔ Issue

In both SQL and relational algebra, queries (i)–(v) are expressed using combinations of:

  • Selection (σ or SQL WHERE)
  • Projection (π or SQL SELECT)
  • Join (θ-join / equality join)

Mermaid overview of how answers are derived by chaining selections and joins:

3

Footnotes

  1. Relational algebra - Wikipedia https://en.wikipedia.org/wiki/Relational_algebra - Overview of relational algebra operations including selection and projection. ↩

  2. Introduction to Relational Algebra in DBMS https://www.geeksforgeeks.org/dbms/introduction-of-relational-algebra-in-dbms - Definitions and examples of selection (σ), projection (π), and joins; discusses how relational algebra relates to SQL. ↩

  3. UCSD CSE132B SQL as Query Language (Part I) slides https://cseweb.ucsd.edu/classes/wi19/cse132B-a/slides/sql-query-part1.pdf - Explains join as combining relations under a join condition and projection as selecting attributes. ↩

Relational Algebra: Selection (σ) and Projection (π) Basics

Assumed SQL conventions

  • Use INNER JOIN between Student/Issue and Issue/Book.
  • Dates: interpret “on or before December 1, 2020” as Date-of-Issue≤′2020-12-01′Date\text{-}of\text{-}Issue \le '2020\text{-}12\text{-}01'. This is standard date comparison logic in SQL when the column is a DATE/TIMESTAMP-compatible type.

Footnotes

  1. How to Compare SQL Dates https://www.dbvis.com/thetable/how-to-compare-sql-dates - Describes comparing dates using operators like <= and using date literals in SQL. ↩

How each query is built

Join relevant relations

1

Connect Student↔Issue and/or Issue↔Book using RollNo and ISBN."

Filter with selection

2

Apply σ predicates like Branch='CSE', Publisher='ABC', RollNo='RAM'."

Keep only requested columns

3

Project π attributes or write SQL SELECT list."

Relational Algebra notation used

I use:

  • σθ(R) for filtering
  • πA(R) for column selection
  • R ⋈θ S for join

Join can also be represented as:

  • R⋈S≡σR.k=S.k(R×S)R \bowtie S \equiv \sigma_{R.k=S.k}(R \times S), i.e., a cartesian product followed by selection.

Footnotes

  1. Introduction to Relational Algebra in DBMS https://www.geeksforgeeks.org/dbms/introduction-of-relational-algebra-in-dbms - Definitions and examples of selection (σ), projection (π), and joins; discusses how relational algebra relates to SQL. ↩

General workflow for queries (SQL and Relational Algebra)

  1. 1
    Step 1

    Determine the final output columns for (i)–(v) to drive SQL SELECT / relational algebra projection π.

  2. 2
    Step 2

    If you need student data + issuance date + book/publisher, you must join via RollNo and ISBN. Join correctness follows from join key equality (θ-join / equality join).

    Footnotes

    1. UCSD CSE132B SQL as Query Language (Part I) slides https://cseweb.ucsd.edu/classes/wi19/cse132B-a/slides/sql-query-part1.pdf - Explains join as combining relations under a join condition and projection as selecting attributes. ↩

  3. 3
    Step 3

    Use WHERE (SQL) or σ (relational algebra) to filter tuples (Branch, Publisher, RollNo/Name, Date).

  4. 4
    Step 4

    Apply π (relational algebra) or SELECT (SQL) to output only required columns.

  5. 5
    Step 5

    Translate “on or before Dec 1, 2020” to a predicate Date-of-Issue≤′2020-12-01′Date\text{-}of\text{-}Issue \le '2020\text{-}12\text{-}01'. Date comparisons use standard comparison operators on date literals.

    Footnotes

    1. How to Compare SQL Dates https://www.dbvis.com/thetable/how-to-compare-sql-dates - Describes comparing dates using operators like <= and using date literals in SQL. ↩

(i) List roll number and name of all students of branch 'CSE'

SQL

1SELECT s.RollNo, s.Name
2FROM Student s
3WHERE s.Branch = 'CSE';

Relational Algebra

πRollNo, Name(σBranch=′CSE′(Student))\pi_{\text{RollNo, Name}}\Big(\sigma_{\text{Branch}='CSE'}(\text{Student})\Big)

Selection then projection directly on Student (no Issue/Book needed).

Footnotes

  1. Introduction to Relational Algebra in DBMS https://www.geeksforgeeks.org/dbms/introduction-of-relational-algebra-in-dbms - Definitions and examples of selection (σ), projection (π), and joins; discusses how relational algebra relates to SQL. ↩

(ii) Find the name of student who has issued a book published by 'ABC' publisher

Interpretation: students who appear in Issue joined to Book where Book.Publisher='ABC'.

SQL

1SELECT DISTINCT s.Name
2FROM Student s
3JOIN Issue i ON i.RollNo = s.RollNo
4JOIN Book b  ON b.ISBN = i.ISBN
5WHERE b.Publisher = 'ABC';
  • DISTINCT avoids duplicates if a student issued multiple 'ABC' books.

Relational Algebra

πName((Student⋈Student.RollNo=Issue.RollNoIssue)⋈Issue.ISBN=Book.ISBN(σPublisher=′ABC′(Book)))\pi_{\text{Name}}\Big( \big(\text{Student} \bowtie_{\text{Student.RollNo}=\text{Issue.RollNo}} \text{Issue}\big) \bowtie_{\text{Issue.ISBN}=\text{Book.ISBN}} \big(\sigma_{\text{Publisher}='ABC'}(\text{Book})\big) \Big)

Relational algebra joins combine attributes from joined relations, and selection filters tuples by predicate. 2

Footnotes

  1. Introduction to Relational Algebra in DBMS https://www.geeksforgeeks.org/dbms/introduction-of-relational-algebra-in-dbms - Definitions and examples of selection (σ), projection (π), and joins; discusses how relational algebra relates to SQL. ↩

  2. UCSD CSE132B SQL as Query Language (Part I) slides https://cseweb.ucsd.edu/classes/wi19/cse132B-a/slides/sql-query-part1.pdf - Explains join as combining relations under a join condition and projection as selecting attributes. ↩

(iii) List title of all books and their authors issued to a student 'RAM'

Interpretation: student identifier is RollNo='RAM' (based on the schema naming). We output (Title, Author) for books issued by that student.

SQL

1SELECT b.Title, b.Author
2FROM Book b
3JOIN Issue i ON i.ISBN = b.ISBN
4JOIN Student s ON s.RollNo = i.RollNo
5WHERE s.RollNo = 'RAM';

Relational Algebra

πTitle, Author((Book⋈Book.ISBN=Issue.ISBNIssue)⋈Issue.RollNo=Student.RollNo(σRollNo=′RAM′(Student)))\pi_{\text{Title, Author}}\Big( (\text{Book} \bowtie_{\text{Book.ISBN}=\text{Issue.ISBN}} \text{Issue}) \bowtie_{\text{Issue.RollNo}=\text{Student.RollNo}} (\sigma_{\text{RollNo}='RAM'}(\text{Student})) \Big)

This follows the “join then filter then project” workflow. 2

[CalloutBlock type="warning" title="Potential naming mismatch"] Your question says “student 'RAM'”, while schema uses RollNo for Student.RollNo and Name for Student.Name. If “RAM” is actually a student name (Name='RAM') instead of a roll number, change the predicate to Name=′RAM′\text{Name}='RAM'. Otherwise the SQL/RA above filters by RollNo. (Schema-driven ambiguity must be resolved by the assignment context.)

Footnotes

  1. Introduction to Relational Algebra in DBMS https://www.geeksforgeeks.org/dbms/introduction-of-relational-algebra-in-dbms - Definitions and examples of selection (σ), projection (π), and joins; discusses how relational algebra relates to SQL. ↩

  2. UCSD CSE132B SQL as Query Language (Part I) slides https://cseweb.ucsd.edu/classes/wi19/cse132B-a/slides/sql-query-part1.pdf - Explains join as combining relations under a join condition and projection as selecting attributes. ↩

(iv) List title of all books issued on or before December 1, 2020

Interpretation: filter Issue rows by date condition, then join with Book to get Title.

SQL

1SELECT DISTINCT b.Title
2FROM Book b
3JOIN Issue i ON i.ISBN = b.ISBN
4WHERE i.Date-of-Issue <= DATE '2020-12-01';

Relational Algebra

πTitle(Book⋈Book.ISBN=Issue.ISBNσDate-of-Issue≤DATE ′2020−12−01′(Issue))\pi_{\text{Title}}\Big( \text{Book} \bowtie_{\text{Book.ISBN}=\text{Issue.ISBN}} \sigma_{\text{Date-of-Issue} \le \text{DATE } '2020-12-01'}(\text{Issue}) \Big)

Date comparisons use standard relational comparison operators with date literals; “on or before” maps naturally to  ≤ \,\le\,.

Footnotes

  1. How to Compare SQL Dates https://www.dbvis.com/thetable/how-to-compare-sql-dates - Describes comparing dates using operators like <= and using date literals in SQL. ↩

(v) List all books published by publisher 'ABC'

This uses Book only (no Issue).

SQL

1SELECT b.Title, b.Author, b.ISBN
2FROM Book b
3WHERE b.Publisher = 'ABC';

(You can adjust the SELECT list—title-only is also valid depending on the expected output.)

Relational Algebra

πTitle, Author, ISBN(σPublisher=′ABC′(Book))\pi_{\text{Title, Author, ISBN}}\Big( \sigma_{\text{Publisher}='ABC'}(\text{Book}) \Big)

[CalloutBlock type="tip" title="Pro Tip"]

When writing SQL joins for these problems, a reliable pattern is:

  1. start from the “fact” relation (Issue) when the query concerns issuance date/student/book links,
  2. join outward to Student/Book,
  3. apply WHERE filters,
  4. finish with SELECT/ DISTINCT to match the exact output.
    This mirrors relational algebra’s join–selection–projection decomposition. 2

Footnotes

  1. Introduction to Relational Algebra in DBMS https://www.geeksforgeeks.org/dbms/introduction-of-relational-algebra-in-dbms - Definitions and examples of selection (σ), projection (π), and joins; discusses how relational algebra relates to SQL. ↩

  2. UCSD CSE132B SQL as Query Language (Part I) slides https://cseweb.ucsd.edu/classes/wi19/cse132B-a/slides/sql-query-part1.pdf - Explains join as combining relations under a join condition and projection as selecting attributes. ↩

Common edge cases and precision

Knowledge Check

Question 1 of 4
Q1Single choice

For (iv) “List title of all books issued on or before December 1, 2020”, which SQL predicate correctly encodes the condition?

Explore Related Topics

1

SQL JOIN Operations: Combining Related Data Across Tables

2

Learn SQL in 30 Days: From Zero to Query Master

SQL (Structured Query Language) is the standard language for creating, managing, updating, and retrieving data from relational databases such as MySQL, PostgreSQL, SQL Server, and Oracle. It is widely used across industries — from software engineering to data analytics — making it one of the most in

3

Relational Algebra Equivalence: Why $\pi_A(R) - \pi_A((\pi_A(R) \times S) - R)$ Represents Division

The expression

[ \pi_A(R)-\pi_A\big((\pi_A(R)\times S)-R\big) ]

is a derived form of the relational‑algebra division operator, returning all (A) values that pair with every tuple in (S).

  • Division is defined as (R\div S={a\mid\forall b\in S,;(a,b)\in R}).
  • The formula works by (1) projecting candidate (A) values, (2) forming all required ((A,B)) pairs with (S), (3) subtracting existing pairs to find missing ones, (4) projecting the missing (A) values, and (5) removing them from the candidates.
  • In the example, (R(A,B)={(1,x),(1,y),(2,x),(2,y),(3,x)}) and (S(B)={x,y}) yield (R\div S={1,2}).
  • This construction captures the universal (“for all”) query pattern, unlike selection, join, or simple projection.
Browse all research articles