2016华南农业大学数据库考试+答案.doc

装订线华南农业大学期末考试试卷（A卷）2015-2016学年第一学期 考试科目： 数据库系统 考试类型：闭卷 考试时间：120 分钟学号 姓名 年级专业 题号一二三四五总分得分评阅人得分Question 1: true-false question (30 points) For each of the following statements, indicate whether it is TRUE or FALSE (Using T for TRUE and F for FALSE). You will get 1 point for each correct answer, and 0 point for each wrong or blank answer. BE SURE TO WRITE YOUR ANSWER IN THE ANSWER SHEET!1. Database systems are designed for both defining storage structures and providing mechanisms for manipulation of information. 2. Comparing with database system, file-processing system has many disadvantages such like data redundancy and consistency, concurrent-access anomalies. 3. Physical data independence in database means that the user working on the logical level need know nothing about complexity of the physical level of database.4. The overall design of database is the database schema, while collection of data stored in the database at a particular moment is an instance of the database. 5. The Entity-Relationship data model is often used in logical design phase. 6. SQL is a relational language that used as both DDL and DML. 7. Metadata is data about data that are stored in data dictionary. 8. A tuple is also known as a column in database table. 9. A relationship in an ER model can has its own attributes. 10. Composite attributes are not allowed in ER model. 11. The closure of a functional dependency set includes all functional dependencies in the set. 12. Dependency preservation is necessary to a practical decomposition algorithm. 13. The most common type of relationship encountered in data modeling is ternary relationship. 14. A relational schema R is in BCNF if domains of all attributes of R are atomic. 15. A tuple variable is a variable that stands for a tuple, in other words, it is a variable whose domain is the set of all tuples. 16. A foreign key of a relation is a set of attributes (one or more) that allow us to identify a tuple in that relation uniquely. 17. The set intersection operation can be replacing by a pair of set-difference operations. 18. Any relation that is not part of the logical model, but is made visible to a user as a virtual relation, is called a view. 19. In SQL, the outer join operation defines how tuples in each relation that do not match any tuple in the other relation are treated. 20. The main database design phase includes requirement analysis, logical-design and coding phase. 21. The concept “relation” in relation model is exactly the same as the concept “entity” in ER model. 22. A weak entity set cannot participate in relationships like usual entity sets. 23. A functional dependency ab is trivial if b a. 24. Armstrongs axioms are used to calculate the closures of attribute sets. 25. Once a transaction is defined, it must be at active, partially committed, failed, aborted or committed state. 26. Serial executions of transaction can improve throughput and resource utilization while add waiting time. 27. A schedule is correct if it is conflict equivalent to a serial schedule, that means it is a serializable schedule.28. 2-Phase Locking Protocol ensures serializability and can avoid dead lock. 29. In recovery technology, Log must be written before updating is written into database. 30. Checkpoint is defined to simplify recovery when a system corruption is occurred.Question 2 single-choice question (2 points for each, and 30 points in total) 得分BE SURE TO WRITE YOUR ANSWER IN HE ANSWER SHEET!1. Assume R(a, b) and S(b, c) are two relations.Q1: =, and Q2: =Which of the followings is correct?A. Q1 and Q2 produce the same answer.B. The answer to Q1 is always contained in the answer to Q2.C. The answer to Q2 is always contained in the answer to Q1.D. Q1 and Q2 produce completely different answers.2. Choose the only one correct expression from the followings: _.A. ( some) in B. (= all) not in C. ( all) not inD. (= some) not in3. Let R(x) be the schema of relation R.Q1: SELECT DISTINCT(x) FROM R rr WHERE NOT EXISTS(SELECT * FROM R WHERE xrr.x);Q2: SELECT MAX(x) FROM R; Then _ is correct.A.Q1 and Q2 produce the same answer. B. The answer to Q1 is always contained in the answer to Q2. C. The answer to Q2 is always contained in the answer to Q1. D. Q1 and Q2 produce different answers.4. In SQL, a DELETE statement without a WHERE clause:A. delete all data in the table. B. drop the table.C. delete one column in a table. D. Results in a Cartesian product.5. Mapping an E-R diagram into relation schemas can happen in the phase of _.A. requirement analysis B. logical design C. conceptual design D. physical design6. If the mapping cardinality from entity set S to entity set T is m-n, then an entity of T is associated with _ entity(entities) of S.A. one B. at most one C. at least one D. none of A,B,C is correct7. Let R(A,B,C,D) be a relation schema, and AB, CD, then_ is wrong.A. ACBD B. ACR C. ADB D. ADBC8. Let R(A,B,C,D,E) be a relation schema, from the functional dependency set F=AB, BCD, CE, we can infer that_.A. AC is a candidate key of R B. BC is a candidate key of RC. CD is a super key of R D. none of the above9. Relation among Normal Form is A. 1NF 3NF BCNFB. 3NF 1NF BCNFC. BCNF 3NF 1NFD. 1NF BCNF 3NF10. 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 _A. like Main% B. like _Main_C.like Main_D. like %Main%11. Suppose the relation R(A,B,C) has the following four tuples:ABC312412314414Which of the following functional dependencies does not hold in R?A. A-B B. BC-A C. C-B D. AB-A12. The main purpose of transaction management is to deal with _ problem(s).A. Concurrency B. Failure C. Memory crash D. both A and B13. If and has appeared in the log after the nearest checkpoint to system crash, then transaction Ti must be _ when system recovery.A. undone B. redone C. deleted D. Neither A or B14. If a transaction Ti has obtain an shared lock on data item A, then transition Tj cannot _.A. obtain an exclusive lock on data item A B. obtain a shared lock on data item B C. obtain an exclusive lock on data item B D. obtain an shared lock on data item A15. in 2PL protocol, at stage, A transaction may not obtain locks, but can release locks. A. Growing phase B. Shrinking phase C. CommittedD. AbortedQuestion 3 (13 points) Consider the following database requirement:得分A laboratory has properties like ID, name, location, rank. A researcher has properties like ID, name, age, skill. A research project has properties like ID, name, budget, and year. The above three entities must satisfy some constraints: Each researcher can be unemployed or employed by one laboratory. One laboratory can employ many researchers. And a researcher can only work at a laboratory at a time. If a researcher is employed, his salary needs to be recorded in the database. A laboratory can participate many projects and a project can be shared by many laboratories.(1) Draw ER diagram to illustrate the requirements. And you can make and state necessary assumptions if any. 6 points(2) Translate your ER diagram into relational database schemas, underline primary keys of each relation, and label foreign keys if it has. Be sure using different way to mark primary keys and foreign keys. 4 points(3) write an sql sentence to create table to store information about which project has been shared by which labs. 3 pointsQuestion 4. (16 points) 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 SQL or in relational algebra expression for each of the following queries. 1) To find all loan number for loans made at the Perryridge branch (that is the name of a certain branch) with loan amounts greater than $1000 in SQL and relational algebra. 4 points2) Find all customers who have both a loan and an account using relational algebra expression. 3 points3) Find name of all customers who have at least one loan that the values for amount is null. In SQL 3 points4) Find the names of all branches where the average account balance is more than $1000 in SQL. 3 points5) Find all customers that live in the same city with branches they open accounts in SQL. 3 pointsQuestion 5 (11 points) Consider a relation R(A, B, C, D, E) with the set of 得分Functional Dependencies F = A BD, BC E, B D, D A 1) Give all candidate keys of R. 4 points2) Give a canonical cover of F. 3 points3) Is R in 3NF? explain why if it is or decompose it into 3NF if not. 4 points华南农业大学期末考试试卷（A卷-Answer Sheets）2015-2016学年第1 学期 考试科目： Database system 考试类型：（闭卷） 考试时间： 120 分钟学号 姓名 年级专业 题号一二三四五总分得分评阅人得分Question 1: true-false question (30 points) For each of the following statements, indicate whether it is TRUE or FALSE (Using T for TRUE and F for FALSE). You will get 1 point for each correct answer, and 0 point for each wrong or blank answer. BE SURE TO WRITE YOUR ANSWER IN THE ANSWER SHEET!题号123456789101112131415得分TFTTFTTF TFTFFF T题号161718192021222324252627282930得分FTTTFF FT F TFTF TT得分Question 2 single-choice question (30 points, and 2 for each)BE SURE TO WRITE YOUR ANSWER IN THE ANSWER SHEET!题号123456789101112131415得分BCAABDDACD BDB ABQuestion 3 (13 points) (ANSWER IN NOT UNIQUE )(1) Draw ER diagram to illustrate the requirements. And you can make and state necessary assumptions if any. 6 points (next page)(2) Translate your ER diagram into relational database schemas, underline primary keys of each relation, and label foreign keys if it has. Be sure using different way to mark primary keys and foreign keys. 4 pointsLaboratory(ID, Name, Location,rank)Researcher(ID, Name, birthday, skill, hiretime, salary, lab_id)project ( ID, name, budget, year)participate(proj_id, lab_id, proj_lab_budget, proj_lab_year) Here primary key is underlined, and foreign key is underlined by wavy line.(3) write an sql sentence to create table to store information about which project has been shared by which labs. 3 pointsCreate table participate(proj_id int, lab_id int, proj_lab_budget real(10,2), proj_lab_year int primary key (proj_id, lab_id),foreign key proj_id reference project(id),foreign key lab_id reference laboratory(id)1. ER diagram as follows:laboratoryparticipateProjectIDNameLocationrankIDNamebudget yearhire hire tinesalaryResearcherIDNameBirthday(Age)skillQuestion 4. (16 points) 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 SQL or in relational algebra expression for each of the following queries. (ANSWER IN NOT UNIQUE )1) To find all loan number for loans made at the Perryridge branch (that is the name of a certain branch) with loan amounts greater than $1000 in SQL and relational algebra. 4 pointsSelect loan_number from loan where