数据库系统基础教程习题答案（AFirstCoursesinDatabaseSystemsAnswers）_数据库系统基础教程第三版课后答案资源-CSDN文库

共9个文件

html：9个

93 浏览量 2016-10-08 11:58:04 上传评论 1 收藏 31KB ZIP 举报

资源推荐

资源详情

资源评论

收起资源包目录

A First Courses in Database Systems Answers数据库系统基础教程习题答案.zip （9个子文件）

A First Courses in Database Systems Answers数据库系统基础教程习题答案

A First Courses in Database Systems Chapter 6.html 12KB

A First Courses in Database Systems Chapter 3.html 14KB

A First Courses in Database Systems Chapter 9.html 7KB

A First Courses in Database Systems Chapter 5.html 12KB

A First Courses in Database Systems Chapter 7.html 6KB

A First Courses in Database Systems Chapter 8.html 15KB

A First Courses in Database Systems Chapter 2.html 5KB

A First Courses in Database Systems Chapter 4.html 12KB

A First Courses in Database Systems Chapter 10.html 8KB

<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>Database Systems: The Complete Book: Solutions for Chapter 8</title> </head> <body bgcolor="E0F7F0"> <center><table> <tbody><tr><td><img src="./Database Systems_ The Complete Book_ Solutions for Chapter 8_files/logo.dbg.gif"> </td><td><table><tbody><tr><td align="MIDDLE"><font size="6">Database Systems: The Complete Book</font> </td></tr><tr><td align="MIDDLE"><font size="6">Solutions for Chapter 8</font></td></tr></tbody></table> </td></tr></tbody></table></center> Revised 11/4/01. <p> <a name="top"></a> </p><p> <a href="http://infolab.stanford.edu/~ullman/dscbsols/sol8.html#sol81">Solutions for Section 8.1</a><br> <a href="http://infolab.stanford.edu/~ullman/dscbsols/sol8.html#sol82">Solutions for Section 8.2</a><br> <a href="http://infolab.stanford.edu/~ullman/dscbsols/sol8.html#sol84">Solutions for Section 8.4</a><br> <a href="http://infolab.stanford.edu/~ullman/dscbsols/sol8.html#sol85">Solutions for Section 8.5</a><br> <a href="http://infolab.stanford.edu/~ullman/dscbsols/sol8.html#sol86">Solutions for Section 8.6</a><br> <a href="http://infolab.stanford.edu/~ullman/dscbsols/sol8.html#sol87">Solutions for Section 8.7</a><br> <a name="sol81"></a> </p><h2>Solutions for Section 8.1</h2> <h3>Exercise 8.1.1(a)</h3> In the following, we use macro <tt>NO_MORE_TUPLES</tt> as defined in the section. <pre>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; } </pre> <h3>Exercise 8.1.1(f)</h3> To make sure that we don't change the price of ``new'' PC's, we have only to make the cursor insensitive.<p> Again, the macro <tt>NO_MORE_TUPLES</tt> is used to test for the end of the relation.</p><p> </p><pre>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; } </pre> <h3>Exercise 8.1.3</h3> <pre>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; </pre> <p> <a href="http://infolab.stanford.edu/~ullman/dscbsols/sol8.html#top">Return to Top</a> </p><p> <a name="sol82"></a> </p><h2>Solutions for Section 8.2</h2> <h3>Exercise 8.2.1(a)</h3> <pre>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; </pre> <h3>Exercise 8.2.1(b)</h3> <pre>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; </pre> <h3>Exercise 8.2.1(c)</h3> <pre>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; </pre> <p> 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 <i>(t, l)</i>, we can compare the length <i>l</i> with the two longest so far. The body of the loop first asks if <i>l</i> 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. </p><h3>Exercise 8.2.2(a)</h3> One can actually write a tricky SQL query that will retrieve the model with the closest price, use this query in a single-row select, and return the selected model number. However, we can also scan the PC's and keep track of how close we have co

评论收藏

内容反馈