This
DATABASE MANAGEMENT
SYSTEMS
SOLUTIONS MANUAL
THIRD EDITION
Raghu Ramakrishnan
University of Wisconsin
Madison, WI, USA
Johannes Gehrke
Cornell University
Ithaca, NY, USA
Jeff Derstadt, Scott Selikoff, and Lin Zhu
Cornell University
Ithaca, NY, USA
CONTENTS
PREFACE
iii
1
INTRODUCTION TO DATABASE SYSTEMS
1
2
INTRODUCTION TO DATABASE DESIGN
6
3
THE RELATIONAL MODEL
16
4
RELATIONAL ALGEBRA AND CALCULUS
28
5
SQL: QUERIES, CONSTRAINTS, TRIGGERS
45
6
DATABASE APPLICATION DEVELOPMENT
63
7
INTERNET APPLICATIONS
66
8
OVERVIEW OF STORAGE AND INDEXING
73
9
STORING DATA: DISKS AND FILES
81
10 TREE-STRUCTURED INDEXING
88
11 HASH-BASED INDEXING
100
12 OVERVIEW OF QUERY EVALUATION
119
13 EXTERNAL SORTING
126
14 EVALUATION OF RELATIONAL OPERATORS
131
i
iiDatabase Management Systems Solutions Manual Third Edition
15 A TYPICAL QUERY OPTIMIZER
144
16 OVERVIEW OF TRANSACTION MANAGEMENT
159
17 CONCURRENCY CONTROL
167
18 CRASH RECOVERY
179
19 SCHEMA REFINEMENT AND NORMAL FORMS
189
20 PHYSICAL DATABASE DESIGN AND TUNING
204
21 SECURITY
215
PREFACE
It is not every question that deserves an answer.
Publius Syrus, 42 B.C.
I hope that most of the questions in this book deserve an answer. The set of questions is unusually extensive, and is designed to reinforce and deepen students’ understanding of the concepts covered in each chapter. There is a strong emphasis on quantitative and problem-solving type exercises.
While I wrote some of the solutions myself, most were written originally by students in the database classes at Wisconsin. I’d like to thank the many students who helped in developing and checking the solutions to the exercises; this manual would not be available without their contributions. In alphabetical order: X. Bao, S. Biao, M.
Chakrabarti, C. Chan, W. Chen, N. Cheung, D.
SYSTEMS
SOLUTIONS MANUAL
THIRD EDITION
Raghu Ramakrishnan
University of Wisconsin
Madison, WI, USA
Johannes Gehrke
Cornell University
Ithaca, NY, USA
Jeff Derstadt, Scott Selikoff, and Lin Zhu
Cornell University
Ithaca, NY, USA
CONTENTS
PREFACE
iii
1
INTRODUCTION TO DATABASE SYSTEMS
1
2
INTRODUCTION TO DATABASE DESIGN
6
3
THE RELATIONAL MODEL
16
4
RELATIONAL ALGEBRA AND CALCULUS
28
5
SQL: QUERIES, CONSTRAINTS, TRIGGERS
45
6
DATABASE APPLICATION DEVELOPMENT
63
7
INTERNET APPLICATIONS
66
8
OVERVIEW OF STORAGE AND INDEXING
73
9
STORING DATA: DISKS AND FILES
81
10 TREE-STRUCTURED INDEXING
88
11 HASH-BASED INDEXING
100
12 OVERVIEW OF QUERY EVALUATION
119
13 EXTERNAL SORTING
126
14 EVALUATION OF RELATIONAL OPERATORS
131
i
iiDatabase Management Systems Solutions Manual Third Edition
15 A TYPICAL QUERY OPTIMIZER
144
16 OVERVIEW OF TRANSACTION MANAGEMENT
159
17 CONCURRENCY CONTROL
167
18 CRASH RECOVERY
179
19 SCHEMA REFINEMENT AND NORMAL FORMS
189
20 PHYSICAL DATABASE DESIGN AND TUNING
204
21 SECURITY
215
PREFACE
It is not every question that deserves an answer.
Publius Syrus, 42 B.C.
I hope that most of the questions in this book deserve an answer. The set of questions is unusually extensive, and is designed to reinforce and deepen students’ understanding of the concepts covered in each chapter. There is a strong emphasis on quantitative and problem-solving type exercises.
While I wrote some of the solutions myself, most were written originally by students in the database classes at Wisconsin. I’d like to thank the many students who helped in developing and checking the solutions to the exercises; this manual would not be available without their contributions. In alphabetical order: X. Bao, S. Biao, M.
Chakrabarti, C. Chan, W. Chen, N. Cheung, D.
References: CHAR(10000), supervisor CHAR(20),