E_P_HOU(SSN) <-- Π ESSN (WORKS_ON ϑ(PNO),(PNUMBER)(σ PLOCATION='Houston'(PROJECT)))D_NO_HOU <-- Π DNUMBER(DEPARTMENT) - Π DNUMBER(σDLOCATION='Houston'(DEPARTMENT))E_D_NO_HOU <-- Π SSN (EMPLOYEE ϑ(PNO),(DNUMBER)(D_NO_HOU))RESULT_EMPS <-- E_P_HOU - E_D_NO_HOU (* this is set DIFFERENCE *)RESULT <-- Π LNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)a) p q r a b c10 a 5 10 b 610 a 5 10 b 525 a 6 25 c 3b)p q r a b c15 b 8 10 b 615 b 8 10 b 5c)p q r a b c10 a 5 10 b 610 a 5 10 b 525 a 6 25 c 315 b 8 -- -- --d)p q r a b c15 b 8 10 b 615 b 8 10 b 5-- -- -- 25 c 3e)p q r10 a 515 b 825 a 610 b 625 c 310 b 5f)p q r a b c10 a 5 10 b 5a) EMP_W_X <-- (σ PNAME='ProductX'(PROJECT)) ϑ (PNUMBER),(PNO)(WORKS_ON)EMP_WORK_10 <-- (EMPLOYEE) ϑ (SSN),(ESSN) (σ HOURS>10(EMP_W_X))RESULT <--Π LNAME,FNAME (σ DNO=5(EMP_WORK_10))b) E <-- (EMPLOYEE)ϑ (SSN,FNAME),(ESSN,DEPENDENT_NAME) (DEPENDENT)R <-- Π LNAME,FNAME (E)c) WONG_SSN <-- Π SSN (σ FNAME='Franklin' ANDLNAME='Wong'(EMPLOYEE))WONG_EMPS <-- (EMPLOYEE) ϑ (SUPERSSN),(SSN) (WONG_SSN)RESULT <-- Π LNAME,FNAME (WONG_EMPS)d) PROJ_HOURS(PNO,TOT_HRS) <-- PNO f SUM HOURS(WORKS_ON)RESULT <-- Π PNAME,TOT_HRS ( (PROJ_HOURS) ϑ (PNO),(PNUMBER)(PROJECT) )e) PROJ_EMPS(PNO,SSN) <-- Π PNO,ESSN (WORKS_ON)ALL_PROJS(PNO) <-- Π PNUMBER (PROJECT)EMPS_ALL_PROJS <-- PROJ_EMPS -:- ALLPROJS (* DIVISION operation *)RESULT <-- Π LNAME,FNAME (EMPLOYEE * EMP_ALL_PROJS)f) ALL_EMPS <-- Π SSN (EMPLOYEE)WORKING_EMPS(SSN) <-- Π ESSN (WORKS_ON)NON_WORKING_EMPS <-- ALL_EMPS - WORKING_EMPS (* DIFFERENCE*)RESULT <-- Π LNAME,FNAME (EMPLOYEE * NON_WORKING_EMPS)g) DEPT_AVG_SALS(DNUMBER,AVG_SAL) <-- DNO f AVG SALARY(EMPLOYEE)RESULT <-- Π DNUMBER,AVG_SAL ( DEPT_AVG_SALS * DEPARTMENT )h) RESULT(AVG_F_SAL) <-- f AVG SALARY(σ SEX='F'(EMPLOYEE) )i) E_P_HOU(SSN) <-- Π ESSN (WORKS_ON ϑ(PNO),(PNUMBER)(σ PLOCATION='Houston'(PROJECT)))D_NO_HOU <-- Π DNUMBER(DEPARTMENT) - Π DNUMBER(σDLOCATION='Houston'(DEPARTMENT))E_D_NO_HOU <-- Π SSN (EMPLOYEE ϑ(PNO),(DNUMBER)(D_NO_HOU))RESULT_EMPS <-- E_P_HOU - E_D_NO_HOU (* this is set DIFFERENCE *)RESULT <-- Π LNAME,FNAME,ADDRESS (EMPLOYEE * RESULT_EMPS)j) DEPT_MANAGERS(SSN)<--Π MGRSSN (DEPARTMENT)EMPS_WITH_DEPENDENTS(SSN) <-- Π ESSN (DEPENDENT)RESULT_EMPS <-- DEPT_MANAGERS - EMPS_WITH_DEPENDENTSRESULT <-- Π LNAME,FNAME (EMPLOYEE *
View Full Document