Information Technology Reference
In-Depth Information
job
varchar(20)
,
salary
integer
,
deptnr
integer not null
,
constraint
pk
primary key
(
empnr
),
constraint
dfk
foreign key
(
deptnr
)
references
department
);
create table
department
(
deptnr
integer not null
,
name
varchar(20) not null
,
managernr
integer
,
constraint
pk
primary key
(
deptnr
),
constraint
efk
foreign key
(
managernr
)
references
employee
).
Consider the transaction on the database produced by the
SQL
program fragment:
exec sql select max
(
empnr
)
into
:e
from
employee
;
exec sql select max
(
deptnr
)
into
:d
from
department
;
exec sql insert into
department
values
(:d
C
1, 'Research', :e);
exec sql insert into
employee
values
(:e
C
1, 'Jones, Mary',
'Sisselenkuja 2, Helsinki', 'research director', 3500, :d
C
1);
exec sql update
department
set
managernr
D
:e
C
1
where
deptnr
D
:d
C
1;
exec sql insert into
employee
values
(:e
C
2, 'Smith, John',
'Rouvienpolku 11, Helsinki', 'researcher', 2500, :d
C
1);
exec sql commit
.
(a) Give the string of tuple-wise actions (readings and insertions of single tuples)
that constitutes the transaction. We assume that the tuples of the relations
employee
and
department
reside in the data pages in an arbitrary order and that
there exist no index to the relations.
(b) Repeat (a) in the case in which there exists an ordered (B-tree or
ISAM
) index
to the relation
employee
on attribute
empnr
and an ordered index to the relation
department
on attribute
deptnr
.
(c) Are the transactions created in (a) and (b) logically consistent? That is, do they
preserve the integrity constraints of the database?
1.2
The following
SQL
program fragments operate on relation r.X;V/. Describe
the transaction produced by the program fragments (1) in the read-write model of
transactions and (2) in the key-range model of transactions.
(a)
update
r
set
V
D
V
C
1
where
X
D
x;
update
r
set
V
D
V
C
1
where
X
D
y;
commit
.
(b)
update
r
set
V
D
V
C
1
where
X
D
x;
update
r
set
V
D
V
C
1
where
X
D
y;
rollback
.
1.3
Explain the meaning of the transaction
BI Œr; x
1
;
v
1
S ŒP
1
I Œr; x
2
;
v
2
S ŒP
2
I Œr; x
3
;
v
3
AŒP
2
I
1
Œr; x
3
;
v
3
C ŒP
2
I Œr; x
4
;
v
4
AŒP
1
I
1
Œr; x
4
;
v
4
I
1
Œr; x
2
;
v
2
C ŒP
1
I Œr; x
5
;
v
5
A
I
1
Œr; x
5
;
v
5
I
1
Œr; x
1
;
v
1
C .
Give
SQL
statements to generate this transaction.
