数据库系统基础教程第九章答案.doc

9.3.1

a)

In the following, we use macro NOT_FOUND as defined in the section.

void closestMatchPC() {

EXEC SQL BEGIN DECLARE SECTION;

char manf, SQLSTATE[6];
int targetPrice, /* holds price given by user */
float tempSpeed, speedOfClosest;
char tempModel[4], modelOfClosest[4];
int tempPrice, priceOfClosest;
/* for tuple just read from PC & closest price found so far */

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(NOT_FOUND) /* print message and exit */ ;

while(1) {
EXEC SQL FETCH pcCursor INTO :tempModel, :tempPrice,
:tempSpeed;
if(NOT_FOUND) 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;
}

b)
void acceptableLaptop() {

EXEC SQL BEGIN DECLARE SECTION;

int minRam, minHd, minScreen; /* given by user */
float minSpeed;
char model[4], maker,
float speed;
int ram, hd, screen, price;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE query1 FROM
‘SELECT model, speed, ram, hd, screen, price, maker
FROM Laptop l, Product p
WHERE speed >= ? AND
ram >= ? AND
hd >= ? AND
screen >= ? AND
l.model = p.model’

EXEC SQL DECLARE cursor1 CURSOR FOR query1;

/* ask user for minimum speed, ram, hd size, and screen size */

EXEC SQL OPEN cursor1 USING :minSpeed, :minRam, :minHd, :minScreen;

while(!NOT_FOUND) {

EXEC SQL FETCH cursor1 INTO
:model, :speed, :ram, :hd, :screen, :price, :maker;

if(FOUND)
{
printf("maker:%s, model:%d, \n
speed:%.2f, ram:%d, hd:%d, screen:%d, price:%d\n",
maker, model, speed, ram, hd, screen, price);

}
}

EXEC SQL CLOSE CURSOR cursor1;
}

c)

void productsByMaker() {

EXEC SQL BEGIN DECLARE SECTION;

char maker, model[4], type[10], color[6];
float speed;
int ram, hd, screen, price;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE query1 FROM
‘SELECT * FROM PC
WHERE model IN (SELECT model FROM Product
WHERE maker = ? AND
type = ‘pc’);
EXEC SQL PREPARE query2 FROM
‘SELECT * FROM Laptop
WHERE model IN (SELECT model FROM Product
WHERE maker = ? AND
type = ‘laptop’);
EXEC SQL PREPARE query3 FROM
‘SELECT * FROM Printer
WHERE model IN (SELECT model FROM Product
WHERE maker = ? AND
type = ‘printer’);

EXEC SQL DECLARE cursor1 CURSOR FOR query1;
EXEC SQL DECLARE cursor2 CURSOR FOR query2;
EXEC SQL DECLARE cursor3 CURSOR FOR query3;

/* ask user for manufacturer */
Printf(“maker:%s\n”, maker);

/* get PCs made by the manufacturer */
EXEC SQL OPEN cursor1 USING :maker;

Printf(“product type: PC\n”);

while(!NOT_FOUND) {

EXEC SQL FETCH cursor1 INTO
:model, :speed, :ram, :hd, :price;

if(FOUND)
{
printf("model:%d,speed:%.2f, ram:%d, hd:%d, price:%d\n”,
model, speed, ram, hd, price);

}
}

/* get Laptops made by the manufacturer */
EXEC SQL OPEN cursor2 USING :maker;

Printf(“product type: Laptop\n”);

while(!NOT_FOUND) {

EXEC SQL FETCH cursor2 INTO
:model, :speed, :ram, :hd, :screen, :price;

if(FOUND)
{
printf("model:%d, speed:%.2f, ram:%d, hd:%d, screen:%d,
price:%d\n", model, speed, ram, hd, screen, price);

}
}

/* get Printers made by the manufacturer */
EXEC SQL OPEN cursor3 USING :maker;

Printf(“product type: Printer\n”);

while(!NOT_FOUND) {

EXEC SQL FETCH cursor3 INTO
:model, :color, :type, :price;

if(FOUND)
{
printf("model:%d, color:%s, type:%s, price:%d\n",
model, color, type, price);

}
}

EXEC SQL CLOSE CURSOR cursor1;
EXEC SQL CLOSE CURSOR cursor2;
EXEC SQL CLOSE CURSOR cursor3;
}

d)

void withinBudget() {

EXEC SQL BEGIN DECLARE SECTION;

int total_budget, rest_budget, pc_price, printer_price;
char pc_model[4], printer_model[4], color[6];
float min_speed;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE query1 FROM
‘SELECT model, price FROM PC
WHERE speed >= ? AND price <= ?
ORDER BY price’;

EXEC SQL PREPARE query2 FROM
‘SELECT model, price FROM Printer
WHERE price <= ? AND color = ?
ORDER BY price’;

EXEC SQL DECLARE cursor1 CURSOR FOR query1;
EXEC SQL DECLARE cursor2 CURSOR FOR query2;

/* ask user for budget & the minimum speed of pc */

/* get the cheapest PC of the minimum speed */
EXEC SQL OPEN cursor1 USING :min_speed, :total_budget;

EXEC SQL FETCH cursor1 INTO :pc_model, :pc_price;

if (NOT_FOUND)
Printf(“no pc found within the budget\n”);
else
{
Printf(“pc model: %s\n”, pc_model);
}

/* get Printer within the budget */
rest_budget = total_budget – pc_price;
color = “true”;

EXEC SQL OPEN cursor2 USING :rest_budget, :color;

EXEC SQL FETCH cursor2 INTO :printer_model;

if(NOT_FOUND) {
EXEC SQL CLOSE CURSOR cursor2;
color = “false”;
EXEC SQL OPEN cursor2 USING :rest_budget, :color;

if(NOT_FOUND)
printf(“no printer found within the budget\n”);
else {
EXEC SQL FETCH cursor2 INTO :printer_model;
printf(“printer model: %s\n”, printer_model);
}
}
else {
printf(“printer model: %s\n”, printer_model);
}

EXEC SQL CLOSE CURSOR cursor1;
EXEC SQL CLOSE CURSOR cursor2;
}

e)

void newPCproduct() {

EXEC SQL BEGIN DECLARE SECTION;

char pmaker, pmodel[4], ptype[6];
float pspeed;
int pram, phd, pscreen, pprice;
int pcount;

EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE stmt1 FROM
‘SELECT COUNT(*) INTO :count
FROM PC
WHERE MODEL = ?;

EXEC SQL PREPARE stmt2 FROM
‘INSERT INTO Product VALUES(?, ?, ?)’;

EXEC SQL PREPARE stmt3 FROM
‘INSERT INTO PC VALUES(?, ?, ?, ?, ?)’;

/* ask user for manufacturer, model, speed, RAM, hard-disk,
& price of a new PC */

EXEC SQL EXECUTE stmt1 USING :pmodel;

IF (count > 0)
Printf(“Warnning: The PC model already exists\n”);
ELSE
{
EXEC SQL EXECUTE stmt2 USING :pmaker, :pmodel, :ptype;

EXEC SQL EXECUTE stmt3 UINGNG :pmodel, :pspeed, :pram,
:phd, :pprice
}

}

9.3.2

a)

void largestFirepower() {

EXEC SQL BEGIN DECLARE SECTION;

char cclass[20], maxFirepowerClass[20];
int cnumGuns, cbore;
float firepower, maxFirepower;

EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT class, numGuns, bore FROM Classes;

EXEC SQL OPEN cursor1;

EXEC SQL FETCH FROM cursor1 INTO :cclass, :cnumGuns, :cbore;

if(NOT_FOUND) /* print message and exit */ ;

maxFirepower = cnumGuns * (power (cbore, 3));
strcpy(maxFirepowerClass, cclass);

while(1) {
EXEC SQL FETCH cursor1 INTO :cclass, :cnumGuns, :cbore;

if(NOT_FOUND) break;

firepower = cnumGuns * (power (cbore, 3));

if( firepower > maxFirepower )
{
maxFirepower = firepower;
strcpy(maxFirepowerClass, cclass);
}
}

printf("Class of maximum firepower :%s\n", maxFirepowerClass);

EXEC SQL CLOSE CURSOR cursor1;
}

b)

void getCountry() {

EXEC SQL BEGIN DECLARE SECTION;

char ibattle[20], iresult[10], ocountry[20];
char stmt1[200], stmt2[200];

EXEC SQL END DECLARE SECTION;

strcpy(stmt1, “SELECT COUNTRY FROM Classes C
WHERE C.class IN (
SELECT S.class FROM Ships S
WHERE S.name IN (
SELECT ship FROM Outcomes
WHERE battle = ?))” );

Strcpy(stm2, “SELECT country FROM Classes
WHERE class = ( SELECT MAX(COUNT(class))
FROM Ships s, Outcomes o
WHERE o.name = s.ship AND
s.result = ‘?’)” );

EXEC SQL PREPARE query1 FROM stmt1;
EXEC SQL PREPARE query2 FROM stmt2;

EXEC SQL DECLARE cursor1 CURSOR FOR query1;
EXEC SQL DECLARE cursor2 CURSOR FOR query2;

/* ask user for battle */

/* get countries of the ships involved in the battle */
EXEC SQL OPEN cursor1 USING :ibattle;

while(!NOT_FOUND) {

EXEC SQL FETCH cursor1 INTO :ocountry;

if(FOUND)
printf("contry:%s\n”, ocoutry);
}

EXEC SQL CLOSE CURSOR cursor1;

/* get the country with the most ships sunk */
strcpy(iresult, “sunk”);

EXEC SQL OPEN cursor2 USING :iresult;

/* loop for the case there’s the same max# of ships sunk */

While(!NOT_FOUND) {

EXEC SQL FETCH cursor2 INTO :ocountry;

If(FOUND)
Printf(“country with the most ships sunk: %s, ocountry);

}

/* get the country with the most ships damaged */
strcpy(iresult, “damaged”);

EXEC SQL OPEN cursor2 USING :iresult;

/* loop for the case there’s the same max# of ships damaged */

While(!NOT_FOUND) {

EXEC SQL FETCH cursor2 INTO :ocountry;

If(FOUND)
Printf(“country with the most ships damaged: %s, ocountry);

}

}

c)

void addShips() {

EXEC SQL BEGIN DECLARE SECTION;

char iclass[20], itype[3], icontry[20], iship[20];
int inumGuns, ibore, idisplacement, ilaunched;
char stmt1[100], stmt2[100];

EXEC SQL END DECLARE SECTION;

strcpy(stmt1, “INSERT INTO Classes VALUES (?, ?, ?, ?, ?, ?)”);

strcpy(stmt2, “INSERT INTO Ships VALUES (?, ?, ?)”);

/* ask user for a class and other info for Classes table */

EXEC SQL EXECUTE IMMEDATE :stmt1
USING :iclass, :itype, :icontry,
:inumGuns, :ibore, :idisplacement;

/* ask user for a ship and launched */

WHILE(there_is_input)
{
EXEC SQL EXECUTE IMMEDATE :stmt2
USING :iship, :iclass, ilaunched;

/* ask user for a ship and launched */
}

}

d)

void findError() {

EXEC SQL BEGIN DECLARE SECTION;

char bname[20], bdate[8], newbdate[8];
char sname[20], lyear[4], newlyear[4];
char stmt1[100], stmt2[100];

EXEC SQL END DECLARE SECTION;

strcpy(stmt1, “UPDATE Battles SET date = ? WHERE name = ?”);
strcpy(stmt2, “UPDATE Ships SET launched = ? WHERE name = ?”);

EXEC SQL DECLARE C1 CURSOR FOR
Select b.name, b.date, s.name, s.launched
FROM Battles b, Outcomes o, Ships s
WHERE b.name = o.battle AND
o.ship = s.name AND
YEAR(b.date) < s.launched;

EXEC SQL OPEN C1;

while(!NOT_FOUND) {

EXEC SQL FETCH C1 INTO :bname, :bdate, :sname, :lyear;

/* prompt user and ask if a change is needed */

if(change_battle)
{
/* get a new battle date to newbdate */
EXEC SQL EXECUTE IMMEDATE :stmt1
USING :bname, :newbdate;
}

if(change_ship)
{
/* get a new launched year to newlyear */
EXEC SQL EXECUTE IMMEDATE :stmt2
USING :sname, :newlyear;
}
}
}
9.4.1

a)

CREATE FUNCTION PresNetWorth(studioName CHAR[15]) RETURNS INTEGER

DECLARE presNetWorth INT;

BEGIN
SELECT netWorth
INTO presNetWorth
FROM Studio, MovieExec
WHERE Studio.name = studioName AND presC# = cert#;
RETURN(presNetWorth);
END;

b)

CREATE FUNCTION status(person CHAR(30), addr CHAR(255)) RETURNS INTEGER

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;

c)

CREATE PROCEDURE twoLongest(
IN studio CHAR(15),
OUT longest VARCHAR(255),
OUT second VARCHAR(255)
)

DECLARE t VARCHAR(255);
DECLARE i INT;
DECLARE Not_Found CONDITION FOR SQLSTATE = '02000';
DECLARE MovieCursor CURSOR FOR
SELECT title FROM Movies WHERE studioName = studio
ORDER BY length DESC;

BEGIN
SET longest = NULL;
SET second = NULL;
OPEN MovieCursor;
SET i = 0;
mainLoop: WHILE (i < 2) DO
FETCH MovieCursor INTO t;
IF Not_Found THEN LEAVE mainLoop END IF;
SET i = i + 1;
END WHILE;
CLOSE MovieCursor;
END;

d)

CREATE PROCEDU