1、精選優質文檔-傾情為你奉上裝 訂 線班級： 學號： 姓名：考試科目： 數據庫系統 考試時間：120分鐘 試卷總分100分考試班級：軟件06-1、2、3、4、5、6班題號IIIIIIIVV總分得分評卷教師I、Choice questions. (Write your answers onto the answer sheet.)(There are 10 questions, totally 20 marks, and each question worth 2 marks.）1. The relationship among Database (DB), Database System (DB

2、S) and Database Management 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 a file.A. search keyB. structured keyC. sequence key

3、D. select key 3. A _D_ of an entity set is a set of one or more attributes whose values uniquely determine each entity.A. keyB. primary keyC. candidate keyD. super key 4. From the following physical storage media, _D_ is VOLATILE.A. optical storage B. tape storage C. magnetic-disk D. main memory5. C

4、hoose 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

5、a relation R(A, B, C) contains the following data. Which of the description about functional dependency is CORRECT? _B_ABC223234335A. 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

6、right figure shows _B_ parallel database architectures.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 “Find the names of all customers whose street end with the substring %Dajie”A. LIKE %DajieSELECT c

7、ustomer_nameB. LIKE %Dajie%FROM customerC. LIKE %DajieWHERE customer_street _C_D. LIKE %Dajie%9. Suppose that there are two relations R(A, B) and S(B, C). Choose the equivalent relational algebra expression for the following SELECT clause:_C_.A. A , B (sC C56 (R S) SELECT A, BB. A , B (sC C56 (R S)

8、FROM RC. R- A , B (sC = C56 (R S) WHERE B NOT IN( SELECT BD. R- A , B (sC C56 (R S) FROM S WHERE C=C56)10. Choose the only one CORRECT expression about SQL from the followings: _C_.A. ( some) in B. (= all) not inC. EXISTS r r D. UNIQUE r r = II、 Blank-filling questions. (Write your answers onto the

9、answer sheet.) (There are 7 questions and 10 blanks, totally 20 marks, each blank worth 2 marks.)1. To design a trigger mechanism, we must specify the under which the trigger is to be executed; specify the to be taken when the trigger executes. conditions , actions 2. Given two original values A=300

10、, B=150; compute both the values of A and B after the transactions T1 and T2 with 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 TWO blanks.1. parser and translator , execution plan 4. In physical level, the databas

11、e is stored as a collection of files. Each file is a sequence of 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. If we ignore CPU costs and the cost to writing output to disk, the cost is _ 8 _ ms for 20 block trans

12、fers and 10 seeks for simplicity.6. In database system, indexing mechanisms are used to speed up access to desired data.7. In distributed database system, data is spread over multiple machines (also referred to as sites or nodes).III、 Briefly description questions. (Write your answers onto the answe

13、r 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

14、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 CGI, and augmenting of CG H to infer CGI HI,

15、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

16、 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, wher

17、e 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 a

18、n 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 acade

19、mic database contains three relations as the following 3 tables, and then give an expression in SQL for each of the following queries. Table SAttribute nameData typePrimary keyDescriptionSnointStudent numbersSnameChar(20)Student namesAGEintStudent agesSEXChar(4)M for male, F for femaleTable SC Attri

20、bute nameData typePrimary keyDescriptionSnointStudent numbersCnointCourse numbersGradeintStudent grades for coursesTable CAttribute nameData typePrimary keyDescriptionCnointCourse numbersCnameChar(35)Course namesTEACHERChar(20)Teacher names Define the relation SC in SQL.Tip: Describe primary keys, f

21、oreign 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

22、 Sno, SNAMEFROM SWHERE AGE22 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 X.Cno !=Y.Cno Find the names and ages of the male students, each of whom is older than all the female students. SELECT

23、SNAME, AGEFROM SWHERE SEX=M AND AGE ALL (SELECT AGE FROM S WHERE SEX=F) List the course name and average grade for all the courses which are taught by teacher LIU. SELECT CNAME, AVG(GRADE)FROM C, SCWHERE C.C#=SC.C# AND TEACHER=LIUGROUP BY C.C#V、 Designing 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 registrars office maintains data about the following entities:(a) students, including student-id, name, program;.(b) instructors, including id, name, department a