CIS4301 - Quiz 3

Problem 1

Page 1 of 3

Consider the following schema: Employee(Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Super_ssn, Dno) Department(Dname, Dnumber, Mgr_ssn, Mgr_start_date) Dept_locations(Dnumber, Dlocation) Project(Pname, Pnumber, Plocation, Dnum) Works_on(Essn, Pno, Hours) Dependent(Essn, Dependent_name, Sex, Bdate, Relationship)

Accomplish the following tasks in SQL.

(1)

Delete all projects on which nobody works on.

Solution. DELETE FROM Project WHERE Pnumber NOT IN (SELECT Pno FROM WORKS_ON);

(2)

Update the location of departments located in EE to CSE (a move of everybody from EE to CSE building). Solution. UPDATE Dept_locations SET Dlocation = ’CSE’ WHERE Dlocation = ’EE’;

(3)

For the departments without a location but has a single project, set the location of the department as the location of the project (Dlocation becomes Plocation). You have to write an INSERT statement to do this.

INSERT INTO Dept_locations SELECT Dnum, Plocation FROM Project WHERE Dnum IN ( (SELECT Dnum FROM Project GROUP BY Dnum HAVING COUNT(Pnumber) = 1) EXCEPT (SELECT Dnumber FROM Dept_locations));

(4)

Write an assertion that ensures that every Employee works on at most 5 projects.

CREATE ASSERTION a1 CHECK (

CIS4301 - Quiz 3

Page 2 of 3

5 >= ALL (SELECT COUNT(Pno) GROUP BY Essn));

(5)

Write a trigger that ensures that employees are managed by department managers; that is, the Super ssn is the ssn of the manager of the department in which the project on which the employee works on is hosted. More specically, upon insertions of tuples in Works on that are not consistent with this constraint, change the Super ssn to the ssn of the department manager that manages the project in which the employee works on.

CREATE TRIGGER FixSuperSSN AFTER INSERT ON Works_on REFERENCING NEW ROW AS NewRow FOR EACH ROW BEGIN UPDATE Employee SET Super_ssn = (SELECT Mgr_ssn FROM Department, Project, Works_on WHERE Department.Dname = Project.Dnum AND Project.Pnumber = Works_on.Pno AND Works_on.Essn = NewRow.Essn) WHERE Employee.SSN = NewRow.Essn; END;

Problem 2

Consider the following schema from Quiz 2: Flights(flno, from, to, distance, departs, arrives, price) Aircraft(aid, aname, cruisingrange) Certified(eid, aid) Employees(eid, ename, salary)

Write the following queries in Relational Algebra. (Note: Please read the questions carefully before you write; they are similar but different from those of Quiz 2.)

(1)

Find the names of pilots who are not certified for any Boeing aircraft.

Solution. πename (Employees o n Certified)− πename (σaname=’Boeing’ (Employees o n Certified o n Aircraft))

CIS4301 - Quiz 3

Page 3 of 3

(2)

Find the names of aircraft such that the average salary of all pilots certified to operate them is more than $80,000. Solution. πaname (σavg>80,000 (γaid, AVG(salary)→avg (Employees o n Certified o n Aircraft)))