This task uses a relational database schema and instance adapted from Fundamentals of Database Systems, Elmasri and Navathe. (Question 5.11), given below.

Most of the attribute names are self-explanatory. Super_SSN refers to corresponding employee's supervisor's SSN (Social Security Number). This example is based on US system, assume SSN is similar to Australian Tax File Number. Arrows indicate foreign keys and the corresponding attributes in parent relation. In the case of Super_SSN, the parent relation is the Employee relation itself (self-referencing).

1. All employees working on project 3 are being reassigned (and having their hours transferred) to project 2. The data entry operator has executed the following SQL statement:

UPDATE works_on

SET pno = 2

WHERE pno = 3;

Write down all the integrity constraints violated by the above operation, and why. If the operation does not violate any constraints, indicate this as no violations.

2. The manager of Administration, Jennifer S Wallace (SSN 987654321) has left the company and had her roles filled by Alicia Zelaya (SSN 999887777). The data entry operator has executed the following SQL statement:

DELETE FROM employee

WHERE ssn = 987654321;

However, it was not successful. a) Write down all the integrity constraints violated by the above operation, and why. If the operation does not violate any constraints, indicate this as no violations. b) Using SQL DELETE and UPDATE statements, show how Jennifer can be removed from the database.

3. The Newbenefits project is going to be relocated to, and managed from, Headquarters. The data entry operator has executed the following SQL statement:

UPDATE project

SET dnum = 1

WHERE pnumber = 30;

a) Write down all the integrity constraints violated by the above operation, and why. If the operation does not violate any constraints, indicate this as no violations. b) Given the database state, is the above SQL statement logically correct? Why or why not?

4. Alicia J Zelaya (SSN 999887777) has a new, as yet unnamed, daughter. To record Alicias new dependent, the data entry operator has executed the following SQL statement:

INSERT INTO dependent (essn, sex, bdate, relationship)

VALUES (999887777,F, 2020-12-14, Daughter);

Write down all the integrity constraints violated by the above operation, and why. If the operation does not violate any constraints, indicate this as 'no violations'.

EMPLOYEE Mini Frame John Franklin Alicia B T J 4 S Jennifer Ramesh Joyce Ahmad James Lame Sarr Buate Address Sex Salary Super son Dno Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445556 5 Wong 33744555 1955-12-08 638 Voss, Houston, TX M 40000 B88665555 5 Zelaya 98904777 1968-01-19 3321 Castle, Spring, TX F25000 987654321 Wallace 6876543211941-06-20 291 Berry, Bellaire, TX 43000 888665565 4 Narayan/666884464 1962-09-15 975 Fire Oak. Humble, TX M 38000 333445555 5 Englisty 453453469 1972-07-31 5631 RerHouston, TX F 25000 333445555 5 Jabb 987987987 1969-03-29 180 Dallas, Houston, TXM 25000 987654321 4 Boyd 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 K A V E DEPARTMENT Drame Research Administration Headquarters Dnumber 5 gr 3346555 987654321 BBB565555 4 1 WORKS ON Essn DEPT LOCATIONS Putgettete Dnumber Dlocation 1988-05-22 1 Houston 1995-01-01 4 Stafford 1981-06-10 5 Bellaire 5 Sugarland 5 Houston PROJECT Pnumber Plocation Drum Product 1 Bellaire 5 Product 2 Sugarland 5 Product 3 Houston 5 Computerization 10 Stafford 4 Reorganization 20 Houston 1 Newbenefits 30 Stafford Pno 1 32.5 2 3 123456789 123456789 666884444 453453453 453453453 333445555 25 40.0 20.0 20.0 1 2 2 10.0 10.0 3 10 10.0 20 10.0 333445665 333445665 333445555 999887777 999887777 987987987 987967967 Sex F 30 10 10 M 30.0 10.0 35.0 5.0 DEPENDENT Ess 333445555 333445555 333445555 987654321 123456789 123456789 123456789 F Dependent name Alice Theodore Joy Abner Michael Alice Elizabeth 30 Bdate Relationship 1986-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-28 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse M 30 M 987654321 987654321 888665555 20.0 15.0 20 F 20 NULL F