4.9

SQL allows a foreign-key dependecy to refer to the same relation, as in the following example:

CREATE TABLE manager ( 
    employee_id char(20),
    manager_id char(20), 
    PRIMARY KEY employee_id,
    FOREIGN KEY (manager_id) REFERENCES manager (employee_id)
        ON DELETE CASCADE
); 

Here, employee_id is a key to the table manager, meaning that each employee has at at most one manager. The foreign-key clause requires that every manager also be an employee. Explain exactly what happens when a tuple in the relation manager is deleted.

---

The tuples of all employees of the manager, at all levels, get deleted as well! This happens in a series of steps. The inital deletion will trigger deletion of all the tuples corresponding to direct employees of the manager. These deletions will in turn cause deletions of second-level employee tuples, and so on, till all direct and indirect employee tuples are deleted.