MCSE Database Design on SQL Server 7考前辅导【2025|PDF下载-Epub版本|mobi电子书|kindle百度云盘下载】

MCSE Database Design on SQL Server 7考前辅导PDF格式电子书版下载

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

CHAPTER 1 AN OVERVIEW OF MICROSOFT SQL SERVER 71

Microsoft SQL Server Version 72

Features In Microsoft SQL Server 74

Speed4

Tuning5

Platforms5

Upgrades5

Data Transformations6

Improvements From Microsoft SQL Server 6.56

New Support For Windows 95 And Windows 98 Servers6

Dynamic Database Size Management7

Wizards8

Locking Enhancements9

Transact SQL Enhancements10

New Programming Interfaces11

COM12

OLE DB12

ADO12

Replication Enhancements13

SQL Server Agent13

Full-Text Search14

Improved Support For Data Warehousing15

Tools15

Enterprise Manager16

Query Analyzer17

SQL Profiler18

Index Tuning Wizard19

Data Transformation Services(DTS)20

osql21

SQL Scheduler21

The Database Diagram Tool22

Chapter Summary23

Review Questions25

Hands-On Projects27

CHAPTER 2 SQL DATABASE ARCHITECTURE31

The Relational Data Model32

The Architecture Of A Relational Database System32

Further Definition Of The Relational Model34

Structures Within A Relational Database34

Data Integrity37

Data Manipulation39

SQL Server Database Architecture41

Logical Database Components43

Physical Database Components46

Chapter Summary51

Review Questions52

Hands-On Projects55

CHAPTER 3 DEVELOPING A LOGICAL DATABASE DESIGN59

The Database Life Cycle60

Gathering Database Requirements61

Logical Database Design62

Entity-Relationship Modeling63

Basic Objects63

Characteristics Of Relationships65

Primary,Alternate,And Foreign Keys68

Nonkey Attributes71

An ER Model Of Northwind(Part 1)72

Data Normalization75

First Normal Form75

Second Normal Form76

Third Normal Form78

Boyce-Codd Normal Form78

Completing The Normalization Process79

An ER Model Of Northwind(Part 2)81

Business Rules83

Insert/Update Rules84

Data Domains86

Other Business Rules87

Chapter Summary88

Review Questions90

Hands-On Projects94

CHAPTER 4 DERIVING THE PHYSICAL DESIGN101

Physical Design In The Database Life Cycle102

Database Design Considerations104

Database Diagrams105

Preliminary Physical Design107

Table Definitions108

Column Definitions109

Column Attributes109

Physical Design For Northwind(Part 1)111

Logical Data Integrity113

Primary Keys And Unique Constraints114

Foreign Keys116

Check Constraint118

User-Defined Integrity118

Physical Design For Northwind(Part 2)119

Performance Expectations121

Indexes For Performance122

Denormalization123

Table Definitions124

Security126

Physical Design For Northwind(Part 3)126

Impact Of Database Design127

Chapter Summary128

Review Questions130

Hands-On Projects134

CHAPTER 5 TABLES137

Table Types138

Column Data Types139

NULL/NOT NULL Columns141

Identity Columns142

Inserting Explicit Values Into An Identity Column143

Checking And Correcting Identity Column Values143

Row Global Unique Identifier Column143

Defaults144

Default Object144

Default Definition145

Rules147

Column-Level And Table-Level Constraints148

CHECK Constraints149

PRIMARY KEY Constraint150

UNIQUE Constraint151

FOREIGN KEY Constraint153

Creating Tables154

Altering Tables156

Adding A Column156

Dropping A Column157

Modifying A Column Definition158

Adding/Dropping The Row Global Unique Identifier Property(ROWGUIDCOL)159

Adding A Constraint159

Dropping A Constraint160

Enabling And Disabling A FOREIGN KEY And CHECK Constraint160

Enabling And Disabling A Trigger161

Dropping Tables161

System Tables161

Chapter Summary162

Review Questions164

Hands-On Projects167

CHAPTER 6 INDEXES171

Index Basics172

Single-Column Index And Composite(Multicolumn)Index173

Index Intersection And Covered Indexes173

Clustered Index174

Nonclustered Index175

Unique Index177

Fill Factors178

PAD_INDEX180

Creating An Index180

CREATE INDEX Command181

Create Index Wizard183

DBCC DBREINDEX185

Dropping An Index186

Viewing An Index187

Statistics189

Updating Statistics191

Creating Statistics192

Dropping Statistics193

DBCC SHOW_STATISTICS193

Index Tuning194

Index Tuning Wizard195

Index Analysis Through The Query Analyzer201

Impact On Total Database Size201

Impact On Query Performance202

Impact On DML Performance203

Chapter Summary203

Review Questions205

Hands-On Projects209

CHAPTER 7 RETRIEVING AND MODIFYING DATA211

Retrieving Data With SELECT212

Simple SELECT Statements212

The WHERE Clause215

Eliminating Duplicate Rows With DISTINCT216

Sorting Query Output With ORDER BY217

Creating Tables With SELECT...INTO218

Using SELECT To Perform Joins221

UNION Queries228

Subqueries230

Using INSERT To Add Data235

Using INSERT To Add Individual Rows235

Using INSERT...SELECT To Insert Multiple Rows238

Using INSERT...EXECUTE To Insert Multiple Rows238

Removing Rows With DELETE240

Removing All Rows With TRUNCATE TABLE242

Using UPDATE To Modify Data244

Using UPDATE With Nested Subqueries245

Using UPDATE...FROM246

Query Tools And Interfaces246

Query Tools246

Programming Interfaces250

Chapter Summary251

Review Questions252

Hands-On Projects258

CHAPTER 8 TRANSACT-SQL SCRIPTING AND COMMAND BATCHES265

Putting The Pieces Together266

Batches And Scripts266

Local Variables269

Declaring Local Variables269

Using Local Variables269

Scope Of Local Variable Names270

Dynamic Batch Execution270

Transact-SQL Comments271

Control-Of-Flow Language273

Statements:The Basic Unit For Control-Of-Flow274

Statement Blocks:Using BEGIN...END274

IF...ELSE275

WHILE/BREAK/CONTINUE276

GOTO278

RETURN279

WAITFOR280

Cursors280

The Life Cycle Of A Cursor282

Types Of Cursors286

Modifying Data With WHERE CURRENT OF289

Grandma s Secret:Making Better Batches291

Chapter Summary293

Review Questions295

Hands-On Projects299

CHAPTER 9 SUMMARIZING AND TRANSFORMING DATA309

Going Beyond Rows And Columns310

Manipulating Data With Scalar Functions310

Configuration Functions310

Cursor Functions316

Date And Time Functions318

Mathematical Functions320

Metadata Functions322

Security Functions326

String Functions329

System Functions338

System Statistical Functions348

Text And Image Functions349

Summarizing Data With Aggregate Functions352

Scalar Aggregation353

Vector Aggregation And GROUP BY354

Other Data Summarization Methods357

Viewing Only The TOP Query Results357

Summarizing Multiple Grouping Levels With CUBE360

ROLLUP——A Hierarchical Subset Of CUBE362

Chapter Summary366

Review Questions368

Hands-On Projects372

CHAPTER 10 VIEWS379

Database Views380

Use Of A View381

Focus The Data For Users And As A Security Mechanism381

Shield The Complexity Of Data Manipulation382

Organize Data Import And Export383

Working With Partitioned Data Across Multiple Tables383

Creating A View384

Restrictions On Views385

Create View Command385

Create View Wizard388

Altering A View390

Dropping A View392

Querying Data Through A View392

Modifying Data Through A View392

System Views394

Nested Views And Performance397

Chapter Summary398

Review Questions399

Hands-On Projects402

CHAPTER 11 TRANSACTIONS AND LOCKING405

Defining A Transaction406

Definition406

ACID Properties406

Isolation Level407

Database Locking412

Locking Scope412

Types Of Locking413

Observing Database Locks414

Dynamic Locking416

Overriding Default Locking Behavior416

Deadlocking419

Observing Deadlock Behavior420

Avoiding Deadlocks421

Programming Transactions423

Implicit,Explicit,And Autocommit Transactions423

Naming Transactions424

Nesting425

Savepoints Within A Transaction426

Transaction Behavior Within Triggers427

Distributed Transactions427

Cursors And Optimistic Locking428

Chapter Summary430

Review Questions432

Hands-On Projects434

CHAPTER 12 STORED PROCEDURES439

Stored Procedures440

Why Use A Stored Procedure?440

Types Of Stored Procedures441

System Stored Procedures441

Extended Stored Procedures442

Local(User)Stored Procedures442

Remote Stored Procedures443

Temporary Stored Procedures443

Deferred Name Resolution And Compilation Of Stored Procedures443

Parsing443

Resolution444

Optimization444

Compilation445

Creating A Stored Procedure445

Grouping Stored Procedures447

CREATE PROCEDURE Statement447

Create Stored Procedure Wizard450

Nesting Stored Procedures451

Stored Procedures Parameters453

Input Parameters454

Output Parameters455

Returning Data From Stored Procedures457

Altering A Stored Procedure459

Dropping A Stored Procedure461

Executing A Stored Procedure461

Enforcing Business Rules With Stored Procedures464

The RAISERROR Statement465

Chapter Summary467

Review Questions468

Hands-On Projects471

CHAPTER 13 TRIGGERS475

What s A Trigger?476

Types Of Triggers476

Special Tables:Deleted Table And Inserted Table477

Creating A Trigger478

CREATE TRIGGER Statement480

Altering A Trigger482

Dropping A Trigger484

Checking For Changes To A Column484

IF UPDATE(<column_name>)484

IF COLUMNS_UPDATED()485

Triggers And Transactions487

Nested Triggers488

Recursive Triggers489

TRIGGER_NESTLEVEL()Function489

Enforcing Business Rules And Data Integrity With Triggers491

Cascading Updates And Cascading Deletes493

Coding Triggers494

Chapter Summary495

Review Questions497

Hands-On Projects501

CHAPTER 14 CREATING THE PHYSICAL DATABASE505

Sizing SQL Server Database Components506

Database File506

Primary Data File507

Secondary Data File507

Transaction Log File508

Database Filegroups508

Primary Filegroup509

User-Defined Filegroup510

Creating A Database510

CREATE DATABASE Statement511

Create Database Wizard514

Modifying A Database516

Adding Data Files516

Adding Transaction Log Files517

Removing A Data Or Transaction Log File517

Modifying A Data Or Transaction Log File517

Adding A Filegroup518

Removing A Filegroup518

Modifying A Filegroup Property(READONLY,READWRITE,DEFAULT)519

Dropping A Database519

Expanding A Database520

Shrinking A Database520

Automatically Shrinking A Databse520

DBCC SHRINKDATABASE Statement521

DBCC SHRINKFILE Statement521

Database Options522

Attaching And Detaching A Database525

sp_detach_db525

sp_attach_db526

Renaming A Database527

Database Owner528

System Databases528

master Database529

model Database529

msdb Database529

tempdb Database529

Storage Options And RAID Levels529

Chapter Summary531

Review Questions533

Hands-On Projects536

CHAPTER 15 REMOTE DATA SOURCES541

Remote Data Sources542

Remote Server Architecture542

Configuring A Remote Server543

Executing Remote Procedures544

Linked Server Architecture544

Seamless SELECT546

FREQUENT OPENQUERY548

Occasional OPENROWSET549

Leaving Locality550

Undeniable Updates552

Configuring Linked Servers553

SQL Server Data Sources554

MS Access Data Sources557

Oracle Data Sources558

Other Data Sources560

Additional Configuration Settings560

Chapter Summary563

Review Questions564

Hands-On Projects567

CHAPTER 16 DATA TRANSFERS569

Importing And Exporting570

Deciding Between Transfer Tools570

Bulk Copy572

Specifying Connection Information573

Working With The Host File574

Bulk Loads580

Processing Options582

Optimizing Performance582

BULK INSERT584

Data Transformation Services585

Import And Export Wizard585

DTS Designer587

Chapter Summary600

Review Questions601

Hands-On Projects604

CHAPTER 17 IMPLEMENTING FULL-TEXT SEARCH607

Full-Text Architecture608

Full-Text Catalog Administration612

Configuring A New Catalog614

Using The Full-Text Catalog Wizard616

Maintaining The Catalog618

Querying623

CONTAINS624

FREETEXT627

CONTAINSTABLE627

FREETEXTTABLE629

Chapter Summary629

Review Questions630

Hands-On Projects635

CHAPTER 18 MAINTAINING A DATABASE639

Database Consistency640

Storage Primer640

DBCC Commands642

Efficiency-Contiguous Storage648

DBCC SHOWCONTIG649

DBCC Reindex653

Identity Columns654

Distribution Statistics656

Database Maintenance Planning Wizard664

Chapter Summary665

Review Questions666

Hands-On Projects669

CHAPTER 19 DIAGNOSING PROBLEMS AND IMPROVING QUERY PERFORMANCE675

Query Plans676

Simple Text Query Plans676

Comprehensive Text Query Plans678

Graphical Query Plans678

Index Tuning Wizard682

Workload Files682

Running The Index Tuning Wizard684

SQL Profiler686

Tracing An Application686

Table Scans688

Finding Deadlocks689

Procedure Usage Profile690

Performance Monitor691

Access Methods693

Databases694

General Statistics695

Locks695

SQL Statistics696

User Settable Statistics697

Chapter Summary697

Review Questions698

Hands-On Projects701

APPENDIX A DESIGN ON SQL SERVER 7 EXAM OBJECTIVES707

APPENDIX B MCSE REQUIREMENTS711

GLOSSARY713

INDEX731