数据库期中考试1

Database System PrinciplesTest One（07401 — 07405）Class____________No____________Name____________1.Fill in blanks (1 x 7 points)(1)The collection of information stored in the database at a particular moment is called an _instance____of the database.(2)The database system provides users with three levels of data abstraction, the _view__ level of abstraction describes only part of the entire database.(3)Database design involves the following phases: requirements analysis,conceptual/external schema design, logical schema design and physical schema design.(4) Data model is a collection of conceptual tools for describing data, datarelationships, data semantics, and data constraints.(5) As human-machine interfaces, the database language consists of two parts, i.e the datadefinition language (DDL) and DML (data manipulation language) . (6)In the following figure, the participation constraints of A in R is partial , themapping cardinality form A to B is many-to-many(7) An entity set that does not have a primary key is referred to as a weak entity set .2.Choice (1 x 9 points)(1)For the entity set Student(#student, sname, department, course, grade), the primaryattributes are .A. #student, courseB.{#student}C. {#student, course}D. #studentE.#student F. course(2)With respect to the following relational database, is the data model,is the relational schema.A.R={ <a1, a2, …., a n> }B.<Smith, 0441112, CS, database, 90>C. Student(sname, #student, department, course, grade)(3)relationship set “服务于” among the entity sets cell and MS, primary_key(服务于) isA. either primary_key(MS) or primary_key(cell)B. primary_key(MS)C. primary_key(cell)D. primary_key(MS) ∪ primary_key(cell)For the descriptive attribute “业务类型” of the relationship set “服务于” among MS and cell, when reducing “服务于” into the relational table, how to deal with the attribute “业务类型”A. “业务类型” can only be assigned as the attribute of the table corresponding to MSB. “业务类型” can only be assigned as the attribute of the table corresponding to cellC. “业务类型” can only be assigned as the attribute of the table corresponding to 服务于(4)For a many-to-one relationship sets R that associates entity set A and B, if R ispartially on the many-side A and the one-side B, how to deal with R ?A.R should be represented as a independent table corresponding to RB.R should not be represented as a independent table, it can be reduced to the table Acorresponding to the many and total side entity set AC.R should not be represented as a independent table, it can be reduced to the table Bcorresponding to the one and part side entity set B(5)For the extended E-R model in Fig.3, if entity-set-L1 ∩ entity-set-L2=Φ, then thegeneralization/specialization isA. overlappingB. disjoint, and if (entity-set-L1 ∪entity-set-L2) ≠H-entity-set, then thegeneralization/specialization isA. totalB. partial(6)Given the cardinalities of the entity sets A and B with respect to therelationship set R, the participation constraints of A can be decided by ;A. l AB. h AC. l BD. h BThe mapping cardinality from A to B can be decided by .A. [l A, l B]B. [h A , h B]C. [h B , h A]D. [l A, h B]答案：A A C B A A B B A CReduce the E-R diagram in Fig.3 into relational schemas.3.(10 points)Answers ：BTS(BTS-id, 发射功率)；天线（BTS-id, 对应扇区， 型号，增益，方向角）4. (6 points) Convert the following E-R diagram into the diagram that containsonly binary relationshipsRBCAAttr-RAnswers ：E = { e i } , | E |= | R |, i.e. each (a i , b i , c i ) in Rcorresponds to one ei in E, or E = { ei }=R={(a i , b i , c i ) } R a = { (e i , a i) | e i∈E , a i∈A }, relating E and AR b = { (e i , b i) | e i ∈E , b i ∈B } , relating E and BR c = { (e i , c i) | e i ∈E , c i ∈B }, relating E and CE has an identifying attribute e ( candidate key) todistinguish each e i i n Eall attributes of R, i.e. attr-R, are assigned to E5. (10 points)Convert the entity set “学生”, of which the attribute “老乡” is amultivalued attribute, in Fig.4 into relational tablesstudent-id籍贯老乡性别年龄男 20 07494 北京 07596,0761107498 河北 07320,女 1907321Fig.4Answers:student-id籍贯性别年龄07494 北京男 2007498 河北女 19student-id老乡07494 0759607494 0761107498 0732007498 073216.(15 points) 给出下列关系代数操作对应的SQL语句(1) σp(r) (2) ∏A1, A2, .., Am ( r )(3) r∞s ,, 假设r(A, B, C), s(C, E, F)(4) r ∩ s(5) loan ←loan∪σamount ≥ 0and amount ≤ 50 (loan)假设loan(loan-number, branch, amount) Answers：(1)select * from r where P(2)select A1, A2, .., Am from r(3)select * from r natual join sselect * from rwhere r.C =s.C(4)r intersect s(5)insert into loanselect * from loanwhere amount ≥ 0and amount ≤ 507.(9 points)给出下列SQL语句对应的关系代数表达式(1) select branch-name, max (salary)from pt-worksgroup by branch-name假设pt-works(employee-name, branch-name, salary) (2) delete form rwhere P(3) update loanset amount = amount *1.2where amount > 1000Answers:(1) branch_name G max(salary) (pt-works)(2) r ← r —σp(r)(3) T1 ← пloan-number, branch_name, amount*1.2σamount > 1000 (loan)T2 ←σamount≤ 1000 (loan)loan ← T1 ∪ T28. (14 points). Here is the schema diagram for the bank-customer database.Use SQL statements to implement the following operations:(1)Define the table account, it is assumed that the null value is inappropriate for theattribute branch_name and the attribute balance ranges from 5000 to 100,000.(3 points)(2)Find out the name of each customer who has accounts in only one branch. (5points)(3)Modify the structure of the table branch, add a new attribute account_amount into it,which describes the total number of accounts in a branch. And then assign thecorrect value of account_amount for each branch in the table branch.(6 points)Answers:（1）create table account{ account-number integer primay keybranch-name varchar not nullbalance integerforeign key (branch-name) reference branchcheck (balance between 5000 and 100000)}（2）select custom-namefrom account, depositorwhere aoount.account-number= depositor.account-numbergroup by customer-namehaving count(distinct branch-name)=1（3） step1.alter table branch add account-amount integerstep2.create view sum-amount as b-name, total{ select branch-name, count(*)from accountgroup by branch-name}step3. （MS SQL Server支持的语法）update branchset account-amount = sum-amount.totalfrom branch, sum-amountwhere branch.branch-name=sum-amount.b-name或：update branchset account-amount = sum-amount.totalfrom branch JOIN sum-amounton branch.branch-name=sum-amount.b-name或：（Oralce和DB2都支持的语法）：update branchset account-amount =select sum-amount.totalfrom sum-amountwhere branch.branch-name=sum-amount.b-name另一种解法：step1.create view sum-amount as b-name, total{ select branch-name, count(*)from accountgroup by branch-name}Step2.create table new-branch{ branch-name varchar primay keybranch-city varchassets integeraccount-amount integer}Step3.insert into new-branchselect branch-name, branch-city, assets, totalfrom branch, sum-amountwhere branch.branch-name = sum-amount.b-name step4. drop branchstep5. 利用DBMS相关机制，将new-branch换名为branch或者：在new-branch上定义新视图branch9. (20 points) A school is going to arrange a sports day for the students. A database to keep track of participants and activities during the sports day is to be created. Consider the following information:(1). Participating persons. Each person has a number and a name. The person is identified by the number.(2). Teams. Each team has a number and a name. The team is identified by the number.(3). A team consists of several persons, and a person can be a member of several teams. A team must have at least one person, and a person may not participate in any team. (4). Activities, such as baseball or long jump. Each activity has a number, a name, and a starting time, and is identified by the number.(5). Participation in the activities. Both teams and individual persons can participate in more than one activities. Each person and each team must participate in at least one activity. Each activity can have many teams and many persons.(1) Design the E/R diagram for the database on the basis of the information mentioned above .( 15 points)Note: mapping cardinality of each relationship and participation of each entity to the relationship should be described in the diagram.(2) Convert the E-R diagram to the proper relational schemas, and give the primary keys of each relation schemas by underlines. (15 points)Answers：team(t_number, t_name)person(p_number, p_name)activity(a_number, a_name, a_time) member(t_number, p_number) participation1(t_number, a_number) participation2(p_number, a_number)。