Database Schema

Relational Schema

The company has at least one office. Each office (identified by oid with location specified by address) consists of one or more departments. Each department (identified by did with a budget dbudget) is located in one office and has one or more employees. Each employee (identified by eid) must belong to a department. The application focuses on two subclasses of employees: engineers and managers. An employee can be neither an engineer nor a manager, and no employee can be both an engineer and a manager. Each employee can specialize in 0 or more areas (identified by aid). Each department must be managed by exactly one manager, and each manager can manage 0 or more departments. Note that the manager of a department does not necessarily belong to that department. Each engineer can work in 0 or more projects, and there must be at least one engineer working in each project. For each project P that an engineer E works on, the number of hours per week that E spends on P is given by hours. Each project (identified by pid with a budget pbudget) must be supervised by exactly one manager. A manager can supervise 0 or more projects. The attributes hours, dbudget and pbudget have non-null values. The unit of dbudget is in millions of dollars (e.g., a value of 10 denote ten million dollars).

1) 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.

2) 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).

eid did dbudget oid address Employees Belongs Departments In Offices Specializes ISA Manages Areas Engineers Managers Offices (oid, address) Departments (did, dbudget, oid, eid) Employees (eid, did) Engineers (eid) Managers (eid) Projects (pid, pbudget, eid) Works (pid, eid, hours) Areas (aid) Specializes (eid, aid) aid Works hours Projects pbudget pid Supervises 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 eid did dbudget oid address Employees Belongs Departments In Offices Specializes ISA Manages Areas Engineers Managers Offices (oid, address) Departments (did, dbudget, oid, eid) Employees (eid, did) Engineers (eid) Managers (eid) Projects (pid, pbudget, eid) Works (pid, eid, hours) Areas (aid) Specializes (eid, aid) aid Works hours Projects pbudget pid Supervises 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