DATABASE MANAGEMENT SYSTEM

CONSIDER THE TABLE:

 

 

Select Operator 

1. Write on RA expression to find all Employees working in the Finance Department.

 

Employee(ID,Name,Dept_Name, Salary)

Solution: 

                 σ  Dept_Name=”Finance” (Employee)

 

 

 

2. Write on RA expression to find all employees whose salary is greater than 50000.

 

Employee(ID,Name,Dept_Name, Salary)

Solution:

σ salary > 50000 (Employee)

 

 

3. Write on RA expression to find all Employees who are  working in the Finance Department and getting a salary greater than 50000.

 

Employee(ID,Name,Dept_Name, Salary)

 

Solution: 

                 σ  Dept_Name=”Finance” ∩salary > 50000  (Employee)

4. Write on RA expression to find all Employees who are  getting salary between 50000 and 80000.

 

Employee(ID,Name,Dept_Name, Salary)

Solution:

σ salary >= 50000 and salary <=80000 (Employee)

5. Write an RA expression to find all Employees in the Employee Table.

 

Employee(ID,Name,Dept_Name, Salary)

Solution:

σ id>1 (Employee)

 

Project:

6. Write an RA expression to list all Employees ID,Name and salary but not Dept_Name  in the Employee Table.

 

Employee(ID,Name,Dept_Name, Salary)

Solution:

Π ID,Name, Salary (Employee)

 

 

7. Write an RA expression to list name of all Employees in the IT Department.

 

Employee(ID,Name,Dept_Name, Salary)

Solution:

Π Name(σ Dept_Name=”IT”(Employee)

 

 

UNION

8. List all Employees names associated with the Company either as a Software Developer or a Project Manager.

Software_Dev(EName, EID, Salary)

Project_Manager(EName,PID, Salary)

Solution:

Π Ename(Software_Dev) ∪ Π Ename(Project_Manager)

9. Consider an University Database

Instructor(ID,Name,Dept_Name,Salary)

Course(Course_ID,Title, Dept_Name, Credits)

Department(Dept_Name, Building, Budget)

Section(Course_ID,Sec_ID,Semester,Year,Building,Room_No,Time_Slot_ID) 

Teaches(ID, Course_ID,Sec_ID,Semester,Year)

Students(ID, Name,Dept_Name,Tot_Cred)

Advisor(S_ID,I_ID)

Takes(ID,Course_ID, Sec_ID,Semester,Year, Grade)

Classroom(Building,Room_Number, Capacity)

Time_Slot(Time_Slot_ID,Day,Start_Time, End_Time)

Find the set of all courses(Course_ID)  taught in the Fall 2009 semester , the spring 2010 semester or both.

Solution: here Section is the table which contain all attributes i.e. Course_ID, Year and Semester.

Set of all courses(Course_ID)  taught in the Fall 2009 semester 

ΠCourse_ID(σSemester=”Fall” ∩ year=2009(Section)) 

Set of all courses(Course_ID)  taught in the Spring 2010 semester 

ΠCourse_ID(σSemester=”Spring” ∩ year=2010(Section)) 

Set of all courses(Course_ID)  taught in the Fall 2009 semester , the spring 2010 semester or both.

ΠCourse_ID(σSemester=”Fall”∩year=2009(Section))∪ ΠCourse_ID (σSemester=”Spring” ∩ year=2010(Section)) 

 

10.  List of all employee name those who are software developer but not a project manager.

Software_Dev(EName, EID, Salary)

Project_Manager(EName,PID, Salary)

Solution: 

Π Ename(Software_Dev) - Π Ename(Project_Manager)

11. Consider the University Database in question number 9.

Find all the courses taught in Fall 2009 semester but not in Spring 2010.

Solution: here Section is the table which contain all attributes i.e. Course_ID, Year and Semester.

Set of all courses(Course_ID)  taught in the Fall 2009 semester 

ΠCourse_ID(σSemester=”Fall” ∩ year=2009(Section)) 

Set of all courses(Course_ID)  taught in the Spring 2010 semester 

ΠCourse_ID(σSemester=”Spring” ∩ year=2010(Section)) 

Set of all courses(Course_ID)  taught in the Fall 2009 semester , the spring 2010 semester or both.

ΠCourse_ID(σSemester=”Fall”∩year=2009(Section))- ΠCourse_ID (σSemester=”Spring” ∩ year=2010(Section)) 

12.  Consider the University Database in question number 9. List all the instructor IDs who are not Advisor.

Solution: here we will take Instructor and Advisor table

Π ID(Instructor)-Π ID(Advisor)

 

13. Consider the University Database in question number 9. List all the instructor IDs who taught in Spring 2020 but are not Advisor.

Solution: here we will take teaches table

Π ID(σSemester=”Spring” ∩ year=2020(teaches))-Π ID(Advisor)

14. Consider the University Database in question number 9. Find the names of all instructors in the physics department together with the course id of all courses they taught.

Solution: 

Here we take relation Instructor and Teaches

σDept_Name=”Physics”(Instructor X Teaches)

 

σDInstructor.ID=Teaches.ID(σDept_Name=”Physics”(Instructor X Teaches))

ΠName, Course_ID(σDInstructor.ID=Teaches.ID(σDept_Name=”Physics”  (Instructor X Teaches)))

 

15. Given a Relation:

employee(name,salary,deptno) and department(deptno,deptname,address)
Write relational algebra for the following questions.

1. Department address of every employee

 

Solution: Π address(σemployee.deptno=department.deptno(employee X department)) 

 

2. Employee whose name is same as their department name

 

Solution: 

Π name(σ employee.deptno=department.deptno ∩ employee.name=department.deptname    (employee X department))