Atnaujinkite slapukų nuostatas

Pro SQL Server 2012 Relational Database Design and Implementation [Minkštas viršelis]

5.00/5 (5 ratings by Goodreads)
,
  • Formatas: Paperback / softback, 784 pages, aukštis x plotis x storis: 254x178x39 mm, weight: 1352 g, 1, black & white illustrations
  • Išleidimo metai: 24-Jul-2012
  • Leidėjas: APress
  • ISBN-10: 1430236957
  • ISBN-13: 9781430236955
Kitos knygos pagal šią temą:
Pro SQL Server 2012 Relational Database Design and ImplementationDidesnis paveikslėlis
  • Formatas: Paperback / softback, 784 pages, aukštis x plotis x storis: 254x178x39 mm, weight: 1352 g, 1, black & white illustrations
  • Išleidimo metai: 24-Jul-2012
  • Leidėjas: APress
  • ISBN-10: 1430236957
  • ISBN-13: 9781430236955
Kitos knygos pagal šią temą:
Pastovi nuoroda: https://www.kriso.lt/db/9781430236955.html
Raktažodžiai:
Learn effective and scalable database design techniques in a SQL Server environment. Pro SQL Server 2012 Relational Database Design and Implementation covers everything from design logic that business users will understand, all the way to the physical implementation of design in a SQL Server database. Grounded in best practices and a solid understanding of the underlying theory, Louis Davidson shows how to "get it right" in SQL Server database design and lay a solid groundwork for the future use of valuable business data. * Gives a solid foundation in best practices and relational theory * Covers the latest implementation features in SQL Server * Takes you from conceptual design to an effective, physical implementation What you'll learn * How to develop conceptual models of client data using interviews and client documentation * How to recognize and apply common database design patterns * How to normalize data models to enhance scalability and the long term use of valuable data * How to translate conceptual models into high--performing SQL Server databases * How to secure and protect data integrity as part of meeting regulatory requirements * How to create effective indexing to speed query performance Who this book is for Pro SQL Server 2012 Relational Database Design and Implementation is designed for programmers of all types who want to use SQL Server 2012 to store data. Chapters on fundamental concepts, the language of database modeling, SQL implementation, and of course, the normalization process, lay a solid groundwork for readers who are just entering the field of database design. More advanced chapters serve the seasoned veteran by tackling the very latest in physical implementation features that SQL Server has to offer. Table of Contents * The Fundamentals * Introduction to Requirements * The Language of Data Modeling * Initial Data Model Production * Normalization * Physical Model Implementation Case Study * Data Protection with Check Constraints and Triggers * Patterns and Anti-Patterns * Table structures and Indexing * Coding for Concurrency * Reusable Standard Database Components * Standardized Data Models * Considering Data Access Strategies * Reporting Design * Appendix A * Appendix B
Foreword xix
About the Author xxii
About the Technical Reviewer xxiii
Acknowledgments xxv
Introduction xxvii
Chapter 1 The Fundamentals
1(36)
Taking a Brief Jaunt Through History
2(7)
Introducing Codd's Rules for an RDBMS
3(5)
Nodding at SQL Standards
8(1)
Recognizing Relational Data Structures
9(15)
Introducing Databases and Schemas
10(1)
Understanding Tables, Rows, and Columns
10(5)
Working with Missing Values (NULLs)
15(1)
Defining Domains
16(1)
Storing Metadata
17(1)
Assigning Uniqueness Constraints (Keys)
18(6)
Understanding Relationships
24(6)
Working with Binary Relationships
26(3)
Working with Nonbinary Relationships
29(1)
Understanding Dependencies
30(1)
Working with Functional Dependencies
30(1)
Working with Determinants
31(1)
Relational Programming
31(1)
Outlining the Database-Specific Project Phases
32(3)
Conceptual Phase
33(1)
Logical Phase
34(1)
Physical
35(1)
Storage Phase
35(1)
Summary
35(2)
Chapter 2 Introduction to Requirements
37(16)
Documenting Requirements
39(2)
Gathering Requirements
41(1)
Interviewing Clients
42(1)
Asking the Right Questions
43(5)
What Data Is Needed?
43(1)
How Will the Data Be Used?
44(1)
What Rules Govern the Use of the Data?
44(1)
What Data Is Reported On?
45(1)
Where Is the Data Now?
46(1)
Will the Data Need to Be Integrated with Other Systems?
47(1)
How Much Is This Data Worth?
47(1)
Who Will Use the Data?
47(1)
Working with Existing Systems and Prototypes
48(1)
Utilizing Other Types of Documentation
48(2)
Early Project Documentation
49(1)
Contracts or Client Work Orders
49(1)
Level of Service Agreement
49(1)
Audit Plans
50(1)
Following Best Practices
50(1)
Summary
50(3)
Chapter 3 The Language of Data Modeling
53(38)
Introducing Data Modeling
54(1)
Entities
55(3)
Attributes
58(9)
Primary Keys
59(2)
Alternate Keys
61(1)
Foreign Keys
62(1)
Domains
63(2)
Naming
65(2)
Relationships
67(14)
Identifying Relationships
68(1)
Nonidentifying Relationships
69(2)
Role Names
71(1)
Relationship Cardinality
72(7)
Verb Phrases (Relationship Names)
79(2)
Descriptive Information
81(1)
Alternative Modeling Methodologies
82(6)
Information Engineering
83(2)
Chen ERD
85(1)
Visio
86(1)
Management Studio Database Diagrams
87(1)
Best Practices
88(1)
Summary
89(2)
Chapter 4 Initial Data Model Production
91(38)
Example Scenario
92(1)
Identifying Entities
93(9)
People
94(1)
Places
95(1)
Objects
95(1)
Ideas
96(1)
Documents
97(1)
Groups
98(1)
Other Entities
99(1)
Entity Recap
100(2)
Relationships between Entities
102(7)
One-to-N Relationships
102(4)
Many-to-Many Relationships
106(1)
Listing Relationships
107(2)
Identifying Attributes and Domains
109(11)
Identifiers
111(2)
Descriptive Information
113(1)
Locators
113(2)
Values
115(1)
Relationship Attributes
116(1)
A List of Entities, Attributes, and Domains
117(3)
Identifying Business Rules
120(2)
Identifying Fundamental Processes
122(2)
The Intermediate Version of the Logical Model
124(2)
Identifying Obvious Additional Data Needs
124(1)
Review with the Client
125(1)
Repeat Until the Customer Agrees with Your Model
126(1)
Best Practices
126(1)
Summary
127(2)
Chapter 5 Normalization
129(40)
The Process of Normalization
130(1)
Table and Column Shape
131(13)
All Columns Must Be Atomic
131(8)
All Rows Must Contain the Same Number of Values
139(2)
All Rows Must Be Different
141(2)
Clues That an Existing Design Is Not in First Normal Form
143(1)
Relationships Between Columns
144(5)
BCNF Defined
144(2)
Partial Key Dependency
146(1)
Entire Key Dependency
147(2)
Surrogate Keys Effect on Dependency
149(7)
Dependency Between Rows
151(1)
Clues That Your Database Is Not in BCNF
152(3)
Positional Meaning
155(1)
Tables with Multiple Meanings
156(6)
Fourth Normal Form: Independent Multivalued Dependencies
157(2)
Fifth Normal Form
159(3)
Denormalization
162(3)
Best Practices
165(1)
Summary
165(2)
The Story of the Book So Far
167(2)
Chapter 6 Physical Model Implementation Case Study
169(76)
Choosing Names
172(5)
Table Naming
173(2)
Naming Columns
175(1)
Model Name Adjustments
176(1)
Choosing Key Implementation
177(7)
Primary Key
177(5)
Alternate Keys
182(2)
Determining Domain Implementation
184(16)
Implement as a Column or Table?
186(2)
Choosing the Datatype
188(10)
Choosing Nullability
198(1)
Choosing a Collation
199(1)
Setting Up Schemas
200(1)
Adding Implementation Columns
201(1)
Using DDL to Create the Database
202(35)
Creating the Basic Table Structures
204(10)
Adding Uniqueness Constraints
214(4)
Building Default Constraints
218(1)
Adding Relationships (Foreign Keys)
219(5)
Adding Basic Check Constraints
224(2)
Triggers to Maintain Automatic Values
226(4)
Documenting Your Database
230(3)
Viewing the Basic Metadata
233(4)
Unit Testing Your Structures
237(4)
Best Practices
241(1)
Summary
242(3)
Chapter 7 Data Protection with Check Constraints and Triggers
245(56)
Check Constraints
247(13)
CHECK Constraints Based on Simple Expressions
252(2)
CHECK Constraints Using Functions
254(4)
Enhancing Errors Caused by Constraints
258(2)
DML Triggers
260(32)
AFTER Triggers
261(18)
Relationships That Span Databases and Servers
279(4)
INSTEAD OF Triggers
283(9)
Dealing with Triggers and Constraints Errors
292(5)
Best Practices
297(1)
Summary
298(3)
Chapter 8 Patterns and Anti-Patterns
301(70)
Desirable Patterns
302(30)
Uniqueness
302(17)
Data-Driven Design
319(1)
Hierarchies
320(12)
Images, Documents, and Other Files, Oh My
332(27)
Generalization
340(5)
Storing User-Specified Data
345(14)
Anti-Patterns
359(9)
Undecipherable Data
360(1)
One-Size-Fits-All Key Domain
361(3)
Generic Key References
364(3)
Overusing Unstructured Data
367(1)
Summary
368(3)
Chapter 9 Database Security and Security Patterns
371(74)
Database Access Prerequisites
372(14)
Guidelines for Server Security
373(1)
Principals and Securables
374(1)
Connecting to the Server
375(1)
Using Login and User
376(3)
Using the Contained Database Model
379(4)
Impersonation
383(3)
Database Securables
386(16)
Grantable Permissions
387(1)
Controlling Access to Objects
388(4)
Roles
392(8)
Schemas
400(2)
Controlling Access to Data via T-SQL-Coded Objects
402(15)
Stored Procedures and Scalar Functions
402(2)
Impersonation within Objects
404(6)
Views and Table-Valued Functions
410(7)
Crossing Database Lines
417(10)
Using Cross-Database Chaining
418(5)
Using Impersonation to Cross Database Lines
423(1)
Using a Certificate-Based Trust
424(2)
Different Server (Distributed Queries)
426(1)
Obfuscating Data
427(2)
Monitoring and Auditing
429(13)
Server and Database Audit
430(4)
Watching Table History Using DML Triggers
434(4)
DDL Triggers
438(3)
Logging with Profiler
441(1)
Best Practices
442(1)
Summary
443(2)
Chapter 10 Table Structures and Indexing
445(60)
Physical Database Structure
447(12)
Files and Filegroups
447(3)
Extents and Pages
450(3)
Data on Pages
453(3)
Partitioning
456(3)
Indexes Overview
459(1)
Basic Index Structure
460(2)
Index Types
462(2)
Clustered Indexes
462(1)
Nonclustered Indexes
463
Nonclustered Indexes on Clustered Tables
461(1)
Nonclustered Indexes on a Heap
462(2)
Basics of Index Creation
464(2)
Basic Index Usage Patterns
466(18)
Using Clustered Indexes
467(4)
Using Nonclustered Indexes
471(13)
Using Unique Indexes
484(1)
Advanced Index Usage Scenarios
484(13)
Indexing Foreign Keys
489(4)
Indexing Views
493(4)
Index Dynamic Management View Queries
497(4)
Missing Indexes
497(3)
Index Utilization Statistics
500(1)
Fragmentation
501(1)
Best Practices
501(2)
Summary
503(2)
Chapter 11 Coding for Concurrency
505(58)
What Is Concurrency?
506(2)
OS and Hardware Concerns
508(1)
Transactions
509(18)
Transaction Syntax
510(8)
Compiled SQL Server Code
518(9)
Isolating Sessions
527(19)
Locks
528(5)
Isolation Levels
533(13)
Coding for Integrity and Concurrency
546(14)
Pessimistic Locking
546(3)
Implementing a Single-Threaded Code Block
549(3)
Optimistic Locking
552(1)
Row-Based Locking
553(5)
Logical Unit of Work
558(2)
Best Practices
560(1)
Summary
561(2)
Chapter 12 Reusable Standard Database Components
563(32)
Numbers Table
564(12)
Determining the Contents of a String
568(2)
Finding Gaps in a Sequence of Numbers
570(1)
Separating Comma Delimited Items
571(2)
Stupid Mathematic Tricks
573(3)
Calendar Table
576(9)
Utility Objects
585(5)
Monitoring Objects
586(2)
Extended DDL Utilities
588(2)
Logging Objects
590(2)
Other Possibilities
592(1)
Summary
593(2)
Chapter 13 Considering Data Access Strategies
595(44)
Ad Hoc SQL
597(16)
Advantages
597(10)
Pitfalls
607(6)
Stored Procedures
613(16)
Encapsulation
614(2)
Dynamic Procedures
616(3)
Security
619(2)
Performance
621(2)
Pitfalls
623(4)
All Things Considered... What Do I Choose?
627(2)
T-SQL and the CLR
629(8)
Guidelines for Choosing T-SQL
633(1)
Guidelines for Choosing a CLR Object
634(1)
CLR Object Types
634(3)
Best Practices
637(1)
Summary
638(1)
Chapter 14 Reporting Design
639(32)
Reporting Styles
639(2)
Analytical Reporting
640(1)
Aggregation Reporting
641(1)
Requirements-Gathering Process
641(1)
Dimensional Modeling for Analytical Reporting
642(19)
Dimensions
644(11)
Facts
655(6)
Analytical Querying
661(3)
Queries
661(2)
Indexing
663(1)
Summary Modeling for Aggregation Reporting
664(3)
Initial Summary Table
665(2)
Additional Summary Tables
667(1)
Aggregation Querying
667(3)
Queries
668(1)
Indexing
669(1)
Summary
670(1)
Appendix A 671(36)
Appendix B 707(28)
Index 735