Microsoft SQL Server 2000 Database Design

437 Pages·2003·5.952 MB·English

by  Not Available (NA)

INSTRUCTOR GUIDE
n
o
i
t
i
d
E

E
r
T
o
A
C
t I
L
c
P
U
u
D

T
r
O
t N
s
O
Microsoft SQL Server 2000 - Database
D
n
Design
I
Microsoft SQL Server 2000
n
Database Design
o
i
t
i
d
E

E
r
T
o
A
C
t I
L
c
P
U
u
D

T
r
O
t N
s
O
D
n
I
M SQL S 2000 - D D
ICROSOFT ERVER ATABASE ESIGN
n
CourseNumber:NH77462 (IGEE)
CourseEdition:1
Forsoftwareversion:2000
ACKNOWLEDGEMENTS o
ProjectTeam
i
CurriculumDeveloperandTechnicalWriter:RozanneM.Whalen•CopyEditor:ChristyD.Flanders•
ReviewingEditors:ElizabethM.Swank,TarynChaseandAngiteJ.French•QualityAssuranceAnalyst:Frank
Wosnick•PrintDesigner:DanielP.Smith
i
ProjectSupport
d
ManagingEditor:ClareS.Dygert•AcquisitionsEditor:TinaMariaNelson
Administration
SeniorDirectorofContentandContentDevEelopment:WilliamO.Ingle•DirectorofCertification:Mike
Grakowsky•DirectorofDesignandWebDevelopment:JoyInsinna•ManagerofOfficeProductivityand
AppliedLearning:CherylRusso•ManagerofDatabases,ERP,andBusinessSkills:MarkOnisk•Director
ofBusinessDevelopment:KentMichels•InstructionalDesignManager:SusanL.Reber•Managerof
PublishingServices:MichaelHoyt
E
r
NOTICES T
o
A
DISCLAIMER:WhileElementKPressLLCtakescaretoensuretheaccuracyandqualityofthesematerials,wecannotguaranteetheiraccuracy,andallmaterialsare
C
providedwithoutanywarrantywhatsoever,including,butnotlimitedto,theimpliedwarrantiesofmerchantabilityorfitnessforaparticularpurpose.Thenameusedinthedata
filesforthiscourseisthatofafictitiouscompany.Anyresemblancetocurrentorfuturecompaniesispurelycoincidental.Wedonotbelievewehaveusedanyone’snamein
creatingthiscourse,butiftwehave,pleasenotifyusandwewillchangethenameinthenextrevisionIofthecourse.ElementKisanindependentproviderofintegrated
trainingsolutionsforindividuals,businesses,educationalinstitutions,andgovernmentagencies.UsLeofscreenshotsoranotherentity’sproductnameorserviceinthisbook
isforeditorialpurposesonly.Nosuchuseshouldbeconstruedtoimplysponsorshiporendorsementofthebookby,noranyaffiliationofsuchentitywithElementK.
c
P
TRADEMARKNOTICES:ElementKandtheElementKlogoaretrademarksofElementKLLC.SQLServerisaregisteredtrademarkofMicrosoftCorporationintheU.S.and
othercountries.Allotherproductnamesandservicesusedthroughoutthisbookmaybecommonlaworregisteredtrademarksoftheirrespectiveproprietors.
U
Copyright©2001ElementKContentLLC.Allrightsreserved.Screenshotsusedforillustrativepurposesarethepropertyofthesoftwareproprietor.Thispublication,orany
partthuereof,maynotbereproducedortransmittedinanyformorbyanymeans,electronicormechanical,includingphotocopying,recording,storageinaninformation
D
retrievalsystem,orotherwise,withoutexpresswrittenpermissionofElementK,500CanalViewBoulevard,Rochester,NY14623,[phone],[phone].ElementK
PressLLC’sWorldWideWebsiteislocatedatwww.elementkpress.com.

ThisbookconveysnorightsinthesoftwareorotherproductsaboutwhTichitwaswritten;alluseorlicensingofsuchsoftwareorotherproductsistheresponsibilityofthe
useraccordingtotermsandconditionsoftheowner.Donotmakeillegalcopiesofbooksorsoftware.Ifyoubelievethatthisbook,relatedmaterials,oranyotherElementK
r
materialsarebeingreproducedortransmittedwithoutpermission,pleasecall1-800-478-7788.
O
t N
s
O
D
n
I
ii
M SQL S 2000 - D D CONTENT
ICROSOFT ERVER ATABASE ESIGN
OVERVIEW
n
o
AboutThis Course . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Lesson 1:An Overview of SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
i
t
Lesson 2:ExploringTransact-SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
i
Lesson 3:Designing and Implementing Databases . . . . . . . . . . . . . . . . . .d. . . . . 91
Lesson 4:Creating and ManagingTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
E
Lesson 5:Designing and Implementing Data Integrity . . . . . . . . . . . . . . . . . . . . . 143

Lesson 6:Implementing Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
E
r
T
Lesson 7:JoiningTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
o
A
C
Lesson 8:DesigningAdvanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
t I
L
Lesson 9:DesigningViews . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
c
P
U
Lesson 10:Creating Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247
u
D
Lesson 11:Using Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
T
r
O
Lesson 12:CreatingTriggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
t N
Lesson 13:UnderstandsingTransactions and Locks . . . . . . . . . . . . . . . . . . . . . . . 317
O
Lesson 14:Implementing Distributed Queries . . . . . . . D. . . . . . . . . . . . . . . . . . . . 337
n
Lesson 15:Optimizing Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
I
Lesson 16:Analyzing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
AppendixA:The Movies Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Contents iii
CONTENT Appendix B:Course Script Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
OVERVIEW Additional Instructor Notes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
n
Glossary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405
o
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409
i
t
i
d
E

E
r
T
o
A
C
t I
L
c
P
U
u
D

T
r
O
t N
s
O
D
n
I
iv
M SQL S 2000 - D D CONTENTS
ICROSOFT ERVER ATABASE ESIGN
n
o
About This Course
About This Course......................................... xvi
Course Setup Information................................... xvi
t
How to Use This Book...................................... xxi
i
L 1: A O SQL S d
ESSON N VERVIEW OF ERVER
TOPIC 1A
Exploring the Components of SQL Server ......E......... 2
Features of Microsoft SQL Server ............................. 3
Task 1A-1 Exploring the Features of Microsoft SQL Server .......... 6
Components of SQL Server ................... ............... 6
Task 1A-2 Exploring the SQL Server Services...r................. 7 E
Administrative Tools ....................................... 7 T
Task 1A-3 Identifying Administrative Utilitioes................... 9
A
Registering Servers........................................ 9
C
Task 1A-4 Using SQL Server Enterprise Manager ................. 10
SQL Query Analyzer...............t......................... 1I2
L
Task 1A-5 Using SQL Query Analyzer......................... 13
c
P
TOPIC 1B
SQL Server Database Structure ........................ 16
U
Task 1B-1 Discussing the Components of SQL Server .............. 20
u
D
System Tables ............................................ 22
Task 1B-2 Identifying the Default Databases and System Tabl es ...... 24
T
Creating Database Diagrrams ................................. 26
O
Task 1B-3 Creating and Working with a Database Diagram .......... 26
Apply Your Knowledtge 1-1 ....................N.............. 31
TOPIC 1C .......s........................... ................ 31
O
SQL Server Architecture..................................... 31
D
Task 1C-1 Understanding the SQL Server Architecture ............. 34
n
Designing a Database Application ............................ 34
Task 1C-2 Exploring the Application Architecture ................ 36
TOPIC 1DI
Identifying SQL Server Management Tasks .............. 36
Task 1D-1 Determining SQL Server Management Tasks ............. 37
An Overview of SQL Server Security ........................... 37
Task 1D-2 Configuring Your Server’s Authentication Mode .......... 39
Creating Login Accounts.................................... 40
Contents v
CONTENTS
Task 1D-3 Creating a SQL Login Account ...................... 40
Apply Your Knowledge 1-2 .................................. 41
Configuring Database Users and Permissions.................... 42
Task 1D-4 Working With Server and Database Roles....n........... 44
Lesson 1 Review .......................................... 46
L 2: E T -SQL o
ESSON XPLORING RANSACT
TOPIC 2A i
Working with Transact-SQL ........................... 50
Task 2A-1 Using SQL Query Analyzer t........................ 51
Osql .................................................... 53
i
Task 2A-2 Using Osql ................................... 54
d
TOPIC 2B
Transact-SQL Statements ............................ 55
Data Definition Language Statements ......................... 56
Task 2B-1 Creating a SimEple Table .......................... 57
Data Manipulation Language Statements ...................... 59
Task 2B-2 Inserting Data ................................ 60
Using SELECT ..... ....................................... 60
Task 2B-3 Selecting Data....................E............. 61
r
Using a WHERE Clause With the SELECT Statement ............... 62
T
Functions....o............................................ 63
A
Apply Your Knowledge 2-1 .................................. 66
C
Using UPDATE ............................................ 68
Using DEtLETE ......................I...................... 68
L
Apply Your Knowledge 2-2 .................................. 68
c
Data Control Language Statements...P......................... 69
Task 2B-4 Assigning PermissionsUto the Public Database Role ....... 71
uUsing DENY .............................................. 72
D
Using REVOKE ............................................ 72

Apply Your Knowledge 2-3 .................................. 73
T
rWorking with Books Online ................................. 73
O
Apply Your Knowledge 2-4 .................................. 74
t N
TOPIC 2C
Programming in Transact-SQL ......................... 75
s
Task 2C-1 UsOing Variables ................................ 77
Executing SQL Statements .................................. 77
D
n Apply Your Knowledge 2-5 .................................. 80
Control-of-Flow Statements ................................. 81
Apply Your Knowledge 2-6 .................................. 83
I Creating SQL Scripts ....................................... 84
Task 2C-2 Saving a Script File ............................. 84
Apply Your Knowledge 2-7 .................................. 85
Working with XML ......................................... 86
Task 2C-3 Choosing an XML Format.......................... 89
Lesson 2 Review .......................................... 90
vi
L 3: D I D CONTENTS
ESSON ESIGNING AND MPLEMENTING ATABASES
TOPIC 3A
Identifying Database Design Issues ................... 92
n
Task 3A-1 Designing Databases ............................ 93
Estimating the Space Requirements for Databases ............... 93
Task 3A-2 Identifying the Space Requirements for Databases ........ 96
o
Optimizing Databases...................................... 96
Task 3A-3 Optimizing Database Performance.................... 99
TOPIC 3B i
Creating Databases ................................. 99
Creating a Database Using Transact-SQL .......................t100
Apply Your Knowledge 3-1 ..................................102
i
Creating Multiple Data Files and Filegroups.....................103
d
Apply Your Knowledge 3-2 ..................................105
Configuring Database Options................................106
Task 3B-1 Setting Database Options .........................108
E
Displaying Information About Databases and Transaction Logs .....109
Task 3B-2 Using Stored Procedures to View Database Information .....109
TOPIC 3C
Managing Databases ................. ...............111
Apply Your Knowledge 3-3 .................r.................112 E
Monitoring the Size of a Transaction Log ......................113 T
Apply Your Knowledge 3-4 ..............o....................114
A
Shrinking Databases or Files ................................114
C
Task 3C-1 ConfiguringSQLServertoAutomaticallyShrinkaDatabaseFile
.................t.........................11I5
L
Deleting a Database .......................................116
c
P
Task 3C-2 Deleting a Database.............................116
Apply Your Knowledge 3-5 ...............................U...117
Lesson 3 Review ..........u................................118
D

L 4: C M T T
ESSON REATING AND ANAGING ABLES
r
O
TOPIC 4A t N
Design and Create Tables.............................120
Task 4A-1 sNormalizing Table Designs......... ................122
O
Defining Columns for a Table ................................123
Apply Your Knowledge 4-1 ...............D...................130
n
Implementing User-defined Data Types ........................131
Apply Your Knowledge 4-2 ..................................132
Creating a Table ..........................................133
I
Apply Your Knowledge 4-3 ..................................135
TOPIC 4B
Maintaining Tables ..................................137
Apply Your Knowledge 4-4 ..................................138
Dropping a Table .........................................139
Apply Your Knowledge 4-5 ..................................139
Contents vii
CONTENTS
Scripting Tables ..........................................139
Task 4B-1 Using SQL Server Enterprise Manager to Generate a Script ...140
Lesson 4 Review ..........................................142
n
L 5: D I D I
ESSON ESIGNING AND MPLEMENTING ATA NTEGRITY
o
TOPIC 5A
Understanding Data Integrity ........................144
Apply Your Knowledge 5-1 ..................................145
i
TOPIC 5B
Implementing Constraints ...........................147
t
Task 5B-1 Adding a Primary Key Constraint ....................150
Apply Your Knowledge 5-2 ........i..........................151
Defining Foreign Key Constraints .d............................152
Task 5B-2 Adding a Foreign Key Constraint to the Movie Table .......154
Apply Your Knowledge 5-3 ..................................155
Default Constraints ......E.................................156
Task 5B-3 Adding a Default Constraint to the Movie Table ..........157
Apply Your Knowledge 5-4 ..................................157
Check Constraints .. .......................................158
Task 5B-4 Adding a Check Constraint to the Movie TaEble ...........159
r
Check Your Skills 5-1.......................................160
T
Managing Conostraints ......................................160
A
Task 5B-5 Disabling Constraint Checking ......................161
C
Using Data Transformation Services ...........................163
Apply Yotur Knowledge 5-5 ............I......................164
L
Lesson 5 Review ..........................................165
c
P
L 6: I I U
ESSON MPLEMENTING NDEXES
u
D
TOPIC 6A
Designing Indexing T.................................168
r
Task 6A-1 UnderstandOing Index Architecture ...................172
Guidelines for Defining Indexes ..............................172
t N
Apply Your Knowledge 6-1 ..................................174
s TOPIC 6B
ImplementOing Indexes ..............................175
Task 6B-1 Creating a Clustered Index on the Movie Table ..........178
D
n Creating Composite Indexes .................................179
Apply Your Knowledge 6-2 ..................................179
Creating a Unique Index ...................................180
I Apply Your Knowledge 6-3 ..................................181
Using the Sysindexes Table..................................181
Task 6B-2 Viewing the Information in Sysindexes ................182
TOPIC 6C
Maintaining Indexes ................................183
Task 6C-1 Rebuilding an Index ............................186
viii
CONTENTS
Using DROP_EXISTING to Change an Index .....................188
Task 6C-2 UsingDROP_EXISTINGtoRe-createanIndexBasedonaPri-
mary Key ....................................189
Using SQL Profiler ........................................190
n
Task 6C-3 Generating a Workload File in SQL Profiler..............191
Using the Index Tuning Wizard ..............................192
Apply Your Knowledge 6-4 ..................................193 o
Managing Index Statistics ..................................194
Task 6C-4 Observing Index Statistics ........................196
Lesson 6 Review ..........................................198i
t
L 7: J T
ESSON OINING ABLES
i
d
TOPIC 7A
Querying Multiple Tables ............................200
Apply Your Knowledge 7-1 ..................................202
Defining an Outer Join ...........................E.........203
Apply Your Knowledge 7-2 ..................................204
Designing a Cross Join .....................................205
Task 7A-1 Working with Cross Joins .......... ...............205
E
TOPIC 7B r
Implementing Advanced Table Joins ...................206
T
Task 7B-1 Joining Multiple Tables ..........................206
o
A
Apply Your Knowledge 7-3 ..................................207
Implementing Self Joins ...................................208C
Task 7B-2 Working with Self Joins .t.........................20I8
L
Combining the Results of Multiple SELECT Statements ............208
c
Task 7B-3 Combining SELECT Statements ....................P.209
Creating a Table Based on a Results Set ....................U...210
Task 7B-4 Creating a NewuTable Based on a SELECT INTO Statement ...211
D
Apply Your Knowledge 7-4 ..................................212
Lesson 7 Review .................................. ........213
T
r
O
L 8: D A Q
ESSON ESIGNING DVANCED UERIES
t N
s
TOPIC 8A O
Designing Subqueries ...............................216
Task 8A-1 Designing Single Value SubqueriesD...................217
Using a Sunbquery to Return a List of Values ....................218
Apply Your Knowledge 8-1 ..................................219
Designing Correlated Subqueries .............................220
I
Apply Your Knowledge 8-2 ..................................221
TOPIC 8B
Changing Data Through Queries .......................222
Task 8B-1 Inserting Data Based on a Query ...................222
Deleting Rows Based on a Query .............................223
Task 8B-2 Deleting Rows Based on a Query ....................224
Contents ix