Pro SQL Server 2012 Relational Database Design and Implementation

Book description

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

    1. Title Page
    2. Dedication
    3. Contents at a Glance
    4. Contents
    5. Foreword
    6. About the Author
    7. About the Technical Reviewer
    8. Acknowledgments
    9. Introduction
    10. CHAPTER 1: The Fundamentals
      1. Taking a Brief Jaunt Through History
      2. Recognizing Relational Data Structures
      3. Understanding Relationships
      4. Understanding Dependencies
      5. Relational Programming
      6. Outlining the Database-Specific Project Phases
      7. Summary
    11. CHAPTER 2: Introduction to Requirements
      1. Documenting Requirements
      2. Gathering Requirements
      3. Interviewing Clients
      4. Asking the Right Questions
      5. Working with Existing Systems and Prototypes
      6. Utilizing Other Types of Documentation
      7. Following Best Practices
      8. Summary
    12. CHAPTER 3: The Language of Data Modeling
      1. Introducing Data Modeling
      2. Entities
      3. Attributes
      4. Relationships
      5. Descriptive Information
      6. Alternative Modeling Methodologies
      7. Best Practices
      8. Summary
    13. CHAPTER 4: Initial Data Model Production
      1. Example Scenario
      2. Identifying Entities
      3. Relationships between Entities
      4. Identifying Attributes and Domains
      5. Identifying Business Rules
      6. Identifying Fundamental Processes
      7. The Intermediate Version of the Logical Model
      8. Best Practices
      9. Summary
    14. CHAPTER 5: Normalization
      1. The Process of Normalization
      2. Table and Column Shape
      3. Relationships Between Columns
      4. Surrogate Keys Effect on Dependency
      5. Tables with Multiple Meanings
      6. Denormalization
      7. Best Practices
      8. Summary
      9. The Story of the Book So Far
    15. CHAPTER 6: Physical Model Implementation Case Study
      1. Choosing Names
      2. Choosing Key Implementation
      3. Determining Domain Implementation
      4. Setting Up Schemas
      5. Adding Implementation Columns
      6. Using DDL to Create the Database
      7. Unit Testing Your Structures
      8. Best Practices
      9. Summary
    16. CHAPTER 7: Data Protection with Check Constraints and Triggers
      1. Check Constraints
      2. DML Triggers
      3. Dealing with Triggers and Constraints Errors
      4. Best Practices
      5. Summary
    17. CHAPTER 8: Patterns and Anti-Patterns
      1. Desirable Patterns
      2. Images, Documents, and Other Files, Oh My
      3. Anti-Patterns
      4. Summary
    18. CHAPTER 9: Database Security and Security Patterns
      1. Database Access Prerequisites
      2. Database Securables
      3. Controlling Access to Data via T-SQL-Coded Objects
      4. Crossing Database Lines
      5. Obfuscating Data
      6. Monitoring and Auditing
      7. Best Practices
      8. Summary
    19. CHAPTER 10: Table Structures and Indexing
      1. Physical Database Structure
      2. Indexes Overview
      3. Basic Index Structure
      4. Index Types
      5. Basics of Index Creation
      6. Basic Index Usage Patterns
      7. Index Usage Scenarios
      8. Index Dynamic Management View Queries
      9. Best Practices
      10. Summary
    20. CHAPTER 11: Coding for Concurrency
      1. What Is Concurrency?
      2. OS and Hardware Concerns
      3. Transactions
      4. Isolating Sessions
      5. Coding for Integrity and Concurrency
      6. Best Practices
      7. Summary
    21. CHAPTER 12: Reusable Standard Database Components
      1. Numbers Table
      2. Calendar Table
      3. Utility Objects
      4. Logging Objects
      5. Other Possibilities...
      6. Summary
    22. CHAPTER 13: Considering Data Access Strategies
      1. Ad Hoc SQL
      2. Stored Procedures
      3. T-SQL and the CLR
      4. Best Practices
      5. Summary
    23. CHAPTER 14: Reporting Design
      1. Reporting Styles
      2. Requirements-Gathering Process
      3. Dimensional Modeling for Analytical Reporting
      4. Analytical Querying
      5. Summary Modeling for Aggregation Reporting
      6. Aggregation Querying
      7. Summary
    24. Appendix A
    25. Appendix B
    26. Index

    Product information

    • Title: Pro SQL Server 2012 Relational Database Design and Implementation
    • Author(s): Louis Davidson, Jessica M. Moss
    • Release date: July 2012
    • Publisher(s): Apress
    • ISBN: 9781430236955