Question
Assignment 1

Consider the following relational schema:
Employee(eid: integer, ename: string, dateofbirth: date, salary: real)
Works(eid: integer, pid: integer, pct time: integer)
Project(pid: integer, budget: real, managerid: integer, duration: integer, city: string)
The attributes of the relation employee are straightforward. The relation Works tells which employee works on which project and also the percentage of the employee time allocated to the project. The relation Project list all the projects, their budgets, the managers, the main city where the project is located and the duration in months of the project.
I)Query Processing and Optimization
-Write every query in SQL
-Discuss indexes to be created to make the execution of the query run faster.
-Execute the query before and after creating the right indexes and
discuss any difference in the order of the query results
1. List all employees that make more than $100,000
2. Find all employees who work on a project that has been going on for more than 17 months as managers.
3. List the pairs of employee names who work on the same project.
4. List Employees whose projects are all located in Newark
5. List Employees whose projects are all located in the same city.

II)Write integrity constraints (domain, key, foreign key, or CHECK constraints; or assertions) or triggers to ensure each of the following requirements, considered independently.

1.Employees must make a minimum salary of $50000.
2.An employee must work on at least 1 project
3. Every manager of a project must be an employee assigned to that project
4. The budget of a project is computed as the total salary of the employees working on the project calculated proportionally to the percentage of the time the employee spends on the project. 20% of the total salary is added to the budget as management expenses.
5. The budget has to be updated each time an employee is added and dropped from a project, and also each time the salary of an employee changes.
Solution
CREATE TABLE EMPLOYEE
(
EID NUMBER NOT NULL
, ENAME VARCHAR2(60)
, DATEOFBIRTH DATE
, SALARY NUMBER
, CONSTRAINT EMPLOYEE_PK PRIMARY KEY
(
    EID
)
   
);

CREATE TABLE PROJECT
(
PID NUMBER NOT NULL
, BUDGET FLOAT DEFAULT 0
, MANAGERID NUMBER
, DURATION NUMBER
, CITY VARCHAR2(60)
, CONSTRAINT PROJECT_PK PRIMARY KEY
(
    PID
)   
);

ALTER TABLE PROJECT
ADD CONSTRAINT PROJECT_FK1 FOREIGN KEY
(
MANAGERID
)
REFERENCES EMPLOYEE
(
EID
)
;

CREATE TABLE WORKS
(
EID NUMBER NOT NULL
, PID NUMBER NOT NULL
, PCT_TIME NUMBER
, CONSTRAINT WORKS_PK PRIMARY KEY
(
    EID
, PID
)   
);...

This is only a preview of the solution. Please use the purchase button to see the entire solution

$10.00
Assisting Tutor

Related Homework Solutions

Get help from a qualified tutor
Live Chats