数据库系统概念第五版 课后习题答案.pdf

INSTRUCTORS MANUAL TO ACCOMPANYDatabase System ConceptsFifth EditionAbraham SilberschatzYale UniversityHenry E KorthLehigh UniversityS.SudarshanIndian Institute of Technology,BombayCopyright 2005 A.Silberschatz,H.Korth,and S.SudarshanPreface 1Chapter 1 IntroductionExercises 3Chapter 2 Relational ModelExercises 7Chapter 3 SQLExercises 11Chapter 4 Advanced SQLExercises 19Chapter 5 Other Relational LanguagesExercises 23Chapter 6 Database Design and the E-R ModelExercises 33ContentsChapter 7 Relational Database DesignExercises 41Chapter 8 Application Design and DevelopmentExercises 47Chapter 9 Object-Based DatabasesExercises 51Chapter 10 XMLExercises 55Chapter 11 Storage and File StructureExercises 61Chapter 12 Indexing and HashingExercises 65Chapter 13 Query ProcessingExercises 69Chapter 14 Query OptimizationExercises 75Chapter 15 TransactionsExercises 79Chapter 16 Concurrency ControlExercises 83Chapter 17 Recovery SystemExercises 89ContentsChapter 18 Data Analysis and MiningExercises 93Chapter 19 Information RetrievalExercises 99Chapter 20 Database-System ArchitecturesExercises 101Chapter 21 Parallel DatabasesExercises 105Chapter 22 Distributed DatabasesExercises 109Chapter 23 Advanced Application DevelopmentExercises 115Chapter 24 Advanced Data Types and New ApplicationsExercises 119Chapter 25 Advanced Transaction ProcessingExercises 123This volume is an instructors manual for the 5th edition of Database System Concepts by Abraham Silberschatz,Henry F.Korth and S.Sudarshan.It contains answers to the exercises at the end of each chapter of the book.Before providing answers to the exercises for each chapter,we include a few remarks about the chapter.The nature of these remarks vary.They include explanations of the inclusion or omission of certain material,and remarks on how we teach the chapter in our own courses.The remarks also include suggestions on material to skip if time is at a premium,and tips on software and supplementary material that can be used for programming exercises.The Web home page of the book,at www.db-,contains a variety of useful information,including up-to-date errata,online appendices describing the network data model,the hierarchical data model,and advanced relational database design,and model course syllabi.We will periodically update the page with supplementary material that may be of use to teachers and students.We provide a mailing list through which users can communicate among themselves and with us.If you wish to use this facility,please visit the following URL and follow the instructions there to subscribe:mailman.cs.yale.edu/mailman/listinfo/db-book-listThe mailman mailing list system provides many benefits,such as an archive of postings,and several subscription options,including digest and Web only.To send messages to the list,send e-mail to:db-book-listcs.yale.eduWe would appreciate it if you would notify us of any errors or omissions in the book,as well as in the instructors manual.Internet electronic mail should be addressed to db-bookcs.yale.edu.Physical mail may be sent to Avi Silberschatz,Yale University,51 Prospect Street,New Haven,CT,06520,USA.2 PrefaceAlthough we have tried to produce an instructors manual which will aid all of the users of our book as much as possible,there can always be improvements.These could include improved answers,additional questions,sample test questions,programming projects,suggestions on alternative orders of presentation of the material,additional references,and so on.If you would like to suggest any such improvements to the book or the instructors manual,we would be glad to hear from you.All contributions that we make use of will,of course,be properly credited to their contributor.This manual is derived from the manuals for the earlier editions.The manual for the 4th edition was prepared by Nilesh Dalvi,Sumit Sanghai,Gaurav Bhalotia and Arvind Hulgeri.The manual for the 3rd edition was prepared by K.V.Raghavan with help from Prateek R.Kapadia.Sara Strandtman helped with the instructor manual for the 2nd and 3rd editions,while Greg Speegle and Dawn Bezviner helped us to prepare the instructors manual for the 1st edition.A.S.H.F.K.S.S.Instructor Manual Version 5.0.0CHAPTERIntroductionExercises1.5 List four applications which you have used,that most likely used a database system to store persistent data.Answer:No answer1.6 List four significant differences between a file-processing system and a DBMS.Answer:Some main deferences between a database management system and a file-processing system are:Both systems contain a collection of data and a set of programs which access that data.A database management system coordinates both the physical and the logical access to the data,whereas a file-processing system coordinates only the physical access.A database management system reduces the amount of data duplication by ensuring that a physical piece of data is available to all programs authorized to have access to it,whereas data written by one program in a file-processing system may not be readable by another program.A database management system is designed to allow flexible access to data(i.e.,queries),whereas a file-processing system is designed to allow predetermined access to data(i.e.,compiled programs).A database management system is designed to coordinate multiple users accessing the same data at the same time.A file-processing system is usually designed to allow one or more programs to access di仔erent data files at the same time.In a file-processing system,a file can be accessed by two programs concurrently only if both programs have read-only access to the file.1.7 Explain the difference between physical and logical data independence.Answer:34 Chapter 1 Introduction Physical data independence is the ability to modify the physical scheme without making it necessary to rewrite application programs.Such modifications include changing from unblocked to blocked record storage,or from sequential to random access files.Logical data independence is the ability to modify the conceptual scheme without making it necessary to rewrite application programs.Such a modification might be adding a field to a record;an application programs view hides this change from the program.1.8 List five responsibilities of a database management system.For each responsibility,explain the problems that would arise if the responsibility were not discharged.Answer:A general purpose database manager(DBM)has five responsibilities:a.interaction with the file manager.b.integrity enforcement.c.security enforcement.d.backup and recovery.e.concurrency control.If these responsibilities were not met by a given DBM(and the text points out that sometimes a responsibility is omitted by design,such as concurrency control on a single-user DBM for a micro computer)the following problems can occur,respectively:a.No DBM can do without this,if there is no file manager interaction then nothing stored in the files can be retrieved.b.Consistency constraints may not be satisfied,account balances could go below the minimum allowed,employees could earn too much overtime(e.g.,hours 80)or,airline pilots may fly more hours than allowed by law.c.Unauthorized users may access the database,or users authorized to access part of the database may be able to access parts of the database for which they lack authority.For example,a high school student could get access to national defense secret codes,or employees could find out what their supervisors earn.d.Data could be lost permanently,rather than at least being available in a consistent state that existed prior to a failure.e.Consistency constraints may be violated despite proper integrity enforcement in each transaction.For example,incorrect bank balances might be reflected due to simultaneous withdrawals and deposits,and so on.1.9 List at least two reasons why database systems support data manipulation using a declarative query language such as SQL,instead of just providing a a library of C or C+functions to carry out data manipulation.Answer:No answer1.10 Explain what problems are caused by the design of the table in Figure 1.5.Answer:No answerExercises1.11 What are five main functions of a database administrator?Answer:Five main functions of a database administrator are:To create the scheme definition To define the storage structure and access methods To modify the scheme and/or physical organization when necessary To grant authorization for data access To specify integrity constraintsCHAPTER 2Exercises2.4 Describe the differences in meaning between the terms relation and relation schema.Answer:A relation schema is a type definition,and a relation is an instance of that schema.For example,student(ss#,name)is a relation schema andss#name123-45-6789456-78-9123Tom Jones Joe Brownis a relation based on that schema.2.5 Consider the relational database of Figure 2.35,where the primary keys are underlined.Give an expression in the relational algebra to express each of the following queries:a.Find the names of all employees who work for First Bank Corporation.b.Find the names and cities of residence of all employees who work for First Bank Corporation.c.Find the names,street address,and cities of residence of all employees who work for First Bank Corporation and earn more than$10,000 per annum.d.Find the names of all employees in this database who live in the same city as the company for which they work.e.Assume the companies may be located in several cities.Find all companies located in every city in which Small Bank Corporation is located.Answer:rson-name 1company-na/me zzFirst Bank Corporation,/(wor5)78 Chapter 2 Relational Modelemployee(person-name,street,city)works(person-name,company-name,salary)company(company-name,city)manages(person-name,manager-name)Figure 2.35.Relational database for Exercises 2.1,2.3 and 2.9.b-person-name,city(GTTiployee Xcompany-name=First Bank Corporation 注s)-person-name,street,citycompany-name=First Bank Corporation A salary 10000)works M employee)d.-person-name(employee X works M company)e.Note:Small Bank Corporation will be included in each answer.(flcity(aCompany-name=/Small Bank Corporation,/(cowaw)2.6 Consider the relation of Figure 2.20,which shows the result of the query“Find the names of all customers who have a loan at the bank.Rewrite the query to include not only the name,but also the city of residence for each customer.Observe that now customer Jackson no longer appears in the result,even though Jackson does in fact have a loan from the bank.a.Explain why Jackson does not appear in the result.b.Suppose that you want Jackson to appear in the result.How would you modify the database to achieve this effect?c.Again,suppose that you want Jackson to appear in the result.Write a query using an outer join that accomplishes this desire without your having to modify the database.Answer:The rewritten query is-customer-name,customer-city,amount(boWOWCV X loCLTl X CUStOTTlCT)a.Although Jackson does have a loan,no address is given for Jackson in the customer relation.Since no tuple in customer joins with the Jackson tuple of borrower,Jackson does not appear in the result.b.The best solution is to insert Jacksons address into the customer relation.If the address is unknown,null values may be used.If the database system does not support nulls,a special value may be used(such as unknown)for Jacksons street and city.The special value chosen must not be a plausible name for an actual city or street.-customer-name,customer-city,amountborrower M loan)型 customer)2.7 Consider the relational database of Figure 2.35.Give an expression in the relational algebra for each request:a.Give all employees of First Bank Corporation a 10 percent salary raise.Exercises 9b.Give all managers in this database a 10 percent salary raise,unless the salary would be greater than$100,000.In such cases,give only a 3 percent raise.c.Delete all tuples in the works relation for employees of Small Bank Corporation.Answer:a。WOTks 100000(l)力 2-1 2 U(J-works.per son-name,company-name,salary*1.1salary*1.1 2 account-holdersaccount-numbernum-holders)(1)b.力i-(Pdi(depositor)x(depositor)x Pd3(depositor2-(dl.account-numberd2.account-numberd3.account-number)(1 1)-dl.account-numb er dl.customer-named2.customer-name Ad2.customer-named.customer-name/d3.customer-namedl.customer-name)(2)2.9 Consider the relational database of Figure 2.35.Give a relational-algebra expression for each of the following queries:a.Find the company with the most employees.b.Find the company with the smallest payroll.c.Find those companies whose employees earn a higher salary,on average,than the average salary at First Bank Corporation.Answer:10 Chapter 2 Relational Modela.力icompan-namePcounf-distinct person-name W。泌，)力2 employees company-strengthcompany-name,num-employees)(力1)-company-name(P力3 company-name,num-employees)(1)*P力4(num-employees)(2)b.11力2company-nameGslW.salary(WOVksmillpagroZZ company-payroll(company-name,payroll)(1 1)-company-name Pts(company-name,payroll)(1)*Pt(payroll)(2)c.刀company-name GaVg salary OTks.24 company-name=Z/First Bank Corporation(“D.company-name(P 加(company-name,avg-salary)(1 1)t.avg-salary first-bank.avg-salary Pfirst-bank(company-name,avg-salary)(2)2.10 List two reasons why null values might be introduced into the database.Answer:Nulls may be introduced into the database because the actual value is either unknown or does not exist.For example,an employee whose address has changed and whose new address is not yet known should be retained with a null address.If employee tuples have a composite attribute dependents,and a particular employee has no dependents,then that tuples dependents attribute should be given a null value.2.11 Consider the following relational schemaemployee(empno,name,office,age)booksjisbn,title,authors,publishe r)loan(empno,isbn,date)Write the following queries in relational algebra.a.Find the names of employees who have borrowed a book published by McGraw-Hill.b.Find the names of employees who have borrowed all books published by McGraw-Hill.c.Find the names of employees who have borrowed more than five different books published by McGraw-Hill.d.For each publisher,find the names of employees who have borrowed more than five books of that publisher.Answer:No answerCHAPTERSQLExercises3.8 Consider the insurance database of Figure 3.11,where the primary keys are underlined.Construct the following SQL queries for this relational database.a.Find the number of accidents in which the cars belonging to“John Smith”were involved.b.Update the damage amount for the car with license number“AABB2000“in the accident with report number AR2197”to$3000.Answer:Note:The participated relation relates drivers,cars,and accidents.a.SQL query:select count(distinct*)from accidentwhere exists(select*from participated,personwhere participated.driverJd=person.driverJdand person.name=John Smithand accident.report-number=participated.report_number)b.SQL query:update participatedset damage