数据库试题-英文卷

哈尔滨理工大学20062007学年第一学期考试试题 A卷装 订 线班级： 学号： 姓名：考试科目： 数据库系统 考试时间：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 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 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 Query 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 database 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 tellers 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 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 customers 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 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. 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 the 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 that, 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 of 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 requirements3. 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 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)+ . 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 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, 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, 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) 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 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 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 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 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_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;.(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 studentsin each course the are enrolled for must be appropriate