1、北京邮电大学 20072008 学年第 2 学期数据库系统原理期末考试试题(A)考试注意事项一、学生参加考试须带学生证或学院证明,未带者不准进入考场。学生必须按照监考教师指定座位就坐。二、书本、参考资料、书包等物品一律放到考场指定位置。三、学生不得另行携带、使用稿纸,要遵守北京邮电大学考场规则 ,有考场违纪或作弊行为者,按相应规定严肃处理。四、学生必须将答题内容做在试题答卷上,做在试题及草稿纸上一律无效。五、填空题用英文答,中文答对得一半分。考试课程考试时间 2008 年 6 月 16 日题号 一 二 三 四 五 六 七 八 九 十 总分满分 9 11 18 12 20 8 5 6 5 6得分
2、阅卷教师1. Fill in blanks. (19 points)(1) DDL is the language for specifying the database schema and as well as other properties of the data. (2) With respect to integrity mechanisms in DBS, trigger defines actions to be executed automatically when some events occur and corresponding conditions are sati
3、sfied.(3) An entity set that does not have sufficient attributes to form a primary key is termed a weak entity set .(4) The commonly-used schemes of organization of records in files are heap file organization , sequential file organization, and hashing file organization.(5) The three steps in query
4、processing are parsing and translation, optimization, and evaluation .(6) The recovery-management component of a database system implements the support for transaction atomicity and durability .(7) A cascadeless schedule ensures that the abort of a transaction does not result in cascading aborts of
5、other transactions.(8) The stric two-phase lock protocol requires that all exclusive-mode locks taken by a transaction be held until that transaction commits.(9) The three types of failures in DBS are the transaction failures, system crash, and disk failures/crash .2. Choice (111 points)(1) With res
6、pect to DBS design , the index is designed at the D phase.A. requirement analysis B. conceptual designC. logical design D. physical design(2) For the E-R diagram given below, the mapping cardinality from A to B is C 0.1 35A. one-to-many B. one-to-one C. many-to-one D. many-to-many(3) The following S
7、QL statement corresponds to the expression C . Select *From r, sA. r s B. r s C. r s D. r s(4) Given the schema R(A, B, C, D, E, F) and the functional dependencies F=ABD, BCE, D F, AB F, CEB holding on it, D is a transitive functional dependency.A. ABD B. BC E C. D F D. ABF E. CEB (5) Given a relati
8、on r(R), which one of the following functional dependencies is satisfied by r. C A. AB B. ACB C. BCA D. BC E. noneA B C1 6 24 5 64 6 67 3 89 1 0(6) In a Select statement, C can be used to take out repetition tuples. A. unique B. count C. distinct D. union(7) In SQL language, the statement that can b
9、e used for security control is C A. insert B. rollback C. revoke D. update(8) All information except D belong to meta-data and are stored in the data dictionary. BRAA. names of the relationsB. names of the authorized users C. attributes on which the index is defined D. tuples in the relations(9) For
10、 three relations r, s, t, their sizes satisfy | r | 2000)(4) 解法一:create table Huabei_customers(c_id int,c_name varchar(10),c_city varchar(10),discount real;primary key (c_id)insert into Huabei_customersselect customer.c_id ,c_name,c_city,discountfrom customer,orders,productswhere customer.c_id = ord
11、ers.c_id and products.p_id = orders.p_idand p_name = TVand p_city inBeijing,Tianjing,Shijz解法二:create table Huabei_customers(c_id int,c_name varchar(10),c_city varchar(10),discount real;primary key (c_id)select customer.c_id ,c_name,c_city,discount into Huabei_customersfrom customer,orders,productswh
12、ere customer.c_id = orders.c_id and products.p_id = orders.p_idand p_name = TVand p_city in (Beijing,Tianjing,Shijz)4. (12 points) The functional dependency set F= ABC, ADEI, BFH, FGH,DIJ holds on the relation schema R = (A, B, C, D, E, F, G, H, I, J), a. Compute (AF)+ (3 points)b. List all the cand
13、idate keys of R. (2points)c. Compute the canonical cover Fc (3 points)d. Give a lossless and dependency-preserving decomposition of R into 3NF. (4 points)Answer:a. (3 points)(AF)+ result=AFADEI result=AFDEIFGH result=AFDEIGHDIJ result=AFDEIGHJb.(2 points)(AB) + =ABCDEFGHc. (3 points)_Fc= ABC , ADE,
14、BF, FGH , DIJ d. (4 points)R1(A,B,C)R2(A,D,E)R3(D,I,J)R4(B,F)R5(F,G,H)5. (20 points) Notown Records company needs to store information about songs, albums and musicians who perform on its albums in a database. Consider the following information: Each musicians that records at company has an Id (whic
15、h is unique), a name , an address, and a phone number. Each instrument used in company has a name and an ID, ID is unique. Each album recorded on the Notown label has a title, a copyright date, a format, and an album identifier. Each song recorded at Notown has a title and an author, and each song c
16、an be identified by title. Each musician may play several instruments ,and a given instruments may be played by several musicians. Each album has a number of songs on it, but no song may appear on more than one album. Each song is performed by one or more musicians, and a musician may perform a numb
17、er of songs. Each album has exactly one musician who acts as its producer. A musician may produce several albums, of course. (1) Design the E/R diagram for hospital database on basis of the information mentioned above .(10 points)Note: mapping cardinality of each relationship and participation of ea
18、ch entity to the relationship should be described in the diagram.(2) Convert the E-R diagram to the proper relational schemas, and give the primary keys of each relation schemas by underlines. (10 points)Answers:(1)全参与和部分参与可有不同答案。(2) musician(m_id, name, address, phone_number)instrument(I_id,I_name)
19、album(a_identifier, title, copyright_date, format, m_id)song(s_title, author, a_identifier)M_idmusician playaddressnameinstrumentI_idI_namealbumproduce performa_identifiertitleformatCopyright_datesong S_titleauthorhavePhone_numberplay( m_id, I_id)perform(m_id, s_title)6. (8 points) Given the data fi
20、le student(s_dept, student_ID, student_name) as shown below, which is organized as a sequential file, taking the attribute s_dept as the search key, (1) define a dense and clustering index for the indexed file student. It is required that the index file and index entries in the index file should be
21、figured out. (4 points) (2) If a tuple (WF, 05922, Bai) is inserted into the indexed file, depict the indexed file and the index file. (4 points)Answer:Xu05802TE Deng05801TE Wei05701ME Zhang0515E Zhou051EWang0542CS Li0541S(s_dept, student_ID, student_ame) the indexd file studentthe index file stu_in
22、dexafter the tuple (WF, 05922, Bai) inserts into indexed file.CSEEMETEWFthe index file stu_indexthe Indexed file student7.(5 points). Is the concurrent schedule S1, as shown below, a recoverable schedule? and why ? S1 CSEEMETECS 05411 LiCS 05422 WangEE 05511 ZhouEE 05515 ZhangME 05701 WeiTE 05801 De
23、ngTE 05802 XuWF 05922 BaiAnswer:S1 is not a recoverable schedule(3 points ). Because T3 read R that was written by T2 before T3 read, but T2 commit before T3 commit(2 points).8. (6 points)Considering the concurrent schedule S on the transaction set T1, T2, T3 that is under the timestamp protocol, it
24、 is assumed that the timestamps of T1, T2 and T3 are 1, 2, and 3 respectively, the initial values of R-timestamp(P), W-timestamp(P), R-timestamp(Q), W-timestamp(Q), R-timestamp(R), W-timestamp(R) are all 0, (1) What are the values of R-timestamp(P) and W-timestamp(Q) when T1 commits? (2 points)(2) W
25、hat are the values of R-timestamp(R) and W-timestamp(Q) when T2 commits?(2 points)(3) What are the values of R-timestamp(R) and W-timestamp(Q) when T3 commits? (2 points)ST1 T2 T3 read(P) read(P) write(R) write(Q)commit write(Q) write(Q) read(R)commit read(R)commit Answer:(1) R-timestamp(P) = 2 W-ti
26、mestamp(Q) = 1T1 T2 T3write(Q)read(Q)commit write(R)read(R)write(R)commitcommit(2) R-timestamp(R) = 3 W-timestamp(Q) = 3(3) R-timestamp(R) = 3 W-timestamp(Q) = 39. (5 points) Considering the concurrent transactions T1, T2, T3, and data items A, B and C modified by these transactions. It is assumed t
27、hat the initial values of these data items are A=10, B=20, C=0 immediate database modification and checkpoint techniques are employedFor the concurrent executing of T1, T2 and T3 as shown below, list the log file at the time when the system crashes.Log file :T1 T2 T3 DBMSbegin-tran(T1)read(A)begin-t
28、ran(T2)read(B)A := A+20checkpointwrite(A)begin-tran(T3)B := B+30commitread(C)C :=C+10checkpointwrite(B)commitwrite(C)crash*crash*10. (6 points) Considering the concurrent transactions T1, T2, T3, T4 and T5, and the data items A, B, C and D modified by these transactions. It is assumed that the initi
29、al values of these data items are A=100, B=200, C=0, D=300. immediate database modification and checkpoint techniques are employedWith respect to the log in the following figure that describes the concurrent executing of T1, T2, T3, T4 and T5, when a failure occurs, the log-based recovery scheme con
30、sults the log to determine the recovery operations (i.e. redo, undo, ignore) done on T1, T2, T3, T4 and T5. After recovery operations on T1, T2, T3, T4 and T5 are completed, (1) what are the values of the data items A, B, C and D in the database? (4 points)(2) which transaction successfully updates the data item B and determines the final value of B.(2 points)*Crash*Answers:(1) We get three lists: redo list: T4,T5 ,undo list: T2,T3, ignore list: T1, so we have: A=250, B=300, C=0, D=100(4 points)(2) T4 (2 points )