哈尔滨理工大学 2006－2007学年第一学期考试试题 A 卷 软件工程系 出题教师：崔香、刘欢 系主任：考试科目： 数据库系统 考试时间：120分钟 试卷总分100分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 DBS B ．DBS includes DB and DBMS C ．DB includes DBS and DBMS D ．DB is DBS, also as DBMS 2. 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 databaseapplication is called as _d______.A. Query languageB. Query ProgramC. FileD. Transaction4. In database fields, we refer SQL as b________.A. Standard Query LanguageB. Structured Query LanguageC. System Query LanguageD. 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 thesystem, ‘bank tellers’ belong to _d_______ from the following four kinds.A. application programmersB. sophisticated usersC. specialized usersD. naïve users8.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 storageD. 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 afterthe transactions T1 and T2 with the next schedule.2007年01 月11 日哈尔滨理工大学2006－2007学年第一学期考试试题A卷A= 150; B= 120.3.In database systems there are three levels of abstraction. They are physicallevel, ①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 theTWO blanks.① optimizer ② evaluation engineIII、A nswer 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.● Schema definition● Storage structure and access method definition● Schema and physical organization modification● Granting user authority to access the database● Specifying integrity constraints● Acting as liaison with users● 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. b) List the users who still have authorization after only edge l is moved.a) 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?U 1U 5 U 3 U 2DBA l mU 4哈尔滨理工大学2006－2007学年第一学期考试试题A卷1.Is AD a super key?1Does AD →R? == Is (AD)+ ⊇ R2.Is any subset of AD a superkey?1Does A→R? == Is (A)+ ⊇ R2Does D→R? == Is (D)+ ⊇ R5.Given the following relation SCT and two functional dependencies:Is the relation schema in BCNF? Why? If it isn’t, 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 followingqueries: (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 –σbranch_name = “Perryridge” (account )c)Insert information in the database specifying that Smith has $1200 inaccount 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_name char(15),balance numeric(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 loanamounts 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 morethan $1,200.d)Find all loan number which appear in the loan relation with null values foramounta) 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 registrar’s office maintains data about the following entities:哈尔滨理工大学2006－2007学年第一学期考试试题A卷(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 students in 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 registrar’s office. ( 10 marks)(This E-R diagram needn’t model a class meeting at different places atdifferent times; also needn’t guarantee that the database does not have twoclasses meeting at the same place and time.)2.Design a relational database corresponding to the preceding E-R diagramwith 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)。