數(shù)據(jù)庫試題英文卷

1、裝 訂 線班級： 學(xué)號： 姓名：考試科目： 數(shù)據(jù)庫系統(tǒng) 考試時間：120分鐘 試卷總分100分考試班級：軟件05-1、2、3、4、5、6班題號IIIIIIIVV總分得分評卷教師I、Choice Questions（choose ONE corresponding description from four choices for the following terms） ( 2 marks × 10 = 20 marks）1. The relationship among Database(DB), Database System(DBS) and Database Manageme

2、nt System(DBMS) is _b_.A. DBMS includes DB and DBSBDBS includes DB and DBMS CDB includes DBS and DBMSDDB is DBS, also as DBMS2. In relation schemas, the relation ship among different normal forms is _c_.A. 1NFÌ 2NFÌ 3NFB. 3NFÌ 1NFÌ 2NFC. 3NFÌ 2NFÌ 1NFD. 2NFÌ 1NF

3、04; 3NF3. A collection of operations that performs a single logical function in a database application is called as _d_.A. Query languageB. Query ProgramC. File D. Transaction4. In database fields, we refer SQL as b_.A. Standard Query Language B. Structured Query LanguageC. System Query Language D.

4、Sequence Query Language5. Choose the only one incorrect description from the followings: _d_A. Neither tuples nor attributes have order.B. Attributes can appear in any order and the relation is still the same.C. Each value in the database must be a member of some domain.D. Duplicate tuples can exist

5、 in relation.6. Choose the only one correct expression from the followings: _c_.A. (¹ some) º in B. (= all) º not inC. exists r Û r ¹ ØD. X Y ¹ Ø Û X Í Y7. Database users are differentiated by the way they expect to interact with the system, bank tel

6、lers belong to _d_ from the following four kinds.A. application programmers B. sophisticated users C. specialized users D. naïve users 8. The following figure shows _d_ parallel database architectures.A. Shared memory B. Shared disk C. Shared nothing D. Hierarchical 9. Consider the following ki

7、nds of storage media :Tape storage, Flash memory, Optical storage, Magnetic-disk,choose the fastest one from the above four kinds of media: _b_.A. Tape storageB. Flash memoryC. Optical storage D. Magnetic-disk10. Choose the proper choice to make the following query to realize:Find the names of all c

8、ustomers whose street includes the substring “Main”.select customer_namefrom customerwhere customer_street _c_A. like Main% B. like _Main_C. like %Main%D. like Main_II、 Blank-filling Questions.（2 marks × 9 = 18 marks）1. To design a trigger mechanism, we must: Specify the condition(s) under whic

9、h the trigger is to be executed;Specify the action(s) to be taken when the trigger executes.2. Given two original values A=200, B=100; compute the values of A and B after the transactions T1 and T2 with the next schedule. A= 150 ; B= 120 .3. In database systems there are three levels of abstraction.

10、 They are physical level, logical level and view level.Given 0.2ms as time to transfer one block and 0.1ms as time for one seek. If we ignore CPU costs, the cost is 4ms for 15 block transfers and 10 seeks for simplicity.4. The basic query process has been list in the following figure, please fill th

11、e TWO blanks. optimizer evaluation engine III、 Answer FOUR briefly from the next five questions. Please mark the question numbers clearly. ( 4marks × 4 = 16 marks)1. Explain the differences among the terms superkey, candidate key and primary key?A superkey is a set of one or more attributes tha

12、t, taken collectively, to identify uniquely an entity in the entity set. Candidate keys are minimal superkeys which no proper subset is a superkey. Primary key is a candidate key that is chosen as the principal means of identifying entities within an entity set.2. List at least FOUR main functions o

13、f a DBA.l Schema definitionl Storage structure and access method definitionl Schema and physical organization modificationl Granting user authority to access the databasel Specifying integrity constraintsl Acting as liaison with usersl Monitoring performance and responding to changes in requirements

14、3. Here is an authorization graph. a) List the users who still have authorization after only edge m is moved.U4b) List the users who still have authorization after only edge l is moved.U1U5U3U2DBAlma) U2, U3 and U5;b)U1, U3, U4 and U5.4. Let R be a relation scheme with a set F of functional dependen

15、cies:R = (A, B, C, D, E, H),F = A ® B, B ® E , A ® C, CD ® E, CD ®H . Is AD a candidate key? Please compute the closure of AD under F , (AD)+ . result = ADresult = ABCD(A ® C and A ® B)result = ABCDE(CD ® E and CD Í ADBC)result = ABCDEH(CD ® H and CD

16、 Í ADBCE)Is AD a candidate key? 1. Is AD a super key?1 Does AD ® R? = Is (AD)+ Ê R2. Is any subset of AD a superkey?1 Does A ® R? = Is (A)+ Ê R2 Does D ® R? = Is (D)+ Ê R5. Given the following relation SCT and two functional dependencies:Is the relation schema in B

17、CNF? Why? If it isnt, decompose it into BCNF.IV、 Complete the following queries. (29marks)Consider the relational database of a banking enterprise with the following relation schemas, where the primary keys are underlined.branch (branch_name, branch_city, assets)customer (customer_name, customer_str

18、eet, customer_city)loan (loan_number, branch_name, amount)borrower (customer_name, loan_number)account (account_number, branch_name, balance)depositor (customer_name, account_number)1. Give an expression in the relational algebra to express each of the following queries: (3 marks × 3=9 marks)a)

19、 Find the names of all customers who have a loan, an account, or both, from the banka)Õcustomer_name (borrower) È Õcustomer_name (depositor)b) Delete all account records in the Perryridge branch.b)account ¬ account s branch_name = “Perryridge” (account )c) Insert information in t

20、he database specifying that Smith has $1200 in account A-973 at the Perryridge branch.account ¬ account È (“Perryridge”, A-973, 1200)depositor ¬ depositor È (“Smith”, A-973)2. Define the relation account in SQL. (4 marks)Tip: Describe primary keys, foreign keys and check constrai

21、ns if necessary.create table account( account_number char(10),branch_namechar(15),balancenumeric(12,2),primary key (account_number),foreign key (branch_name) references branch,check (balance >=0)3. Give an expression in SQL for each of the following queries. (4 marks × 4=16 marks)a) To find

22、all loan number for loans made at the Perryridge branch with loan amounts greater than $1200.b) Find all customers who have both a loan and an accountc) Find the names of all branches where the average account balance is more than $1,200.d) Find all loan number which appear in the loan relation with

23、 null values for amounta) select loan_numberfrom loanwhere branch_name = Perryridge and amount > 1200b)(select customer_name from depositor)intersect(select customer_name from borrower)c)select branch_name, avg (balance)from accountgroup by branch_namehaving avg (balance) > 1200d)select loan_n

24、umberfrom loanwhere amount is nullV、 Resolve the following questions of designing. (17marks)A university registrars office maintains data about the following entities:(a) students, including student-id, name, program;.(b) instructors, including id, name, department and title.(c) courses, including c-number, title, credits, syllabus and prerequisites;(d) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom;Further, the enrollment of students in courses and grades awarded to stude