数据库试题-英文卷(共7页).doc

《数据库试题-英文卷(共7页).doc》由会员分享，可在线阅读，更多相关《数据库试题-英文卷(共7页).doc（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精选优质文档-倾情为你奉上装 订 线班级： 学号： 姓名：考试科目： 数据库系统 考试时间：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 Ma

2、nagement 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 3NF3. A collection of operations that

3、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. Sequence Query Language5. Choose the only

4、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 in relation.6. Choose the only one correc

5、t 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 tellers belong to _d_ from the following four kinds.A. application programmers B. sophisticated users C. speciali

6、zed users D. nave 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 kinds of storage media :Tape storage, Flash memory, Optical storage, Magnetic-disk,choose the fastest one from the abo

7、ve 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 customers whose street includes the substring “Main”.select customer_namefrom customerwhere customer_street _c_A. lik

8、e 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 which the trigger is to be executed;Specify the action(s) to be taken when the trigger executes.2. Given two original values A=

9、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. 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

10、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 the TWO blanks. optimizer evaluation engine III、 Answer FOUR briefly from the next five questions. Please mark the question n

11、umbers 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 that, taken collectively, to identify uniquely an entity in the entity set. Candidate keys are minimal superkeys which no proper subs

12、et 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 of a DBA.l Schema definitionl Storage structure and access method definitionl Schema and physical organization modificationl Granti

13、ng user authority to access the databasel Specifying integrity constraintsl Acting as liaison with usersl Monitoring performance and responding to changes in requirements3. Here is an authorization graph. a) List the users who still have authorization after only edge m is moved.U4b) List the users w

14、ho 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 dependencies: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

15、)+ . result = ADresult = ABCD(A C and A B)result = ABCDE(CD E and CD ADBC)result = ABCDEH(CD H and CD 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

16、 functional dependencies:Is the relation schema in BCNF? 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, branc

17、h_city, assets)customer (customer_name, customer_street, 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

18、 the following queries: (3 marks 3=9 marks)a) 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) Inse

19、rt information in the 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 constrains if nec

20、essary.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 all loan number for

21、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 null values for amo

22、unta) 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_numberfrom loanwhere amount is

23、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, sylla

24、bus 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 studentsin each course the are enrolled for must be appropriately modeled. A class meets onl

25、y at one particular place and time. 1. Construct an E-R diagram for the university registrars office. ( 10 marks)(This E-R diagram neednt model a class meeting at different places at different times; also neednt guarantee that the database does not have two classes meeting at the same place and time

26、.)2. Design a relational database corresponding to the preceding E-R diagram with marked primary keys. ( 7 marks)The relational database corresponding to the preceding E-R diagram：student( sid, name, program)course_offering(courseno, secno, year, semester, time, room)Instructor(iid, name, department,title)courses(courseno, title, credits, syllabus)enrolls(sid, secno, grade)teaches( iid, secno)requires(couseno, prerequisite, maincourse)专心-专注-专业