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

1、 C. BCNFc 3NFC1NF D. 1NF(=BCNF c3NFChoose tlie proper choice to make the following query to realize:Find the names of all customers whose street includes the substring “Main”, select ctistomernarnefrom customerwhere customer_stiet A likeB like MainJC. like MainD. like %MainSVSuppose tlie relation R(

2、A，B，C) has the following four tuples:ABC312412314414Which of the following functional dependencies does not hold iii R?A A-BB BC-AC. C-BD AB-AThe main purpose of transaction 直 nan a gement is to deal witli problem(s).A. Concurrency B. FailureC. Memory crash D both A and BIf and has appeared in the l

3、og after the nearest checkpoint to system crash, then transaction Timust be when system recoveiy.A. undone B. redone C. deleted D. Neither A or BIf a transaction Tx has obtain an shared lock on data item A, then transition Tj cannot A obtam an exclusive lock on data item A B. obtaui a shared lock on

4、 data item B C. obtain an exclusive lock on data item B D obtain an shared lock on data item Ain 2PL protocol, at stage, A transaction may not obtain locks, but can release locks.A. Growing phase B Shrinking phase C. Committed D. AbortedQuestion 3 (13 points) Consider the following database requirem

5、ent A laboratory has properties like ID, name, location, rank A researcher has properties like ID, name, ageB 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 laboratoi

6、y One laboratory can employ many researchers And a researcher can only work at a laboratoiy at a time. If a researcher is employed, his salaiy 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 illus

7、trate the requirements. And you can make and state necessary assumptions if any 6 pointsTranslate your ER diagram into relational database schemas, underline primaiy keys of each relation, and label foreign keys if it has. Be sure using different way to mark prunaiy keys and foreign keys 4 pointsv/r

8、ite an sql sentence to create table to store infonnation about which project has been sharedby which labs.【3 pointsQuestion 4. (16 points) Consider the relational database of a banking eiiterpnse with thefollowing relation schemas, where the pnmaiy keys are underlined得分branch (branch_namel branch ci

9、ty, assets)customei* (customer name, customerstreet，customer_city)loan (loan number, branchjiame, amount)boirower (customer name、loan number)account (account number, branch一name, balance) depositor (custonierjiaine、account number)Give an expression in SQL or in relational algebra expression for each

10、 of the following quenes.To find all loan number for loans made at the Penyridge branch (that is the name of a ceitain branch) with loan amounts greater than $1000 in SQL and relational algebra 4 pointsFind all customers v/ho have both a loan and an account using relational algebra expression 3 poin

11、tsFind name of all customers who have at least one loan that the values for amount is null In SQL 3 pointsFind the names of all branches v/here the average account balance is more than $1000 in SQL 3 pointsFind all customers that live in the same city with branches they open accounts in SQL. 3 point

12、s得分Question 5 (11 points) Considei a relation R(A, B, C, D, E) with the set of Functional DependenciesF = A BD.BC D.D A 1Give all candidate keys of R 4 points12)3)Give a canonical cover ofF. 3 points2)3)Is R in 3NF? explain why if it is or decompose it nito 3NF if not 4 points题号二三四五总分得分评阅人订 题号二三四五总分

13、得分评阅人订 Question 1： true-false question (30 points) For each of tlie following statements, indicate wlietlier it is TRUE or FALSE (UsuigT for TRUE得分and F for FALSE). You will get 1 point for each correct answer, and 0 point for each wrong or题号123456789101112131415得分TFTTFTTFTFTFFFT题号161718192021222324

14、252627282930得分FTTTFFFTFTFTFTTblank 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!得分题号123456789101112131415得分BCAABDDACDBDBAB华南农业大学期末考试试卷(A卷-Answer Sheets)2015-2016学年第1学期考试科目：Databa

15、se system考试类型：(闭卷)考试时间： 120 分钟学号 姓名 年级专业 Question 3 (13 points) (ANSWER IN NOT UNIQUE )Draw ER diagram to illustiate the reqiiiiements. And you can make and state necessary assumptions if any. |6 points (next page)Translate your ER diagram into relational database schemas, underline primary keys of

16、each relation, and label foreign keys if it has. Be sure using diffeieut way to mark piimaiy keys and foreign keys. 4 pointsL ab oratory (ID，N am e，Locati on,rank)Research er (ID，Name，birthday, skill，liiretime, salary，lab icf) project (ro, name, budget, year)paiticlpate(proj id. lab id, prqj lab bud

17、get，piqj Jab_year) Here pnmaiy key is underlined, and foreign key is underlined by wavy linewrite an sql sentence to create table to store infonnation about which project has been shared by wliich labs. 3 pointsCreate table paiticipatefproi idiiit，lab id int，proj lab budget real(10,2)， prqj_lab_yeai

18、 intprimary key (proj id. lab id)， foreign key prqjd reference project(id), foreign key lab id reference laboiatoiy(id)1. ER (Uagram as follows:Question 4. (16 points) Consider the relational database of a banking enterprise with the following relation schemas, where the primary keys are underlined,

19、 branch (blanch name, branch city, assets)customei (customer name，customer street, custonier city) loan (loau number, branch_name，amount)bonower (customer name，1 oaii number) account (accountiiuiibei、branch nanie, balance)depositor (customer name, account nuniber)Give ail expression in SQL oi in rel

20、ational algebra expiessiou for each of the following queries. (ANSWER IN NOT UNIQUE )To find all loan number for loans made at the Penyiidge branch (that is the name of a ceitain branch) with loan amounts gieater than $1000 in SQL and relational algebra. 4 pointsSelect loanjmmber from loan where bra

21、nch_name= Penyridge branch and amount1000II loan_nuinber bxaxKh_ranw=1 Perijiidge branch*andaiuouiit1000 ( loail)g$g$Find all ciistomeis who have both a loan and an account using relational algebra expression. 3 pointsfl customename (borrower XI depositor)Find name of all customers who have at least one loan that the values for amount is null. In SQL 3 pointsSelect distinct customei name fiom loan where amount is nullFind the names of all branches where the average account balance is more than $1000 ill SQL. 3 pointsSe