|
|
|
|
|
Solutions for Section 8.1
Solutions for Section 8.2
Solutions for Section 8.4
Solutions for Section 8.5
Solutions for Section 8.6
Solutions for Section 8.7
void closestMatchPC() {
EXEC SQL BEGIN DECLARE SECTION;
char manf[30], SQLSTATE[6];
int targetPrice, /* holds price given by user */
speedOfClosest, modelOfClosest, priceOfClosest,
/* for closest price found so far */
tempSpeed, tempModel, tempPrice;
/* for tuple just read from PC */
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor CURSOR FOR
SELECT model, price, speed FROM PC;
EXEC SQL OPEN pcCursor;
/* ask user for target price and read the answer into variable
targetPrice */
/* Initially, the first PC is the closest to the target price.
If PC is empty, we cannot answer the question, and so abort. */
EXEC SQL FETCH FROM pcCursor INTO :modelOfClosest, :priceOfClosest,
:speedOfClosest;
if(NO_MORE_TUPLES) /* print message and exit */ ;
while(1) {
EXEC SQL FETCH pcCursor INTO :tempModel, :tempPrice,
:tempSpeed;
if(NO_MORE_TUPLES) break;
if( /* tempPrice closer to targetPrice than is priceOfClosest */) {
modelOfClosest = tempModel;
priceOfClosest = tempPrice;
speedOfClosest = tempSpeed;
}
}
/* Now, modelOfClosest is the model whose price is closest to
target. We must get its manufacturer with a single-row select */
EXEC SQL SELECT maker
INTO :manf
FROM Product
WHERE model = :modelOfClosest;
printf("manf = %s, model = %d, speed = %d\n", :manf, :modelOfClosest,
:speedOfClosest);
EXEC SQL CLOSE CURSOR pcCursor;
}
Again, the macro NO_MORE_TUPLES is used to test for the end of the relation.
void lowerPrices() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor INSENSITIVE CURSOR FOR PC;
EXEC SQL OPEN pcCursor;
while(1) {
EXEC SQL FETCH FROM pcCursor;
if(NO_MORE_TUPLES) break;
EXEC SQL UPDATE PC
SET price = price - 100
WHERE CURRENT OF execCursor;
}
EXEC SQL CLOSE pcCursor;
}
void twoMoreExpensive() {
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6], cd[5], cd1[5];
int model, model1, speed, speed1, ram, ram1, price, price1;
float hd, hd1; /* we use the variables cd1, model1, etc, to
read the tuple 2 positions later, to see if the speed has
not changed (in which case there are at least two PC's
with the same speed and at least as high a price) */
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE pcCursor SCROLL CURSOR FOR
SELECT * FROM PC ORDER BY speed, price;
EXEC SQL OPEN pcCursor;
while(1) {
EXEC SQL FETCH NEXT FROM pcCursor INTO :model, :speed, :ram,
:hd, :cd, :price;
if(NO_MORE_TUPLES) break;
EXEC SQL FETCH RELATIVE +2 FROM pcCursor INTO :model1, :speed1, :ram1,
:hd1, :cd1, :price1;
if(NO_MORE_TUPLES) break;
if(speed1 == speed) /* print the tuple :model, :speed, etc. */ ;
EXEC SQL FETCH RELATIVE -2 FROM pcCursor INTO :model1, :speed1, :ram1,
:hd1, :cd1, :price1;
/* line above just to reset the cursor to where it was */
}
EXEC SQL CLOSE pcCursor;
CREATE FUNCTION PresNetWorth(IN studioName CHAR[15])
DECLARE presNetWorth INT;
BEGIN
SELECT netWorth
INTO presNetWorth
FROM Studio, MovieExec
WHERE Studio.name = studioName AND presC# = cert#;
RETURN(presNetWorth);
END;
CREATE FUNCTION status(IN person, IN addr)
DECLARE isStar INT;
DECLARE isExec INT;
BEGIN
SELECT COUNT(*)
INTO isStar
FROM MovieStar
WHERE MovieStar.name = person AND MovieStar.address = addr;
SELECT COUNT(*)
INTO isExec
FROM MovieExec
WHERE MovieExec.name = person AND MovieExec.address = addr;
IF isStar + isExec = 0 THEN RETURN(4)
ELSE RETURN(isStar + 2*isExec)
END IF;
END;
CREATE PROCEDURE twoLongest(
IN studio CHAR(15),
OUT longest VARCHAR(255),
OUT second VARCHAR(255)
)
DECLARE longestLg INT;
DECLARE secondLg INT;
DECLARE t VARCHAR(255);
DECLARE l INT;
DECLARE Not_Found CONDITION FOR SQLSTATE = '02000';
DECLARE MovieCursor CURSOR FOR
SELECT title, length FROM Movie WHERE studioName = studio;
BEGIN
SET longest = NULL;
SET second = NULL;
SET longestLg = -1;
SET secondLg = -1;
OPEN MovieCursor;
mainLoop: LOOP
FETCH MovieCursor INTO t, l;
IF Not_Found THEN LEAVE mainLoop END IF;
IF l > longestLg THEN
SET secondLg = longestLg;
SET second = longest;
SET longestLg = l;
SET longest = t;
ELSIF l > secongLg THEN
SET secondLg = l;
SET second = t;
END IF;
END LOOP;
CLOSE MovieCursor;
END;
In explanation, as we run through movies, we need to remember not only the titles of the two longest movies seen so far, but their lengths. That way, when we see a new title and length, fetched into the pair of local variables (t, l), we can compare the length l with the two longest so far. The body of the loop first asks if l is longer than the longest; if so, the old longest becomes second, and the current movie becomes longest. If the current movie is not longest, then we next ask if it is longer than the second longest, and we replace the latter, if so.
Since we need to take the first model as ``closest'' regardless of its price, we use -1 as a value of the square of the price differences to indicate that there is no closest model yet selected.
CREATE FUNCTION closestMatchPC(IN targetPrice INT)
DECLARE closestModel INT;
DECLARE diffSq INT;
DECLARE currSq INT;
DECLARE m INT;
DECLARE p INT;
DECLARE Not_Found CONDITION FOR SQLSTATE '02000';
DECLARE PCCursor CURSOR FOR
SELECT model, price FROM PC;
BEGIN
SET closestModel = -1;
SET diffSq = -1;
OPEN PCCursor;
mainLoop: LOOP
FETCH PCCursor INTO m, p;
IF Not_Found THEN LEAVE mainLoop END IF;
SET currSq = (p - targetPrice)*(p - targetPrice);
IF diffSq = -1 OR diffSq > currSq
THEN BEGIN
SET closestModel = m;
SET diffSq = currSq;
END IF;
END LOOP;
CLOSE PDCursor;
RETURN(closestModel);
END;
#include sqlcli.h
int closestMatchPC(int targetPrice) {
int diffSq, currSq, closestModel;
SQLHENV myEnv;
SQLHDBC myCon;
SQLHSTMT pcStat;
SQLINTEGER m, p, mInfo, pInfo;
diffSq = closestModel = -1;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv);
SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon);
SQLAllocHandle(SQL_HANDLE_STMT, myCon, &pcStat);
SQLPrepare(pcStat, "SELECT model, price FROM PC", SQL_NTS);
SQLExecute(pcStat);
SQLBindCol(pcStat, 1, SQL_INTEGER, &m, size(m), &mInfo);
SQLBindCol(pcStat, 2, SQL_INTEGER, &p, size(p), &pInfo);
while(SQLFetch(pcStat) != SQL_NO_DATA) {
currSq = (p - targetPrice)*(p - targetPrice);
if(diffSq == -1 || diffSq > currSq) {
diffSq = currSq;
closestModel = m;
}
}
return(m);
}
#include sqlcli.h
void lowerPrices() {
SQLHENV myEnv;
SQLHDBC myCon;
SQLHSTMT pcStat;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnv);
SQLAllocHandle(SQL_HANDLE_DBC, myEnv, &myCon);
SQLAllocHandle(SQL_HANDLE_STMT, myCon, &pcStat);
SQLPrepare(pcStat, "UPDATE PC SET price = price - 100", SQL_NTS);
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(
"SELECT model, price FROM PC"
);
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("UPDATE PC SET price = price - 100");
}
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 LEVEL 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 READ UNCOMMITTED;
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.