0911 数据库作业答疑

0911数据库作业答疑 2012年5月

第一次作业 • 1.2,1.3 (Version 7) 1.?(Version 8) 使用数据库系统的优点和缺点是什么？优点：数据共享减少冗余避免不一致提供事务支持保持完整性增强安全性平衡相互冲突的请求加强标准化缺点：折衷的安全性折衷的完整性需要额外的硬件执行开销非常大成功的操作至关重要系统可能很复杂

1.7 (Version 7) 1.6 (Version 8) • SELECT WINE, PRODUCER FROM CELLAR WHERE BIN# = 72; 检索结果：解释： Rafanelli是Zinfandel酒的生产商。

SELECT WINE, PRODUCER FROM CELLAR WHERE YEAR > 1996; 解释： Buena Vista是Chardonnay的生产商，且年份是在1996年之后（表中第一行）。

SELECT BIN#, WINE, YEAR FROM CELLAR WHERE READY < 1999; 解释：一些1996年生产的Chardonnay酒并且ready时间在1999年之前

SELECT BIN#, WINE, YEAR FROM CELLAR WHERE PRODUCER = ‘Robt. Mondavi’ AND BOTTLES > 6; 解释：一些Cab. Sauvignon酒，它们是1993年生产的，生产商是Robt. Mondavi，瓶数大于6。注意：不要再解释中加入查询结果或条件没有的信息。

2.4 列出DBMS的主要功能 答： • 数据定义，比如create database,createtable,createview,create index. • 数据操纵 ,比如select insert update delete • 优化和执行,比如内部对sql语句的执行会优化。 • 数据安全性和完整性，比如密码安全，还有数据表中记录值的完整性，主键，外键参照性 • 数据恢复和并发，比如突然断电，多用户访问同一记录时的处理。 • 监控器 • 数据字典，比如对数据库中各种对象（数据库，表等）的描述数据。 • 性能

2.5 区分数据的物理独立性和逻辑独立性。 答：物理独立性和逻辑独立性： • 物理独立性：用户和用户的应用程序能对于数据库物理结构（组织形式，存储方式）的改变而保持不变，物理结构改变，程序可以不变。 • 逻辑独立性：用户和用户的应用程序对于数据库逻辑结构的改变而保持不变比如有个程序对应于视图，若修改视图对应的表结构，修改相应视图即可。应用程序不用变化。

2.6 如何理解元数据？ 答：元数据即字典，是“数据的数据”，包括了对系统自身有用的各种对象的细节信息，是一组系统关系变量，它包括了关于对数据库有用的各种条目的细节信息（基本关系变量、视图、索引和用户，等等）。用户能够像访问自己的数据一样访问字典. 如何理解“数据的数据”，比如记录数据库系统中有哪些数据库，每个数据库的名字，每个数据库中有哪些表，每个表有哪些字段，可以查看mssqlserver中有个专门的数据库记录这些值。

第二次作业 • 3.2 描述供应商和零件数据库的字典变量TABLE 和 COLUMN的内容。 TABLE、COLUMN的自身关系包含或不包含均可， TABLE表中阴影部分的数据之所以不能给出精确值，是因为一个特定系统的字典必须包含许多系统特定的信息。分别看自身关系不包含和包含的情况。

TABLE: 注意：ROWCOUNT不要把列名给算上。

COLUMN:

3.3 在3.6节提到，字典是自描述的——也就是说，包括字典关系变量自身的条目。扩展图3.6，以包括关系变量TABLE和COLUMN的条目。 TABLE：

COLUMN:

第三次作业 • 补1：Create following tables and some index, based on your understanding. Movie(title, year, length, inColor, studioName, producerC#) CREATE TABLE Movie ( TITLE CHAR(20), YEAR NUMERIC(4), LENGTH NUMERIC(4), INCOLOR CHAR(10), STUDIONAME CHAR(15), PRODUCERC# CHAR(15), PRIMARY KEY(TITLE), FOREIGN KEY(STUDIO NAME) REFERENCES STUDIO(NAME)); FOREIGN KEY(PRODUCERC#) REFERENCES MOVIEEXEC(NAME)); CREATE INDEX I1 ON MOVIE (TITLE);

StarsIn(movieTitle, movieYear, strName) CREATE TABLE STARSIN (MOVIETITLE CHAR(20), MOVIEYEAR CHAR(20), STRNAME CHAR(20), PRIMARY KEY(MOVIETITLE,STRNAME), FOREIGN KEY(MOVIETITLE) REFERENCE MOVIE(TITLE)); FOREIGN KEY(STRNAME) REFERENCES MOVIESTAR(NAME)); CREATE INDEX I2 ON STARSIN(MOVIETITLE,STRNAME); MovieStar(name, address, gender, birthdate) CREATE TABLE MOVIESTAR (NAME CHAR(20), ADDRESS CHAR(30), GENDER NUMERIC(10), BIRTHDATE CHAR(15), PRIMARY KEY(NAME)); CREATE INDEX I3 ON MOVIESTAR(NAME);

MovieExec(name, address, cert#, netWorth) CREATE TABLE MOVIEEXEC (NAME CHAR(20), ADDRESS CHAR(30), CERT CHAR(10), NETWORTH CHAR(20), PRIMARY KEY(NAME)); CREATE INDEX I4 ON MOVIEEXEC(NAME); Studio(name, address, presC#) CREATE TABLE STUDIO (NAME CHAR(15), ADDRESS CHAR(30), PRES CHAR(15), PRIMARY KEY(NAME)); CREATE INDEX I5 ON STUDIO(NAME);

Classes(class, type, country, numGuns, bore, displacement) CREATE TABLE CLASSES (CLASS CHAR(10), TYPE NUMERIC(6), COUNTRY CHAR(15), NUMGUNS NUMERIC(10), BORE CHAR(10), DISPLACEMENT CHAR(15), PRIMARY KEY(CLASS)); CREATE INDEX I1 ON CLASSES (CLASS); Ships(name, class, launched) CREATE TABLE SHIPS (NAME CHAR(10), CLASS CHAR(10), LAUNCHED NUMERIC(5), PRIMARY KEY(NAME), FOREIGN KEY(CLASS) REFERENCE CLASSES); CREATE INDEX I2 ON SHIPS(NAME);

Battles(name, date) CREATE TALBLE BATTLES (NAME CHAR(15), DATE CHAR(15), PRIMARY KEY(NAME)); CREATE INDEX I3 ON BATTLES(NAME); Outcomes(ship, battle, result) CREATE TABLE OUTCOMES (SHIP CHAR(20), BATTLE CHAR(15), RESULT NUMERIC(4), PRIMARY KEY(BATTLE), FOREIGN KEY(SHIP) REFERENCE SHIPS, FOREIGN KEY(BATTLE) REFERENCE BATTLES); CREATE INDEX I4 ON OUTCOMES(BATTLE);

补2： Write following queries in SQL, based on the Exercise 1. (1). Find Sandra Bullock’s birthdate SELECT birthdate fromMovieStarwhere name = ‘Sandra Bullock’; (2). Find all executives worth at least $10,000,000 Selectname fromMovieExecwherenetWorth >= 10,000,000; (3). Find the countries whose ships had the largest number of guns.（总和） select country from Classes group by country havingsum(numGuns)>=ANY(selectsum(numGuns) from Classes group by country)

(4). Find the average number of guns of battleship classes. Select AVG(numGuns) from Classes where class = ‘battleship’ (5). Find all the stars who either are male or live in Malibu. Selectname fromMovieStarwhere gender = ’male’ or address = ’Malibu’; (4).Which stars appeared in movies produced by MGM in 1995? SELECTstrNamefromStarsInwheremovieTitlein (select title from Movie where STUDIONAME =’MGM’ and year = ‘1995’);

第四次作业 • 选择自己生活、学习中的需求，创建有2-3 个关系的数据库。略。 • 在创建的数据库上给出两个查询访问要求，并用SQL语句实现（要求有join/group等）略。

第五次作业 • 8.1 写出“供应商-工程”数据库中的一些完整性约束（SQL） b. 合法的供应商编号为大于两个字母的字符串，其中第一个字母为”S”，其余部分为从0到9999范围内的十进制整数。 CREATE DOMAIN S# CHAR CONSTRAINT VALID_S# CHECK ( SUBSTRING ( VALUE FROM 1 FOR 1 ) = ’S’ AND CAST ( SUBSTRING ( VALUE FROM 2 ) AS INTEGER ) >= 0 AND CAST ( SUBSTRING ( VALUE FROM 2 ) AS INTEGER ) <= 9999 )

d.任何两个工程不能位于同一城市 CREATE ASSERTION D CHECK ( NOT EXISTS ( SELECT * FROM J AS J1, J AS J2 WHERE J1.J# <> J2.J# AND J1.CITY = J2.CITY ) ) g.供应商状态值最高的供应商和状态值最低的供应商不能再同一城市中 CREATE ASSERTION G CHECK ( NOT EXISTS ( SELECT * FROM S AS S1, S AS S2 WHERE S1.STATUS = ( SELECT MAX ( S.STATUS ) FROM S ) AND S2.STATUS = ( SELECT MIN ( S.STATUS ) FROM S )AND S1.CITY = S2.CITY AND S1.STATUS <> S2.STATUS ) )

j.至少有一种红色的零件 CREATE ASSERTION J CHECK ( EXISTS ( SELECT * FROM P WHERE P.COLOR = ‘Red’ ) OR NOT EXISTS ( SELECT * FROM P ) ) g.至少有一种红色零件的单重小于50磅 CREATE ASSERTION M CHECK ( EXISTS ( SELECT * FROM P WHERE P.COLOR = ‘Red’ AND P.WEIGHT < 50.0 ) OR NOT EXISTS ( SELECT * FROM P WHERE P.COLOR = ‘Red’ ) )

o.在London的供应商提供的零件总数量要多于在Paris的供应商提供的零件总数量o.在London的供应商提供的零件总数量要多于在Paris的供应商提供的零件总数量 CREATE ASSERTION O CHECK ( SELECT SUM ( SPJ.QTY ) FROM S, SPJ WHERE S.S# = SPJ.S# AND S.CITY = ‘London’ GROUP BY S# > SELECT SUM (SPJ.QTY ) FROM S, SPJ WHERE S.S# = SPJ.S# AND S.CITY = ‘Paris’ GROUP BY S# )

o.位于Athens的供应商只能搬到London或Paris，London的供应商只能搬到Paris。o.位于Athens的供应商只能搬到London或Paris，London的供应商只能搬到Paris。此题为关系变量上的动态约束类型，而SQL不支持动态约束，用SQL无法描述，可以用Tutorial D语句，但是，书上答案有错误。 CONSTRAINT Q IS_EMPTY ( ( S JOIN ( S’ WHERE CITY = ’Athens’ ) ) WHERE CITY <> ’Athens’ AND CITY <> ’London’ AND CITY <> ’Paris’ ) AND IS_EMPTY ( ( S JOIN ( S’ WHERE CITY = ’London’ ) ) WHERE CITY <> ’London’ AND CITY <> ’Paris’ ) 引入如下的约定：在更新操作之前，关系变量名（这里是S’）已被理解为指向对应的关系变量。这样，本题中的约束就可以这样理解： (a) 将更新前CITY=’Athens’ 的关系变量与更新后的关系变量执行JOIN操作； (b) 找到不在Athens住也不在London住，不在Paris住的元组； (c) 结果集必须为空。另一种情况同理。动态约束是指数据库从一种状态转变为另一种状态时新、旧值之间所应满足的约束条件，它是反映数据库状态变迁的约束。

8.9 用图4-5中“供应商-零件-工程”例子中的数据，说出下列操作会引起什么结果。 • (b) 更新零件P5，置P#值为P4； • 会被拒绝，违反了主码唯一性。 • (d) 删除供应商S3，此时的参照行为是CASCADE； • 接受，S中供应商S3对应的元祖被删去，SPJ中所有S#=‘S3’的元组也被删去。 • (h) 更新供货量S5-P5-J5，置J#为J7； • 拒绝，违反了主码唯一性。 • (j) 插入供货量记录S5-P6-J7； • 接受。

第六次作业 • 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’

9.6 对于下面的视图定义 VAR HEAVYWEIGHT VIEW ((P RENAME WEIGHT AS WT, COLOR AS COL) WHERE WT > WEIGHT(14.0)) {P#, WT, COL}; 请给出对下列语句实施替换过程后的转化形式： • 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 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 失败，因为插入的元组不满足视图谓词

第七次作业 关系代数：黑色关系演算：绿色 RANGEVAR SX RANGES OVER S; RANGEVAR PX RANGES OVER P; RANGEVAR JX RANGES OVER J; RANGEVAR SPJX RANGES OVER SPJ; 一定要先定义范围变量 • 6.13 求所有有关工程的信息 ∏J#,JNAME,CITY(J) 或者 J JX

6.15 求为工程J1提供零件的供应商的号码。 ∏S#(бJ#=J1(SPJ)) SPJX.S# WHERE SPJX.J# = J#( ‘J1’ ) • 6.20 求所有的供应商号/零件号/工程号三元组。其中所指的供应商、零件和工程三者中的任意两个都不在同一个城市。 ∏S#,P#,J#(бS.city≠P.city∧P.city≠J.city∧J.city≠S.city(S×P×J)) (SX.S#, PX.P#, JX.J#) WHERE SX.CITY≠ PX.CITY AND PX.CITY ≠ JX.CITY AND JX.CITY ≠ SX.CITY • 6.34 求使用了S1供应的零件的总量。 ∏J#(∏P#(б S#=S1(SPJ)) SPJ) SPJX.J# WHERE EXISTS SPJY ( SPJX.P# = SPJY.P# AND SPJY.S# = S#(‘S1’)) 注意：其他供应商供应的也应算入总量

7.3 设p(x)和q(x)是任意的合式公式，x、y分别为自由变量，下面哪一种说法是正确的？ • EXISTS x (p(x)) AND EXISTS y(q(y)) ≡ EXISTS x EXISTS y (p(x) AND q(y)) 正确 • EXISTS x (IF p(x) THEN q(x) END IF) ≡ IF FORALL x (p(x)) THEN EXISTS x (q(x)) END IF

第八次作业 • 1.求最小函数依赖集R(A,B,C,D,E,F,G)F={AB→C, C→A, BC→D, ACD→B, D→EG, BE→C, CG→BD, CE→AG} 解：将右边写出单属性并去除重复FD（分解律） F={AB→C, C→A, BC→D, ACD→B, D→E，D→G, BE→C, CG→B，CG→D, CE→A，CE→G} 消去左部冗余属性CE→A，C→A；ACD→CD，D→G，CD→CG，CG→B，则ACD->B变为CD→B； F={AB→C, C→A, BC→D, CD→B, D→E，D→G, BE→C, CG→B，CG→D,CE→G} 消去冗余函数依赖 ①CD→B冗余，因为可以由D→G, CG→B推出 F={AB→C, C→A, BC→D, D→E，D→G, BE→C, CG→B，CG→D,CE→G} CG→D冗余，因为可以由CG→B，BC→D推出 F={AB→C, C→A, BC→D, D→E，D→G, BE→C, CG→B，CE→G} ②CG→B冗余，因为可以由CG→D, CD→B推出 F={AB→C, C→A, BC→D, CD→B, D→E，D→G, BE→C, CG→D,CE→G}

2 R(A,B,C,D), F={A→C, D→C, BD→A}，问模式分解 p={R1,R2,R3} 相对于F是否是无损分解？是否保持函数依赖？其中: R1(A,B),R2(A,C,D),R3(B,C,D) 解：根据Chase过程（略），终止时表格中未出现全a的行，所以不是无损分解 ∏R1(F)＝{按自反律推出的平凡FD} ∏R2(F)＝{ A→C, D→C，以及按自反律推出的平凡FD} ∏R3(F)＝{ D→C，以及按自反律推出的平凡FD} ∏R1(F)∪∏R2(F)∪∏R3(F)＝{ A→C, D→C }+ ≠F+ 不保持函数依赖

3 设关系模式R(S#,C#,Score,Tname,Addr)，其属性分别表示学号、课程号、成绩、任课教师姓名、教师地址，规定：每个学生每门课只有一个成绩，每门课只有一个教师任教，每个教师只有一个地址，教师没有同名同姓。(1)写出R的基本函数依赖和候选码，并说明理由由题意得出函数依赖集：S#, C#->Score C#->TnameTname->Addr 可以推导出S#,C# -> S#, C#, Tname, Addr，可知（S#,C#）是超码，而S#和C#不是超码，所以（S#,C#）是候选码

(2)把R分解为2NF模式，并说明理由 将R分解为R1(S#, C#, Score) R2(C#, Tname, Addr) 验证R1和R2是2NF 2NF：每一个非主属性不局部依赖于主码 (3)把R分解为3NF模式，并说明理由 R1(S#, C#, Score)R2(C#, Tname)R3(Tname, Addr) 验证R1，R2和R3是3NF3NF：每一个非主属性都不传递依赖于于主码

E D • 4 R(ABCDEF), F={E→D,C→B,CE→F,B→A} 问： (1)R最高属于第几范式？候选码为CE。为1NF。(2)无损联接并保持函数依赖地分解R为3NF1. 先将R保持函数依赖地分解到3NF，R1(E,D), R2(C, B), R3(C, E, F), R4(B, A) 2. 得到无损连接并保持函数依赖的3NF分解 q = {R1, R2, R3, R4} p = q U {R(C, E)}因为{C,E}为{C,E,F}子集，故去掉。所以1结果即为结果。 C B A F

(3)无损联接地分解R为BCNF1. p = {R} 2. B->A不满足BCNF定义，分解R: p={R1(A,B), R2(B,C,D,E,F)} 3. R2中C->B不满足BCNF定义，分解R2: p={R1(A,B), R3(B,C), R4(C,D,E,F)} 4. R4中E->D不满足BCNF定义，分解R4: p={R1(A,B),R3(B,C),R5(D,E),R6(C,E,F)} 5. p中各关系模式均满足BCNF定义，终止。则p={R1(A,B),R3(B,C),R5(D,E),R6(C,E,F)}

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

2. Transaction state transfer condition.

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 • 3. Give two transaction examples. 银行转账；火车站订票。(应详细说明）

第十次作业 • 14.4下列情况下，恢复的含义分别是什么？ • Commit时将缓冲区的内容强制的写入数据库系统故障恢复时无须“重做操作”：已经commit的事务一定已经物理的写入数据库，所以不需要redo b. Commit之前缓冲区的内容不物理的写入数据库无须“撤消”操作，进而用于“撤消”的日志记录也不再必要：没有commit的事务一定没有物理的写入数据库，所以不需要undo

14.6 利用供应商和零件数据库，写一个SQL应用程序，按零件编号顺序查询并显示所有的零件，每十个记录重新开始一个新的事物，并且要求将第十个记录删除。假设从零件表到供应表的DELETE外码规则为CASCADE（也就是说，该练习可忽略对供应表的操作）。注：可是用SQL游标机制。 and write the possible log records for it. Change the exercises to delete the every third part according the data on the book. 日志： <Ti, start> <Ti, P10, old_value, NULL> <Ti, commit> 注：<Ti, D, old_value, NULL> 或<Ti, delete, D, value>

EXEC SQL DELARE CP CURSOR FOR SELECT P.P#, P.PNAME, P.COLOR, P.WEIGHT, P.CITY FROM P WHERE P.P# > previous_P# (previous_P#需要因为游标关闭后不再和原来的项对应） ORDER BY P# ; previous_P# := ’ ’ ; eof := false ; no := 0 ; DO WHILE ( eof = false ) ; EXEC SQL OPEN CP ; 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

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 ; END DO ; EXEC SQL CLOSE CP ; EXEC SQL COMMIT ; previous_P# := :P# ; END DO ;

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> 恢复的基础：从Log修改记录中没有数据项之前的值可判断这是Deffered Database Modification. commit 才写入 ——没有<Commit,T>记录的，操作必定没有改写磁盘数据，因此在恢复时可以不理会 ——有<Commit,T>记录的，结果可能还未写回磁盘，因此在恢复时要Redo (a)无操作 (b)redo U；将数据库中B值置为20，D值置为40 在日志中写入<T, abort>;； (c)redo U 将数据库中B值置为20，D值置为40 在日志中写入<T, abort>； (d)redo U , redo T 将数据库中A值置为10，B值置为20，C值置为30，D值置为40，E值置为50 。

0911 数据库作业答疑