Database Systems Design, Implementation, and Management

by Rob, Peter; Coronel, Carlos

• ISBN13: 9781418835934
• ISBN10: 1418835935
• Edition: 7th
• Format: Hardcover
• Copyright: 2006-01-27
• Publisher: Course Technology

Summary

Database Systems: Design, Implementation, and Management, Seventh Edition, is one of the most comprehensive database textbooks available. We have retained the Parts organization of our market-leading sixth edition and streamlined the chapter coverage to make this the ideal choice for your classroom.

Table of Contents

PART I DATABASE CONCEPTS
Business Vignette: The Relational Revolution	3	(1)
Database Systems	4	(24)
Data vs. Information	5	(1)
Introducing the Database and the DBMS	6	(3)
Role and Advantages of the DBMS	7	(1)
Types of Databases	8	(1)
Why Database Design Is Important	9	(1)
Historical Roots: Files and File Systems	10	(3)
Problems with File System Data Management	13	(4)
Structural and Data Dependence	15	(1)
Field Definitions and Naming Conventions	15	(1)
Data Redundancy	16	(1)
Database Systems	17	(11)
The Database System Environment	18	(2)
DBMS Functions	20	(3)
Managing the Database System: A Shift in Focus	23	(1)
Summary	24	(1)
Key Terms	24	(1)
Review Questions	25	(1)
Problems	25	(3)
Data Models	28	(32)
The Importance of Data Models	29	(1)
Data Model Basic Building Blocks	30	(1)
Business Rules	31	(1)
Discovering Business Rules	31	(1)
Translating Business Rules into Data Model Components	32	(1)
The Evolution of Data Models	32	(14)
The Hierarchical Model	33	(1)
The Network Model	34	(2)
The Relational Model	36	(2)
The Entity Relationship Model	38	(3)
The Object-Oriented (OO) Model	41	(2)
Other Models	43	(1)
Database Models and the Internet	43	(1)
Data Models: A Summary	43	(3)
Degrees of Data Abstraction	46	(13)
The External Model	46	(2)
The Conceptual Model	48	(1)
The Internal Model	48	(1)
The Physical Model	49	(2)
Summary	51	(1)
Key Terms	51	(1)
Review Questions	52	(1)
Problems	53	(6)
PART II DESIGN CONCEPTS
Business Vignette: Database Modeling Feeding Children	59	(1)
The Relational Database Model	60	(42)
A Logical View of Data	61	(3)
Tables and Their Characteristics	61	(3)
Keys	64	(5)
Integrity Rules	69	(1)
Relational Set Operators	70	(6)
The Data Dictionary and the System Catalog	76	(2)
Relationships Within the Relational Database	78	(8)
The I:M Relationship	78	(2)
The I:I Relationship	80	(1)
The M:N Relationship	81	(5)
Data Redundancy Revisited	86	(2)
Indexes	88	(1)
Codd's Relational Database Rules	89	(13)
Summary	91	(1)
Key Terms	91	(1)
Review Questions	92	(2)
Problems	94	(8)
Entity Relationship (ER) Modeling	102	(45)
The Entity Relationship (ER) Model	103	(21)
Entities	103	(1)
Attributes	103	(6)
Relationships	109	(1)
Connectivity and Cardinality	109	(1)
Existence Dependence	110	(1)
Relationship Strength	111	(3)
Weak Entities	114	(2)
Relationship Participation	116	(2)
Relationship Degree	118	(2)
Recursive Relationships	120	(2)
Composite Entities	122	(2)
Developing an ER Diagram	124	(7)
Database Design Challenges: Conflicting Goals	131	(16)
Summary	134	(1)
Key Terms	134	(1)
Review Questions	135	(1)
Problems	136	(11)
Normalization of Database Tables	147	(36)
Database Tables and Normalization	148	(1)
The Need for Normalization	148	(3)
The Normalization Process	151	(7)
Conversion to First Normal Form	152	(3)
Conversion to Second Normal Form	155	(2)
Conversion to Third Normal Form	157	(1)
Improving the Design	158	(4)
Surrogate Key Considerations	162	(1)
Higher-Level Normal Forms	163	(5)
The Boyce-Codd Normal Form (BCNF)	164	(2)
Fourth Normal Form (4NF)	166	(2)
Normalization and Database Design	168	(4)
Denormalization	172	(11)
Summary	173	(2)
Key Terms	175	(1)
Review Questions	175	(1)
Problems	176	(7)
Advanced Data Modeling	183	(31)
The Extended Entity Relationship Model	184	(6)
Entity Supertypes and Subtypes	184	(1)
Specialization Hierarchy	185	(1)
Inheritance	186	(1)
Subtype Discriminator	187	(1)
Disjoint and Overlapping Constraints	187	(2)
Completeness Constraint	189	(1)
Specialization and Generalization	189	(1)
Entity Clustering	190	(1)
Entity Integrity: Selecting Primary Keys	191	(5)
Natural Keys and Primary Keys	192	(1)
Primary Key Guidelines	192	(1)
When to Use Composite Primary Keys	193	(1)
When to Use Surrogate Primary Keys	194	(2)
Design Cases: Learning Flexible Database Design	196	(5)
Design Case #1: Implementing I:I Relationships	196	(1)
Design Case #2: Maintaining History of Time-Variant Data	197	(2)
Design Case #3: Fan Traps	199	(1)
Design Case #4: Redundant Relationships	200	(1)
Data Modeling Checklist	201	(12)
Summary	203	(1)
Key Terms	203	(1)
Review Questions	204	(1)
Problems	204	(9)
PART III ADVANCED DESIGN AND IMPLEMENTATION
Business Vignette: Database Systems Managing Nuclear Waste	213	(1)
Introduction to Structured Query Language (SQL)	214	(71)
Introduction to SQL	215	(1)
Data Definition Commands	216	(14)
The Database Model	217	(2)
Creating the Database	219	(1)
The Database Schema	219	(1)
Data Types	220	(2)
Creating Table Structures	222	(3)
SQL Constraints	225	(4)
SQL Indexes	229	(1)
Data Manipulation Commands	230	(7)
Adding Table Rows	230	(2)
Saving Table Changes	232	(1)
Listing Table Rows	232	(2)
Updating Table Rows	234	(1)
Restoring Table Contents	234	(1)
Deleting Table Rows	235	(1)
Inserting Table Rows with a Select Subquery	235	(2)
Select Queries	237	(10)
Selecting Rows with Conditional Restrictions	237	(4)
Arithmetic Operators: The Rule of Precedence	241	(1)
Logical Operators: And, Or, and Not	241	(2)
Special Operators	243	(4)
Advanced Data Definition Commands	247	(6)
Changing a Column's Data Type	247	(1)
Changing a Column's Data Characteristics	247	(1)
Adding a Column	248	(1)
Dropping a Column	248	(1)
Advanced Data Updates	249	(1)
Copying Parts of Tables	250	(2)
Adding Primary and Foreign Key Designations	252	(1)
Deleting a table from the Database	253	(1)
Advanced Select Queries	253	(9)
Ordering a Listing	253	(2)
Listing Unique Values	255	(1)
Aggregate Functions	255	(5)
Grouping Data	260	(2)
Virtual Tables: Creating a View	262	(2)
Joining Database Tables	264	(21)
Joining Tables with an Alias	266	(1)
Recursive Joins	266	(1)
Outer Joins	267	(2)
Summary	269	(1)
Key Terms	270	(1)
Review Questions	270	(4)
Problems	274	(11)
Advanced SQL	285	(74)
Relational Set Operators	286	(7)
Union	286	(2)
Union All	288	(1)
Intersect	288	(1)
Minus	289	(1)
Syntax Alternatives	290	(3)
SQL Join Operators	293	(7)
Cross Join	294	(1)
Natural Join	295	(1)
Join Using Clause	296	(1)
Join On Clause	297	(1)
Outer Joins	298	(2)
Subqueries and Correlated Queries	300	(12)
Where Subqueries	302	(1)
In Subqueries	303	(1)
Having Subqueries	304	(1)
Multirow Subquery Operators: Any and All	305	(1)
From Subqueries	306	(1)
Attribute List Subqueries	307	(2)
Correlated Subqueries	309	(3)
SQL Functions	312	(6)
Date and Time Functions	312	(2)
Numeric Functions	314	(1)
String Functions	315	(1)
Conversion Functions	316	(2)
Oracle Sequences	318	(3)
Updatable Views	321	(3)
Procedural SQL	324	(23)
Triggers	328	(10)
Stored Procedures	338	(6)
PL/SQL Processing with Cursors	344	(2)
PL/SQL Stored Functions	346	(1)
Embedded SQL	347	(12)
Summary	352	(1)
Key Terms	353	(1)
Review Questions	353	(1)
Problems	354	(5)
Database Design	359	(37)
The Information System	360	(1)
The Systems Development Life Cycle (SDLC)	361	(3)
Planning	362	(1)
Analysis	363	(1)
Detailed Systems Design	363	(1)
Implementation	363	(1)
Maintenance	364	(1)
The Database Life Cycle (DBLC)	364	(23)
The Database Initial Study	365	(4)
Database Design	369	(13)
Implementation and Loading	382	(3)
Testing and Evaluation	385	(1)
Operation	386	(1)
Maintenance and Evolution	386	(1)
Database Design Strategies	387	(1)
Centralized vs. Decentralized Design	388	(7)
Summary	391	(1)
Key Terms	391	(1)
Review Questions	391	(1)
Problems	392	(3)
PART IV ADVANCED DATABASE CONCEPTS
Business Vignette: AT&T's Daytona in the Lead	395	(1)
Transaction Management and Concurrency Control	396	(29)
What is a Transaction?	397	(7)
Evaluating Transaction Results	399	(2)
Transaction Properties	401	(1)
Transaction Management with SQL	402	(1)
The Transaction Log	402	(2)
Concurrency Control	404	(4)
Lost Updates	404	(1)
Uncommitted Data	405	(1)
Inconsistent Retrievals	405	(2)
The Scheduler	407	(1)
Concurrency Control with Locking Methods	408	(7)
Lock Granularity	408	(3)
Lock Types	411	(2)
Two-Phase Locking to Ensure Serialization	413	(1)
Deadlocks	414	(1)
Concurrency Control with Time Stamping Methods	415	(1)
Wait/Die and Wound/Wait Schemes	415	(1)
Concurrency Control with Optimistic Methods	416	(1)
Database Recovery Management	416	(9)
Transaction Recovery	418	(3)
Summary	421	(1)
Key Terms	422	(1)
Review Questions	422	(3)
Problems	425	(1)
Database Performance Tuning and Query Optimization	425	(30)
Database Performance-Tuning Concepts	426	(4)
Performance Tuning: Client and Server	427	(1)
DBMS Architecture	427	(2)
Database Statistics	429	(1)
Query Processing	430	(2)
SQL Parsing Phase	430	(2)
SQL Execution Phase	432	(1)
SQL Fetching Phase	432	(1)
Indexes and Query Optimization	432	(2)
Optimizer Choices	434	(2)
Using Hints to Affect Optimizer Choices	435	(1)
SQL Performance Tuning	436	(3)
Index Selectivity	436	(1)
Conditional Expressions	437	(2)
Query Formulation	439	(1)
DBMS Performance Tuning	440	(2)
Query Optimization Example	442	(13)
Summary	450	(1)
Key Terms	451	(1)
Review Questions	451	(1)
Problems	452	(3)
Distributed Database Management Systems	455	(35)
The Evolution of Distributed Database Management Systems	456	(2)
DDBMS Advantages and Disadvantages	458	(1)
Distributed Processing and Distributed Databases	459	(2)
Characteristics of Distributed Database Management Systems	461	(1)
DDBMS Components	462	(1)
Levels of Data and Process Distribution	463	(4)
Single-Site Processing, Single-Site Data (SPSD)	464	(1)
Multiple-Site Processing, Single-Site Data (MPSD)	465	(1)
Multiple-Site Processing, Multiple-Site Data (MPMD)	466	(1)
Distributed Database Transparency Features	467	(1)
Distribution Transparency	468	(2)
Case 1: The Database Supports Fragmentation Transparency	469	(1)
Case 2: The Database Supports Location Transparency	469	(1)
Case 3: The Database Supports Local Mapping Transparency	470	(1)
Transaction Transparency	470	(6)
Distributed Requests and Distributed Transactions	470	(3)
Distributed Concurrency Control	473	(2)
Two-Phase Commit Protocol	475	(1)
Performance Transparency and Query Optimization	476	(1)
Distributed Database Design	477	(6)
Data Fragmentation	477	(4)
Data Replication	481	(2)
Data Allocation	483	(1)
Client/Server vs. DDBMS	483	(1)
C. J. Date's Twelve Commandments for Distributed Databases	484	(6)
Summary	485	(1)
Key Terms	486	(1)
Review Questions	486	(1)
Problems	487	(3)
The Data Warehouse	490	(56)
The Need for Data Analysis	491	(1)
Decision Support Systems	492	(7)
Operational Data vs. Decision Support Data	493	(4)
DSS Database Requirements	497	(2)
The Data Warehouse	499	(5)
DSS Architectural Styles	502	(1)
Twelve Rules That Define a Data Warehouse	502	(2)
Online Analytical Processing	504	(11)
Multidimensional Data Analysis Techniques	504	(1)
Advanced Database Support	505	(1)
Easy-to-Use End-User Interface	506	(1)
Client/Server Architecture	506	(1)
OLAP Architecture	507	(4)
Relational OLAP	511	(2)
Multidimensional OLAP	513	(2)
Relational vs. Multidimensional OLAP	515	(1)
Star Schemas	515	(10)
Facts	515	(1)
Dimensions	516	(1)
Attributes	516	(2)
Attribute Hierarchies	518	(1)
Star Schema Representation	519	(3)
Star Schema Performance-Improving Techniques	522	(3)
Implementing a Data Warehouse	525	(2)
The Data Warehouse as an Active Decision Support Framework	525	(1)
A Company-Wide Effort That Requires User Involvement	526	(1)
Satisfy the Trilogy: Data, Analysis, and Users	526	(1)
Apply Database Design Procedures	526	(1)
Data Mining	527	(4)
SQL Extensions for OLAP	531	(14)
The Rollup Extension	532	(1)
The Cube Extension	532	(1)
Materialized Views	533	(5)
Summary	538	(1)
Key Terms	539	(1)
Review Questions	539	(1)
Problems	540	(5)
PART V DATABASES AND THE INTERNET
Business Vignette: eBay Disaggregates to Stay Competitive	545	(1)
Database Connectivity and Web Development	546	(44)
Database Connectivity	547	(9)
Native SQL Connectivity	547	(1)
ODBC, DAO, and RDO	547	(4)
OLE-DB	551	(2)
ADO.NET	553	(3)
Internet Databases	556	(5)
Web-to-Database Middleware: Server-Side Extensions	557	(1)
Web Server Interfaces	558	(1)
The Web Browser	559	(1)
Client-Side Extensions	560	(1)
Using a Web-to-Database Production Tool: ColdFusion	561	(14)
How ColdFusion Works	562	(1)
The RobCor Sample Database	563	(1)
Creating a Simple Query with CFQUERY and CFOUTPUT	564	(5)
Creating a Simple Query with CFQUERY and CFTABLE	569	(1)
Creating a Dynamic Search Page	570	(4)
The Web As a Stateless System	574	(1)
Extensible Markup Language (XML)	575	(14)
Document Type Definitions (DTD) and XML Schemas	577	(3)
XML Presentation	580	(3)
XML Applications	583	(2)
Summary	585	(1)
Key Terms	586	(1)
Review Questions	586	(1)
Problems	587	(2)
PART VI DATABASE ADMINISTRATION
Business Vignette: NASDAQ High-Performance IT	589	(1)
Database Administration	590	(430)
Data as a Corporate Asset	591	(1)
The Need for and Role of Databases in an Organization	592	(1)
Introduction of a Database: Special Considerations	593	(1)
The Evolution of the Database Administration Function	594	(3)
The Database Environment's Human Component	597	(14)
The DBA's Managerial Role	600	(5)
The DBA's Technical Role	605	(6)
Database Administration Tools	611	(4)
The Data Dictionary	611	(2)
Case Tools	613	(2)
Developing a Data Administration Strategy	615	(2)
The DBA at Work: Using Oracle for Database Administration	617	(403)
Oracle Database Administration Tools	618	(1)
The Default Login	618	(1)
Ensuring an Automatic RDBMS Start	619	(1)
Creating Tablespaces and Datafiles	620	(1)
Managing the Database Objects: Tables, Views, Triggers, and Procedures	621	(1)
Managing Users and Establishing Security	622	(3)
Customizing the Database Initialization Parameters	625	(1)
Creating a New Database	626	(7)
Summary	633	(1)
Key Terms	634	(1)
Review Questions	634
Appendix A Designing Databases with Visio Professional: A Tutorial
Appendix B The University Lab: Conceptual Design
Appendix C The University Lab: Conceptual Design Verification, Logical Design, and Implementation
Appendix D Converting an ER Model into a Database Structure
Appendix E Comparison of ER Model Notations
Appendix F Client/Server Systems
Appendix G Object-Oriented Databases
Appendix H Unified Modeling Language (UML)
Appendix I Databases in Electronic Commerce
Appendix J Web Database Development with Coldfusion
Appendix K The Hierarchical Database Model
Appendix L The Network Database Model
Answers to Selected Questions and Problems