TRIBHUVAN UNIVERSITY
Institute of Management studies
LAB REPORT
RELATIONAL ALGEBRA
PROGRAM BACHELOR OF INFORMATION MANAGEMENT
COURSE DATABASE MANAGEMENT SYSTEM
COURSE CODE IT 220
SUBMITTED BY KOMAL OLI (14498/22)
-----------------------------------
SUPERVISIOR
Date: July 11, 2024
Selection (σ)
a. Retrieve all courses with CourseID equal to 102.
Relational Algebra: σ CourseID=102 (Course)
Output:
b. Find all enrollments where the Grade is 'A'.
Relational Algebra: σ Grade='A' (Enrollment)
Output:
c. Get all departments located in 'Building C'.
Relational Algebra: σ Location='Building C' (Department)
output:
Projection (π)
a. Display only the CourseID and Instructor for all courses.
Relational Algebra: π CourseID, Instructor (Course)
Output:
b. Show only the StudentID from the Enrollment table.
Relational Algebra: π StudentID (Enrollment)
Output:
c. List only the DeptName from the Department table.
Relational Algebra: π DeptName (Department)
Output:
Union (∪)
a. Find the union of courses taught by 'Dr. Smith' and 'Prof. Johnson'.
Relational Algebra:
π CourseID, CourseName, Instructor (σ Instructor='Dr. Smith' (Course)) ∪ π CourseID,
CourseName, Instructor (σ Instructor='Prof. Johnson' (Course))
Output:
c. Union the enrollments with Grade 'A' and Grade 'B'.
Relational Algebra: σ Grade='A' (Enrollment) ∪ σ Grade='B' (Enrollment)
Output:
Intersection (∩)
a. Find the intersection of courses enrolled by StudentID 1001 and 1002.
Relational Algebra:
π CourseID (σ StudentID=1001 (Enrollment)) ∩ π CourseID (σ StudentID=1002
(Enrollment))
Output:
b. Find common departments between 'Building A' and 'Building C'.
Relational Algebra: π DeptName (σ Location='Building A' (Department)) ∩ π DeptName (σ
Location='Building C' (Department))
Output:
c. Intersection of enrollments with Grade 'A' and Grade 'C'.
Relational Algebra: σ Grade='A' (Enrollment) ∩ σ Grade='C' (Enrollment)
Output:
Set Difference (−)
a. List departments that are not located in 'Building B'.
Relational Algebra: Department - σ Location='Building B' (Department)
Output:
b. Set difference between enrollments with Grade 'A' and Grade 'B'.
Relational Algebra: σ Grade='A' (Enrollment) - σ Grade='B' (Enrollment)
Output:
Join Operations
a. Perform an inner join between Course and Enrollment tables.
Relational Algebra: Course ⨝ Enrollment
Output:
b. Perform a left outer join between Course and Enrollment tables.
Relational Algebra: Course ⟕ Enrollment
Output:
c. Perform a right outer join between Enrollment and Department tables.
Relational Algebra: Enrollment ⟖ Department
Output:
Division (÷)
c. Find students who have not enrolled in any course.
Relational Algebra: π StudentID (Enrollment) - π StudentID (Enrollment)
Output:
Rename (ρ)
a. Rename the Course table to CourseInfo.
Relational Algebra: ρ CourseInfo (Course)
Output:
b. Rename the Enrollment table to StudentEnrollment.
Relational Algebra: ρ StudentEnrollment (Enrollment)
Output:
c. Rename the Department table to DeptDetails.
Relational Algebra: ρ DeptDetails (Department)
Output:
Duplicate Elimination
a. Eliminate duplicate enrollments from the Enrollment table.
Relational Algebra: σ EnrollmentID=1 (Enrollment) ∪ σ EnrollmentID=2 (Enrollment)
Output:
b. Eliminate duplicate departments from the Department table.
Relational Algebra: σ DeptID=1 (Department) ∪ σ DeptID=2 (Department) ∪ σ DeptID=3
(Department)
Output:
