Question

Q2: Get SSN and the last name of married female employees who work on three or more projects Write Q2 in Relational Algebra:

Answer 1

Solution:

Schema of required tables

Employee ( SSN,LNAME,FNAME,SEX)

Dependent(ESSN,Relationship)

Workson(ESSN,PNO)

Write relation algebra query part by part and then combine all to get the final query.

First find the female employees from employee table

Female_Employees = ∏ _SSN ( ( σ _{SEX = ‘F’} (Employee)))

After finding the female employees find the employee ids whose relationship is spouse . This gives the result of married employees.

So Query is

Married_Employees = ∏_ESSN ( ( σ_{Relationship = ‘Spouse’} (Dependent) ))

Now combine the first two queries to get the female married employees i.e

Married_Female_Employees =  ∏_SSN (Female_Employees ⋈_SSN=ESSN Married_Employees )

After that find the employees whose count of project numbers is greater than or equal to 3 from workson table.

So the query is

Employees_more_than_3_projects = ( σ_{Count(pno) >= 3} (_ESSN F _COUNT(pno) WORKS_ON))

So the above query gives the results of more than 3 projects employees.

And then Join Married_Female_Employees and Employees_more_than_3_projects on SSN Column

output1 = ∏_SSN(Married_Female_Employees ⋈ _SSN=ESSN Employees_more_than_3_projects )

Now do output1 natural join with employee table on common column ssn to get the last name of employee.

i.e

Final_Output= ∏_SSN,LNAME (Employee * output1 )

So the final query is

Final_Query = ∏_SSN,LNAME (Employee * (∏_SSN((∏_SSN ((∏ _SSN ( ( σ _{SEX = ‘F’} (Employee))))⋈_SSN=ESSN (∏_ESSN ( ( σ_{Relationship = ‘Spouse’} (Dependent) ))))) ⋈ _SSN=ESSN (( σ_{Count(pno) >= 3} (_ESSN F _COUNT(pno) WORKS_ON))) )))

Note: It's very difficult to understand the query if we write directly so derive each part and then combine all parts is better.

Note: if you have any queries please post a comment thanks a lot..always available to help you...