Скачать или смотреть Solving Examples on Relational Algebra || Lesson 53 || DBMS || Learning Monkey ||

Here we will have a discussion on Solving Examples on Relational Algebra.
To have a better understanding please go through the following posts.

Introduction to Relational Algebra and its operators in DBMS


Rename Union Intersection Set Difference and Cartesian Product Operators in Relational Algebra


Inner join Theta join Equi join Natural join


Outer join Left outer join Right outer join Full outer join


Solving Examples on Relational Algebra:

We will solve the following queries.

1. Retrieve the name and address of all employees who work in the 'Research' department.

2. For every project located in 'Stafford', List the project number, controlling department number, and the department manager's last name, address, and birthdate.

Example 1:

Retrieve the name of all employees who work in the 'Research' department.
The Relational algebraic expression for the query is given below.

fname, lname ( Dname = 'Research' (Department (Dnumber = Dno) Employee) )

How this query will get executed?

Consider the innermost part of the query (Department (Dnumber = Dno) Employee) with this we will get the department names in which the employee is working.
The next part of the query is Dname = 'Research'.

From the above output, we are going to select the rows where Dname = 'Research'.
Now we have to project fname and lname ( fname, lname ).

Example 2:

For every project located in 'Stafford', List the project number, controlling department number, and the department manager's last name, address, and birthdate.

In order to solve the query, we should have employee, project and department tables of the company database
We will solve this problem step by step.

STEP 1:

First, we will find the projects which are located in 'Stafford'.

LOC_STAFF ( Plocation = 'Stafford' (Project))

With this, we will select the rows with Plocation = 'Stafford' in the project table.
STEP 2:

Now, we will find the controlling department of these projects.

The Dnum field states that these projects (Computerization and Newbenefits) are controlled by department 4.

We have to get the name of that department.

CONT_DEPT (LOC_STAFF (Dnum = Dnumber) Department)
STEP 3:

Now, we have to get the Lname, Address, and Birthdate of the department manager.

With the Mgr_SSN field of the above table, we knew that they are the managers of the controlling departments.

The details of the managers were located in the employee table.

PROJ_MGRS ( CONT_DEPT (Mgr_SSN = SSN ) Employee)
STEP 4:

Now we have to project the Pno, Dnumber, Lname, Address, B date.

RESULT ( Pno, Dnumber, Lname, Address, B date (PROJ_MGRS))



Link for our website: https://learningmonkey.in

Follow us on Facebook @   / learningmonkey  

Follow us on Instagram @   / learningmonkey1  

Follow us on Twitter @   / _learningmonkey  

Mail us @ [email protected]