Relational Schema

1) Find all employees who are located in the office with address 'A'. The schema of the output table is (eid).

2) Find all managers who are not supervising any project. The schema of the output table is (eid).

3) Find all engineers who spend at most 1 hour per week on every project that she/he works on. Exclude engineers who do not work on any project. The schema of the output table is (eid).

4) Find all engineers who work on all the projects that have a budget of at least 10 million dollars and are not working on any project with a budget of below 10 million dollars. Exclude engineers who do not work on any project. The schema of the output table is (eid).

5) Find all projects where the number of engineers who work on that project is exactly two. The schema of the output table is (pid).

6) For each specialization area, find the departments that have some employee who belongs to that department and specializes in that area. Include areas that are not specialized by any employee. The schema of the output table is (aid,did).

7) Find all manager pairs (m1,m2) such that for every project that is managed by m1, the projects budget is higher than the budget of every project managed by m2. Each of m1 and m2 must manage some project. The schema of the output table is (eid,eid2) where eid and eid2 are identifiers of managers m1 and m2, respectively.

8) We say that a manager M manages an engineer E if E belongs to a department that is managed by M. We say that a manager M supervises an engineer E if E works on some project that is supervised by M. We say that a manager M is controlling if for every engineer E that is managed by M, either E is not supervised by any manager or E is supervised by only M and no other manager. Find all controlling managers. Include managers who do not manage any engineers. The schema of the output table is (eid).

Answers need to be in postgresql

CREATE TABLE Offices oid INTEGER address TEXT PRIMARY KEY (old) /*eid - eid of departent's manager / CREATE TABLE Departments ( did INTEGER dbudget INTEGER NOT NULL oid INTEGER NOT NULL. eid INTEGER NOT NULL. PRIMARY KEY (did). FOREIGN KEY (oid) REFERENCES Offices CREATE TABLE Employees eid INTEGER did INTEGER NOT NULL PRIMARY KEY (eid). FOREIGN KEY (did) REFERENCES Departments CREATE TABLE Engineers eid INTEGER PRIMARY KEY (eid). FOREIGN KEY (eid) REFERENCES Employees CREATE TABLE Managers eid INTEGER PRIMARY KEY (eid). FOREIGN KEY (eid) REFERENCES Employees /*eid - eid of project's supervisor CREATE TABLE Projects pid INTEGER poudget INTEGER NOT NULL eid INTEGER NOT NULL PRIMARY KEY (pid). FOREIGN KEY (eid) REFERENCES Managers CREATE TABLE Works pid INTEGER eid INTEGER hours INTEGER NOT NULL PRIMARY KEY (pid,eid). FOREIGN KEY (eid) REFERENCES Engineers, FOREIGN KEY (pid) REFERENCES Projects CREATE TABLE Areas sid TEXT PRIMARY KEY (ald) CREATE TABLE Specializes ( eid INTEGER eid TEXT, PRIMARY KEY (eid, aid). FOREIGN KEY (eid) REFERENCES Employees, FOREIGN KEY (aid) REFERENCES Aress