中山大学-2015数据库期末试卷.docVIP

?▓中山大学本科生期末考试试卷?▓ 中山大学软件学院本科生期末考试 考试科目：《数据库系统原理》（A卷） 学年学期：2014学年第3学期 姓 名： 学 院/系：软件学院 学 号： 考试方式：开卷 年级专业： 考试时长：120分钟 班 别： 警示 《中山大学授予学士学位工作细则》第八条：“考试作弊者，不授予学士学位。” 以下为试题区域，共7道大题，总分100分,考生请在答题纸上作答 1. (10 marks) Let R = {A, B, C, D, E, F, G} and F = {AB?C, A?C, A?E, B?C, EF?EG, G?F}. Answer the following three questions. (4 marks) Compute the minimal cover of F. B?C A?C A?E EF?G G?F (4 marks) Decompose R into 3NF relations. {B, C}, {A, C, E}, {E, F, G} and {A, B, D, F} (OR {A, B, D, G}) (2 marks) Is the composition in (b2) in BCNF? Briefly explain your answer. No. For {E, F, G} and G?F, G is not a candidate key. 2. (10 marks) Assume there is an employee database Employee (eid: 8 bytes, ename: 16 bytes, did: 4 bytes, email: 12 bytes), where eid and ename are respectively the id and name of an employee and did is the id of the department in which the employee works. Suppose there are 50,000 employee records and 500 departments (i.e. each department has 100 employees on average). A page size is 1,000 bytes and a pointer costs 4 bytes. (4 marks) Assume that the employee file is sorted sequentially on did and there is no index. Estimate the page access cost for retrieving the records of all employees working in a department with a given did. (You should show your argument and the main steps of the estimation clearly in the answer.) Answer Record size = 40 bytes, 25 records per page, 2,000 pages. Finding the first record requires log22000 + 3 more pages to search the remaining records (each dept has 100 employees which are distributed in 4 pages). (6 marks) Assume only 20 pages of main memory are available for running the external sorting of the employee file on did. How many PASSes are needed for the external sorting? In each PASS, how many runs are created? What is the total cost of the sorting in terms of pages? Answer: 3 PASSe