Specify the updates of Exercise 3.11 using the SQL update commands.
EXERCISE 3.11 : Suppose that each of the following Update operations is applied directly to the database state shown in Figure 3.6. Discuss all integrity constraints violated by each operation, if any, and the different ways of enforcing these constraints.
- Insert <‘Robert’, ‘F’, ‘Scott’, ‘943775543’, ‘1972-06-21’, ‘2365 Newcastle Rd,
Bellaire, TX’, M, 58000, ‘888665555’, 1> into EMPLOYEE.
b. Insert <‘ProductA’, 4, ‘Bellaire’, 2> into PROJECT.
c. Insert <‘Production’, 4, ‘943775543’, ‘2007-10-01’> into DEPARTMENT.
d. Insert <‘677678989’,NULL, ‘40.0’> into WORKS_ON.
e. Insert <‘453453453’, ‘John’, ‘M’, ‘1990-12-12’, ‘spouse’> into DEPENDENT.
f. Delete the WORKS_ON tuples with Essn= ‘333445555’.
g. Delete the EMPLOYEE tuple with Ssn= ‘987654321’.
h. Delete the PROJECTtuple with Pname= ‘ProductX’.
i. Modify the Mgr_ssn and Mgr_start_dateof the DEPARTMENT tuple with
Dnumber= 5 to ‘123456789’ and ‘2007-10-01’, respectively.
j. Modify the Super_ssnattribute of the EMPLOYEE tuple with Ssn=
‘999887777’ to ‘943775543’.
k. Modify the Hoursattribute of the WORKS_ON tuple with Essn=
‘999887777’ and Pno= 10 to ‘5.0’
_____________________________________________________________________________________
FIGURE 3.6
_____________________________________________________________________________________
|
EMPLOYEE
|
|---|
|
Fname
|
Minit
|
Lname
|
Ssn
|
Bdate
|
Address
|
Sex
|
Salary
|
Super_ssn
|
Dno
|
|
John
|
B
|
Smith
|
123456789
|
1985-01-09
|
731 Fondren ,Houston,TX
|
M
|
30000
|
333445555
|
5
|
|
Franklin
|
T
|
Wong
|
333445555
|
1955-12-08
|
638 Vose,Houston,TX
|
M
|
40000
|
888665555
|
5
|
|
Alicia
|
J
|
Zelaya
|
999887777
|
1968-01-19
|
3321 Castle,Spring,TX
|
F
|
25000
|
987654321
|
4
|
|
Jennifer
|
S
|
Wallace
|
987654321
|
1941-06-20
|
291 Berry,Bellaire,TX
|
F
|
43000
|
888665555
|
4
|
|
Ramesh
|
K
|
Narayan
|
666884444
|
1962-09-15
|
975 Fire Oak,Humble,TX
|
M
|
38000
|
333445555
|
5
|
|
Joyce
|
A
|
English
|
453453453
|
1972-07-31
|
5631 Rice,Houston,TX
|
F
|
25000
|
333445555
|
5
|
|
Ahmad
|
V
|
Jabbar
|
987987987
|
1969-03-29
|
980 Dallas,Houston,TX
|
M
|
25000
|
987654321
|
4
|
|
James
|
E
|
Borg
|
888665555
|
1937-11-10
|
450 Stone,Houston,TX
|
M
|
55000
|
NULL
|
1
|
|
DEPARTMENT
|
|---|
|
Dname
|
Dnumber
|
Mgr_ssn
|
Mgr_start_date
|
|
Research
|
5
|
333445555
|
1988-05-22
|
|
Administration
|
4
|
987654321
|
1995-01-01
|
|
Headquarters
|
1
|
888665555
|
1981-06-19
|
|
DEPT_LOCATIONS
|
|---|
|
Dnumber
|
Dlocation
|
|
1
|
Houston
|
|
4
|
Stafford
|
|
5
|
Bellaire
|
|
5
|
Sugarland
|
|
5
|
Houston
|
|
WORKS_ON
|
|---|
|
Essn
|
Pno
|
Hours
|
|
123456789
|
1
|
32.5
|
|
123456789
|
2
|
7.5
|
|
666884444
|
3
|
40.0
|
|
453453453
|
1
|
20.0
|
|
453453453
|
2
|
20.0
|
|
333445555
|
2
|
10.0
|
|
333445555
|
3
|
10.0
|
|
333445555
|
10
|
10.0
|
|
333445555
|
20
|
10.0
|
|
999887777
|
30
|
30.0
|
|
999887777
|
10
|
10.0
|
|
987987987
|
10
|
35.0
|
|
987987987
|
30
|
5.0
|
|
987654321
|
30
|
20.0
|
|
987654321
|
20
|
15.0
|
|
888665555
|
20
|
NULL
|
|
PROJECT
|
|---|
|
Pname
|
Pnumber
|
Plocation
|
Dnum
|
|
ProductX
|
1
|
Bellaire
|
5
|
|
ProductY
|
2
|
Sugarland
|
5
|
|
ProductZ
|
3
|
Houston
|
5
|
|
Computerization
|
10
|
Stafford
|
4
|
|
Reorganization
|
20
|
Houston
|
1
|
|
Newbenefits
|
30
|
Stafford
|
4
|
|
DEPENDENT
|
|
Essn
|
Dependent_name
|
Sex
|
Bdate
|
Relationship
|
|
333445555
|
Alice
|
F
|
1988-04-05
|
Daughter
|
|
333445555
|
Theodore
|
M
|
1983-10-25
|
Son
|
|
333445555
|
Joy
|
F
|
1958-05-03
|
Spouse
|
|
987654321
|
Abner
|
M
|
1942-02-28
|
Spouse
|
|
123456789
|
Michael
|
M
|
1988-01-04
|
Son
|
|
123456789
|
Alice
|
F
|
1988-12-30
|
Daughter
|
|
123456789
|
Elizabeth
|
F
|
1967-05-05
|
Spouse
|
