北京航空航天大学《 数据库系统概论 》期末考试卷

数据库期末试题2010级友情提醒：闭卷考试，有一定难度，英文，考试时间2小时，需要好好复习。

建议好好做那份样卷（即09年试卷），大题目题型和那上面差不多，选择改为了判断，我们这届没有简答题。

题型：判断（10题），简答题（5题）判断题没有记录，主要考基本概念。

简答题：（1）事务，串行化调度，两阶段锁协议（2）Sql语句和关系代数语句写出查询（3）ER图设计并写出关系主键，外键等（4）给出函数依赖，并且推断属于何种范式（BCNF，第三范式）（5）题目给出关系表与关系代数表达式，求出运算结果班号学号姓名成绩《数据库系统概论》期末考试卷注意事项：1、考试时间2小时；2、答案写在答题纸上题目：一、……………………………………………………………( 分)二、……………………………………………………………( 分)三、……………………………………………………………( 分)四、……………………………………………………………( 分)五、……………………………………………………………( 分)六、……………………………………………………………( 分)一：单选题(本大题共12小题，每小题3分，共36分)1. 对现实世界进行第一层抽象的是【 D 】A. 用户数据模型B. 物理数据模型C. 逻辑数据模型D. 概念数据模型2. 以下不属于集合运算的是________。

【 C 】A. 并B. 广义笛卡尔积C. 除D. 差3. 若一个关系有函数依赖集（AB→CD, A→D），则可确定它最高属于：【 A 】A. 1NFB. 2NFC. 3NFD. BCNF4. 以下哪个SQL语句没有语法错误【 A 】A. Grant select on TableA to User1 with grant optionB. select count(a) from b where count(a)>3C. insert into TableA set a=1, b=2D. drop TableA where a=15. 定义学生对象来表示张三、李四等学生个体，这种抽象方法被称为【A】A. 分类B. 聚集C. 类比D. 概括6. 哪一级封锁协议解决了读脏数据问题？【B】A. 一级封锁协议B.二级封锁协议C. 三级封锁协议D. 以上都不是7. 工资表（职工号，岗位级别，岗位工资）中有如下约束：岗位级别低的职工的岗位工资应低于岗位级别高的职工的岗位工资。

这种约束属于什么约束类型？【E】A. 静态列级约束B. 动态列级约束C. 静态元组约束D. 动态元组约束E. 静态关系约束F. 动态关系约束8. 设有关系R（A，B，C）的值如下：下列叙述正确的是：【 D 】A. 关系中存在函数依赖A→CB. 关系中存在函数依赖BC→AC. 关系中存在函数依赖C→AD. 无法获知函数依赖关系9．有关系模式A (a, b, c, d, e, f)，存在如下函数依赖集：F={(a)→b，(a, c)→d，(c, d)→e, (e)→f }可知其候选键是【 B 】A. (a, c, e)B. (a, c)C. (b, d, f)D. (a, c, d, e)10．如果有5个不同的实体，存在5个不同的二元联系，其中2个为1：1联系，3个为M：N联系，那么根据转换规则，转换出来的关系表个数不可能是：【 A 】A. 7个B.8个C.9个D. 10个11. 以下不属于数据库保护数据安全的机制的是：【 A 】A. 索引B. 并发控制C. 存取控制D. 数据完整性约束12. 以下属于数据库逻辑结构设计阶段工作内容的是：【 C 】I.数据字典II. 范式分解III. ER图IV. 数据流图V. 用户模式A. I, IVB. II, III, VC. II, VD. I, III, IV二：简答题：(3题，每题6分)1.试述数据库三层模式结构的含义。

2.举例说明什么是动态关系级完整性约束。

3.请简述索引的作用和工作原理。

三：本题6分S: A B C134342156734D B C 13null 234 342请给出∏R.A, S.B(σ()R.A=S.D（R▷◁S）)，R]▷◁S (R与S的左外连接) 的结果。

五．本题30分设关系数据库的模式包含下列关系：国家(国名, 首都名, 面积)（首都名为外键，它所引用的是城市表的主键—城市名）城市(城市名，首都名，人口) （‘首都名’字段为外键，它所引用的本表的主键—城市名。

说明：若一个城市是首都，则城市名、首都名字段内容相同，都是该城市名。

）邻国（国名，邻国）（记录国与国之间相邻的关系。

国名、邻国字段构成联合主键，二者都引用国家表的国名字段。

）请用关系代数实现下列查询：1．找到所有跟中国相邻的国家名称。

2．查询中国的所有城市及其人口。

3. 查找与德国的所有邻国都接壤的国家请用SQL语言实现下列操作：4．查找没有邻国的国家名。

5．找出邻国数量在5个以上的国家，列出国名和邻国数量，按邻国数量升序排序。

6．列出所有跟中国间接相邻的国家名。

(说明：中国与越南相邻，越南与柬埔寨相邻，则中国与柬埔寨为间接相邻。

本题只考虑间隔一个国家的间接相邻。

注意：要排除直接相邻的国家)7．写一个视图，列出每个国家人口最多的城市的人口数量。

字段列表为（国名，人口）。

8．在国家表中增加一列：城市数量（int型）。

9．在国家表新增的城市数量字段中，填写表中每个国家的城市数量。

10．列出每个国家中人口最少的城市名称和人口数量六．本题10分公司要设计一个数据库应用系统来管理企业人力资源。

业务规则如下：●公司有若干员工。

记录每个员工的ID号、姓名及入职时间。

●公司有多个部门。

需记录部门名称、部门职责和部门领导信息。

●每个员工隶属于一个部门。

●员工可以被别的部门借调使用（一次只能被一个部门借调）。

●系统还需管理公司的工程项目信息，包括项目名称、项目开始时间、结束时间。

●每个工程项目由若干部门和部门所借调的员工参加。

1．试按上述业务规则设计ER模型，并在图上注明属性、联系的类型。

2．将ER图转换成关系模式，指出每个关系的主键和外键。

班号学号姓名成绩《数据库系统概论》期末考试卷注意事项：1、考试时间2小时；2、答案写在答题纸上题目：一、……………………………………………………………( 分)二、……………………………………………………………( 分)三、……………………………………………………………( 分)四、……………………………………………………………( 分)五、……………………………………………………………( 分)六、……………………………………………………………( 分)Problem 1: TRUE or FALSE QUESTIONS (30 points).For each of the following statements, indicate whether the entire statement is TRUE or FALSE1. There are five different entities and five binary relationships in an ER diagram. Two of therelationships are 1:1 relationship; 3 of them are M:N. After translating from ER model to Relation model, we can probably get 9 relations (tables).TURE2. An E-R diagram will translate uniquely to a relational schema. FALSE3. 3. A relation with two attributes is always in 3NF, but may not be in BCNF.FALSE4. R ▷◁(S ∩T) = (R ▷◁S)∩(R ▷◁T) TRUE5. In relational algebra, join is a derived operator. TRUE6. In a table, there is exactly one key, but there can be multiple candidate keys.TRUE7. The natural join of two relations R(A,B) and S(C,D), which have no common attributes, is equivalent to their Cartesian product. TRUE8. In SQL, without GROUP BY, we cannot use HAVING.TRUE9. Triggers can operate on insertion, deletion, and updates. TRUE10. There are two relations: Stu_Course(stu_name, course, score), Score_Sum(stu_name, Sum). The user wants to define a constraint as: for the same student, the value of Sum attribute in score_sum table equals the sum of all score attribute values in stu_course table. We can use Check constraint to apply this requirement.FALSEProblem 2: (10 Points)(a) What serial schedule is this equivalent to? If none, then explain why.The serializability graph for the above schedule is: T 1→T 2 ← T 3. Any order that complies with the topological order of the graph like T 1 → T 3 → T 2 is an equivalent serial schedule for our schedule(b) Is this schedule consistent with two phase locking? Explain why.If we assume that all transactions get the locks exactly before the operation and release them afterwards, it is not consistent with two phase locking. This is because T1 releases its lock on B after its second operation while acquiring a lock on D at its last two operations. By removing the last two operations of T1 the schedule becomes 2PL.If we assume that the transactions get all the locks they need at the beginning of the transaction, and release them after the finish the operation, this schedule will be 2PL. The minimum operations that could be added to the schedule will be "T1 reads item A". In this case, T1 has to acquire the lock on A again after releasing its lock on A after its first write.Problem 3: (5 Points) There are two relations:S:∏R.A, S.B (σR.A =S.D （R ▷◁S ）)， R]▷◁S (left outer join of R and S).Problem4: (5 points)Consider a relation R(A,B,C,D,E), with FDs AB → C, C → A, C → BD, D → E Remember Armstrong’s Axioms:1) XY → X (reflexive)2) X → Y => XZ → YZ (augmentation)3) X → Y & Y → Z => X → Z (transitivity)4) X → Y & X → Z => X → YZ (union)5) X → YZ => X → Y & X → Z (decomposition)6) X → Y & YZ → U => XZ → U (pseudo transitivity)(a) Is the FD: ABC -> E implied? Show your derivation. (5 points)ABC → C reflexiveC → BD givenC →D decompositionD →E given(b) Complete the missing values in the following table. The last column is filled in as an examplea. Consider the attribute subsets, X, in the table below.b. Compute the attribute closure of each subset X+(e) Is this relation in BCNF? If you answer is yes, explain why it is. If you answer is no, decompose relation into BCNF, showing your decomposition steps.No it is not, the FD, D → E, violates BCNF. We can separate into two relations:1: R (ABCD)2: R (DE)Problem 5: (20 points)Tables 1, 2, and 3 below show an example instance of corporate（企业）database for a coalition（联合）of retail（零售）stores. Those tables maintain information about stores, products, and the inventories(库存) of products in different stores. We assume that all the stores sell each product at the same price in Table 2.Write down queries in relational algebra for the following questions. Please refer to the Store relation, Product relation, and Inventory relation as S, P, and I respectively in your solutions.(a) Return the name of the stores that have at least one product in their inventories whose unit price is greater than 2 USD.(b) Return the name of the products with the maximum unit price.(c) Each store has an average unit price. It is the average of the unit prices of the products available at the store. Return the average unit prices of all stores. Your query should produce pairs of store names and their average unit prices此题不算分(d) Return the product(s) that are available in at least three stores.Inventory: I1, I2, I3Π(productname)σ(I1.productname=I2.productname and I1.productname=I3.productname and I1.storeid<>I2.storeid and I1.storeid<> I3.storeid and I2.storeid <> I3.storeid)Consider the relations given above. Write down the following queries in SQL.(e) Return the names of the stores that have Tomato and Lettuce（莴苣）in their inventories.(Select StoreNameFrom Store,InventoryWhere Store.StoreID = Inventory.StoreID and Inventory.Product ='Tomato')INTERSECT(Select StoreNameFrom Store,InventoryWhere Store.StoreID = Inventory.StoreID and Inventory.Product ='Lettuce')(f) Return the names of the products whose unit prices are below the average unit price of all the products.Select ProductNameFrom ProductWhere UnitPrice <( Select Avg(UnitPrice)From Product)(g) Each store has an average unit price. It is the average of the unit prices of the products available at the store. Return the average unit prices of all stores. Your query should produce pairs of store names and their average unit prices.Select StoreName, Avg(UnitPrice)From Store, Inventory,ProductWhere Store.StoreID = Inventory.StoreID and Inventory.ProductName = Product.ProductName GroupBy StoreName(h) Return the product(s) that are available in at least three stores.Select ProductNameFrom InventoryGroupBy ProductNameHaving (Count(distinct StoreID) >= 3)Problem 6: (10 points)Consider the following information about a company.• Company has departments.• Department has a name, number, and at most one manager.• Manager is an employee.• Manager has a starting date.• Department controls projects.• Project has name, number.• Each employee has a name, social security number, address, birthdate.• An employee is in exactly one department but may work on several projects. • Record is kept of hours each employee works on each project.• Some employees have a supervisor.(a) Design and draw an ER diagram that captures the information about the company. Pick the most suitable key for each entity. Try to minimize the number of resulting entities and relationships. Be sure to indicate keys and multiplicity constraints. State all assumptions you make.(b) Translate your ER diagram into schema. Try to minimize the number of resulting relations. Specify the key of each relation in your schema.Solution:Project(Name, Number)Department(Name, Number, managerSSN, ManagerStartDate)Employee(SSN, Name, Address, Birthdate, SupervisorSSN)WorksOn(EmployeeSSN, ProjectName, Hours)Controls(DepartmentName, ProjectName)。