Faculty-Student Database: Relational Algebra and SQL Queries with Examples

Faculty-Student Database: Relational Algebra and SQL Queries

This post explains how to perform relational algebra and equivalent SQL queries on the Faculty-Student database. Output tables are included for clarity.

---

1. Given Tables

Faculty Table

faculty_id	faculty_name	department	contact_number
F01	Dr. Ahmed	CSE	01711111111
F02	Dr. Sultana	EEE	01722222222
F03	Dr. Rahim	ME	01733333333

Student Table

student_id	student_name	major	faculty_id
S01	Arif Hasan	CSE	F01
S02	Maria Khan	EEE	F02
S03	Joy Das	CSE	F01
S04	Nina Roy	ME	F03

---

2. Relational Algebra and SQL Queries

Q1: Student Names whose Faculty belongs to CSE

Relational Algebra:

π_student_name (σ_department='CSE' (Student ⨝ Faculty))

SQL Query:

SELECT s.student_name
FROM Student s
JOIN Faculty f ON s.faculty_id = f.faculty_id
WHERE f.department = 'CSE';

Output:

student_name
Arif Hasan
Joy Das

Q2: Rename Faculty as Teacher, display EEE department

Relational Algebra:

ρ_Teacher(faculty_id, teacher_name, department, contact_number) 
(σ_department='EEE'(Faculty))

SQL Query:

SELECT faculty_id, faculty_name AS teacher_name, department, contact_number
FROM Faculty
WHERE department = 'EEE';

Output:

faculty_id	teacher_name	department	contact_number
F02	Dr. Sultana	EEE	01722222222

Q3: List student IDs, names with faculty names and department

Relational Algebra:

π_student_id, student_name, faculty_name, department (Student ⨝ Faculty)

SQL Query:

SELECT s.student_id, s.student_name, f.faculty_name, f.department
FROM Student s
JOIN Faculty f ON s.faculty_id = f.faculty_id;

Output:

student_id	student_name	faculty_name	department
S01	Arif Hasan	Dr. Ahmed	CSE
S02	Maria Khan	Dr. Sultana	EEE
S03	Joy Das	Dr. Ahmed	CSE
S04	Nina Roy	Dr. Rahim	ME

Q4: Total number of students per major, descending

Relational Algebra:

γ_major, COUNT(student_id)→total (Student)

SQL Query:

SELECT major, COUNT(student_id) AS total
FROM Student
GROUP BY major
ORDER BY major DESC;

Output:

major	total
ME	1
EEE	1
CSE	2

---

3. Course Table Operations

Schema:

CREATE TABLE Course (
    course_code VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(50),
    semester VARCHAR(10)
);

Add Department column:

ALTER TABLE Course
ADD Department VARCHAR(30);

Insert sample tuple:

INSERT INTO Course (course_code, course_name, semester, Department)
VALUES ('CSE101', 'Introduction to CSE', 'Fall', 'CSE');

Output Table:

course_code	course_name	semester	Department
CSE101	Introduction to CSE	Fall	CSE