Solve Exercise 6.6.3

306 CHAPTER 6. THE DATABASE LANGUAGE SQL then a sest that is available on the first query at Step (1) will remain available at subeequent queries. However, suppoes some new tuples enter the relation Flights. For exaznple, the airline may have switched the flight to a larger plane, creating some new taples that weren't shere before. Then under repeatable read isolation, a subenquent query for available coats may also retriere the new sests. Figure 6.17 simmarizes the differences between the four SQL isolation levels. Figure 6.17: Propertives of SQL isalation levels 6.6.7 Exercises for Section 6.6 Exercise 6.6.1 This and the next exercises inwolve certain programs that opcrate on the two relations Fraduct (nalcor, rodel, type) FC(aoda1, apeed, ras, hd, price) forn aar running PC exrercise. Sketch the following programs, inclading SQL statements and work done in a conventional language. Do not forget to issue BECIN TRAMSCTION, COMMIT, and ROL.LBACK statements at the proper times and to tell tbe system your transactions are read-oaly if they are. a) Given a speed and amount of RAM (is arguments of the function), look up the PC's with that speed and RAM, printing the nodel number and price of eacd. b) Given a model number, delete the tuple for that model from both PC and Froduct. c) Given a mudel number, decrease the price of that model PC by 8100 . d) Given a maker, model number, processor speed, RAM size, hard-disk sice, and price, check that there is no product with that model. If there is such a model, print an error mesange for the user. If no such model existed in the database, cater the information about that model into the PC and Precuet, tables. 6.7. SUMMARY OF CHAPTER 6 307 ! Exercise 6.6.2 : Fur exch of the programs of Exercise 6.6.1, discuss the atonicity problems, if any, that could occur shoold the system crash in the middle of an execution of the program. ! Exercise 6.6.3 : Suppose we execule as a transaction T one of the four programs of Exercise 6.6.1, wile other transactions that are executions of the same or a different one of the four programs may also be executing at sbous the same time. What behaviora of transaction T may be oberved if al the transactione run with isolation level READ ONCOMHITTED that would not be poseible if they all ran with isolation level SER.IALIZABLE? Consider separately the ease that. T is any of the programs (s) through (d) of Exereise 6.6.1. 11 Exercise 6.6.4 : Suppose we bave a tramsaction T that is a function which runs "farever," and at each hour checks wbenter there is a PC that has a speed of 3.5 ar more and sells for inder $1000. If it finds one, it ptints the information and terninaves. During this time, other transactions that are execations of oee of the four proprams described in Exercise 6.6.1 may run. For ehct of the four isolstion levels - serialiable, repcatable read, read committed, and read unecmmitied - tell what the effect on T of runaigg at this isolation lenel is. 6.7 Summary of Chapter 6 - SQL Tbe language SQL is the peindpal query language for relational databse systems. The most recent full standerd is called SQI-99 or SQL3. Commercial systems penerally vury from this standard. - Select-From-Where Queries: The maet commue form of SQL query has the form select-from-where. It allows us to take the prodnct of several reiations (the FROM clause), apply a condition to the tuples of the result (the BIIERE clause), and produce desired components (tbe SELBCT clauBe). - Sobpueries: Select-from-where ques can also be usod as subquseries withis a VHERE clause or FRDM claase of another query. The operators EIISTS, IN, ALL, and AKY may be used to express boobosn-valuod onnditions ahout the relations that are the result of a subquery in a wHERE clnses. - Set Opentions on Rolations: We can take the union, intersection, or difference of relations by connecting the relations, or connecting querries defining the relations, with the bywords TEIOM, INTERSECT, and XCEPT