Chapter 2. Database Design for SQL Server 2000


O BJECTIVES

The software to be used is not necessarily relevant to the database design, but because the software, SQL Server 2000 Enterprise Edition, is predetermined in this case, the features and limitations of the product as they relate to the database structure have been brought into this chapter. A complete breakdown of all SQL Server data types is covered here as a lead-in to Chapter 3, which covers SQL Server specifics in much more detail.

Define entities. Considerations include entity composition.

  • Specify entity, attributes, and relationships in a logical model.

  • This objective deals with creating and identifying entities and attributes in a data model. The coverage of this objective teaches you how an entity behaves during the three prominent phases of database normalization.

Define entities. Considering normalization and denormalization.

  • Specify degree of normalization.

  • For this objective, you will have to fully normalize a database structure and then decide whether denormalization of a database is appropriate in a given situation.

Design entity keys. Considerations include FOREIGN KEY constraints, PRIMARY KEY constraints, and UNIQUE constraints.

  • Specify attributes that uniquely identify records.

  • Specify attributes that reference other entities.

  • Here you will learn how to undergo the process of creating FOREIGN KEY and PRIMARY KEY constraints and deciding on ways to uniquely identify records. You have to decide on which attribute or attributes will be required to uniquely identify records. This objective requires you to know how to create relationships by choosing FOREIGN KEY and PRIMARY KEY candidates.

Design attribute domain integrity. Considerations include CHECK constraints, data types, and nullability.

  • Specify scale and precision of allowable values for each attribute.

  • Allow or prohibit NULL for each attribute.

  • Specify allowable values for each attribute.

  • Domain integrity involves restricting the data entered to a domain or range of values. Using a variety of different data types, permitting different formats and allowing different ranges of values can define these domains. For this objective, you need to decide on whether NULL s are allowed and the values that are allowed on attributes.

Handle client/server configurations of 50 to 5,000 or more users.

  • One, two, and n tier operations.

  • Internet applications. Web configurations that use Internet Information Server (IIS) or COM+ .

  • Heterogeneous databases using other non- SQL Server data sources.

  • Very large database support.

  • Many types of environments exist. In some environments a database server is used on its own. In others the database server operates as only a small portion of the system with assistance from other machines and applications. The separations of this processing creates a tier effect. This chapter explores the roles of the software at various tiers and observes how the database servers function in each model.

O UTLINE

A Case Scenario Approach

Case Scenario: Lloyd's Hospital

Entity Relationship Modeling

Entities

Defining Attributes

Designing Entity Keys

Primary Keys

Foreign Keys

Understanding Entity Relationships

Data Modeling Overview

Understanding Database Normalization

First Normal Form

Second Normal Form

Third Normal Form

When to Denormalize

Maintaining Data Integrity

Entity Integrity

Domain Integrity

Referential Integrity

Using Appropriate Data Types

Data Types in a Logical Model

The Client/Server Model

One- and Two-Tier Systems

Three- or More Tier Systems

Internet Applications

Very Large Database Applications

Third-Party Database Interactions

Apply Your Knowledge

Exercises

Review Questions

Exam Questions

Answers to Review Questions

Answers To Exam Questions

S TUDY S TRATEGIES

  • Database design is a large topic and can span many texts . I advise you to look at different articles and books with the many resources listed in the "Suggested Readings" section. Doing so is not required, but it ensures you a more thorough understanding of database design.

  • Database design can sometimes get nerve -racking and so requires a lot of patience. Go through each step of the process in a slow and methodical manner. Don't be too rushed to put things into SQL Server until the design is ready.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net