Database Systems An Application-Oriented Approach, Introductory Version

by Kifer, Michael; Bernstein, Arthur; Lewis, Philip M.

• ISBN13: 9780321228383
• ISBN10: 0321228383
• Additional ISBN(s): 9780201708721
• Edition: 2nd
• Format: Paperback
• Copyright: 2004-03-30
• Publisher: Pearson
• More Book Details

Summary

This book presents the conceptual principles underlying the design and implementation of databases and their applications by providing a solid foundation of the theory underlying database systems.This book takes an applications-oriented approach to database concepts and covers topics including; ER Modeling, UML, XML, object-oriented databases, SQL, database tuning, and the important software issues that arise when implementing database applications.This book is appropriate for programmers who want to understand database system concepts and how they apply to real-world databases.

Table of Contents

Preface	xvii
PART ONE Introduction	1	(28)
Overview of Databases and Transactions	3	(10)
What Are Databases and Transactions?	3	(3)
Features of Modern Database and Transaction Processing Systems	6	(1)
Major Players in the Implementation and Support of Database and Transaction Processing Systems	7	(2)
Decision Support Systems---OLAP and OLTP	9	(4)
The Big Picture	13	(16)
Case Study: A Student Registration System	13	(1)
Introduction to Relational Databases	14	(6)
What Makes a Program a Transaction---The ACID Properties	20	(9)
Bibliographic Notes	25	(1)
Exercises	25	(4)
PART TWO Database Management	29	(290)
The Relational Data Model	31	(38)
What Is a Data Model?	31	(4)
The Relational Model	35	(11)
Basic Concepts	35	(3)
Integrity Constraints	38	(8)
SQL---Data Definition Sublanguage	46	(23)
Specifying the Relation Type	46	(1)
The System Catalog	46	(1)
Key Constraints	47	(1)
Dealing with Missing Information	48	(1)
Semantic Constraints	49	(4)
User-Defined Domains	53	(1)
Foreign-Key Constraints	53	(3)
Reactive Constraints	56	(3)
Database Views	59	(1)
Modifying Existing Definitions	60	(2)
SQL-Schemas	62	(1)
Access Control	63	(2)
Bibliographic Notes	65	(1)
Exercises	66	(3)
Conceptual Modeling of Databases with Entry-Relationship Diagrams and the Unified Modeling Language	69	(58)
Conceptual Modeling with the E-R Approach	70	(1)
Entities and Entity Types	70	(3)
Relationships and Relationship Types	73	(5)
Advanced Features in Conceptual Data Modeling	78	(8)
Entity Type Hierarchies	78	(3)
Participation Constraints	81	(2)
The Part-of Relationship	83	(3)
From E-R Diagrams to Relational Database Schemas	86	(9)
Representation of Entities	86	(2)
Representation of Relationships	88	(2)
Representing IsA Hierarchies in the Relational Model	90	(2)
Representation of Participation Constraints	92	(2)
Representation of the Part-of Relationship	94	(1)
UML: A New Kid on the Block	95	(11)
Representing Entities in UML	96	(1)
Representing Relationships in UML	97	(4)
Advanced Modeling Concepts in UML	101	(4)
Translation to SQL	105	(1)
A Brokerage Firm Example	106	(5)
An Entity-Relationship Design	106	(4)
A UML Design	110	(1)
Case Study: A Database Design for the Student Registration System	111	(8)
The Database Part of the Requirements Document	112	(1)
The Database Design	113	(6)
Limitations of Data Modeling Methodologies	119	(8)
Bibliographic Notes	123	(1)
Exercises	123	(4)
Relational Algebra and SQL	127	(66)
Relational Algebra: Under the Hood of SQL	128	(19)
Basic Operators	128	(9)
Derived Operators	137	(10)
The Query Sublanguage of SQL	147	(35)
Simple SQL Queries	148	(6)
Set Operations	154	(3)
Nested Queries	157	(6)
Quantified Predicates	163	(1)
Aggregation over Data	164	(6)
Join Expressions in the FROM Clause	170	(1)
A Simple Query Evaluation Algorithm	171	(3)
More on Views in SQL	174	(3)
Materialized Views	177	(4)
The Null Value Quandary	181	(1)
Modifying Relation Instances in SQL	182	(11)
Inserting Data	182	(2)
Deleting Data	184	(1)
Updating Existing Data	185	(1)
Updates on Views	185	(2)
Bibliographic Notes	187	(1)
Exercises	188	(5)
Database Design with the Relational Normalization Theory	193	(58)
The Problem of Redundancy	193	(2)
Decompositions	195	(3)
Functional Dependencies	198	(2)
Properties of Functional Dependencies	200	(7)
Normal Forms	207	(4)
The Boyce-Codd Normal Form	208	(2)
The Third Normal Form	210	(1)
Properties of Decompositions	211	(8)
Lossless and Lossy Decompositions	212	(3)
Dependency-Preserving Decompositions	215	(4)
An Algorithm for BCNF Decomposition	219	(2)
Synthesis of 3NF Schemas	221	(7)
Minimal Cover	222	(2)
3NF Decomposition through Schema Synthesis	224	(2)
BCNF Decomposition through 3NF Synthesis	226	(2)
The Fourth Normal Form	228	(5)
Advanced 4NF Design	233	(7)
MVDs and Their Properties	234	(1)
The Difficulty of Designing for 4NF	235	(3)
A 4NF Decomposition How-To	238	(2)
Summary of Normal Form Decomposition	240	(1)
Case Study: Schema Refinement for the Student Registration System	241	(3)
Tuning Issues: To Decompose or Not to Decompose?	244	(7)
Bibliographic Notes	245	(1)
Exercises	246	(5)
Triggers and Active Databases	251	(16)
What Is a Trigger?	251	(1)
Semantic Issues in Trigger Handling	252	(4)
Triggers in SQL: 1999	256	(8)
Avoiding a Chain Reaction	264	(3)
Bibliographic Notes	265	(1)
Exercises	265	(2)
Using SQL in an Application	267	(52)
What Are the Issues Involved?	267	(1)
Embedded SQL	268	(17)
Status Processing	271	(2)
Sessions, Connections, and Transactions	273	(1)
Executing Transactions	274	(2)
Cursors	276	(6)
Stored Procedures on the Server	282	(3)
More on Integrity Constraints	285	(1)
Dynamic SQL	286	(8)
Statement Preparation in Dynamic SQL	287	(3)
Prepared Statements and the Descriptor Area	290	(3)
Cursors	293	(1)
Stored Procedures on the Server	293	(1)
JDBC and SQLJ	294	(13)
JDBC Basics	294	(3)
Prepared Statements	297	(1)
Result Sets and Cursors	297	(3)
Obtaining Information about a Result Set	300	(1)
Status Processing	300	(1)
Executing Transactions	301	(1)
Stored Procedures on the Server	302	(1)
An Example	303	(1)
SQLJ: Statement-Level Interface to Java	303	(4)
ODBC	307	(8)
Prepared Statements	309	(1)
Cursors	309	(3)
Status Processing	312	(1)
Executing Transactions	312	(1)
Stored Procedures on the Server	313	(1)
An Example	313	(2)
Comparison	315	(4)
Bibliographic Notes	316	(1)
Exercises	316	(3)
PART THREE Optimizing DBMS Performance and Transaction Processing	319	(168)
Physical Data Organization and Indexing	321	(58)
Disk Organization	322	(7)
Raid Systems	326	(3)
Heap Files	329	(4)
Sorted Files	333	(4)
Indices	337	(10)
Clustered versus Unclustered Indices	340	(2)
Sparse versus Dense Indices	342	(2)
Search Keys Containing Multiple Attributes	344	(3)
Multilevel Indexing	347	(13)
Index-Sequential Access	350	(3)
B+ Trees	353	(7)
Hash Indexing	360	(11)
Static Hashing	360	(3)
Dynamic Hashing Algorithms	363	(8)
Special-Purpose Indices	371	(2)
Bitmap Indices	371	(1)
Join Indices	372	(1)
Tuning Issues: Choosing Indices for an Application	373	(6)
Bibliographic Notes	374	(1)
Exercises	375	(4)
The Basics of Query Processing	379	(26)
Overview of Query Processing	379	(1)
External Sorting	380	(4)
Computing Projection, Union, and Set Difference	384	(2)
Computing Selection	386	(6)
Selections with Simple Conditions	387	(2)
Access Paths	389	(2)
Selections with Complex Conditions	391	(1)
Computing Joins	392	(7)
Computing Joins Using Simple Nested Loops	393	(3)
Sort-Merge Join	396	(2)
Hash Join	398	(1)
Multirelational Joins	399	(2)
Computing Aggregate Functions	401	(4)
Bibliographic Notes	401	(1)
Exercises	401	(4)
An Overview of Query Optimization	405	(24)
Query Processing Architecture	405	(2)
Heuristic Optimization Based on Algebraic Equivalences	407	(3)
Estimating the Cost of a Query Execution Plan	410	(8)
Estimating the Size of the Output	418	(2)
Choosing a Plan	420	(9)
Bibliographic Notes	425	(1)
Exercises	425	(4)
Database Tuning	429	(26)
Disk Caches	430	(3)
Tuning the Cache	431	(2)
Tuning the Schema	433	(10)
Indices	433	(7)
Denormalization	440	(1)
Repeating Groups	441	(1)
Partitioning	442	(1)
Tuning the Data Manipulation Language	443	(3)
Tools	446	(1)
Managing Physical Resources	447	(1)
Influencing the Optimizer	448	(7)
Bibliographic Notes	451	(1)
Exercises	451	(4)
An Overview of Transaction Processing	455	(32)
Isolation	455	(17)
Serializability	456	(2)
Two-Phase Locking	458	(4)
Deadlock	462	(1)
Locking in Relational Databases	463	(2)
Isolation Levels	465	(3)
Lock Granularity and Intention Locks	468	(3)
Summary	471	(1)
Atomicity and Durability	472	(5)
The Write-Ahead Log	472	(4)
Recovery from Mass Storage Failure	476	(1)
Implementing Distributed Transactions	477	(10)
Atomicity and Durability---The Two-Phase Commit Protocol	478	(2)
Global Serializability and Deadlock	480	(2)
Replication	482	(2)
Summary	484	(1)
Bibliographic Notes	484	(1)
Exercises	485	(2)
PART FOUR Software Engineering Issues and Documentation	487	(48)
Requirements and Specifications	489	(20)
Software Engineering Methodology	489	(4)
UML Use Cases	490	(3)
The Requirements Document for the Student Registration System	493	(7)
Requirements Analysis---New Issues	500	(2)
Specifying the Student Registration System	502	(2)
UML Sequence Diagrams	503	(1)
The Specification Document for the Student Registration System: Section III	504	(2)
The Next Step in the Software Engineering Process	506	(3)
Bibliographic Notes	506	(1)
Exercises	507	(2)
Design, Coding, and Testing	509	(26)
The Design Process	509	(6)
Database Design	510	(1)
Describing the Behavior of Objects with UML State Diagrams	510	(2)
Structure of the Design Document	512	(2)
Design Review	514	(1)
Test Plan	515	(3)
Project Planning	518	(3)
Coding	521	(2)
Incremental Development	523	(1)
The Project Management Plan	524	(1)
Design and Code for the Student Registration System	525	(10)
Completing the Database Design: Integrity Constraints	526	(2)
Design of the Registration Transaction	528	(2)
Partial Code for the Registration Transaction	530	(3)
Bibliographic Notes	533	(1)
Exercises	533	(2)
PART FIVE Advanced Topics in Databases	535	(104)
Introduction to Object Databases	537	(30)
Shortcomings of the Relational Data Model	537	(6)
The Conceptual Object Data Model	543	(7)
Objects and Values	544	(1)
Classes	545	(1)
Types	546	(3)
Object-Relational Databases	549	(1)
Objects in SQL: 1999 and SQL:2003	550	(17)
Row Types	551	(1)
User-Defined Types	552	(1)
Objects	553	(1)
Querying User-Defined Types	554	(1)
Updating User-Defined Types	555	(3)
Reference Types	558	(2)
Inheritance	560	(1)
Collection Types	561	(2)
Bibliographic Notes	563	(1)
Exercises	564	(3)
Introduction to XML and Web Data	567	(72)
Semistructured Data	567	(3)
Overview of XML	570	(16)
XML Elements and Database Objects	573	(2)
XML Attributes	575	(2)
Namespaces	577	(5)
Document Type Definitions	582	(3)
Inadequacy of DTDs as a Data Definition Language	585	(1)
XML Schema	586	(29)
XML Schema and Namespaces	587	(3)
Simple Types	590	(5)
Complex Types	595	(8)
Putting It Together	603	(3)
Shortcuts: Anonymous Types and Element References	606	(2)
Integrity Constraints	608	(7)
XML Query Languages	615	(24)
XPath: A Lightweight XML Query Language	616	(7)
SQL/XML	623	(10)
Bibliographic Notes	633	(1)
Exercises	634	(5)
Bibliography	639	(10)
Index	649