Vishwakarma Government Engineering College, Chandkheda
Department of Information & Communication Technology
Semester- III
Database Management Systems --BE03000091
ALA – Assignment: 4
Course Outcome-2,3
1 What is relational Algebra. List various relational algebraic operations.
2 Explain following Relational Algebra Operation with example.
I. Selection
II. Projection
III. Cross Product
IV. Joins (Inner Join, Outer Joins)
V. Rename
VI. Division
VII. Set operators
3 Explain different aggregate functions with example.
4 Consider the following relational database, where the primary keys are
underlined. Give an expression in the relational algebra to express each of the
following queries
▪ employee (ssn, name, dno, salary, hobby, gender)
▪ department (dno, dname, budget, location, mgrssn)
▪ works_on (ssn, pno)
▪ project (pno, pname, budget, location, goal)
1. List all pairs of employee names and the project numbers they
work on.
2. List out department number, department name and department
budget.
3. List all projects that Raj Yadav works on by project name.
4. List the names of employees who supervise themselves.
5 Consider the relational database given below and give an expression in the
relational algebra:
▪ Employee (person-name, street, city) , Works (person-name,
company-name, salary)
▪ Company (company-name, city) , Manages (person-name,
manager-name)
1. Find the names of all employees in this database who live in the
same city as the company for which they work.
2. Find the names, street address, and cities of residence of all
employees who work for HCL and earn more than $10,000 per
annum.
6 Consider the following relations and write an relational algebra:
▪ EMP (empno, ename, jobtitle, managerno, hiredate, sal,
commission, deptno)
▪ DEPT (deptno, dname, location)
I. Find the Employees working in the department number10, 20, 30
only.
II. Find Employees whose names start with letter A or letter a.
III. Find Employees along with their department name.
IV. Find the Employees who are working in Smith's department
V. Find the Employees who get salary more than Allen’s salary.
VI. Display employees who are getting maximum salary in each
department.
VII. Find list of employees whose hire date is on or before 1-April-18.
7 The relational database schema is given below and write the relational algebra
expressions for the given queries.
▪ employee (person-name, street, city)
▪ works (person-name, company-name, salary)
▪ company (company-name, city)
▪ manages (person-name, manager-name)
1. Find the names of all employees who work for First Bank
Corporation.
2. Find the names and cities of residence of all employees who work
for First Bank Corporation.
3. Find the names, street address, and cities of residence of all
employees who work for First Bank Corporation and earn more
than $10,000 per annum.
4. Find the names of all employees in this database who do not work
for First Bank Corporation.
8 What is the difference between open source and commercial DBMS.
