考试科目： 数据库系统 考试时间：120分钟 试卷总分100分题号I II III IV V 总分 得分 评卷教师I 、Choice questions. (Write your answers onto the answer sheet.)(There are 10questions, totally 20 marks, and each question worth 2 marks.）1. The relationship among Database (DB), Database System (DBS) and DatabaseManagement System (DBMS) is ___A_____. A. “DBS includes DB and DBMS” B. “DBMS includes DB and DBS” C. “DB includes DBS and DBMS” D. “DB is DBS, also as DBMS”2. In an index, ___A_____ is the attribute to set of attributes used to look up records in afile.A. search keyB. structured keyC. sequence keyD. select key3. A _____D___ of an entity set is a set of one or more attributes whose values uniquelydetermine each entity. A. key B. primary key C. candidate key D. super key4. From the following physical storage media, ____D___ is VOLATILE .A. optical storageB. tape storageC. magnetic-diskD. main memory5. 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 a relation.6. There is a relation R(A, B, C ) contains the following data. Which of the descriptionabout functional dependency is CORRECT? ___B_______ A. Functional dependence A → B holds on R. B. Functional dependence BC → A holds on R. C. Functional dependence B → A holds on R.D. Functional dependence A → BC holds on R.7. The right figure shows ___B_____ parallel databasearchitectures.A. ‘Shared memory’B. ‘Shared disk’C. ‘Shared nothing’D. ‘Hierarchical’8. Choose the proper choice to make the following SQL statement to realize query “Findthe names of all customers whose street end with the substring ‘%Dajie’”A B C 2 2 3 2 3 43 3 5 装订线班级：学号：姓名：A. LIKE ‘\%Dajie’SELECT customer_nameB. LIKE ‘\%Dajie%’FROM customerC. LIKE ‘%\%Dajie’WHERE customer_street ____C______D. LIKE ‘%\Dajie%’9.Suppose that there are two relations R(A, B) and S(B, C). Choose the equivalentrelational algebra expression for the following SELECT clause:_C__.A.∏A , B (σC≠‘C56’ (R S)) SELECT A, BB. ∏A , B (σC ≠ ‘C56’ (R S)) FROM RC. R- ∏A , B (σC = ‘C56’ (R S)) WHERE B NOT IN( SELECT BD. R- ∏A , B (σC ≠ ‘C56’ (R S)) FROM SWHERE C=’C56’)10.Choose the only one CORRECT expression about SQL from the followings:____C___.A. (≠ some) ≡ inB. (= all) ≡ not inC. EXISTS r ⇔r ≠ ØD. UNIQUE r ⇔r = ØII、Blank-filling questions.(Write your answers onto the answer sheet.) (There are7 questions and 10 blanks, totally 20 marks, each blank worth 2 marks.)1.To design a trigger mechanism, we must specify the ①under which thetrigger is to be executed; specify the ②to be taken when the trigger executes.①conditions , ②actions2.Given two original values A=300,B=150; compute both the values of Aand B after the transactions T1T2with the right side schedule.A= ;B= .A=225 ; B= 225 3.The basic query process has been list in the following figure, please fill the TWOblanks.1.①parser and translator , ②execution plan4.In physical level, the database is stored as a collection of files. Each file is a sequenceof records , each of which is a sequence of fields.5.Given 0.3ms as the time to transfer one block and 0.2ms as the time for one seek. Ifwe ignore CPU costs and the cost to writing output to disk, the cost is ___ 8 ______ ms for 20 block transfers and 10 seeks for simplicity.6.In database system, indexing mechanisms are used to speed up accessto desired data.7.In distributed database system, data is spread over multiple machines(also referred to as sites or nodes).III、B riefly description questions. (Write your answers onto the answer sheet.) (There are 4 questions, totally 16 marks, and each question worth 4 marks.）1.Please briefly list FOUR duties of a database administrator.2.Please give the definition and the ACID properties of transaction.3.Given the relation schema R = (A, B, C, G, H, I), and the functional dependency F = {A → B, A → C, CG → H, CG → I, B → H}. Explain whether AG → I and CG → HI are the members of F+? Why?AG →I is a member of F+.By augmenting A →C with G, to get AG →CG and then transitivity with CG →I, we can get AG →I.CG →HI is a member of F+.By augmenting CG →I to infer CG → CG I, and augmenting of CG →H to infer CGI →HI, and then transitivity, we can get CG →HI.4.Given the relation schema R = (A, B, C, G, H, I), and the functional dependency F = {A → B, A → C, CG → H, CG → I, B → H}. Compute (AG)+.The processes of computing result of (AG)+ have been shown below.① result = AG② result = ABCG (A →C and A → B)③ result = ABCGH (CG →H and CG ⊆AGBC)④ result = ABCGHI (CG →I and CG ⊆AGBCH)IV、Query questions. (Write your answers onto the answer sheet.) (There are 2 questions with 7 queries. There are totally 28 marks, each query worth 4 marks.)1.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)Give an expression in the relational algebra to express each of the following two queries:①Find the names of all customers who have a loan, an account, or both, from the bank.②Insert information in the database specifying that Smith has $4500 in account A-911 at the Perryridge branch.2.Consider the academic database contains three relations as the following 3 tables, andthen give an expression in SQL for each of the following queries.①Tip: Describe primary keys, foreign keys and check constrains if necessary. CREATE TABLE SC( Sno int,Cno int,Grade int,PRIMARY KEY (Sno, Cno),FOREIGN KEY (Sno) REFERENCES S,FOREIGN KEY (Cno) REFERENCES C,CHECK (Grade >= 0))②Find the student numbers and names of the male students, each of whom is older than 22-year-old.SELECT Sno, SNAMEFROM SWHERE AGE>22 AND SEX=‘M’;③Find the student numbers of the students, each of whom has at least chosen two courses.SELECT SnoFROM SC AS X, SC AS YWHERE X.Sno=Y.Sno AND o !=o④Find the names and ages of the male students, each of whom is older than all the female students.SELECT SNAME, AGEFROM SWHERE SEX=‘M’ AND AGE> ALL (SELECT AGEFROM SWHERE SEX=‘F’)⑤List the course name and average grade for all the courses which are taught by teacher LIU.SELECT CNAME, A VG(GRADE)FROM C, SCWHERE C.C#=SC.C# AND TEACHER=‘LIU’GROUP BY C.C#V、D esigning questions. (Write your answers onto the answer sheet.) (There are two questions, totally 16 marks. Question 1 worth 10 marks and question 2 worth 6 marks.)A university registrar’s 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 course 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 they are enrolled for must be appropriately modeled.1.Construct an E-R diagram for the university registrar’s office. A class meets only at one particular place and time; y ou needn’t model a class meeting at different places at different times; also n eedn’t guarantee that the database does not have two classes meeting at the same place and time.2.Design a relational database, which is constructed by relation schemas, corresponding to the preceding E-R diagram with marked primary keys.。
