Relational Algebra I — Set & Unary Operators

10. Practice Problems

1. Write σ_{cgpa ≥ 3.7}(student) in SQL using the demo table from §3.
   σ_{cgpa ≥ 3.7}(student) কে SQL-এ লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   p1.sql

   SELECT * FROM student WHERE cgpa >= 3.7;

   Run Copy Open in SQLite Online

2. Express “list of distinct departments that have at least one student” in algebra and SQL.
   যেসব department-এ অন্তত একজন student আছে তাদের তালিকা — algebra ও SQL-এ লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   Algebra: π_dept(student). SQL: SELECT DISTINCT dept FROM student;

3. π and σ — does it matter which order you apply them? Give an example.
   π এবং σ-এর প্রয়োগের ক্রম কি ফলাফল বদলায়?
   ✨ Show Answer (উত্তর দেখুন)

   If σ’s predicate uses only the columns that π will keep, then π(σ(R)) = σ(π(R)) — order doesn’t matter. If σ uses a column that π drops, you must apply σ first; otherwise the column is gone. Optimizers prefer pushing σ down (selection-pushdown) because filtering early reduces the rows π must process.

4. Are these two relations union-compatible? R(name TEXT, age INT) and S(age INT, name TEXT).
   R(name, age) এবং S(age, name) কি union-compatible?
   ✨ Show Answer (উত্তর দেখুন)

   No. Union compatibility is by position, not by name. Position 1 of R is TEXT but position 1 of S is INT. To union them, rename: SELECT name, age FROM S first.

5. Show how to compute R ∩ S using only ∪ and −.
   শুধু ∪ এবং − ব্যবহার করে R ∩ S বের করুন।
   ✨ Show Answer (উত্তর দেখুন)

   R ∩ S = R − (R − S). Or symmetrically, S − (S − R).

6. Write “customers who use bKash but not Nagad” using EXCEPT.
   যেসব গ্রাহক bKash ব্যবহার করেন কিন্তু Nagad ব্যবহার করেন না — EXCEPT দিয়ে লিখুন।
   ✨ Show Answer (উত্তর দেখুন)

   p6.sql

   SELECT msisdn FROM bkash_user
   EXCEPT
   SELECT msisdn FROM nagad_user;

   Run Copy Open in SQLite Online

7. What is the cardinality of σ_true(R) and σ_false(R)?
   σ_true(R) এবং σ_false(R)-এর row সংখ্যা কত?
   ✨ Show Answer (উত্তর দেখুন)

   σ_true(R) = R (every row passes), so |σ_true(R)| = |R|. σ_false(R) = ∅, so |σ_false(R)| = 0.

8. Does π always reduce the number of rows? Always preserve it? Always increase it? Choose one and justify.
   π কি সর্বদা row কমায়, একই রাখে, না বাড়ায়?
   ✨ Show Answer (উত্তর দেখুন)

   π never increases row count. It can decrease (if removing columns causes duplicate rows that get deduped) or keep the count the same (if the projected columns include a key). It can never grow.

9. Use ρ to write a query that finds pairs of students from the same department (each pair listed once).
   একই department-এর ছাত্র জোড়া দেখান (প্রতিটি জোড়া একবার)।
   ✨ Show Answer (উত্তর দেখুন)

   p9.sql

   SELECT a.name AS student_a, b.name AS student_b, a.dept
   FROM student AS a, student AS b
   WHERE a.dept = b.dept AND a.id < b.id;

   Run Copy Open in SQLite Online

10. Why does pure relational algebra not have an ORDER BY?
    বিশুদ্ধ relational algebra-তে ORDER BY নেই কেন?
    ✨ Show Answer (উত্তর দেখুন)

    Because relations are sets, and sets have no inherent order. Ordering is a presentation concern, not a data concern. SQL adds ORDER BY as a final-stage non-relational layer for human readers.

11. Translate this English to algebra: “Names of CSE students with CGPA above 3.5”.
    “CSE-এর CGPA 3.5-এর বেশি ছাত্রদের নাম” — algebra-তে লিখুন।
    ✨ Show Answer (উত্তর দেখুন)

    π_name(σ_{dept='CSE' ∧ cgpa > 3.5}(student))

12. Are UNION and UNION ALL the same in algebra?
    SQL-এ UNION এবং UNION ALL কি algebra-তে এক?
    ✨ Show Answer (উত্তর দেখুন)

    No. UNION matches algebra’s ∪ (deduplication). UNION ALL is a bag operation that keeps duplicates — it doesn’t exist in pure relational algebra; it’s an extension for performance and bag-semantics SQL.

13. Write the algebra and SQL: “Students whose name appears in either the library table or the hostel table”.
    যাদের নাম library-তে অথবা hostel-এ আছে — algebra ও SQL।
    ✨ Show Answer (উত্তর দেখুন)

    Algebra: π_name(library) ∪ π_name(hostel).

    p13.sql

    SELECT name FROM library
    UNION
    SELECT name FROM hostel;

    Run Copy Open in SQLite Online

14. Translate this SQL to algebra: SELECT DISTINCT name FROM student WHERE dept = 'CSE' OR dept = 'EEE';
    উপরের SQL-কে algebra-তে রূপান্তর করুন।
    ✨ Show Answer (উত্তর দেখুন)

    π_name(σ_{dept='CSE' ∨ dept='EEE'}(student)). Equivalently: π_name(σ_dept='CSE'(student)) ∪ π_name(σ_dept='EEE'(student)).