Beginning SQL Server Modeling (eBook)

Bart Weller is a software developer and writer. He lives in the Central Rockies of Colorado with his wife, two country cats, six misanthropic llamas, and dog Duffey (named in honor of the late John Duffey, former lead tenor with the Seldom Scene bluegrass band). He loves all kinds of music except for Heavy Metal and is a volunteer DJ at the local community radio station. His hobbies are clearing deadfall and fixing things. Among the people he admires are Albert Einstein, Alan Kay, Abraham Lincoln, George Carlin, Richard Feynman, and J.J. Cale.

Title Page 1
Copyright Page 2
Contents at a Glance 4
Table of Contents 5
About the Author 10
About the Technical Reviewer 11
Acknowledgments 12
Introduction 13
CHAPTER 1 Installing and Setting Up SQL Server Modeling 15
Software Pre-Requisites 15
Hardware and Operating System Requirements 16
Configuring SQL Server 16
Downloading and Installing 18
Checking the Installation 21
If Something Goes Wrong… 22
The Repair Option 23
The Uninstall Option 23
Summary 26
CHAPTER 2 Introduction to Intellipad 27
Getting Started with Intellipad 27
The File Menu 28
The Edit Menu 30
The Find Commands 31
The Replace Command 32
The Go to Line Command 33
Behaviors 33
Disable External Changes 34
The View Menu 34
Full Screen 35
Zoom 37
Split Windows 37
The Mini-Buffer 38
The Help Menu 39
Commands List 39
Intellipad Primer 40
View Title Banner Functions 40
View Modes 41
CHAPTER 3 Domain-Specific Languages 101: Lola’s Lunch Counter 47
Some Caveats 47
A Simple Exercise: The Sandwich Language 48
Where You Want to End Up 48
Getting Started with the Intellipad DSL Grammar Mode Interface 50
Broadening the Choices 53
Interleaving (Ignoring) Whitespace 54
Defining Tokens 55
Enabling Multiple DSL Statements 56
Tightening Up the Syntax 59
Moving Toward Structured Data 59
Testing the Syntax 62
Making the Syntax More Flexible 66
The SandwichLanguage DSL MGraph 67
Extending SandwichOrders to More Than One Main Ingredient 68
Deployment 70
Thinking Ahead 92
Concluding Thoughts 93
CHAPTER 4 Introduction to Quadrant 94
My Car: Creating a Simple Model in Quadrant 94
Building the Car Model in Quadrant 95
Deploying the Model to SQL Server 98
Viewing the Model and Adding Data in the Explorer 100
Customizing Column Views in Quadrant 106
Viewing and Editing the Model in SQL Server 108
Managing Changes to the Data in Quadrant 110
Managing Conflicts in Quadrant 112
Using the Quadrant Explorer Query Bar 115
More on Customizing the View 118
Summary 127
CHAPTER 5 M – The Modeling Language 128
Why M? 128
Getting Started with M 128
Modules 129
The Four Basic Constructs of M 130
Types 130
Intrinsic and Derived Types 131
M’s Built-Ins: The Intrinsic Types 133
The Collection Type 135
Multiplicity Constraints 136
Collection Operators 136
The Entity Type 138
Entity Value Initializers 139
Member Names 139
Entity Values 139
Entity Value Operators 140
Modules Revisited: Import and Export Directives 140
Extents 142
Generating T-SQL Code for the Car Model 144
Computed Values 146
Overloading 148
Languages 149
Summary 149
CHAPTER 6 SQL Server Modeling Services – The Folder Pattern 150
The Modeling Services Folder Pattern 151
Example: A Quality Control System for CarModel 152
CHAPTER 7 SQL Server Modeling Services – Security 175
Using Security to Limit Data Visibility 176
Setting Up – Installing the PatternApplication Sample 176
Building on the CarModel 177
Building the MfgComponentModel Project in Visual Studio 180
Refining the Model to Include Security 182
HasFolderAndAutoId 187
Adding the PatternApplication Module 188
Building the Project 193
Deploying to the Database 194
Creating the QC Folders 199
Building the Sample Data 202
Setting Up the QC Manager Test Users 207
Configuring Test-User Permissions in SQL Server Management Studio 210
Testing 213
Summary 217
APPENDIX A Intellipad Primer 218
Intellipad Basics 218
Buffers 218
Views 219
Modes 219
The Mini-Buffer 219
Working with M in Intellipad 220
SQL Preview 220
Basic DSL Authoring Configuration 220
Customizing Intellipad 220
Changing the Menus 221
Changing the Colors 221
Adding New Modes 221
Customizing Commands 222
Intellipad Components 222
Compiled Components 222
Declarative Components 223
Script Components 223
List of Available Modes 223
Command-Line Options 224
APPENDIX B Intellipad Mini-Buffer Commands 226
APPENDIX C Intellipad Commands and Gestures 227
APPENDIX D The Quadrant Menu Tree 235
APPENDIX E Generated T-SQL for the Car Model Example 238
Index 244