Contents
Table 252 • Creating the WORK Table and the 1:N ARTIST-to-WORK
Relationship 254 • Implementing Required Parent Rows 255 • Implementing
1:1 Relationships 256 • Casual Relationships 256 • Creating Default Values
and Data Constraints with SQL 256 • Creating the View Ridge Database
Tables 258 • The SQL ALTER TABLE Statement 261 • The SQL DROP TABLE
Statement 262 • The SQL TRUNCATE TABLE Statement 263
SQL DML Statements 263
The SQL INSERT Statement 263 • Populating the View Ridge Database Tables 264
• The SQL UPDATE Statement 270 • The SQL MERGE Statement 271 •
The SQL DELETE Statement 272
New Forms of Join 272
The SQL JOIN ON Syntax 272 • Outer Joins 274
Using SQL Views 277
Using SQL Views to Hide Columns and Rows 280 • Using SQL Views to Display
Results of Computed Columns 281 • Using SQL Views to Hide Complicated SQL
Syntax 282 • Layering Built-in Functions 284 • Using SQL Views for
Isolation, Multiple Permissions, and Multiple Triggers 285 • Updating SQL Views 286
Embedding SQL in Program Code 287
SQL/Persistent Stored Modules (SQL/PSM) 288 • Using SQL Triggers 289 •
Using Stored Procedures 295
Summary 298 • Key Terms 299 • Review Questions 299 • Project
Questions 303 • Marcia’s Dry Cleaning 306 • Morgan Importing 309
Chapter 8: Database Redesign 313
Chapter Objectives 313
The Need for Database Redesign 314
SQL Statements for Checking Functional Dependencies 314
What Is a Correlated Subquery? 315
How Do I Analyze an Existing Database? 320
Reverse Engineering 320 • Dependency Graphs 322 • Database Backup and
Test Databases 322
Changing Table Names and Table Columns 323
Changing Table Names 323 • Adding and Dropping Columns 325 • Changing
a Column Data Type or Column Constraints 326 • Adding and Dropping
Constraints 326
Changing Relationship Cardinalities and Properties 326
Changing Minimum Cardinalities 327 • Changing Maximum Cardinalities 328
Adding and Deleting Tables and Relationships 331
Forward Engineering(?) 331
Summary 331 • Key Terms 333 • Review Questions 333 • Project
Questions 335 • Marcia’s Dry Cleaning 335 • Morgan Importing 336
PART 4 • MULTIUSER DATABASE PROCESSING 337
Chapter 9: Managing Multiuser Databases 338
Chapter Objectives 338
Database Administration 339
Managing the Database Structure 340
Concurrency Control 341
The Need for Atomic Transactions 342 • Resource Locking 346 • Optimistic
Versus Pessimistic Locking 348 • Declaring Lock Characteristics 349 • Implicit and
Explicit Commit Transaction 350 • Consistent Transactions 351 • Transaction
Isolation Level 352 • Cursor Type 353