1、INSTRUCTORS MANUAL TO ACCOMPANYDatabaseSystem ConceptsFifth EditionAbraham SilberschatzYale UniversityHenry F. KorthLehigh UniversityS. SudarshanIndian Institute of Technology, BombayCopyright c2005 A. Silberschatz, H. Korth, and S. SudarshanContentsPreface 1Chapter 1 IntroductionExercises 3Chapter
2、2 Relational ModelExercises 7Chapter 3 SQLExercises 11Chapter 4 Advanced SQLExercises 19Chapter 5 Other Relational LanguagesExercises 23Chapter 6 Database Design and the E-R ModelExercises 33iiiiv ContentsChapter 7 Relational Database DesignExercises 41Chapter 8 Application Design and DevelopmentExe
3、rcises 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 Con
4、currency ControlExercises 83Chapter 17 Recovery SystemExercises 89Contents vChapter 18 Data Analysis and MiningExercises 93Chapter 19 Information RetrievalExercises 99Chapter 20 Database-System ArchitecturesExercises 101Chapter 21 Parallel DatabasesExercises 105Chapter 22 Distributed DatabasesExerci
5、ses 109Chapter 23 Advanced Application DevelopmentExercises 115Chapter 24 Advanced Data Types and New ApplicationsExercises 119Chapter 25 Advanced Transaction ProcessingExercises 123PrefaceThis volume is an instructors manual for the 5thedition of Database System Conceptsby Abraham Silberschatz, Hen
6、ry F. Korth and S. Sudarshan. It contains answers tothe exercises at the end of each chapter of the book. Before providing answers to theexercises for each chapter, we include a few remarks about the chapter. The nature ofthese remarks vary. They include explanations of the inclusion or omission of
7、certainmaterial, and remarks on how we teach the chapterinourowncourses.Theremarksalso include suggestions on material to skip if time is at a premium, and tips onsoftware and supplementary material that can be used for programming exercises.The Web home page of the book, at http:/www.db-,containsav
8、arietyof useful information, including up-to-date errata, online appendices describing thenetwork data model, the hierarchical data model, and advanced relational databasedesign, and model course syllabi. We will periodically update the page with supple-mentary material that may be of use to teacher
9、s and students.We provide a mailing list through which users can communicate among them-selves and with us. If you wish to use this facility, please visit the following URL andfollow the instructions there to subscribe:http:/mailman.cs.yale.edu/mailman/listinfo/db-book-listThe mailman mailing list s
10、ystem provides many benefits, such as an archive of post-ings, and several subscription options, including digest and Web only. To send mes-sages 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 thebook, as well as in the
11、instructors manual. Internet electronic mail should be ad-dressed to db-bookcs.yale.edu. Physical mail may be sent to Avi Silberschatz, YaleUniversity, 51 Prospect Street, New Haven, CT, 06520, USA.12 PrefaceAlthough we have tried to produce an instructors manual which will aid all ofthe users of ou
12、r book as much as possible, there can always be improvements. Thesecould include improved answers, additional questions, sample test questions, pro-gramming projects, suggestions on alternative orders of presentation of the material,additional references, and so on. If you would like to suggest any
13、such improve-ments 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 theircontributor.This manual is derived from the manuals for the earlier editions. The manual forthe 4thedition was prepared by N
14、ilesh Dalvi, Sumit Sanghai, Gaurav Bhalotia andArvind Hulgeri. The manual for the 3rdedition was prepared by K. V. Raghavan withhelp from Prateek R. Kapadia. Sara Strandtman helped with the instructor manualfor the 2ndand 3rdeditions, while Greg Speegle and Dawn Bezviner helped us toprepare the inst
15、ructors manual for the 1stedition.A. S.H. F. K.S. S.Instructor Manual Version 5.0.0CHAPTER 1IntroductionExercises1.5 List four applications which you have used, that most likely used a databasesystem to store persistent data.Answer: No answer1.6 List four significant differences between a file-proce
16、ssing system and a DBMS.Answer: Some main differences between a database management system anda file-processing system are: Both systems contain a collection of data and a set of programs which accessthat data. A database management system coordinates both the physicaland the logical access to the d
17、ata, whereas a file-processing system coordi-nates only the physical access. A database management system reduces the amount of data duplication byensuring that a physical piece of data is available to all programs authorizedto have access to it, whereas data written by one program in a file-process
18、ingsystem 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 pre-determined access to data (i.e., compiled programs). A database management system is designed to coord
19、inate multiple usersaccessing the same data at the same time. A file-processing system is usuallydesigned to allow one or more programs to access different data files atthe same time. In a file-processing system, a file can be accessed by twoprograms concurrently only if both programs have read-only
20、 access to thefile.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 schemewithout making it necessary to rewrite application programs. Such modifi-cations include changing from
21、unblocked to blocked record storage, or fromsequential to random access files. Logical data independence is the ability to modify the conceptual schemewithout making it necessary to rewrite application programs. Such a modi-fication might be adding a field to a record; an application programs viewhi
22、des this change from the program.1.8 List five responsibilities of a database management system. For each responsi-bility, explain the problems that would arise if the responsibility were not dis-charged.Answer: A general purpose database manager (DBM) has five responsibilities:a. interaction with t
23、he 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 pointsout that sometimes a responsibility is omitted by design, such as concurrencycontrol on a single-user DBM for a mic
24、ro computer) the following problems canoccur, respectively:a. No DBM can do without this, if there is no file manager interaction thennothing stored in the files can be retrieved.b. Consistency constraints may not be satisfied, account balances could go be-low the minimum allowed, employees could ea
25、rn 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 accesspart of the database may be able to access parts of the database for whichthey lack authority. For example, a high school student
26、 could get accessto national defense secret codes, or employees could find out what theirsupervisors earn.d. Data could be lost permanently, rather than at least being available in a con-sistent state that existed prior to a failure.e. Consistency constraints may be violated despite proper integrity
27、 enforce-ment in each transaction. For example, incorrect bank balances might bereflected due to simultaneous withdrawals and deposits, and so on.1.9 List at least two reasons why database systems support data manipulation usinga declarative query language such as SQL, instead of just providing a a
28、libraryof 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 answerExercises 51.11 What are five main functions of a database administrator?Answer: Five main functions of a database administrator
29、 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 2Relational ModelExercises2.4 Describe the differences in meaning b
30、etween the terms relation and relationschema.Answer: A relation schema is a type definition, and a relation is an instanceof that schema. For example, student (ss#, name)isarelationschemaandss# name123-45-6789 Tom Jones456-78-9123 Joe Brownis a relation based on that schema.2.5 Consider the relation
31、al database of Figure 2.35, where the primary keys are un-derlined. Give an expression in the relational algebra to express each of the fol-lowing 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 Fi
32、rstBank Corporation.c. Find the names, street address, and cities of residence of all employees whowork 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 cityas the company for which they work.e. Assume the compan
33、ies may be located in several cities. Find all companieslocated in every city in which Small Bank Corporation is located.Answer:a. person-name(company-name = “First Bank Corporation”(works)78 Chapter 2 Relational Modelemployee (person-name, street, city)works (person-name, company-name, salary)compa
34、ny (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(employee a49(company-name = “First Bank Corporation”(works)c. person-name, street, city(company-name = “First Bank Corporation” salary 10000)works a49 employ
35、ee)d. person-name(employee a49 works a49 company)e. Note: Small Bank Corporation will be included in each pany-name(company (city(company-name=“Small Bank Corporation”(company)2.6 Consider the relation of Figure 2.20, which shows the result of the query “Findthe names of all customers who have a loa
36、n at the bank.” Rewrite the queryto 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 thoughJackson does in fact have a loan from the bank.a. Explain why Jackson does not appear in the result.b. Suppose
37、 that you want Jackson to appear in the result. How would youmodify the database to achieve this effect?c. Again, suppose that you want Jackson to appear in the result. Write a queryusing an outer join that accomplishes this desire without your having tomodify the database.Answer: The rewritten quer
38、y iscustomer-name,customer-city,amount(borrower a49 loan a49 customer)a. Although Jackson does have a loan, no address is given for Jackson in thecustomer relation. Since no tuple in customer joins with the Jackson tuple ofborrower, Jackson does not appear in the result.b. The best solution is to in
39、sert Jacksons address into the customer relation. Ifthe address is unknown, null values may be used. If the database systemdoes not support nulls, a special value may be used (such as unknown)forJacksons street and city. The special value chosen must not be a plausiblenameforanactualcityorstreet.c.
40、customer-name,customer-city,amount(borrower a49 loan) a49 customer)2.7 Consider the relational database of Figure 2.35. Give an expression in the rela-tional algebra for each request:a. Give all employees of First Bank Corporation a 10 percent salary raise.Exercises 9b. Give all managers in this dat
41、abase a 10 percent salary raise, unless the salarywould 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 Corpora-tion.Answer:a. works person-name,company-name,1.1salary(company-name=“First Bank Corporation”)(w
42、orks) (works company-name=“First Bank Corporation”(works)b. The same situation arises here. As before, t1,holdsthetuplestobeupdatedand t2holds these tuples in their updated form.t1 works.person-name,company-name,salary(works.person-name=manager-name(works manages)t2 works.person-name,company-name,sa
43、lary1.03(t1.salary 1.1 100000(t1)t2 t2 (works.person-name,company-name,salary1.1(t1.salary 1.1 100000(t1)works (works t1) t2c. works works companyname=“Small Bank Corporation”(works)2.8 Using the bank example, write relational-algebra queries to find the accountsheld by more than two customers in th
44、e following ways:a. Using an aggregate function.b. Without using any aggregate functions.Answer:a. t1account-numberGcount customer-name(depositor)account-numberparenleftbignum-holders2parenleftbigaccount-holders(account-number,num-holders)(t1)parenrightbigparenrightbigb. t1 (d1(depositor) d2(deposit
45、or) d3(depositor)t2 (d1.account-number=d2.account-number=d3.account-number)(t1)d1.account-number(d1.customer-namenegationslash=d2.customer-name d2.customer-namenegationslash=d3.customer-name d3.customer-namenegationslash=d1.customer-name)(t2)2.9 Consider the relational database of Figure 2.35. Give
46、a relational-algebra expres-sion 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
47、Relational Modela. t1company-nameGcount-distinct person-name(works)t2 maxnum-employees(company-strength(company-name,num-employees)(t1)company-name(t3(company-name,num-employees)(t1) a49 t4(num-employees)(t2)b. t1company-nameGsum salary(works)t2 minpayroll(company-payroll(company-name,payroll)(t1)co
48、mpany-name(t3(company-name,payroll)(t1) a49 t4(payroll)(t2)c. t1company-nameGavg salary(works)t2 company-name = “First Bank Corporation”(t1)pany-name(t3(company-name,avg-salary)(t1)a49t3.avg-salary first-bank.avg-salary(first-bank(company-name,avg-salary)(t2)2.10 List two reasons why null values mig
49、ht be introduced into the database.Answer: Nulls may be introduced into the database because the actual valueis either unknown or does not exist. For example, an employee whose addresshas changed and whose new address is not yet known should be retained witha null address. If employee tuples have a composite attribute dependents,anda particular employee has no dependents, then that tuples dependents attributeshould be given a nu
