CSC 742 Database Management Systems Topic 7 Relational Algebra Supplement Spring 2002 CSC 742 DBMS by Dr Peng Ning 1 Employee Dependent Fname Lnane SSN Fname Lnane ESSN Alice Zelaya 999 88 7777 Eric Zelaya 999 88 7777 Jennifer Wallace 111 22 3333 Alex Wallace 111 22 3333 Joyce White 222 33 4444 Employee DFname DLname SSN Dependent Fname Lnane SSN DFName DLnane Alice Zelaya 999 88 7777 Eric Zelaya Jennifer Wallace 111 22 3333 Alex Wallace Joyce White 222 33 4444 NULL NULL Spring 2002 CSC 742 DBMS by Dr Peng Ning 2 Faculty Student Name SSN Rank Department Alice 999 88 7777 Professor CSC Jennifer 111 22 3333 Assistant Prof ECE Joyce 222 33 4444 Associate Prof CSC Name SSN Advisor Department Eric 999 88 7777 Alice CSC Alex 111 22 3333 Jennifer ECE Faculty OUTER UNION Student Name SSN Rank Advisor Department Alice 999 88 7777 Professor NULL CSC Jennifer 111 22 3333 Assistant Prof NULL ECE Joyce 222 33 4444 Associate Prof NULL CSC Eric 999 88 7777 NULL Alice CSC Alex 111 22 3333 NULL Jennifer ECE Spring 2002 CSC 742 DBMS by Dr Peng Ning 3 A Complete Set of Relational Algebra The complete set of operations Any of the other relational algebra operations can be expressed as a sequence of operations from this set Examples R S R c S Spring 2002 CSC 742 DBMS by Dr Peng Ning 4 Take home Exercise Prove that is not redundant in the set of relational algebra operations Spring 2002 CSC 742 DBMS by Dr Peng Ning 5 Division Operation R Z S Z where X Z Let Y Z X The result is a relation T Y that includes a tuple t if tuples tR apear in R with tR Y t and with tR X tS for every tuple tS in S Spring 2002 CSC 742 DBMS by Dr Peng Ning 6 R Spring 2002 A B a1 b1 a1 a2 b1 a2 a3 b1 a3 a4 b1 a1 b2 a3 b2 a2 b3 a3 b3 a1 b4 a2 b4 a3 b4 S T A B b1 b4 CSC 742 DBMS by Dr Peng Ning 7 Exercise 1 Find names of employees in the research dept Employee Fname Lname SSN Bdate Address Sex Salary SuperSSN Dno Department Dname Dnumber MgrSSN MgrStartDate Spring 2002 CSC 742 DBMS by Dr Peng Ning 8 Exercise 2 For every project in Stafford list the controlling dept number and the dept manager s last name Employee Fname Lname SSN Bdate Address Sex Salary SuperSSN Dno Department Dname Dnumber MgrSSN MgrStartDate Project Pname Pnumber Plocation Dnum Spring 2002 CSC 742 DBMS by Dr Peng Ning 9 Exercise 3 Find SSNs of employees who work on all the projects controlled by dept 5 Employee Fname Lname SSN Bdate Address Sex Salary SuperSSN Dno Works on ESSN Pno Hours Project Pname Pnumber Plocation Dnum Spring 2002 CSC 742 DBMS by Dr Peng Ning 10