华南农业大学期末考试试卷（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 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 logicaldesign phase.6.SQL is a relational language that used as both DDL andDML.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.C omposite attributes are not allowed in ER model.11.T he closure of a functional dependency set includes allfunctional dependencies in the set.12.D ependency preservation is necessary to a practicaldecomposition algorithm.13.T he most common type of relationship encountered in datamodeling is ternary relationship.14.A relational schema R is in BCNF if domains of all attributesof R are atomic.15.A tuple variable is a variable that stands for a tuple, in otherwords, 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.T he set intersection operation can be replacing by a pair ofset-difference operations.18.A ny relation that is not part of the logical model, but is madevisible to a user as a virtual relation, is called a view.19.I n SQL, the outer join operation defines how tuples in eachrelation that do not match any tuple in the other relation are treated.20.T he main database design phase includes requirementanalysis, logical-design and coding phase.21.T he concept “relation” in relation model is exactly the sam eas the concept “entity” in ER model.22.A weak entity set cannot participate in relationships like usualentity sets.23. A functional dependency α→β is trivial if β⊆α.24.A rmstrong‟s axioms are used to calculate the closures ofattribute sets.25.O nce a transaction is defined, it must be at active, partiallycommitted, failed, aborted or committed state.26.S erial executions of transaction can improve throughput andresource utilization while add waiting time.27.A schedule is correct if it is conflict equivalent to a serialschedule, that means it is a serializable schedule.28.2-Phase Locking Protocol ensures serializability and canavoid dead lock.29.I n recovery technology, Log must be written before updatingis written into database.30. C heckpoint 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 is associated 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 s A. 1NF ⊂ 3NF ⊂BCNF B. 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_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. 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 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. in2PL 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 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 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)}。