华南农业大学期末考试试卷（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 TRUE and F for FALSE). You will get 1 point for each correct answer, and 0 point for each wrong or blankanswer. BE SURE TO WRITE YOUR ANSWER IN THE ANSWER SHEET!1. Database systems are designed for both defining storage structures and providing mechanisms formanipulation of information.2. Comparing with database system, file-processing system has many disadvantages such like dataredundancy and consistency, concurrent-access anomalies.3. Physical data independence in database means that the user working on the logical level need knownothing 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 databaseat 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 isthe 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 thatrelation 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, iscalled a view.19. In SQL, the outer join operation defines how tuples in each relation that do not match any tuple in theother 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 ERmodel.22. A weak entity set cannot participate in relationships like usual entity sets.23. A functional dependency a—0 is trivial if pea.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 or committedstate.26.Serial executions of transaction can improve throughput and resource utilization while add waitingtime.27. A schedule is correct if it is conflict equivalent to a serial schedule, that means it is a serializableschedule.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 WRITE1.Assume R(a, b) and S(b, c) are two relations.Q1: = D,(R S), and Q2: = n ac(R x S)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 in3.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 analysisB. logical designC. conceptual designD. 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 f D, then is wrong. A. AC -BDB. AC -RC. AD -BD. AD f BC8. Let R(A,B,C,D,E) be a relation schema, from the functional dependency set F={A f 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 RD. none of the above9. Relation among Normal Form is ___________ A. 1NFu 3NF uBCNF B. 3NFu 1NF uBCNF C. BCNFu 3NF u1NF D. 1NFu BCNF u3NF10. 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 customer where customer_streetA. like 'Main '%'B. like …_Main_'C. like …\Main_'D. like …%Main%'11. Suppose the relation R(A,B,C) has the following four tuples:Which of the following functional dependencies does not hold in R?A. A->BB. BC->AC. C->BD. AB->A12. The main purpose of transaction management is to deal with _________ p roblem(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. redone C. deleted D. Neither A or B14. If a transaction T i has obtain an shared lock on data item A, then transition T j cannotA. 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. Aborted Question 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 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 f BD, BC f E, B f D, D f 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 ）得分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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 得分 T F T T F T T F T F T F F F T 题号16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 得分FTTTFFFTFTFTFTTQuestion 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,得分题号 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 得分B C A A B D D A C D B D B A BQuestion 2 single-choice question (30 points, and 2 for each) BESURE TO WRITE YOUR ANSWER IN THE ANSWER SHEET!2015-2016学年第1学期考试科目：Database systemQuestion 1: true-false question (30 points) For each of the followingstatements, indicate whether it is TRUE or FALSE (Using T for TRUEloan_number branch_name=' Perryridge branch' and amount>1000 ( loan)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 $1000 in SQL and relational algebra. [4 points]Select loan_number from loan where branch_name=' Perryridge branch' andamount>10002)Find all customers who have both a loan and an account using relational algebra expression. [3 points](borrower IV| depositor) customer_name1. ER diagram as follows:3)Find name of all customers who have at least one loan that the values for amountis 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] (ANSWERIN NOT UNIQUE )No,decompose it into {R1(AB), R2(BCE),r3(BD),R4(DA)}。
