数据库系统原理》试卷-A卷-试题-答案

1、北京邮电大学20072008学年第2学期数据库系统原理期末考试试题（A）考试注意事项一、学生参加考试须带学生证或学院证明，未带者不准进入考场。学生必须按照监考教师指定座位就坐。二、书本、参考资料、书包等物品一律放到考场指定位置。三、学生不得另行携带、使用稿纸，要遵守北京邮电大学考场规则，有考场违纪或作弊行为者，按相应规定严肃处理。四、学生必须将答题内容做在试题答卷上，做在试题及草稿纸上一律无效。五、填空题用英文答，中文答对得一半分。考试课程考试时间2008 年 6月 16日题号一二三四五六七八九十总分满分91118122085656得分阅卷教师1. Fill in blanks. (19 po

2、ints)(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 satisfied.(3) An entity set that

3、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 processing are parsing and tr

4、anslation, 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 other transactions.(8) The st

5、ric 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 respect to DBS design , the inde

6、x is designed at the D phase. A. requirement analysis B. conceptual design C. logical design D. physical design(2) For the E-R diagram given below, the mapping cardinality from A to B is C R 0.1 3.5ABA. one-to-many B. one-to-one C. many-to-one D. many-to-many(3) The following SQL statement correspon

7、ds to the expression C . Select * From r, s A. 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 relation r(R), which one

8、of the following functional dependencies is satisfied by r. C A. AB B. ACB C. BCA D. BC E. noneABC162456466738910(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 be used for security control is

9、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. A. names of the relationsB. names of the authorized users C. attributes on which the index is defined D. tuples in the relations(9) For three relations r, s, t, their si

10、zes satisfy | r | | s | 2000)(4) 解法一：create table Huabei_customers(c_idint,c_namevarchar(10),c_cityvarchar(10),discountreal;primary key (c_id)insert into Huabei_customersselect customer.c_id ,c_name,c_city,discountfrom customer,orders,productswhere customer.c_id = orders.c_id and products.p_id = ord

11、ers.p_idand p_name = TVand p_city inBeijing,Tianjing,Shijz解法二：create table Huabei_customers(c_idint,c_namevarchar(10),c_cityvarchar(10),discountreal;primary key (c_id)select customer.c_id ,c_name,c_city,discount into Huabei_customersfrom customer,orders,productswhere customer.c_id = orders.c_id and

12、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 candidate keys of R. (2points)c. Compute

13、 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, BF, FGH , DIJ d. (4 points)R1(A,B,C)

14、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:l Each musicians that records at company has an Id (which is unique), a name , an address,

15、and a phone number.l Each instrument used in company has a name and an ID, ID is unique.l Each album recorded on the Notown label has a title, a copyright date, a format, and an album identifier.l Each song recorded at Notown has a title and an author, and each song can be identified by title.l Each

16、 musician may play several instruments ,and a given instruments may be played by several musicians.l Each album has a number of songs on it, but no song may appear on more than one album.l Each song is performed by one or more musicians, and a musician may perform a number of songs.l Each album has

17、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 each entity to the relationshi

18、p 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)addressM_idnameI_idI_nameplayinstrumentmusicianPhone_numberperformproducehaveS_titletitlesongalbumauthorformatCop

19、yright_datea_identifier全参与和部分参与可有不同答案。(2) musician(m_id, name, address, phone_number) instrument(I_id,I_name) album(a_identifier, title, copyright_date, format, m_id) song(s_title, author, a_identifier) play( m_id, I_id) perform(m_id, s_title)6. (8 points) Given the data file student(s_dept, student

20、_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 figured out. (4 points) (2

21、) If a tuple (WF, 05922, Bai) is inserted into the indexed file, depict the indexed file and the index file. (4 points)Answer:CSEEMETEthe index file stu_indexafter the tuple (WF, 05922, Bai) inserts into indexed file.CS05411LiCS05422WangEE05511ZhouEE05515ZhangME05701WeiTE05801DengTE05802XuWF05922Bai

22、CSEEMETE WFthe index file stu_indexthe Indexed file student7.(5 points). Is the concurrent schedule S1, as shown below, a recoverable schedule? and why ? S1 T1 T2 T3write(Q)read(Q)commit write(R)read(R) write(R) commit commitAnswer:S1 is not a recoverable schedule（3 points）. Because T3 read R that w

23、as 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 is assumed that the timestamps of T1, T2 and T3 are 1, 2, and 3 respectively, the initial values of

24、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) What are the values of R-timestamp(R) and W-timestamp(Q) when T2 commits? (2 points)(3) What are the v

25、alues 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-timestamp(Q) = 1(2) R-timestamp(R) = 3 W-timestamp(Q) = 3(3) R-timestamp(R) = 3 W-timestamp(Q) = 39. (

26、5 points) Considering the concurrent transactions T1, T2, T3, and data items A, B and C modified by these transactions. It is assumed thatl the initial values of these data items are A=10, B=20, C=0l immediate database modification and checkpoint techniques are employedFor the concurrent executing o

27、f T1, T2 and T3 as shown below, list the log file at the time when the system crashes.T1 T2 T3 DBMSbegin-tran(T1) read(A) begin-tran(T2)read(B)A := A+20checkpoint write(A) begin-tran(T3) B := B+30commitread(C)C :=C+10checkpoint write(B)commitwrite(C)crashLog file :*crash*10. (6 points) Considering the concurrent transactions T1, T2, T3, T4 and T5, and the