Database Reference
In-Depth Information
1. The log shown in Figure 7-19 includes four transactions that completed successfully. For each of the four trans-
actions, list the transaction ID and the table(s) modified. Also, list whether the modification to the table added,
changed, or deleted a record.
Transaction ID
Time
Action
Record Updated
Before Image
After Image
1
11:00
Start
2
11:01
Start
1
11:02
Insert
ServiceRequest (16)
(new values)
256
3
11:03
Start
2
11:04
Update
Marina (1)
(old values)
(old values)
(new values)
(new values)
3
11:05
Update
Owner (EL25)
1
11:06
Commit
4
11:07
Start
3
11:08
Update
MarinaSlip (2)
(new values)
(old values)
3
11:09
Commit
2
11:10
Update
MarinaSlip (2)
(old values)
(new values)
2
11:11
Commit
4
11:12
Update
(new values)
MarinaSlip (2)
(old values)
4
11:13
Update
Owner (EL25)
(old values)
(new values)
4
11:14
Commit
FIGURE 7-19
Sample log in which four transactions commit normally
2. Suppose a catastrophe destroys the database just after 11:10. Which transactions in the sample log shown in
Figure 7-19 would the recovery program use to update the restored database? Which transactions would have
to be reentered by users?
3. If two of the four transactions shown in Figure 7-19 started at different times, deadlock could have occurred.
Adjust the log to create deadlock between these two transactions.
4. Two of the five tables in the Alexamara database are defined as follows:
Owner (OwnerNum, LastName, FirstName, Address, City,
State, Zip)
MarinaSlip (SlipID, MarinaNum, SlipNum, Length,
RentalFee, BoatName, BoatType, OwnerNum)
Suppose that a user accesses the database via the following view:
CREATE VIEW OwnerBoat AS
SELECT Owner.OwnerNum, LastName, FirstName, BoatName
FROM Owner, MarinaSlip
WHERE Owner.OwnerNum=MarinaSlip.OwnerNum
;
Suppose further that the database requirements have changed so that a boat can have multiple owners, just as
owners can have more than one boat. What's the new database design for the Owner and MarinaSlip tables, as
well as any other table(s) needed to satisfy the new requirements? Does the new database design affect the
OwnerBoat view? If so, what's the new defining query for the view?
Search WWH ::




Custom Search