四川大学期末考试试题（闭卷）（2007-2008学年第2学期）课程号： 31113740 课序号：课程名称数据库系统原理（A卷）任课教师：适用专业年级：软件工程2006级学生人数： 340印题份数： 350 学号：姓名：考试须知四川大学学生参加由学校组织或由学校承办的各级各类考试，必须严格执行《四川大学考试工作管理办法》和《四川大学考场规则》。
有考试违纪作弊行为的，一律按照《四川大学学生考试违纪作弊处罚条例》进行处理。
四川大学各级各类考试的监考人员，必须严格执行《四川大学考试工作管理办法》、《四川大学考场规则》和《四川大学监考人员职责》。
有违反学校有关规定的，严格按照《四川大学教学事故认定及处理办法》进行处理。
题号一二三四五六七八九十得分阅卷教师阅卷时间总成绩100 期末卷面50 %半期测验10 %平时练习10 %项目练习30 %1.Multiple Choices. (2points×10)(1)If relation R has n tuples and relation S has m tuples, the maximum number of tupes that R─S can contain i s _____.A. nB. mC. n+mD. |n-m|(2)Whi ch of the followi ng statements are true.I. 4NF implies BCNFII. BCNF implies 3NF.III. 3NF implies BCNFA. NoneB. I onlyC. I and IID. I and III(3)The FD X -> Y i s a full dependency in a relation R, i f there is _____ attribute A that can be _____ X and thedependency still holds.A. at least one, added toB. at l east one, removed fromC. no, added to C. removed from(4)An excl usive l ock on a data i tem represents permi ssi on to perform whi ch of the operations, read and wri te, on thedata item?A. Both read and write.B. Wri te onl y.C. Read only.D. Nei ther read nor wri te.(5)For two tables to be uni on compatible, corresponding col umns from each table should have whi ch of the followi ng?A.different domai nsB.different namesC.the same domai nD.the same name(6)Just to avoi d “dirty read”, whi ch SQL Isol ation level should be set?A.READ UNCOMMITTEDB.READ COMMITTEDC.REPEATABLE READD.SERIALIZABLE(7)What attributes does a subcl ass have?A.Just the attributes from the superclassB.All the attributes of its superclass, and possibly m oreC. A subset of the attributes of its superclassD.None of the attributes of its superclass(8)After a system failure, what actions are used as part of the crash recovery or restart procedure?A.undoB.redoC.both undo and redoD.backup copy and redo(9)Whi ch of the followi ng can be used to measure hard disk dri ve performance?I.Seek timeII.Rotational latencyIII.Block transfer timeA.I onlyB. II onl yC. II and IIID. I, II, and III(10)In a two-phase locking protocol, what happens wh en a transaction requests a conflicting lock?A.The transaction imm ediately acquires the lock from the current lock-holder.B.The transaction proceeds without acquiring the lock.C.The transaction is blocked to acquire the lock.D.The transaction is aborted imm ediately.2.Write SQL statem ents.(5points×6)You will be working wi th the followi ng schema:Empl oyee (SSN, name, salary, DNo)Department (DNo, DeptName, MgrSSN)Project (PNo, l ocation, ProjName)HourLog (SSN, PNo, hours)The Empl oyee relation provi des a list of employees wi th thei r SSN, name, salary, and department number (DNo). The SSN is unique for each employee. Each employee bel ongs to onl y one department. The Department relation contai ns a li st of the departments for the company. Its schema i ncludes a unique department number called DNo. It al so incl udes the name of the department (DeptName) and the social security number of the department's manager (MgrSSN). Each department has a onl yone manager. The Project relation incl udes a uni que project number (PNo), l ocation and the project name (ProjName). An employee can be assigned to any number (including zero) projects. Each project has at least one person assigned to i t. Fi nally, the HourLog relati on li sts for each project the number of hours of work for each employee who i s assi gned to that project. The key of thi s relati on i s SSN and PNo. Write SQL statements in SQL2 to perform the following commands.(1)List all managers’ SSN, name and salary.(2)Find the name and the SSN of everyone who works more than 100 hours on one parti cul ar project.(3)Find the name and the SSN of everyone who works on at least two projects.(4)Find the name and the SSN of everyone who i s not working on any projects.(5)List alphabeti cally the names and SSNs of employees whose total work hours on all project are more than the averagehours.(6)Assume the relation Empl oyee and Department has been created usi ng the following statements:CREA TE TABLE Employee( SSN char (4) pri mary key,Name varchar (30),Salary fl oat,Dno char (2));CREA TE TABLE Department( DNo char(2) primary key,Deptname varchar(30),MgrSSN char(4))Write AL TER statements to speci fy the referential integrity constraints on above two relations.3.Design an E/R diagram for the following situation. (15points)It is about books, author s, book publisher s, book seller s, and book stor es.●Books have an ISBN number (the key), a name, and pri ce. A book can be wri tten by many authors and on authorcan wri te many books.●Authors have a social security number (the key) and a name.●Publishers are the companies that manage the printing and producti on of books. Publi shers have a name (key), aphone number, and address.● A given book has a uni que publi sher.●Book sellers mai ntain book stores to sell books; a given book seller can have many stores (and must have at l eastone store, to qualify as a book seller).● A book store has a name (key), address and a phone number.4.Convert the following E-R schema into a r elational schema using the mapping algorithm specified in this course. Specify keys and r efer ential integrity constraints, using dir ected arcs. Make sur e you also identify alternate keys. Label each step of the mapping algorithm. （15p oints）5.The following table stor es information about librarians and their working sections. (20points) Librarian_Section:Perform the following tasks.(1)List the prim ary key.(2)List all the FDs.(3)What norm al form is the relation in? Explain.(4)Apply norm alization to it increm entally, carrying the norm alization process through each of the higher norm alform s possible up to 3NF.。
