2015数据库系统期末试题及答案

华南农业大学期末考试试卷（A 卷）2015-2016学年第一学期 考试科目： 数据库系统 考试类型：闭卷 考试时间： 120 分钟 学号 姓名 年级专业Question 1: true-false question (30 points) For each of the followingstatements, indicate whether it is TRUE or FALSE (Using T for TRUEand 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 mechanismsfor manipulation of information.2. Comparing with database system, file-processing system has many disadvantages such likedata redundancy and consistency, concurrent-access anomalies.3. Physical data independence in database means that the user working on the logical level needknow 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 thedatabase 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 whosedomain 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 tuplein 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 virtualrelation, is called a view.19. In SQL, the outer join operation defines how tuples in each relation that do not match anytuple in the other relation are treated.20. The main database design phase includes requirement analysis, logical-design and codingphase.21. The concept “relation” in relation model is exactly the same as the concept “entity ” in ERmodel.22. A weak entity set cannot participate in relationships like usual entity sets.23. A functional dependency α→β is trivial if β ⊆ α.24. Armstrong‟s 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 orcommitted state.26. Serial executions of transaction can improve throughput and resource utilization while addwaiting time.27. A schedule is correct if it is conflict equivalent to a serial schedule, that means it is aserializable 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: =()S R c a ,∏, and Q2: =)(,S R c a ⨯∏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) ≡ inB. (= all) ≡ not inC. (<> all) ≡ not inD. (= some) ≡ not in 3. Let R(x) be the schema of relation R.Q1: SELECT DISTINCT(x) FROM R rrWHERE NOT EXISTS(SELECT * FROM R WHERE x>rr.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 isassociated with ____ entity(entities) of S.A. oneB. at most oneC. at least oneD. none of A,B,C is correct7. Let R(A,B,C,D) be a relation schema, and A →B, C →D, then_____ is wrong. A. AC →BD B. AC →R C. AD →B D. AD →BC8. Let R(A,B,C,D,E) be a relation schema, from the functional dependency set F={A →B, BC →D, C →E}, we can infer that_____. A. AC is a candidate key of R B. BC is a candidate key of R C. CD is a super key of R D. none of the above 9. Relation among Normal Form i sA. 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 nam es of all customers whose street includes the substring “Main”.select customer_name from customer where customer_street __________ A. like …Main \%‟ B. like …_Main_‟ C. like …\Main_‟ D. like …%Main%‟Which 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 <T i start> and <T i commit> has appeared in the log after the nearest checkpoint to system crash, then transaction T i must be ______ when system recovery. A. undone B. redoneC. deletedD. Neither A or B14. If a transaction T i has obtain an shared lock on data item A, then transition T j cannot ______.A. obtain an exclusive lock on data item AB. obtain a shared lock on data item BC. obtain an exclusive lock on data item BD. 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 phaseB. Shrinking phaseC. CommittedD. AbortedQuestion 3 (13 points) Consider the following database requirement:A laboratory has properties like ID, name, location, rank. A researcher hasproperties 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 salaryneeds 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 points]Question 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 points]2) Find all customers who have both a loan and an account using relational algebra expression . [3 points]3) Find name of all customers who have at least one loan that the values for amount is null. In SQL [3 points]4) Find the names of all branches where the average account balance is more than $1000 in SQL . [3 points]5) Find all customers that live in the same city with branches they open accounts in SQL. [3 points]Question 5 (11 points) Consider a relation R(A, B, C, D, E) with the set of Functional DependenciesF = { A → BD, BC → E, B → D, D → A }1) Give all candidate keys of R. [4 points] 2) Give a canonical cover of F. [3 points]3) 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 followingstatements, indicate whether it is TRUE or FALSE (Using T for TRUEand 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!Question 2 single-choice question (30 points, and 2 for each)BE SURE TO WRITE YOUR ANSWER IN THE ANSWER SHEET!Question 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 points] Laboratory (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 points] Create table participate (proj_id int,lab_id int, proj_lab_budget real(10,2),proj_lab_year intprimary key (proj_id, lab_id),foreign key proj_id reference project(id),foreign key lab_id reference laboratory(id))Question 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 $1000in SQL and relational algebra. [4 points]Select loan_number from loan where branch_name=‟Perryridge branch‟ and amount>1000∏ loan_numberσbranch_name=‟ Perryridge branch‟ and amount>1000 ( loan)2)Find all customers who have both a loan and an account using relational algebra expression. [3 points]customer_name ( borrower d epositor)3)Find name of all customers who have at least one loan that the values for amount is null. In SQL [3 points]Select distinct customer_name from loan where amount is null4)Find the names of all branches where the average account balance is more than $1000 in SQL. [3 points]Select branch_name from account group by branch_name having avg(balance)>10005) Find all customers that live in the same city with branches they open accounts in SQL. [3 points]Select customer_name, customer_city from ((customer natural join depositor) natural join account) natural join branch where customer_city= branch_cityQuestion 5 (11 points) Consider a relation R(A, B, C, D, E) with the set of Functional DependenciesF = { A →BD, BC →E, B →D, D →A }1)Give all candidate keys of R. [4 points]AC，BC, DC2)Give a canonical cover of F. [3 points] (ANSWER IN NOT UNIQUE )F c = { A →B, BC →E, B →D, D →A }3)Is R in 3NF? explain why if it is or decompose it into 3NF if not. [4 points](ANSWER IN NOT UNIQUE )No,decompose it into {R1(AB), R2(BCE),r3(BD),R4(DA)}。