Tarea 4
 

Fecha de Entrega: Jueves 11 de Marzo (en clase)
Tipo de Entrega: papel

Crear en álgebra relacional las siguientes consultas, (Ejercicios 3.5, 3.8 y 3.10 libro Silberschatz) en base al siguiente esquema:

employee (person-name, street,city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

  1. Find the names of all employees who work for First Bank Corporation
    person-name(company-name='First Bank Corporation' (works) )

  2. Find the names and cities of residence of all employees who work for First Bank Corporation
    person-name,city( employee |X| company-name='First Bank Corporation' ( works) )

  3. Find the names, street addresses, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per annum.
    person-name, street,city ( employee |X| company-name='First Bank Corporation' and salary>10,000 (works) )

  4. Find the names of all employees in this database who live in the same city as the company for which they work.
    person-name( employee |X| works |X| company )

  5. Find the names of all employees who live in the same city and on the same street as do their managers.
    person-name(
         employee.person-name=manages.person-name and
          manager.person-name=manages.manager-name and
          employee.street=manager.street and
          employee.city=manager.city
           ( employee X manager ( employee) X manages ) )

  6. Find the names of all employees in this database who do not work for First Bank Corporation.
    person-name( company-name != 'First Bank Corporation' (works) )

  7. Find the names of all employees who earn more than every employee of Small Bank Corporation.
    maximum-sbc <-- max(salary) as max ( company-name='Small Bank Corporation' (works ))
    person-name(
    company-name!='SmallBankCorporation' and salary > maximum-sbc.max ( works X maximum-sbc ))

  8. Assume the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.
    company-name, city ( company-name != 'Small Bank Corporation' (company)) /
    city ( company-name = 'Small Bank Corporation' (company))
  9. Modify the database so that Jones now lives in Newtown


    employee <-- person-name,street,city ( person-name != 'Jones' (employee)
       person-name,street,'Newtown' ( person-name = 'Jones' (employee)

     

  10. Give all employees of First Bank Corporation a 10 percent salary raise.
    works<-- person-name,company-name,salary ( company-name != 'First Bank Corporation' (works)
       person-name,compay,salary*1.10 ( company-name = 'First Bank Corporation' (works)

  11. Give all managers in this database a 10 percent salary raise, unless the salary would be greater than $100,000. In such cases, give only a 3 percent raise.

     

    manager <--
    works.person-name as person-name,company-name,salary(works.person-name=manages.manager-name( works X manages ) )


    notmanager <-- works - manager

    works <-- person-name,company-name,salary (notmanager)
       person-name,company-name,salary*1.03 ( salary>100,000( manager))
       person-name,company-name,salary*1.10 ( salary < 100,000( manager))

     

  12. Delete all tuples in the works relation for employees of Small Bank Corporation.
    works<-- works - company-name='SmallBankCorporation'(works)

  13. Find the company with the most employees.

    max-employees <--max(exployees) as maximum(company-name,count(person-name) as employees (works))

    company-name ( employees=maximum

    (company-name,count(person-name) as employees (works) X max-employees))
    company-name ( employees=max(employees)(company-name,count(person-name) as employees (works)))

  14. Find the company with the smallest payroll.

    min-payroll<-- min(payroll) as minumum (company-name,sum(salary) as payroll ( works ) )

    company-name ( payroll=minimum

    (company-name,sum(salary) as payroll ( works ) X minpayroll ))

    company-name ( payroll=min(payroll)(company-name,sum(salary) as payroll ( works )) )

     

  15. Find those companies whose employees earn a higher salary, on average, than the average salary at First Bank Corporation.
    a.company-name(
    a.company-name != 'FirstBankCorporation' and b.company-name = 'FirstBankCorporation' and
         a.average > b.average (
    a (company-name,avg(salary) as average (works) )   X

    b (company-name,avg(salary) as average (works) ) ) )

Detalles de la entrega