《江西财经大学数据库库系统及应用（英文）期末试题B答案》由会员分享，可在线阅读，更多相关《江西财经大学数据库库系统及应用（英文）期末试题B答案（6页珍藏版）》请在金锄头文库上搜索。
1、考试复习重点资料（最新版）考试复习重点资料（最新版） 封封 面面 第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 tha
2、t 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. (
3、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, 2
4、0 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
5、, 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
6、. 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-p
7、hase 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) Re
8、ad(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
9、 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 i
10、n 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 t
11、ake 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(t
12、able 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 grou
13、pby 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
