Relational Algebra, SQL, and Tuple Relational Calculus for Student Enrollment

This course section develops query solutions for the schema:

• Student(SID, Name, Dept, Age)
• Course(CID, CName, Faculty)
• Enroll(SID, CID, Marks)

A relation is treated as a set of tuples. Selection $\sigma$ filters tuples, while Projection $\pi$ selects attributes. A Join combines relations through common attributes such as SID or CID.

The main correspondence is:

Relational algebra is procedural in its notation, whereas tuple relational calculus describes the properties that qualifying tuples must satisfy.

Footnotes

1. Introduction of Relational Algebra in DBMS - Overview of selection, projection, joins, and set difference. ↩

2. Relational Algebra in DBMS: Basics & Operations - Explains relational algebra as a foundation for SQL and query processing. ↩

1. Relational Algebra Expressions

Relational algebra expressions are built by composing operations. When a requested result requires attributes from multiple relations, use a join before applying selection or projection.

a) Students enrolled in DBMS

The query must:

1. Select the course whose name is DBMS.
2. Join that course with enrollment records using CID.
3. Join the result with students using SID.
4. Project student attributes or names.

A complete expression returning student details is:

$$\pi_{SID,Name,Dept,Age} \left( Student \bowtie_{Student.SID=Enroll.SID} \left( Enroll \bowtie_{Enroll.CID=Course.CID} \sigma_{CName='DBMS'}(Course) \right) \right)$$

If only student names are required:

$$\pi_{Name} \left( Student \bowtie_{Student.SID=Enroll.SID} \left( Enroll \bowtie_{Enroll.CID=Course.CID} \sigma_{CName='DBMS'}(Course) \right) \right)$$

A natural join may be used when the common attributes have identical names and compatible domains:

$$\pi_{Name}\left(Student \bowtie Enroll \bowtie \sigma_{CName='DBMS'}(Course)\right)$$

---

b) Students scoring greater than 80 marks

Because Marks is stored in Enroll, select enrollment tuples first and then join them with Student:

$$\pi_{SID,Name,Dept,Age} \left( Student \bowtie_{Student.SID=Enroll.SID} \sigma_{Marks>80}(Enroll) \right)$$

For only names:

$$\pi_{Name} \left( Student \bowtie_{Student.SID=Enroll.SID} \sigma_{Marks>80}(Enroll) \right)$$

The selection operation filters rows but does not remove attributes; projection is therefore needed to return only the desired student information.

---

c) Students not enrolled in any course

First project all student identifiers, then subtract the identifiers appearing in Enroll:

$$\pi_{SID}(Student)-\pi_{SID}(Enroll)$$

To return the names of students not enrolled in any course:

$$\pi_{Name} \left( Student \bowtie \left( \pi_{SID}(Student)-\pi_{SID}(Enroll) \right) \right)$$

An equivalent expression using set difference over compatible student tuples is:

$$Student-\pi_{SID,Name,Dept,Age}(Student\bowtie Enroll)$$

Set difference returns tuples present in the first relation but absent from the second; its operands must be union-compatible.

Footnotes

1. Introduction of Relational Algebra in DBMS - Overview of selection, projection, joins, and set difference. ↩

2. Relational Algebra - Database Systems - CS 186 - Describes set difference and union compatibility. ↩

2. SQL Queries

SQL uses aggregate functions to calculate values over sets of rows. MAX() returns the largest value, AVG() computes a mean, and COUNT() counts rows or non-null values. GROUP BY creates one group per department or student, while HAVING filters groups after aggregation.

a) Find highest marks

To return the highest marks value:

1SELECT MAX(Marks) AS HighestMarks
2FROM Enroll;

To return every student who achieved the highest marks, use a subquery:

1SELECT DISTINCT s.SID, s.Name, e.Marks
2FROM Student AS s
3JOIN Enroll AS e
4  ON s.SID = e.SID
5WHERE e.Marks = (
6    SELECT MAX(Marks)
7    FROM Enroll
8);

The subquery produces a scalar maximum, and the outer query retrieves all matching students, including ties.

---

b) Find average marks department-wise

The department is stored in Student, while marks are stored in Enroll, so the tables must be joined:

1SELECT s.Dept,
2       AVG(e.Marks) AS AverageMarks
3FROM Student AS s
4JOIN Enroll AS e
5  ON s.SID = e.SID
6GROUP BY s.Dept;

To display the average with a specified precision, in systems supporting ROUND():

1SELECT s.Dept,
2       ROUND(AVG(e.Marks), 2) AS AverageMarks
3FROM Student AS s
4JOIN Enroll AS e
5  ON s.SID = e.SID
6GROUP BY s.Dept;

Departments with no enrolled students do not appear because this is an inner join. To include them, use a left join:

1SELECT s.Dept,
2       AVG(e.Marks) AS AverageMarks
3FROM Student AS s
4LEFT JOIN Enroll AS e
5  ON s.SID = e.SID
6GROUP BY s.Dept;

---

c) Display students enrolled in more than two courses

Group enrollment rows by student and retain groups whose count exceeds two:

1SELECT s.SID,
2       s.Name,
3       COUNT(DISTINCT e.CID) AS CourseCount
4FROM Student AS s
5JOIN Enroll AS e
6  ON s.SID = e.SID
7GROUP BY s.SID, s.Name
8HAVING COUNT(DISTINCT e.CID) > 2;

HAVING is appropriate because the condition applies to an aggregate count after groups have been formed. WHERE filters individual rows before grouping.

If the database guarantees that each student-course pair occurs at most once, COUNT(e.CID) is also sufficient:

1SELECT s.SID,
2       s.Name
3FROM Student AS s
4JOIN Enroll AS e
5  ON s.SID = e.SID
6GROUP BY s.SID, s.Name
7HAVING COUNT(e.CID) > 2;

Footnotes

1. How to Use HAVING With Aggregate Functions in SQL - Explains GROUP BY, aggregate functions, and HAVING. ↩ ↩²

3. Tuple Relational Calculus

Tuple relational calculus uses tuple variables and logical predicates rather than a sequence of operations. Its general form is:

$$\{t \mid P(t)\}$$

Here, $t$ is a result tuple and $P(t)$ is a predicate. Existential quantification, written $\exists$, states that at least one tuple satisfies a condition.

Query: Find names of students from CSE department

A TRC expression returning result tuples containing student names is:

$$\{ t \mid \exists s \in Student ( s.Dept='CSE' \land t.Name=s.Name ) \}$$

If the result tuple is intended to contain only the Name attribute, an explicit schema-style form is:

$$\{ t(Name) \mid \exists s ( Student(s) \land s.Dept='CSE' \land t.Name=s.Name ) \}$$

If the query returns complete student tuples instead of only names, use:

$$\{ s \mid Student(s)\land s.Dept='CSE' \}$$

The variable $s$ is bound by membership in Student, while $t$ represents the output tuple.

Footnotes

1. Tuple Relational Calculus in DBMS - Defines TRC syntax, tuple variables, predicates, and quantifiers. ↩

4. Translating Relational Algebra into SQL

Given:

$$\pi_{Name} \left( \sigma_{Marks>85} (Student \bowtie Enroll) \right)$$

Interpretation:

1. Student ⋈ Enroll combines each student with enrollment records having the same SID.
2. $\sigma_{Marks>85}$ retains only enrollment records with marks above 85.
3. $\pi_{Name}$ returns only student names.
4. SQL uses DISTINCT to preserve the duplicate-elimination behavior normally associated with relational algebra projection.

The SQL translation is:

1SELECT DISTINCT s.Name
2FROM Student AS s
3JOIN Enroll AS e
4  ON s.SID = e.SID
5WHERE e.Marks > 85;

If duplicate names should remain distinct by student identity, select SID as well:

1SELECT DISTINCT s.SID, s.Name
2FROM Student AS s
3JOIN Enroll AS e
4  ON s.SID = e.SID
5WHERE e.Marks > 85;

The first query matches the requested projection on Name; the second avoids conflating two different students who happen to have the same name.