2012年数据库系统试卷(B)

华南农业大学期末考试试卷（B卷）2012学年第一学期 考试科目： 数据库系统 考试类型：闭卷 考试时间：120分钟学号 姓名 年级专业 题号一二三四五六总分得分评阅人 Instructions to candidates:1.Write your name, student number and class on both the question papers and the answer papers.2.DO NOT write your answers on the question papers. Write them ALL ON THE ANSWER PAPERS. 3. Write your answers in either Chinese or English. If the answer in English is correct, you can get bonus marks.3.Hand in all papers (both the question papers and the answer papers). Question 1 45 marks, 1.5 marks for each.: Single choice questions，select the most appropriate answer please.1.5CM1. A data model consists of A. schema B. set of operations C. set of constraints D. all of A, B, and C2. The Entity-Relationship data model is A. DBMS dependent. B. DBMS independent. C. both A and B D. neither A nor B3. A key in a database table is A. minimal superkey B. a proper superset of a candidate key. C. proper subset of a candidate key D. neither a.) nor b.)4. _ operation(s) on relation tables is/are implementation of the relationships of conceptual schema database in entity-relationship model. A. Select operation B. Cartesian product C. Projection D. Natural join5. A database is a data collection without the following additional feature A. database contain both data and metadata, the latter being descriptive data about the raw data.B. It provides a generic mechanism for storing relationships among the data items.C. It provides a means to constrain that data to meaningful values as determined by the application.D. It provides a uniform service interface for data maintenance and query activity, such as SQL in the case of the relational model.6. A foreign key can make reference to A. an attribute that is a primary key of another table.B. an attribute that is a primary key of the same table.C. both a.) and b.) D. neither a.) nor b.).7. is a minimal set of attributes for which it holds that: no two distinct tuples in the relation can have the same values for the attributes in this set. A. Candidate key B. SuperkeyC. Foreign key D. Primary key8. The higher normalized database schema implies A. more decomposed database tables.B. more join operations when data from these decomposed tables are combined.C. slower processing for queries involved the joining these decomposed tables.D. all of A, B, and C.9. The lossy-join means A. loss of correct tuples during the join operation. B. loss of correct information.C. loss of spurious tuples during the join operation. D. loss of join path.10. If there is a one-to-one relationship between entity A and B, then A. there exists a functional dependency from the primary key in B to the primary key in A, i.e., PK(B)PK(A).B. there exists a functional dependency from the primary key in A to the primary key in B, i.e., PK(A)PK(B).C. both A and B. D. neither A nor B11. In the context of the Relational model of data, relation means: A. a table consisting of a set of rows (or tuples) that have the same attributes.B. a table that can have sub-tables used to store information about components.C. structure of data in relational databaseD. table used to store information of entity12. Which of the following is not correct?A. database instance is a snapshot of database B. Relation schema is logical design of the whole databaseC. Relation schema is in corresponding to type definition in programming language.D. A relation schema can have numerous instances.13. Physical data independence means A. Ensures that conceptual schema does not have to change when physical storage is reorganized B. A framework for facts C. Separation of logical and external levels D. Conceptual unit of manipulation 14. Stored Procedures A. Can be executed on database server or application server.B. is a subroutine available to applications that access a relational database systemC. is just the same as Stored Function D. A Stored Procedure is a transaction that must be treated as a whole.15. Neither of these transactions can ever proceed with its normal execution. This situation is called A. Deadlock B. Starvation C. Waiting D. Aborted16. In the follows, is correctA. Schema normalization not only reduces potential data redundancy but also enhances query efficiency. B. A schema in 3NF will be in BCNF as well.C. If A B and C D hold, then AC BD also holds.D. If A C and BC D hold, then AC BD also holds.17. In the follows, is correctA. For any SQL query, there exists a unique translation into relation algebra.B. In SQL, a view can be used like a stored relation in any operations.C. In SQL, the value NULL is ignored in any expression.D. SQL is a declarative query language, in which only what we want must be declared, in formulating a query.18. Among the following sentences about transaction, is wrong.A. Operations in a Trigger and operation arouses the trigger is in one transaction B. assertions must be, in principle, checked any time when data is modifiedC. triggers allow user to specify when such checking should occur.D. A call of a Stored Procedure must be called in one transaction19. Among the following sentences about transaction state, is wrong.A. Partially commit means transaction is finished but had not been committed.B. committed means the effect of transaction is permanent to databaseC. When a transaction is in state of Failed, it can be redo as the same transactionD. Each transaction is in Active State after has been created20. Index belong to A. inner schema B. Logical SchemaC. Concept Schema D. User Schema21. Techniques for implementing integrity constraints are A. Declarative Integrity constraintsB. Database TriggersC.Data definition LanguageD. Both A and B22. When a E-R diagram is transform into Relational schema, Both entity and relationship can be expressed as A. Attribute B. RelationC. Key D. Domain23. The CHECK keyword in SQL is used for the purpose of _A. data redundancy B. query efficiency C. reference integrity D. data constraint24. The _ keyword in SQL is not used for data constraintA. NOT NULL B. UNIQUE C. HAVING D. FOREIGN KEY25 If the buffer pool is large enough that uncommited data are never forced to disk, of the following is correct.A. REDO operation is necessary B. REDO operation is unnecessaryC. UNDO operation is necessaryD. Both REDO and UNDO are not necessary26. Checkpoint is a technique that can reduce recovery time after a crash. of the follows is correct.A. After a soft crash (which does not affect data on hard drives), the log only needs to be scanned back until the last checkpoint is found. The log beyond the last checkpoint will not be read during the recovery process.B. Once a checkpoint is done, the log must be truncated.C. Checkpoint is automatically performed after every transaction commit.D. Checkpoints should be done after every update to the database.27. If a course can be taught by many teachers, and a teacher can teach only one course, then the mapping cardinality from course to teacher isA. one-to-one B. one-to-many C. many-to-one D. many-to-many28. If a functional dependency A, B R holds on relation R(A, B, C), then (A, B) is a _ of R.A. super key B. primary key C. candidate key D. foreign key29. A relational schema R is in _ if the domains of all attributes of R are atomicA. 1NF B. 3NF C. BCNF D. 4NF30. Which one of the following statement is true?A. 3NF is more strict than BCNF B. 4NF is more strict than BCNFC. 1NF is more strict than BCNF D. BCNF is the most strict normal form1.5CMQuestion 2 12 marks: Translate the following E-R diagram into a set of relation schemas. Select a primary key for each relation (this can be indicated by underlining the selected attributes) and indicate foreign key for each relation as follows: member_of (mno, cno, balance) - FK mno reference member - FK cno reference cinema1.5CMQuestion 3 8 marks: Write relational algebra expressions to satisfy query requirements in the following database. (1) Find the exercise number and points of Chapter 1. 2 marks(2) Find the full name of the student who has the highest point of all the exercises. 2 marks(3) Find all exercise points related to the topic SQL. 2 marks(4) Write out the result of the relational algebra expression 2 marks1.5CMQuestion 4 12 marks: Consider the following relational schema:Write the following queries in SQL.(1) Retrieve the name, ID, and GPA of all students in the ICS Department with GPA 3.0 or above. 3 marks(2) Retrieve the number of advisees (students advised) for each faculty in the ICS Department. Sort the result by the number of advisees in descending order. 3 marks(3) Retrieve the name, ID, and GPA of a