资源预览内容
第1页 / 共6页
第2页 / 共6页
亲,该文档总共6页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
考试复习重点资料(最新版)考试复习重点资料(最新版) 封封 面面 第1页 资料见第二页资料见第二页 ? 06?07 ? ?96? ? ? ? ? ? 1. Noun explanation (5 for each issue, 30 in all) (1) Data Model A collection of tools for describing data ,data relationships, data semantics, data constraints. (2) Transaction A transaction is a collection of operations that performs a single logical function in a database application (3) Nested subquery A subquery is a select-from-where expression that is nested within another query. (4) Trigger A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database. (5) 3NF If schema R is the second normal form, and every non_candidate keys attributes are not deliver dependencies on the candidate keys, we call the schema R as the 3NF. (6) view A window from which users can access part data in database. 2. Please answer these questions simply (5 for each issue, 20 in all) (1) What is the primary goal of a DBMS DBMS provides an environment that is both convenient and efficient to use. (2) List four kinds of mapping cardinalities one to one, one to many, many to one, many to many (3) List five possible states of transaction active, partially committed, failed, aborted, committed (4)List two pitfalls of lock_based protocol deadlock,starvation 3. Please analyse and answer these questions (10 for each issue, 20 in all) (1) Consider the precedence graph as following. Is the corresponding schedule conflict serializable? List one of the serializability orders. The corresponding schedule is conflict serializable.T1 T2 T3 T4 T5 (2) Consider the following two transactions: T1: read(A) Read(B) if A=0 then B:=B+1; write(B) T2:read(B) read(A) if B=0 then A:=A+1; write(A) Add lock and unlock instructions to transactions T1 and T2,so that they observe the two-phase locking protocol. Can the execution of these transactions result in a deadlock? If it can result in deadlock, please give an example schedule which can result in deadlock. T1: Lock_s(A) Read(A) Lock_x(B) Read(B) If A=0 then B:=B+1; Write(B) Unlock(A) Unlock(B) T2: Lock_s(B) Read(B) Lock_x(A) Read(A) T1T2 T3T4 T5 If B=0 then A:=A+1; Write(A) Unlock(A) Unlock(B) Yes, it can result in deadlock. For example, T1: T2: Lock_s(A) Read(A) Lock_s(B) Read(B) Lock_x(B) Read(B) Lock_x(A) Read(A) 4. Compute: (2 for each issue, 10 in all) R W P Q T Y 2 9 2 9 7 7 b a b a g g c e e d e c d f f e f d D T Y C E d f (1) R1=?P,Q(R) P Q 2 9 7 b a g (2)R2=?P7?T=d(R) ? ? ? ? ? ? ? ? ? ? (3)R3=RD T Y B c c d d d f m n n P Q T Y 9 a d e P Q T Y 2 9 2 7 7 b a b g g c e e e c d f f f d (4)R4=? 2,1 ,6 (?3=5?R?D) ? ? ? ? ? ? (5)R5=R? P Q 2 7 b g 5. Programming:use SQL language to realize (10 for each issue, 20 in all) (1) student course ssn name address enrollment a) Find all the students who take at least one course selected by student 00005 select * from Students where ssn in (select ssn from Enrollment where course_no in (select Course_no from Enrollment where SSN =00005 ) b) Find all the students who take all courses: select * from Students as s where not exists (select * from Courses as c where not exists (select * from Enrollment P Q Y b a b g g 2 9 2 7 7 d f f f d Course_noname edit ssn Course_no where SSN = s.SSN and Course_no = c.Course_no) (2) a) Computer sum of salary for each department(table employee as following ). employee emp_no char(5) Not null primary key Employee number emp_name char(10) Not null Employee name sex char(1) Not null Employee sex dept char(4) Not null department birthday datetime Null birthday salary int Not null salary select dept,sum(salary) from employee groupby dept b) Computer average price for each product(table employee as following) sale_item table order_no int Not null, Order number prod_id char(5) Not null, primary key Product number qty int Not null Sales quantity unit_price numeric(9,2) Not null Unit price order_date datetime null Order date select prod_id,avg(unit_price) from employee groupby prod_id
收藏 下载该资源
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号