Example on Relational Algebra

Example:

Consider the following relational database of a college.

Student(RollNumber, StudentName, Address)

Teachers(TeacherID, TecherName, TeachingSubject)

College(RollNumber, TeacherID)

Write SQL or Relational algebra expressions for the following requests.

1. Find the name of Students who live in Lalitpur.
2. Find the name of the teacher who teaches Database Management System subject.
3. Find the name of the teacher who teaches Computer Organisation subject to John Smith.
4. Insert a new tuple into relation teachers.
5. Delete records of students whose address is "Pokhara".

Let us now see the queries.

1.  Π_studentname(σAddress= “Lalitpur”(Student))
   Here we will be using select operator with projection operator to select all tuples that satisfy the condition where the address is Lalitpur.
2. Π_TeacherNamet(σTeachingSubject=“Database Management System”))
   Here we will be using select operator with projection operator to select all tuples that satisfy the condition where the subject is DBMS.
3. Π_TeacherNamet(σTeachingSubject=“Computer Organisation”^StudentName=“John Smith” S⨝X⨝T)) 
   Firstly here we will be using select operator with projection operator to select all tuples that satisfy the condition where the subject is computer organization and name is john smith. In the end, we join the relations (Student, College, Teacher) using the binary joining operator.
4. Teachers<-Teacher ∪ {“T07”, “Manish”, “C++”)
   Here we will be using the assignment operator first and then the union operator to add the tuple.
5. Student<Student-σAddress= “Pokhara”(Students))
   Here we will be using the assignment operator first and then subtracting with the select operator to remove the data.

Its’ time to head for some FAQs.

You can also read about the Multiple Granularity Locking and Checkpoint in DBMS

FAQs

1. What are the types of operators used in Relational language?
   There is two types of operators used in Relational language i.e., Binary and Unary.
    
2. Identify the operator (ρ) and write down its’ use.
   This is the rename operator. The outcomes of relational algebra are also relations, although they lack a name. We may rename the output relation using the rename method.
    
3. Identify the operator (⨝) and write down its’ use.
   This is the natural join operator. It is a binary operator that joins two relations on common attributes’ values.
    
4. Define Relational Algebra.
   Relational Algebra is a query language that accepts instances of relations as input and produces instances of relations as output. It makes use of operators to carry out queries. 
    
5. How are Relational Algebra and Relational Calculus similar yet so different?
   Relational algebra and relational calculus are both formal languages related to the relational model that are used to define fundamental retrieval requests. 
   Relational algebra employs a procedural approach by outlining a step-by-step technique for carrying out a query, whereas relational calculus employs a non-procedural approach by describing the information.

Key Takeaways

In this article first, we extensively discussed Relational Algebra. We learnt the definition of relational algebra. In the next section, we learnt about the operators that are to be used while solving the problems. In the next section, we understood an example by solving it. In the final section, we went through the FAQs of the topic. 

Recommended Readings: 

• Recursive Relationship in DBMS
• Introduction to JQuery
• Aggregation in DBMS

We hope that this blog has helped you enhance your knowledge regarding Relational Algebra and if you would like to learn more, check out our articles on Top 100 SQL Problems and if you would like to learn more, check out our articles on Coding Ninjas Studio. Do upvote our blog to help other ninjas grow. Happy Coding!