The following tables form part of a database held in an RDBMS: Employee (empNo,

Question

The following tables form part of a database held in an RDBMS:

Employee (empNo, fName, lName, address, DOB, sex, position, deptNo)

Department (deptNo, deptName, mgrEmpNo)

Project (projNo, projName, deptNo)

WorksOn (empNo, projNo, dateWorked, hoursWorked)

Employee contains employee details and empNo is the key. Department contains department details and deptNo is the key. mgrEmpNo identifies the employee who is the manager of the department. There is only one manager for each department. Project contains details of the projects in each department and the key is projNo (no two departments can run the same project). WorksOn contains details of the hours worked by employees on each project, and empNo/projNo/dateWorked form the key.

Formulate the following queries in relational algebra.

(a) List all the details of employees who are female.

(b) List the names and addresses of all employees who are managers.

(c) Produce a list of the names and addresses of all employees who work for the IT department.

(d) Produce a list of the names of all employees who work on the SCCS project.

(e) Find out how many employees are managed by “James Adam.”

(f) Produce a report of the total hours worked by each employee.

Explanation / Answer

Please find below SQLs

1) Select * from Employee where sex like '%female%';
2) Select fName,lName,address from Employee where position like '%managers%'
Select fName,lName,adress from Employee JOIN Employee on Department on Employee.empNo = Department.mgrEmpNo where Department.deptName = 'IT'
5) Select count(empNo) from Employee where position like '%manager%' and fName ='James' and lName ='Adam'

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.