《数据库试题-英文卷.doc》由会员分享，可在线阅读，更多相关《数据库试题-英文卷.doc（35页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-date数据库试题-英文卷装 订 线班级： 学号： 姓名：考试科目： 数据库系统 考试时间：120分钟 试卷总分100分考试班级：软件05-1、2、3、4、5、6班题号IIIIIIIVV总分得分评卷教师I、Choice Questions（choose ONE corresponding description from four choices for the follow
2、ing terms） ( 2 marks 10 = 20 marks）1. The relationship among Database(DB), Database System(DBS) and Database Management 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
3、normal forms is _c_.A. 1NF 2NF 3NFB. 3NF 1NF 2NFC. 3NF 2NF 1NFD. 2NF 1NF 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 Qu
4、ery Language B. Structured Query LanguageC. System Query Language D. 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 d
5、atabase must be a member of some domain.D. Duplicate tuples can exist 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 te
6、llers belong to _d_ from the following four kinds.A. application programmers B. sophisticated users C. specialized 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 o
7、f 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 custom
8、ers 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 which the trigge
9、r 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. They are ph
10、ysical 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 the TWO blanks
11、. 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 that, taken collective
12、ly, 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 of a DBA.l Schema de
13、finitionl 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 requirements3. Here is an autho
14、rization 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 dependencies:R = (A, B, C,
15、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 ADBCE)Is AD a candidate key? 1. Is AD a super key?1 Does AD R? = Is (AD)+ R2. Is any su
16、bset 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 BCNF? Why? If it isnt, decompose it into BCNF.IV、 Complete the following queries. (29marks)Consider the relational database of a banking e
17、nterprise with the following relation schemas, where the primary keys are underlined.branch (branch_name, branch_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, bala
18、nce)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) Find the names of all customers who have a loan, an account, or both, from the banka)customer_name (borrower) customer_name (depositor)b) Delet
19、e all account records in the Perryridge branch.b)account account s branch_name = “Perryridge” (account )c) Insert 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. De
20、fine the relation account in SQL. (4 marks)Tip: Describe primary keys, foreign keys and check constrains 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. G
21、ive an expression in SQL for each of the following queries. (4 marks 4=16 marks)a) To find 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
22、 balance is more than $1,200.d) Find all loan number which appear in the loan relation with 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
23、(balance)from accountgroup by branch_namehaving avg (balance) 1200d)select loan_numberfrom 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;.(
24、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
25、 grades awarded to studentsin each course the are enrolled for must be appropriately modeled. A class meets only 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 differ
26、ent times; also neednt guarantee that the database does not have two classes meeting at the same place and time.)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)-