习题答案

1. 习题答案 数据库系统及应用 2014年

2. Exercise 7 • (version 7)9.2 定义一个视图，这一视图包括了“供应商和他所提供的零件不在同一地点”的供应商号和零件号。 CREATE VIEW V92 AS SELECT S.S#, P.P# FROM S, P, SP WHERE S.CITY <> P.CITY AND S.S# = SP.S# AND P.P# = SP.P#; • 9.5 在供应商-零件-工程数据库上定义视图，使它包含所有由S1提供零件而且使用零件P1的工程（只要求工程编号和城市属性）。 CREATE VIEW V95 AS SELECT J.J#, J.CITY FROM J, SPJ WHERE J.J# = SPJ.J# AND SPJ.S# = ‘S1’ AND SPJ.P# = ‘P1’;

3. Exercise 7 • 9.6 对于下面的视图定义，请给出下列语句实施替换过程后的转化形式： VAR HEAVYWEIGHT VIEW ((P RENAME WEIGHT AS WT, COLOR AS COL) WHERE WT > WEIGHT(14.0)) { P#, WT, COL}; a. RA := HEAVYWEIGHT WHERE COL = COLOR (‘Green’); SELECT P.P#, P.WEIGHT AS WT, P.COLOR AS COL FROM P WHERE P.WT > 14.0 AND P.COL = ‘Green’; b. RB := ( EXTEND HEAVYWEIGHT ADD WT + WEIGHT (5.3) AS WTP) { P#, WTP}; SELECT P.P#, P.WEIGHT + 5.3 AS WTP FROM P WHERE P.WEIGHT > 14.0;

4. Exercise 7 c. UPDATE HEAVYWEIGHT WHERE WT = WEIGHT (18.0) COL := ‘White’; UPDATE P SET COLOR = ‘White’ WHERE ( WEIGHT > 14.0) AND WEIGHT = 18.0; d. DELETE HEAVYWEIGHT WHERE WT < WEIGHT (10.0); DELETE FROM P WHERE ( WEIGHT > 14.0) AND WEIGHT < 10.0 e. INSERT INTO HEAVYWEIGHT RELATION { TUPLE { P# P#(‘P99’), WT WEIGHT(12.0), COL COLOR(‘Purple’)}}; INSERT INTO P ( P#, WEIGHT, COLOR ) VALUES ( ‘P99’, 12.0, ‘Purple’ ) CHECK WEIGHT > 14.0

5. Exercise 7 • 16.1 假定关系变量STATS与16.4节中的定义相同，如下所示： STATS {NAME, SEX, CHILDREN, OCCUPATION, SALARY, TAX, AUDITS} PRIMARY KEY{NAME} 采用16.2节中的语言定义下面给定的安全性约束： a. 用户Ford对整个关系变量具有RETRIEVE权限； AUTHORITY AAA GRANT RETRIEVE ON STATS TO Ford; b. 用户Smith对整个关系变量具有INSERT和DELETE权限； AUTHORITY BBB GRANT INSERT, DELETE ON STATS TO Smith; c. 每个用户只对自己的元组具有RETRIEVE权限； AUTHORITY CCC GRANT RETRIEVE ON STATS WHEN USER() = NAME TO ALL; 这里假定用户把自己的名字作为ID，无参内置运算符USER()是指当前用户的ID。

6. Exercise 7 d. 用户Nash对整个关系变量具有RETRIEVE权限，但只对SALARY和TAX具有UPDATE权限； AUTHORITY DDD GRANT RETRIEVE, UPDATE(SALARY, TAX) ON STATS TO Nash; e. 用户Todd只对NAME、SALARY和TAX属性具有RETRIEVE权限； AUTHORITY EEE GRANT RETRIEVE(NAME,SALARY,TAX) ON STATS TO Todd; f. 用户Ward的RETRIEVE权限与Todd一样，而只对SALARY和TAX属性具有UPDATE权限； AUTHORITY FFF GRANT RETRIEVE(NAME,SALARY,TAX), UPDATE(SALARY,TAX) ON STATS TO Ward;

7. Exercise 7 g. 用户Pope具有对职业为preacher的元组的所有权限； VAR PREACHERS VIEW STATS WHERE OCCUPATION = 'Preacher'; AUTHORITY GGG GRANT ALL ON PREACHERS TO Pope;

8. Exercise 7 h. 用户Jones具有对职业为非特殊性质的元组的DELETE权限，这里非特殊性质的职业为人数超过10个的职业； VAR NONSPECIALIST VIEW WITH(STATS RENAME OCCUPATION AS X) AS T1, (EXTEND STATS ADD COUNT (T1 WEHRE X = OCCUPATION) AS Y) AS T2, (T2 WHERE Y > 10) AS T3: T3 {ALL BUT Y} AUTHORITY HHH GRANT DELETE ON NONSPECIALIST TO Jones;

9. Exercise 7 i.用户King具有对每个职业最高和最低工资的RETRIEVE权限。 VAR JOBMAXMIN VIEW WITH(STATS RENAME OCCUPATION AS X) AS T1, (EXTEND STATS ADD MAX(T1 WHERE X = OCCUPATION, SALARY) AS MAXSAL, MIN(T1 WHERE X = OCCUPATION, SALARY) AS MINSAL) AS T2: T2{OCCUPATION, MAXSAL, MINSAL} AUTHORITY III GRANT RETRIEVE ON JOBMAXMIN TO King;

10. Exercise 7 • 10.8 关系变量{A,B,C,D,E,F,G}满足下列函数依赖： A->B，BC->DE， AEF->G。计算{A, C}关于这个函数依赖集的闭包{A,C}+，该函数依赖集是否蕴含函数依赖ACF->DG？ 答案：{A,C}+={A,B,C,D,E}，是 证明： ∵ A->B ∴AC->BC 又BC->DE ∴AC->DE ∴AC->E 又AC->A ∴ACF->AEF 又AEF->G ∴ACF->DG • 10.11 下面两个函数依赖集等价吗？1) A->B,AB->C,D->AC,D->E; 2)A->BC,D->AE; 这两个函数依赖集等价。 只要说明由1）能推出2）中的两个式子，而2）能推出1）的式子即可。

11. Exercise 8 • Version 7 11.4 订货系统的数据库设计，参考11.3练习答案 • 语义假设 • 任何两个顾客的收货地址都不相同 • 每一个订单都有一个唯一的订单号码 • 每个订单的订单细则在这个订单里有一个唯一的编号 • 建立初始关系变量结构 CUST0 {CUST#, SHIPTO0{ADDRESS}, BAL, CREDLIM, DISCOUNT} ORDER0 {ORD#, CUST#, ADDRESS, DATE, ORDLINE0{LINE#, ITEM#, QTYORD, QTYOUT}} ITEM0 {ITEM#, IP0{PLANT#, QTYOH, DANGER}, DESC}

12. Exercise 8 • 消除关系值属性 CUST1 {CUST#, BAL, CREDLIM, DISCOUNT} PRIMARY KEY {CUST#} SHIPTO1 {CUST#, ADDRESS} PRIMARY KEY {CUST#, ADDRESS} ORDER1 {ORD#, CUST#, ADDRESS, DATE} PRIMARY KEY {ORD#} ORDLINE1 {ORD#, LINE#, ITEM#, QTYORD, QTYOUT} PRIMARY KEY {ORD#, LINE#} ITEM1 {ITEM#, DESC} PRIMARY KEY {ITEM#} IP1 {ITEM#, PLANT#, QTYOH, DANGER} PRIMARY KEY {ITEM#, PLANT#}

13. Exercise 8 • 归约成3NF CUST {CUST#, BAL, CREDLIM, DISCOUNT} PRIMARY KEY {CUST#} SHIPTO {ADDRESS, CUST#} PRIMARY KEY {ADDRESS} ORDER {ORD#, ADDRESS, DATE} PRIMARY KEY {ORD#} ORDLINE {ORD#, LINE#, ITEM#, QTYORD, QTYOUT} PRIMARY KEY {ORD#, LINE#} ITEM {ITEM#, DESC} PRIMARY KEY {ITEM#} IP {ITEM#, PLANT#, QTYOH, DANGER} PRIMARY KEY {ITEM#, PLANT#}

14. Exercise 8 • FD BAL CREDLIM CUST# ADDRESS DISCOUNT ORD# QTYORD DATE QTYOUT LINE# QTYOH DESC ITEM# PLANT# DANGER

15. Exercise 8 • Version 7 11.5 假设只有少量的顾客（<=1%）有多个发货地址，改进上题关系。 • 对于每一个顾客，指定一个合法的收货地址作为主地址，则对99%的顾客，该地址就是他的唯一地址，存放在关系变量CUST中，其它地址放在关系变量SECOND中。 • 关系变量CUST的定义如下： CUST {CUST#, ADDRESS, BAL, CREDLIM, DISCOUNT} PRIMARY KEY {CUST#} • 关系变量SHIPTO可以用下面的关系替代： SECOND {ADDRESS, CUST#} PRIMARY KEY {ADDRESS}

16. Exercise 9 • Version 7 12.3 有一个包含推销员、推销地和产品的数据库……对这些数据设计一组合适的关系变量。（具体过程见练习答案） • 关系变量 • REP{REP#, …} KEY {REP#} • AREA{AREA#, …} KEY {AREA#} • PRODUCT{PROD#, …} KEY {PROD#} • APR {AREA#, PROD#, REP#} KEY {AREA#, PROD#} • 约束条件 • REP# ->-> PROD# | AREA# • APR{AREA#, PROD#} = AREA{AREA#} TIMES PRODUCT{PROD#}

17. Exercise 9 • Version 7 12.4 修改11.3算法，使其产生4NF的关系变量。 对于练习11.3，将对函数依赖和BCNF的参照替换为类似的对多值依赖和4NF的参照。下面是将关系变量R分解成4NF关系变量集D的算法过程： 1) 初始化D为只包括R 2) 对D中每个非4NF关系变量T，执行步骤3，4 3) 设X->->Y为T的一个不满足4NF要求的多值函数依赖 4) 用T的两个投影，在X和Y上的投影及在除Y中属性之外的所有属性上的投影代替D中的T

18. Exercise 9 补充题1 Normalize R to 4NF Relation . • 由MVD中(class, section) ->-> (student, major, exam, year)|… 可有 R1(class, section, student, major, exam, year) R2(class, section, instructor, rank, salary, text, day, room) • 由MVD中的(class, section) ->-> (instructor, rank, salary) 将R2分解为 R21(class, section ,instructor, rank, salary) R22(class, section, text, day, room) • 由(class, section) ->-> text 分解R22 R221(class, section ,text) R222(class, section, day, room)

19. Exercise 9 • 由class ->-> text 分解R221 R2211(class, text) R2212(class, section) • 由(class, student, section) ->-> exam 分解R1 R11(class, section, student, exam) R12(class, section, student, major, year) • 由(class, section) ->-> instructor instructor -> (rank, salary) 分解R21 R211(class, section, instructor) R212(instructor, rank, salary)

20. Exercise 9 • 由student -> (major, year) 分解 R121(student, major, year) R122(class, section, student) 4NF关系 r1(student, major, year) r2(class, section, instructor) r3(instructor, rank, salary) r4(class, section, day, room) r5(class, text) r6(class, section, student, exam)

21. Exercise 10 • 13.6 画出11.3的公司员工数据库的E-R图，应用这个图导出一组适当的基本关系变量定义。 • 13.8 画出12.3的销售数据库的E-R图，应用这个图导出一组适当的基本关系变量定义。 参考第七版教材P309

22. Exercise 10 • 补充题1. ACID property 参见第七版教材P336 原子性（atomicity）：事务是原子的，要么都做，要么都不做。 一致性（consistency）：事务保证了数据库的一致性。事务将数据库从一个一致状态转变为另一个一致状态，但在事务内无须保证一致性。 隔离性（isolation）：事务相互隔离。也就是说，即使通常多个事务并发执行，任一事务的更新操作直到其成功提交对其他事务都是不可见的。 持久性（durability）：一旦事务成功提交，即使系统崩溃，其对数据库的更新也将永久有效。

23. Exercise 10 • 补充题2. Transaction state transfer condition Chapter 13 Transaction PPT15-17 Active: the initial state; the transaction stays in this state while it is executing Partially committed: after the final statement has been executed. Failed: after the discovery that normal execution can no longer proceed. Aborted: after the transaction has been rolled back and the database restored to its state prior to the start of the transaction Commit: after successful completion

24. Exercise 10 • 补充题3. Give two transaction examples 银行转账；火车站订票 需要详细说明

25. Exercise 11 • 14.4 下列情况下，恢复的含义分别是什么？ • Commit时将缓冲区的内容强制的写入数据库 • Commit之前缓冲区的内容不物理的写入数据库 答： • 系统故障时，无需做redo，因为都已经写入数据库中了 • 系统故障时，无需做undo，因为都没有写入数据库

26. Exercise 11 • 14.6 利用供应商和零件数据库，写一个SQL应用程序，按零件编号顺序查询并显示所有的零件，每十个记录重新开始一个新的事务，并且要求将第十个记录删除。 EXEC SQL DELARE CP CURSOR FOR SELECT P.P#, P.PNAME, P.COLOR, P.WEIGHT, P.CITY FROM P WHERE P.P# > previous_P# ORDER BY P# ; previous_P# := ’ ’ ; eof := false ; DO WHILE ( eof = false ) ; EXEC SQL OPEN CP ;

27. Exercise 11 • 14.6 DO count := 1 TO 10 ; EXEC SQL FETCH CP INTO : P#, PNAME, COLOR, WEIGHT, CITY ; IF SQLSTATE = ‘02000’ THEN DO ; EXEC SQL CLOSE CP ; EXEC SQL COMMIT ; eof:= true ; END DO ; ELSE print P#, PNAME, COLOR, WEIGHT, CITY ;

28. Exercise 11 • 14.6 END IF ; END DO ; EXEC SQL DELETE FROM P WHERE P.P#=:P#; EXEC SQL CLOSE CP ; EXEC SQL COMMIT ; previous_P# := :P# ; END DO ;

29. Exercise 11 • 14.6 possible log record <Ti, start> <Ti, P10, old_value, NULL> 或 <Ti, delete, D, value> <Ti, commit>

30. Exercise 11 • 14.6 Change the exercises to delete the every third part according the data on the book. EXEC SQL DELARE CP CURSOR FOR SELECT P.P#, P.PNAME, P.COLOR, P.WEIGHT, P.CITY FROM P WHERE P.P# > previous_P# ORDER BY P# ; previous_P# := ’ ’ ; eof := false ; no := 0 ; DO WHILE ( eof = false ) ; EXEC SQL OPEN CP ;

31. Exercise 11 • 14.6 DO count := 1 TO 10 ; EXEC SQL FETCH CP INTO : P#, PNAME, COLOR, WEIGHT, CITY ; IF SQLSTATE = ‘02000’ THEN DO ; EXEC SQL CLOSE CP ; EXEC SQL COMMIT ; eof := true ; END DO ;

32. Exercise 11 • 14.6 ELSE DO ; print P#, PNAME, COLOR, WEIGHT, CITY ; no := no + 1 ; IF no = 3 THEN DO ; no := 0 ; EXEC SQL DELETE FROM P WHERE P.P# = :P# ; END DO ; END IF ; END DO ; END IF ;

33. Exercise 11 • 14.6 END DO ; EXEC SQL CLOSE CP ; EXEC SQL COMMIT ; previous_P# := :P# ; END DO ;

34. Exercise 11 • （3）Two transaction T and U have log record as follows: <T,Start>,<T,A,10>,<U,Start>,<U,B,20>,<T,C,30>,<U,D,40>,<U,Commit>,<T,E,50>,<T,Commit> Describe the action of the recovery manager, if there is a crash and the last log record to appear on disk is: a) <U,Start> b) <U,Commit> c) <T,E,50> d) <T,Commit> 基于redo • 无操作 • Redo U，将B设为20，D设为40，并在日志中写<T, abort> • Redo U，将B设为20，D设为40，并在日志中写<T, abort> • Redo U，T。 基于undo a) Undo U,T b) Undo T c) Undo T d)无操作

35. Exercise 11 • （4）Consider cache buffering of disk pages in memory under the assumption that, to begin with, the cache buffer disk pages are empty, and that the maximum number of pages that can be held in cache is four. Assume in the history that follows that each of the data items A, B, C, D, E, and F accessed in the history lies on a distinct page. H=R1(A,1)R2(B,2)W1(A,3)R3(C,4)W2(B,5)C2W3(C,6)R4(D,7)R5(E,8)W5(E,9)R6(B,5)R6(A,3)R3(F,10)W3(F,11)W4(D,12) • (a)Name the first operation where an existing page in buffer must be dropped in order that another page can be read in. • (b)Pages in buffer are called dirty if they have been updated in buffer but not yet written back out to their place on disk. What are the dirty pages in buffer at the time of the operation named in (a). 如果事务提交后，其占用页面置为“空闲” (a) R6(B,5)；(b) A,C,E 如果事务提交后，其占用页面不被置为“空闲” (a)R5(E,8)；(b) A,C 说明：在R5(E,8)之前只有A,B,C执行了W操作修改了值，由于执行了C2，所以B的值被写回去了，所以A,C是脏的。

36. Exercise 12 • 15.3 (a) T1-T2-T3 A = 1；T1-T3-T2 A = 2；T2-T1-T3 A = 1； T2-T3-T1 A = 2；T3-T1-T2 A = 4；T3-T2-T1 A = 3； (b) 由事务的内部结构可知每个事务由Read和Write两个操作，而事务内部的操作顺序不能改变，故有6! / (2!*2!*2!) = 90种可能的调度。 (c)　是。只要找到与６个可能的串行调度中的结果相同即可。如调度R1-R2-R3-U3-U2-U1.当A初值不为0时，得不到正确结果。 (d)　是。例如：R1-R3-U1-U3-R2-U2是可串行的，但在T1、 T2、T3都遵守2PL时不能出现，因为会发生死锁。详细见课后答案。

37. Exercise 12 • 15.4 详见课后习题答案 • (2) 基于下面关系矩阵： If a lock cannot be granted, the requesting transaction is made to wait till all incompatible locks held by other transactions have been released. The lock is then granted. Lock_S(A){ if( xlock(A) == true ) then wait else slock(A) = true; }

38. Exercise 12 Lock_X(A){ if( xlock(A) == true || slock(A) == true ) then wait else xlock(A) = true; } Unlock(A){ Xlock(A) = false; Slock(A) = false; }

39. Exercise 12 • (3) a.可以，因为全是read操作； b.R1(x)在W2(x)之前,而R1(y)在W2(y)之后， 所以非冲突可串，其优先图： c.可串，不存在冲突。

40. Exercise 12 • (4) 1. a) S1(A);r1(A);S2(B);r2(B); S3(C);r3(C); X1(B);w1(B);U1(A);U1(B);X2(C);w2(C); U2(B);U2(C);X3(D);w3(D);U3(C);U3(D); b) 相当于串行调度：T3-T2-T1 2. a)S1(A);r1(A);S2(B);r2(B);S3(C);r3(C);X1(B);w1(B);U1(A);U1(B);X2(C);w2(C); U2(B);U2(C);X3(A);w3(A);U3(C);U3(A); b) 死锁 3. a) S1(A);r1(A); S2(B);r2(B); S3(C);r3(C);S1(B);r1(B);S2(C);r2(C);S3(D);r3(D); X1(C);w1(C);U1(A);U1(B);U1(C);X2(D);w2(D);U2(B);U2(C);U2(D);X3(E);w3(E); U3(C);U3(D);U3(E); b) 相当于串行调度：T3-T2-T1 注意：X锁和S锁一般要保持到事务结束（commit和rollback）

41. 主页：http://home.ustc.edu.cn/~qhuang/database.html