=========================preview======================
(IELM230)asgt4m.pdf
Back to IELM230 Login to download
======================================================
IELM 230 Industrial Data Systems, Fall 2005
Assignment 4. MODEL. [prepared by Lam Chi Ming]

Relational Algebra

For each of the following questions, please use the Employee-Department-Projects Database tables that you also used in your lab. In each question, write the RA expression(s) to give the required answer, and the expected output.

Q1. Report the Last Name and Address of Employees who are not in Dept 5.

OUPUT = PROJECT[lname,address] SELECT[dno<>5] (EMPLOYEE)

Expected output:
lname address
=================
Borg 450 Stone
Wallace 291 Berry
Jabbar 980 Dallas
Zeleya 3321 Castle



Q2. Report the Last Name and Address of Employees who are not in the Research dept.

OUPUT = PROJECT[lname,address] JOIN[EMPLOYEE.dno = DEPARTMENT.dno]
((EMPLOYEE),SELECT[dname<>"Research"] (DEPARTMENT))

Expected output:
lname address
=================
Borg 450 Stone
Wallace 291 Berry
Jabbar 980 Dallas
Zeleya 3321 Castle



Q3. Report the full name of all department managers.

OUTPUT = PROJECT[lname,minit,fname] JOIN[EMPLOYEE.ssn = DEPARTMENT.mssn] (EMPLOYEE,DEPARTMENT)

Expected output:
lname minit fname
============================
Borg E James
Wallace S Jennifer
Wong T Franklin


Q4. Report the last names of all employees involved in a project in Houston.

X = PROJECT[EMPLOYEE.lname,WORKON.pno]
(JOIN[EMPLOYEE.ssn = WORKON.ssn] (EMPLOYEE,WORKON))
Y = SELECT[plocation="Houston"] (PROJECT)
OUTPUT = PROJECT [X.lname] JOIN[X.pno = PROJECT.pno] (X, Y)

Expected output:
lname
======
Wong
Narayan
Borg
Jabbar

Q5. Report SSN and Last name of all employees whose supervisor is female.

X = PROJECT[sssn,lname,sex] (EMPLOYEE)
Z = JOIN[X.sssn = EMPLOYEE.ssn] (X,EMPLOYEE)
Y = PROJECT[EMPLOYEE.ssn] SELECT[sex="f"] ( Z)
OUTPUT = PROJECT[EMPLOYEE.ssn,EMPLOYEE.lname]
(JOIN[Y.ssn = EMPLOYEE.sssn] (Y,EMPLOYEE))

Expected output:
lname SSN
====================
Zeleya 999887777
Jabbar 987987987

Q6. Report the Last name of managers who do not supervise any employee.

A = JOIN[EMPLOYEE.ssn = DEPARTMENT.mssn] (EMPLOYEE,DEPARTMENT)
X = PROJECT[EMPLOYEE.ssn]( A)
Y = DIFFERENCE(PROJECT[ssn](EMPLOYEE),PROJECT[sssn] (EMPLOYEE))
OUTPUT = PROJECT[X.lname] JOIN[X.ssn = Y.ssn](X,Y)

Expected output:
Null