SQLPrepare(pcStat, \ SQLExecute(pcStat); }

Solutions for Section 8.5

Exercise 8.5.1(a)

int closestMatchPC(int targetPrice) {

Connection myCon = DriverManager.getConnection(appropriate arguments); PreparedStatement PCStat = myCon.createStatement( \ );

ResultSet PCresult = PCStat.executeQuery(); int diffSq = -1;

int closestmodel = -1; while(PCresult.next()) {

int m = PCresult.getInt(1); int p = PCresult.getInt(2);

int currSq = (p - targetPrice)*(p - targetPrice); if(diffSq == -1 || diffSq > currSq) { diffSq = currSq; closestModel = m; } }

return(m); }

Exercise 8.5.1(f)

void lowerPrices() {

Connection myCon = DriverManager.getConnection(appropriate arguments); Statement PCStat = myCon.createStatement();

PCStat.executeUpdate(\}

Solutions for Section 8.6

Exercise 8.6.1(b)

EXEC SQL BEGIN DECLARE SECTION; int modelNo;

EXEC SQL END DECLARE SECTION;

void deleteModel() {

/* get the model number from the user and store it in modelNo */

EXEC SQL SET TRANSACTION ISOLATION SERIALIZABLE;

EXEC SQL DELETE FROM Product WHERE model = :modelNo;

EXEC SQL DELETE FROM PC

WHERE model = :modelNo;

EXEC SQL COMMIT; }

We think that it is wise to set the isolation level to serializable, even though no tuples are written by this transaction.

The potential problem is that another transaction could read one of the two tuples for a given model, either from Product or PC, while this transaction was deleting the other tuple.

On the other hand, that transaction, if it really needed to be sure that either both tuples or none were present, could set its own isolation level to serializable.

Since there is no harm that can come to our transaction if one and not the other tuple is present, we think it is also acceptable to replace the SET-TRANSACTION statement by:

EXEC SQL SET TRANSACTION READ WRITE ISOLATION LEVEL UNCOMMITTED;

LEVEL

READ

Exercise 8.6.4

It makes no sense to run T serializably.

Once T starts, it would never see any changes to the database.

In practice, a long-running transaction like T would have to be aborted by the system periodically.

That would have the fortunate effect of allowing T to restart and see any new PC's that were added to the database.

For T's purposes, it is sufficent if it runs with isolation level repeatable-read.

That will guarantee that whenever it sees a tuple, it will continue to see that tuple.

However, it will also be allowed to see new tuples as they are added to the database.

The potential problem with repeatable-read is that the transaction of Exercise 8.6.1(c), which lowers the price of a PC, might not be able to run while T is running, because it would change the tuple with the old price, which T is required to see.

On the other hand, systems that support ``multiversion'' concurrency control would allow T to see the old tuple, while other transactions can see the new one.

T can also run at isolation level read-committed, and this choice is probably best.

It will eventually see any new or updated tuple that the transactions of Exercise 8.6.1(c) or (d) produce, while not putting any constraints on these transactions.

Isolation level read-uncommitted for T is a possibility, although it would have the consequence that a new PC could be inserted into the database very briefly, while the person inserting the PC has a change of heart and aborts the change at the terminal, while T has already seen the PC's information.

Solutions for Section 8.7

Exercise 8.7.1(c)

We need:

SELECT on Movie

SELECT on Studio

INSERT on Studio

The privilege INSERT(name) on Studio will suffice for (3).

Exercise 8.7.2

Here are the diagrams after Step 4;

Step 5;

Step 6.

Database Systems: The Book

Complete Solutions for Chapter 9

Solutions for Section 9.1

Exercise 9.1.1(a)

Add to the definition of Product the declaration lowerPrice(inout real);

Exercise 9.1.1(b)

Add to the definition of Product the declaration real getSpeed() raises notComputer;

Exercise 9.1.2(a)

SELECT p.model FROM PCs p

WHERE p.price < 2000

Exercise 9.1.2(c)

SELECT DISTINCT lp1.manufacturer FROM Printers lp1, Printers lp2

WHERE lp1.manufacturer = lp2.manufacturer AND lp1.printerType = \ lp2.printerType = \ lp1.model != lp2.model

Exercise 9.1.4(a)

SELECT p.model

FROM (SELECT x FROM PCs x WHERE x.price < 2000) p

Exercise 9.1.4(c)

SELECT DISTINCT lp1.manufacturer

FROM (SELECT x FROM Printers x WHERE x.printerType = \ (SELECT y FROM Printers y WHERE y.manufacturer = lp1.manufacturer

AND y.printerType = \ AND y.model != lp1.model) lp2

Notice that the second collection in the FROM clause is empty unless there is a second laser printer by the manufacturer of lp1.

Solutions for Section 9.2

Exercise 9.2.1(a)

SELECT pc.manufacturer FROM PCs pc

WHERE EXISTS pr IN Printers :

pr.manufacturer = pc.manufacturer

Exercise 9.2.1(b)

SELECT pc1.manufacturer FROM PCs pc1

WHERE FOR ALL pc2 IN PCs :