Formulate the following queries in Relational Algebra on the Company Relational Data Model:
- Find fname and lname of employees in the 'Research' department
that has a 'daughter'
H1 = πessn( σrelationship='daughter'(dependent) ) // ssn of emp's with a daughter H2 = πssn( σdname='Research'(employee⋈dno=dnumberdepartment) ) // ssn of emp's in 'R' dept H3 = H1 ∩ H2 // ssn of emp's in 'R' dept and has a daughter Answer = πfn,ln(employee⋈ssn=(e)ssnH3)
- Find first and last names of employees who
has more than 5 female dependents
H1 = σsex='F'(dependent) // All female dependents H2 = essnFcount(*)(H1) // # female dependents per emp (essn) H3 = σcount>5(H2) // essn of emp's with > 5 depedents Answer = πfn,ln(employee⋈ssn=essnH3) - Find first & last names of employees who work the longest time
(= most number of hours)
on the "ProductX" project
H1 = σpname='ProductX'(project) // ProjectX H2 = works_on⋈pno=pnumberH1 // Emp's who works on ProjectX H3 = Fmax(hrs)(H2) // Max # hrs worked on ProjectX H4 = H3 ⋈hours=maxH2 // Emps that worked max hrs on ProjectX Answer = πfn,ln(employee⋈ssn=essnH4) - Find first & last names of employees in the 'Research'
department who work least number of total hours
H1 = πssn( σdname='Research'(employee⋈dno=dnumberdepartment) ) // ssn of emp's in 'R' dept H2 = works_on⋈essn=essnH1 // Hours worked by 'R' emps H3 = essnFsum(hrs)(H2) // Total # hrs worked per 'R' emp (essn) H4 = Fmin(sum)( H3 ) ) // Lowest total hrs overall of 'R' emps H5 = H3 ⋈sum=minH4 // Emps in 'R' that worked min total # hrs Answer = πfn,ln(employee⋈ssn=essnH5) - Find the name of projects that are worked on by
only female employees.
H1 = πssn( σsex='M'(employee) ) // ssn's of all male employees H2 = πpno ( works_on⋈essn=ssnH1 ) // pno of projects worked by a male employee H3 = πpnumber(project) − H2 // pno of projects NOT worked by any male employee // = pno of projects worked by ONLY female employee Answer = πpname(project⋈pnumber=pnoH3) - Find the name of projects that are
controlled by the "Research" department and
that are worked on by only the female employees
in the "Research" department
H1 = πssn( σsex='M'(employee⋈dno=dnumber(σdname='Research'(department)) ) // ssn's of all male employees in the 'Research' dept H1 = πssn(employee) − πssn( σsex='F'(employee⋈dno=dnumber(σdname='Research'(department)) ) // ssn's of all employees excluding the female emps in the 'Research' dept (There was a confusion what "only" meant. I will count both solutions as correct)
H2 = πpno ( works_on⋈essn=ssnH1 ) // pno of projects worked by an employee that is not // a female in the 'Research' dept H3 = project⋈dnum=dnumber(σdname='Research'(department)) // pno of projects controlled by the 'Research' dept H4 = πpnumber(H3) − H2 // pno of projects controlled by the 'Research' dept // that are NOT worked by "an employee that is not a // female employee in the 'Research' dept" // = pno of projects controlled by the 'Research' dept // that are worked by ONLY female employee in the 'Research' dept Answer = πpname(project⋈pnumber=pnoH4) - For each department, list the department name,
the average salary paid to the male employees,
the average salary paid to the female employees
the number of female employees and
the number of male employees
in the department.
(dname is a key in relation department)
The output relation has the following format:
Dname Sex AvgSal NumberEmps -------------------------------------- Research F .... .... Research M .... .... Payroll F .... ....
Answer:
H1 = employee⋈dno=dnumberdepartment // Form a relation with all info (dname !) Answer = dname,sexFavg(salary),count(*)(H1)
- Find the name of the project that is worked on by the
most number of employees.
H1 = pnoFcount(*)(works_on) // Find number of emp's working on each project (per project) H2 = Fmax(count)( H1 ) // max value over all numbers of emp's H3 = H1 ⋈count=max H2 // Picks out the project in H1 with the max count // This is the project with the most number of emp's Answer = πpname ( project ⋈pnumber=pno H3 ) // Get project name - Find the name of department(s) in which all (every single)
employees in the department
have (at least one) dependent
Suppose the employees who have dependents are: e1, e2, e3 These employees do NOT have any dependents: e4, e5, e6 Suppose the employee relation is as follows: e1 d1 (emp e1 works in dept d1) e2 d4 e3 d1 e4 d4 e5 d5 e6 d5 You want to output department d1 Logic: if a department has an employee from the set {e4, e5, e6}, you must exclude the department from the output (Because if of one {e4, e5, e6} works in the department, then the department does NOT have all employees with at least one dependent).
H1 = πssn(employee) − πessn(dependent) // emp's who do NOT have any dependent H2 = πdno( employee ⋈ssn=ssn H1 ) // dno's of dept with an employee // who do NOT have any dependent H3 = πdnumber(department) − H2 // dno's of dept with ONLY employees // who have a dependent // I.e.: all emp's in dept have dependents Answer = πdname ( department ⋈dnumber=dnumber H3 ) // Get department name - Find the name of projects that is not worked on by any employee
in the department that controls the project.
H1 = employee ⋈ssn=essn works_on ⋈pno=pnumber project // Form a relation with all info needed // Who is working on which project controlled by which department H2 = πpnumber( σdno=dnum(H1) ) // dno=dnum is testing: employee.dno = project.dnum // H2 = project numbers of projects that is worked // on by an employee in the department // that controls the project H3 = πpnumber(project) − H2 // H3 = project numbers of projects that is NOT worked // on by any employee in the department // that controls the project Answer = πpname ( project ⋈pnumber=pnumber H3 ) // Get project name
Statement of Policy on Homework Assignments
Students will be graded partially on the basis of their homework assignments. These homework assignments are to be treated as examinations, and are expected to be your individual work. While discussions with other students in the course may be permitted or encouraged by your instructor, you should write your program yourself. Your instructor (and any teaching assistants assigned to the course) will be glad to help you to the extent that he or she feels reasonable.
Submissions based on other students solutions in prior offerings of the course specifically violate these guidelines, as do submissions prepared with the help of an outside "tutor".
You should take precautions to protect the confidentiality of your work, do not collaborate on questions that you turn in for a grade, do not show your solution to a fellow student, not even after the due date for some students may have receive extension.
All submissions should include a comment statement near the top of the program of the form:
THIS CODE IS MY OWN WORK, IT WAS WRITTEN WITHOUT CONSULTING A TUTOR OR CODE WRITTEN BY OTHER STUDENTS - your name
Cases of apparent plagiarism or collusion will be referred to the Honor Council.