Sql Server 2000 - Introduction

Level 1 Introduction to Microsoft SQL Server 2000

Table of Contents

Introduction to Microsoft SQL Server 2000 Table of Contents INTRODUCTION .......................................................................................INTRO-1 Courseware Conventions ..............................................................INTRO-2 The Practice Disks.........................................................................INTRO-3 Chapter Files......................................................................INTRO-3 Attaching the SQL Server Shark Database .......................INTRO-3 About the Authors ..........................................................................INTRO-6 SQL SERVER TOUR .........................................................................................1-1 SQL Server Editions ...............................................................................1-2 Features Supported for Each Edition..........................................1-3 Graphical Tools.......................................................................................1-6 Analysis Services........................................................................1-6 English Query .............................................................................1-6 Books Online...............................................................................1-6 Client Network Utility...................................................................1-7 Configure SQL XML Support in IIS .............................................1-8 Enterprise Manager ....................................................................1-8 Import and Export Data...............................................................1-9 Profiler.......................................................................................1-10 Query Analyzer .........................................................................1-10 Server Network Utility ...............................................................1-13 Service Manager.......................................................................1-14 Exploring the Enterprise Manager ........................................................1-15 The Wizards..............................................................................1-17 Databases.................................................................................1-18 Data Transformation Services (DTS)........................................1-21 Management .............................................................................1-22 Replication ................................................................................1-27 Security .....................................................................................1-27 Support Services.......................................................................1-29 Meta Data Services...................................................................1-30 Working with SQL Server Books Online ...............................................1-31 The Active Subset.....................................................................1-32 The Index Tab...........................................................................1-32 The Search Tab ........................................................................1-33 The Favorites Tab.....................................................................1-34 Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

TOC-1

Table of Contents LAB 1: SQL SERVER TOUR...........................................................................1-39 Lab 1 Overview.....................................................................................1-40 Investigate Server Properties and Run a Wizard..................................1-41 Use the Query Analyzer........................................................................1-47 INSTALLING SQL SERVER...............................................................................2-1 Multiple Instances ...................................................................................2-2 Multiple Instance Scenarios ........................................................2-2 Using Named Instances..............................................................2-3 Installing an Instance of SQL Server ......................................................2-5 Before Installing SQL Server ......................................................2-5 Setting up the System Accounts .................................................2-9 Installing SQL Server 2000 .......................................................2-11 Configuring the Server..........................................................................2-26 General Properties....................................................................2-28 Memory .....................................................................................2-28 Processor..................................................................................2-30 Security .....................................................................................2-31 Connections ..............................................................................2-32 Server Settings .........................................................................2-32 Database Settings.....................................................................2-34 Replication ................................................................................2-36 Active Directory.........................................................................2-37 DESIGNING AND CREATING A DATABASE....................................................3-1 Relational Database Design Principles...................................................3-2 The Origins of Relational Design ................................................3-3 Data Normalization .....................................................................3-5 Understanding Relationships ......................................................3-8 Beyond Normalization...............................................................3-12 Implementing the Design ......................................................................3-13 Database Storage .....................................................................3-13 Creating Databases ..................................................................3-15 Creating Tables.........................................................................3-24 Creating Constraints .................................................................3-31 Triggers.....................................................................................3-39 Creating Indexes.......................................................................3-40 Database Diagrams ..................................................................3-43 LAB 3: DESIGNING AND CREATING A DATABASE.....................................3-51 Lab 3 Overview.....................................................................................3-52 TOC-2

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Table of Contents Create a SQL Server Database............................................................3-53 Create SQL Server Tables ...................................................................3-55 Create Relationships Using a Database Diagram ................................3-58 BASIC QUERYING.............................................................................................4-1 Introduction to the Query Analyzer .........................................................4-2 Retrieving Data .......................................................................................4-5 Understanding Transact-SQL .....................................................4-5 The Shark Sample Database......................................................4-6 The SELECT Statement .............................................................4-7 The WHERE Clause .................................................................4-11 Sorting Data Using ORDER BY ................................................4-20 The GROUP BY Clause............................................................4-23 Joining Tables.......................................................................................4-30 Principles of Joining ..................................................................4-30 Inner Joins ................................................................................4-34 Outer Joins................................................................................4-38 Self Joins ..................................................................................4-45 LAB 4: BASIC QUERYING..............................................................................4-51 Lab 4 Overview.....................................................................................4-52 Simple Select Query .............................................................................4-53 Aggregate Query ..................................................................................4-56 Joining Tables with an Inner Join .........................................................4-58 Aggregate Query with Multiple Inner Joins ...........................................4-60 Aggregate Query with Inner and Outer Joins .......................................4-62 ACTION QUERIES.............................................................................................5-1 Modifying Data........................................................................................5-2 What Is an Action Query? ...........................................................5-2 Inserting Data .........................................................................................5-3 Inserting a Single Value ..............................................................5-4 Inserting Multiple Values.............................................................5-6 Inserting Multiple Rows...............................................................5-8 Creating a New Table Using SELECT INTO ..............................5-9 Temporary Tables.......................................................................5-9 Using BULK INSERT ................................................................5-12 Updating Data.......................................................................................5-14 Updating a Single Row .............................................................5-14 Updating Multiple Rows ............................................................5-15 Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

TOC-3

Table of Contents Updating From Another Table...................................................5-15 Deleting Data ........................................................................................5-16 Deleting a Single Row...............................................................5-16 Deleting Multiple Rows .............................................................5-16 LAB 5: ACTION QUERIES ..............................................................................5-21 Lab 5 Overview.....................................................................................5-22 Adding a Product ..................................................................................5-23 Editing a Product ..................................................................................5-26 Deleting a Product ................................................................................5-29 INDEX........................................................................................................INDEX-1

TOC-4

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Designing and Creating a Database

Designing and Creating a Database Objectives •

Review relational database design principles.

•

Create a SQL Server database based on sound design principles.

•

Build tables using the Enterprise Manager.

•

Learn about SQL Server data types.

•

Create constraints, triggers, and indexes.

•

Create a Database Diagram to enforce referential integrity.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-1

Designing and Creating a Database

Relational Database Design Principles The first and most important step in the development of a database is the process of defining a set of requirements and modeling how those requirements will be met using a relational database. The first part of this chapter is devoted to a quick review of the principles of relational database design, and the remainder of the chapter delves into the details of implementation. A database is a complex structure, and as such, requires a blueprint in the same way that a house requires one before you start pouring the concrete. Your blueprint must include information about what pieces of data you need to store, what kinds of constraints you need to place on each of those pieces, how the pieces relate to each other, and how you are going to arrange those pieces in database tables. One essential ingredient for successful database design and implementation is not derived from relational theory or knowledge of database systems, but from knowledge of the business you are trying to model. You must have a clear and complete understanding of the process that is being modeled in the database in order to build a successful database application. Acquiring this knowledge involves knowing the industry, having the ability to listen and watch carefully, and the courage to ask many questions. We start our discussion here at the point where this all-important process has concluded—you know which pieces of data need to be stored, and you’re ready to build an efficient and safe structure to hold them.

3-2

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Relational Database Design Principles Database Design References There are several books on database design that provide greater depth of coverage for database design concepts: Database Design for Mere Mortals, by Michael J. Hernandez. Addison Wesley Longman. Designing Relational Database Systems, by Rebecca Riordan. Microsoft Press. Data & Databases: Concepts in Practice, by Joe Celko. Morgan Kaufman. An Introduction to Database Systems, by C. J. Date. Addison Wesley. Database Modeling & Design, by Tobey J. Teorey. Morgan Kaufmann.

The Origins of Relational Design The relational model, developed by E. F. Codd just 30 years ago, makes use of mathematical set theory to define a set of rules that can be applied to database systems. The purpose of these rules is to facilitate the creation of databases that can efficiently maintain the consistency of the data and can efficiently answer questions about the data. In relational terminology, there are certain key terms that have a slightly different meaning from their everyday usage.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-3

Designing and Creating a Database Key Terms Relation

Tables in a database are known as relations.

Attribute

Columns in a table (relation) are called attributes.

Tuple

Rows in a table (relation) are called tuples.

Candidate key

A means of uniquely identifying a tuple (row) based on one or more of its attributes (columns) in a relation (table). Any attribute or combination of attributes that is guaranteed to be unique is a candidate key.

Primary key

One of the candidate keys is selected to be the primary key.

Surrogate key

Surrogate primary key attributes are created when none of the candidate keys in a relation are considered to be suitable as a primary key. This is often done simply to improve performance and efficiency of storage. In SQL Server, identity columns are often used as surrogate keys.

Foreign key

When an attribute or set of attributes is defined as being able to contain only values that are in the primary key of another relation (or Nulls, if they are allowed), it is called a foreign key. For example, CustomerID in an Orders table is a foreign key.

In relational theory, tables are referred to as relations (hence the name), columns are called attributes, and rows are called tuples. Actually, tables containing columns and rows are just the most common representation of the abstract logical construct of relations as sets of tuples, each with the same set of attributes. The tuples (rows) in a relation (table) must be unique. To do anything with a row of data, you need to be able to find it, which means you need a way to identify it uniquely based on one or more of its attributes. Any attribute or combination of attributes that is guaranteed to be unique is a candidate key. To make implementation convenient, one of the candidate keys is selected as the primary key. Also for convenience, if all candidate keys are very long, or composed of many attributes, or subject to frequent changes, then a surrogate key is created that is not only unique to each tuple, but also compact and relatively constant. Access autonumber fields and SQL Server identity columns are examples of surrogate keys. It can be argued that surrogate keys should never be necessary, because every tuple should have some other unique attribute or set of attributes reflecting 3-4

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Relational Database Design Principles attributes of that entity in the “real world,” which is called the problem set (also sometimes referred to as the problem domain or simply the problem space). If database engines were perfect, then things like autonumbers and identity columns wouldn’t be needed, because, for example, all customers could be uniquely identified by their name; address; and the date, time, and location of their first order. Even if two people at the same address had the same name, they presumably couldn’t both place an order at the same location at the same exact date and time. But, using all that data each time you needed to identify a customer, and maintaining changes to all that data every place it was used, would put an unacceptable burden on the system. So, surrogate keys are often employed as a concession to the imperfection of our database systems, to help them perform in the most efficient way possible. Foreign key attributes allow tuples in one relation to reference tuples from another relation. Foreign keys always contain values from the primary key of another relation, creating a relationship between the two relations. For example, the CustomerID column in the tblOrder contains primary key values from tblCustomer, creating a relationship that allows customer data to be referenced from order data. These relationships are covered later in the chapter.

Data Normalization See Normal.SQL

Defining a primary key for each relation is a necessary first step toward creating a well-designed database, but it’s just the beginning. Dr. Codd defined a series of progressively more stringent standards, called normal forms, which further optimize data for efficient storage and retrieval.

First Normal Form First normal form requires that each attribute in a relation contain only one piece of information that you care about, and each piece of information that you care about should be held in only one attribute. For example, consider the following table of values: StudentID

Name

1234

Martin R. Jones

5678

Gilda Swanson

The Name column holds the full name of each student, including the family name, or last name, and the given name, or first name. Now you can see why our definition of first normal form contained the somewhat awkward phrase, “piece of information that you care about.” If all you ever cared about was the full name, then this table would be in first normal form. However, if you ever want to search or sort by the last name or first name separately, then this is a

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-5

Designing and Creating a Database violation of first normal form. Fix it by breaking the Name column up into separate columns, one for each piece of the name that you care about: StudentID

LastName

FirstName

1234

Jones

Martin

5678

Swanson

Gilda

MiddleInitial R.

Always break information down into the most atomic units needed to solve your business problems. Relational databases make it very easy to join these pieces back together later for presentation to the users.

Repeating Groups Another way to violate first normal form is to have to look to multiple attributes to find one piece of data. The most common example of this is a pattern that is often seen in spreadsheets—repeating groups. In the following table, a student taking on a third class would require adding an additional column to the table for Course3. StudentID

Course1

Course2

1234

Elementary Chemistry

Hydroponic Gardening

5678

Zen Meditation

Elementary Chemistry

If some students only enroll in one class, or two classes, then the additional columns consume excess storage space. However, the most serious problem with this arrangement is that it would be very difficult to figure out how many students were signed up for Elementary Chemistry. You’d need to look in all of the repeating columns for the information. The following table structure supports an unlimited number of classes for each student and allows you to efficiently count how many times a particular class is being taken. StudentID

Course

1234

Elementary Chemistry

1234

Hydroponic Gardening

5678

Zen Meditation

5678

Elementary Chemistry

The primary key for this table would need to include both columns in order to uniquely identify each row. To add efficiency, you might want to create a ClassID field and only store the full name of each class in a separate table.

Second Normal Form Second normal form ensures that each relation models just one entity or event in the problem set. Although second normal form also looks at the attributes of 3-6

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Relational Database Design Principles a relation, it is really about the relation as a whole. At the technical level, second normal form states that in addition to conforming to first normal form, every attribute of a relation must be functionally dependent on the entire primary key. This means that each primary key value, including all attributes in the key if there are more than one, should uniquely determine the value of every other attribute in each tuple (row). In the following table showing student enrollments, StudentID and CourseID are the primary key. The CourseName column is dependent on CourseID, but not on StudentID: StudentID

CourseID

CourseName

1234

2222

Penthouse Farming

5678

3333

Animal Husbandry in Manhattan

To conform to second normal form, CourseName should probably be off in a separate relation that contains data pertaining to each course, not here in a relation about enrollments. Could the table above ever be considered to be in second normal form? Actually, it could. If the course names for a particular course ID changed from time to time, and you needed to keep track of what the name was for this particular enrollment, then the CourseName might indeed be dependent on the full primary key and not just on CourseID. A clearer example of that occurs with products and prices. You need to record a product’s price every time you record a sale, even though a standard price may also appear in the product table. That’s because the sale price isn’t just dependent on the product, it is also dependent on which sale you’re referring to.

Third Normal Form Third normal form builds on second normal form with the additional requirement that all attributes are not functionally dependent on any other columns besides the primary key. For example, the following table would not meet third normal form because the TotalCost column derives its value from the Units value multiplied by CostPerUnit. CourseID

CourseDescription

Units

CostPerUnit

TotalCost

2222

Penthouse Farming

4

40.00

160.00

3333

Animal Husbandry in Manhattan

3

50.00

150.00

If changes are made to the values in Units or CostPerUnit, then TotalCost is no longer accurate. And any change to TotalCost would have to be accompanied by an appropriate change to at least one of the other columns. The argument against violating third normal form is less convincing in SQL Server, which supports computed columns and triggers, both of which are capable of updating the Total value when the Unit or CostPerUnit values Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-7

Designing and Creating a Database change. In theory, this column is completely redundant, but in practice calculated values are sometimes stored to improve performance. As long as the interdependency is enforced at the database level, data integrity won’t be threatened. There is more to third normal form, however, than just a ban on calculated, redundant values. Third normal form builds on second normal form by ensuring that all attributes that aren’t in the primary key not only are functionally dependent on the full key, but also are not functionally dependent on each other. In the following sample, PersonID is the primary key of the Person table. The remaining columns are all dependent on PersonID. However, the City and State columns are not only dependent on PersonID, they are also dependent on the ZipCode. Theoretically, once you know the new zip code, you could determine the new City and State. A perfectly normalized table might only contain PersonID, StreetAddress, and ZipCode. PersonID

StreetAddress

City

State

ZipCode

1234

300 Ocean Drive

Singer Island

FL

33404

5678

44 Clematis

West Palm Beach

FL

33405

Third normal form is the most frequently violated normal form, for the sake of convenience or performance. Few databases go to the trouble of maintaining the postal lookup table that would be needed to bring the example above into full compliance with third normal form.

Beyond Codd’s Normal Forms In the years following Dr. Codd’s original formulation of the first three normal forms, other database scientists have presented well-accepted arguments for three additional normal forms. Boyce/Codd is a variation on third normal form that addresses fairly uncommon cases where there are at least two composite candidate keys with overlapping values. Fourth normal form deals with separating multivalued dependencies into separate relations, and fifth normal form deals with a rare kind of three-table circular dependency. For most business applications, conformance to the first three normal forms is all that is needed.

Understanding Relationships Once you’ve normalized your relations, logically related pieces of information are segregated into multiple tables. To maintain consistent data, the database must enforce the referential integrity of these logical relationships. For example, if student data is stored in one table and enrollment information is stored in another table, the database needs a way to know that every StudentID in the Enrollment table has a corresponding record in the Student table.

3-8

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Relational Database Design Principles A relationship between two relations is defined by creating an attribute or set of attributes in one relation, called a foreign key, which contains values matching the values in the primary key of the other relation. For example, each row in the Enrollment table has a foreign key column, StudentID, containing a value that can also be found in the primary key StudentID column in the Student table. Foreign key columns can be allowed to contain Null values, but any non-Null values must match primary key values in the related table.

One-to-Many Relationships A one-to-many relationship is based on matching the values in the primary key of one relation (table) with the values in the corresponding foreign key of another relation. Each tuple in the first relation could potentially match up with many tuples in the second relation. For example, one student could be enrolled in many classes. The student-enrollment relationship is a master-child or “has a” type of one-tomany relationship. One student “has” some number of class enrollments. A student might exist with zero class enrollments, or one, or more, but every student enrollment maps back to exactly one student, as shown in Figure 1.

Figure 1. The one-to-many relationship between students and class enrollments.

Another type of one-to-many relationship is a lookup or “is a” relationship. For example, each instructor “is a” member of a department in the school. The record in the Department table with a value of “Domestic Arts” matches any number of records in the Instructor table. But each instructor matches up with exactly one department, as shown in Figure 2.

Figure 2. The relationship between instructor and department is a lookup relationship.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-9

Designing and Creating a Database

Many-to-Many Relationships Each student can be enrolled in many classes, and many students can attend each class. This is called a many-to-many relationship, and it can only be represented in the database by using a third table. The Enrollment table, is used to record the various pairings of students and classes. The primary key in the Enrollment table is a composite key containing unique combinations of StudentID and ClassID values. Also consider that each course may be taught in several different classes, held at different times during the week, taught by different instructors, and located in different rooms. The Classes table itself represents several many-to-many relationships between courses, instructors, and rooms. All of these relationships are shown in Figure 3.

Figure 3. The many-to-many relationships between students, classes, courses, instructors, and rooms.

One-to-One Relationships Sometimes, a foreign key can also be the entire primary key. This is called a one-to-one relationship, since there can only be one matching record in each of the two relations. The most common usage of one-to-one relationships is to define attributes that only apply to a subset of the tuples (records) in a relation.

3-10

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Relational Database Design Principles For example, you might have a Person table that holds general information about all people in the database, and another Instructor table that holds extra information that only applies to faculty. Each record in the Person table might or might not have one matching record in the Instructor table, but each record in the Instructor table would have exactly one matching record in the Person table, as shown in Figure 4.

Figure 4. A one-to-one relationship between Person and Instructor.

Enforcing Referential Integrity Enforced relationships require that every foreign key value have a matching primary key value in the related table. In other words, you can’t have a StudentID in the Enrollment table that doesn’t correspond to a matching StudentID in the Student table. Enforcing referential integrity also means that a student record can’t be deleted while any enrollment records exist that depend on that student. In addition, the StudentID can’t be changed for a student without making the same change to the StudentID values in related enrollment records.

Cascading Updates and Deletes Unlike earlier versions of SQL Server, SQL Server 2000 allows you to enforce referential integrity by cascading updates and/or deletes to related tables. So, for example, if you try to change a StudentID value in the Student table that was also used as a foreign key in the Enrollment table, SQL Server will “cascade” the update, permitting the change in the Student table and updating all related records in the Enrollment table as well. Cascading deletes would entail deleting a student, and having all related records deleted from the Enrollment table, leaving no orphans behind. Cascading deletes is potentially dangerouscarelessly implementing cascading deletes can easily wipe out all of your historical data.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-11

Designing and Creating a Database

Beyond Normalization Even with fully normalized tables and well-enforced referential integrity, there are plenty of ways that garbage can creep into your database. You can’t prevent all data entry errors, but you can do a lot at the database level to increase the chances that invalid data will be rejected.

Enforcing Data Integrity Relational theorists have formulated distinct categories of integrity. For example, the uniqueness of each tuple in a relation is generally considered to be a form of entity integrity. The restriction that a particular attribute can contain only dates is one way of enforcing domain integrity. And the creation of relationships between primary and foreign keys enforces referential integrity. However, these distinctions between different types of data integrity are not as simple as they appear, since entity integrity and referential integrity could be seen as forms of domain integrity. Domain integrity is the restriction of attributes to a defined “domain” of valid values. When you select a data type for a column, you are restricting the domain of values that can be stored in that column. When you select whether Nulls will be permitted, you are refining the definition of the domain. Designating a primary key or a unique index could also be seen as restricting the domain to values that would not result in duplicate key values. Even the creation of relationships can be seen as restricting the domain of non-Null foreign key values to values that exist in the corresponding primary key in the related table. So, in a sense all forms of data integrity are variations on the basic concept of domain integrity.

Enforcing Business Rules Some types of data integrity are referred to as business rules. For example, you may want to restrict the domain of class dates to include only dates that fall on weekdays. Or you may need to run a procedural test to determine the domain of acceptable class assignments for a student based on the number of credits needed for graduation. Although SQL Server includes a rich set of tools for defining and enforcing such business rules, which we discuss later in this chapter, some programmers prefer to define only the most basic forms of data integrity within the database itself, and to enforce the more complex and changeable business rules in code that lives outside the database. To ensure that these rules are enforced, external code must always be used to make changes to the affected objects in the database. Business rules that are applied within the database itself have the advantage of being automatically enforced no matter what external method is used to manipulate data.

3-12

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Implementing the Design The relational model is logical, and completely independent of the actual physical system used to implement it. However, successfully implementing the logical relational model in SQL Server requires knowledge of how SQL Server performs the physical management of the data you’re storing.

Database Storage SQL Server uses three underlying physical storage concepts: •

Pages

•

Extents

•

Files

Pages A page is the smallest unit of SQL Server storage, at 8,192 bytes (or 8 KB). This is also the maximum size of a row in a SQL Server table, not counting large text and image columns, which are stored in separate pages. A page can only contain data from a single object, although an object can occupy multiple pages. For example, a table can span many pages, but a page can only contain data from a single table.

Extents An extent is eight contiguous pages, or 64 KB of data. When you create a new table or index, it is given a single page in an existing extent that may contain pages belonging to other objects. When the new object grows to the point where it occupies eight pages in mixed extents, it gets moved to its own uniform extent. SQL Server then creates additional extents as needed.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-13

Designing and Creating a Database

Files Databases are stored directly in operating system files, which can be configured to grow automatically as data is added to them. You can specify the growth increment and an optional maximum size when you create the database, as shown later in the chapter. Databases can be stored in multiple files, but a single file must contain data from only one database. SQL Server uses three file extensions for its data files, one for each of the three types of files that it creates: •

MDF: The primary data file for each database.

•

NDF: Secondary data files, holding additional data for a database. SQL Server allows you to create multiple files in addition to the primary data file.

•

LDF: The log file, which contains the transaction log.

Using Files and Filegroups The primary data file is automatically created when you create a new database. You can optionally add additional files and filegroups, locating them on different devices. Multiple filegroups improve performance for larger databases by allowing a single database to be accessed on multiple hardware devices simultaneously. If different drive controllers service different files, data updates can be completed more quickly. When you create tables and indexes, you can also specify that they be located on a filegroup other than the primary file group.

Data Integrity and the Transaction Log The transaction log is fundamental to the way SQL Server works, and is automatically created when you create a new database. The transaction log records all activity in the database, and provides an extra layer of protection for your data. SQL Server uses a write-ahead strategy to maintain the log, taking the following actions for any data modification: 1. The change is recorded in the transaction log. 2. The data pages to be modified are read into the in-memory storage cache. 3. The changes are made in the cached pages. 4. The checkpoint process saves the changes to disk. The checkpoint process automatically executes at frequent intervals, writing data from the cache to the physical drive the data is stored on. You can force the checkpoint process manually by issuing the CHECKPOINT statement in TransactSQL, which immediately writes any cached pages to disk.

3-14

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design In the event of system failure, you can restore the database from a backup, then apply transactions from the transaction log to recover data changes that occurred after the last backup and that would otherwise be lost. The transaction log is a separate data file (.ldf) that is normally located on a separate device from the data files.

WARNING!

Do not use a write-caching disk controller on any drives storing SQL Server databases. A write-caching disk controller interferes with the internal logic SQL Server uses during the checkpoint process. SQL Server ensures that data can be recovered from the transaction log at the time of any failure by storing disk writes internally and committing them when the CPU is less busy. Since SQL Server depends on knowing when the checkpoint process actually wrote to the physical drive, a write-caching disk controller could make your transaction log unrecoverable.

Creating Databases Creating new databases can be done in the Enterprise Manager, either with the Create Database Wizard, or through the menus. You can also use the TransactSQL CREATE DATABASE statement to create a new database. Both the Enterprise Manager Wizard and menu options collect input and then execute the CREATE DATABASE statement behind the scenes, using the parameter values you’ve chosen in the dialog boxes. Almost all actions that you take using graphical tools in SQL Server follow this pattern—behind the scenes it all boils down to executing Transact-SQL statements.

Try It Out! To use the menu options in the Enterprise Manager to create a new database, follow these steps: 1. Expand the server node until the Databases node is exposed. Rightclick on Databases and choose New Database.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-15

Designing and Creating a Database 2. The Database Properties dialog box will then load, with the General tab displayed, as shown in Figure 5. The name must be unique, and conform to the rules for SQL Server identifiers, as listed in the sidebar below. If you already have a sample database named School, use a different name for the new one, such as School2. The Collation name defaults to the collation used when installing SQL Server and it defines the language and sort order for the data. SQL Server 2000 supports different collation orders for different databases, and even for different columns in a single database.

Figure 5. Type in the Name of the new database and select the Collation name for the language you want to use. Unless you have a good reason to do otherwise, just select Server default for the Collation name.

3-16

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design 3. Click the Data Files tab to specify the File Name and Location of the data files for your database. This dialog box allows you to specify the name and location of the primary filegroup, as shown in Figure 6. You can also create additional filegroups at this time by typing in the information on the lines under that displayed for the primary filegroup. The File properties option at the bottom of the dialog box shows the default settings, which include automatically growing the database file as needed, and not setting a ceiling on file growth. One significant improvement that was introduced in SQL Server 7.0 was the ability of databases to grow as needed to accommodate data being entered.

Figure 6. Specifying the name and location of the data file in the primary filegroup.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-17

Designing and Creating a Database 4. Click the Transaction Log tab to specify the name and location of the transaction log. Figure 7 shows the name and location of the transaction log, with the default options to grow the file as needed. As mentioned earlier, it is recommended that the transaction log be located on a different device from the primary data files, in case of a disk failure. Click OK when finished, and the database will be created.

Figure 7. Setting the transaction log file properties.

3-18

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design Rules for SQL Server Identifiers Identifiers are simply the names of objects, and SQL Server has the same rules governing identifiers for all its objects, including databases, tables, columns, views, and stored procedures. •

Identifiers must be between one character and 128 characters in length.

•

Identifiers must start with a letter or the symbols @, _, or #. This can be any Unicode character, which includes a through z, A through Z, and most foreign characters.

•

Identifiers starting with @ can only be local variables or parameters in TransactSQL.

•

Identifiers starting with # can only be local temporary objects.

•

Identifiers starting with ## can only be global temporary objects.

•

Characters after the first character can be letters, decimal numbers, or the symbols #, $, or _.

•

Spaces and other special characters can only be used if the identifier is always delimited with double quote marks or square brackets. Square brackets are always supported; double quote marks are only allowed if the “Use quoted identifiers” database option has been set.

•

Identifiers must not be reserved words. There are two Books Online topics with the title “Reserved Keywords” that list all reserved words for Transact-SQL and Embedded SQL for C (ESQL/C).

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-19

Designing and Creating a Database

Configuring Database Properties Once you’ve created the database, you can configure additional database properties. Right-click on the new database, and select Properties from the menu. The General, Data Files, and Transaction Log tabs are the same ones you saw when you created the database. Figure 8 shows the Filegroups tab, which you can use to create additional filegroups.

Figure 8. The Filegroups tab in the Database Properties dialog box.

3-20

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design The Options tab, shown in Figure 9, allows you to set additional database options.

Figure 9. Setting additional database options.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-21

Designing and Creating a Database What the Options Mean The options shown in Figure 9 are not the complete list of available options, but merely the most commonly used ones. Table 1 shows the list of database options you can set from the Enterprise Manager and what they mean. Option

Meaning

Restrict accessMembers of db_owner, dbcreator or sysadmin

Only users who are owners or system administrators can use the database.

Restrict accessSingle user

Only one user at a time can open the database.

Restrict accessRead-only

The database will be read-only. This speeds up data access operations because SQL Server will not need to manage any locks.

Recovery Model

This setting allows you to set the recover model for restoring from backups to Simple, Bulk-Logged, or Full.

ANSI NULL default

Columns in tables allow Null values unless explicitly defined with NOT NULL.

Recursive triggers

Allows triggers in a table to make changes that cause the same trigger to be fired again.

Auto update statistics

Instructs SQL Server to automatically update any out-of-date statistics.

Torn page detection

Allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.

Autoclose

Shuts down the database cleanly when the last user exits.

Autoshrink

Instructs SQL Server to periodically shrink the database if needed.

Auto create statistics

Instructs SQL Server to automatically create any missing statistics needed by a query during the optimization process. Valid statistics help the query processor determine the most efficient execution plan for data access.

Use quoted identifiers

Allows double quotation marks to delimit identifiers (names of objects). If this is turned off, then double quotation marks delimit literal strings.

Database compatibility level

Forces SQL Server to work in 70, 65, or 60 compatibility. However, features introduced subsequent to the selected compatibility level will be unavailable. Table 1. Database options set using the Enterprise Manager.

3-22

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design In addition, there are other useful database options not shown in the Enterprise Manager that are listed in Table 2. Option

Meaning

Select into/bulk copy

Allows non-logged bulk data loading operations.

Truncate log on checkpoint

Destroys the contents of the transaction log each time the checkpoint process commits data to disk.

ANSI null default

The default setting for SQL Server databases is NOT NULL. Setting this to ON changes the database default to NULL.

ANSI nulls

When set to ON, all comparisons to a null value evaluate to NULL.

ANSI warnings

When set to ON, errors or warnings are issued when conditions such as “divide by zero” occur or null values appear in aggregate functions. When set to OFF, no warnings are raised when null values appear in aggregate functions, and null values are returned when conditions such as “divide by zero” occur. The SQL Server default is OFF.

ANSI padding

When set to ON, trailing blanks in character values inserted into varchar columns and trailing zeros in binary values inserted into varbinary columns are not trimmed.

concat null yields null

When set to ON, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL.

Offline | Online

Used to take a database offline or bring it back online. When it’s offline, it’s closed cleanly and marked offline. You need to bring it back online in order to use it. Table 2. Other useful database options not listed in the Enterprise Manager.

To change database options not listed in the Enterprise Manager, use the Transact-SQL syntax ALTER DATABASE. Database options can also be set using the sp_dboption system stored procedure, which is supported in SQL Server 2000 for backwards compatibility. Use the DATABASEPROPERTYEX function to retrieve current settings for database options. The following statement executed in a Query Analyzer window will return 0 if the Auto Close property is not set, and 1 if it is: SELECT DATABASEPROPERTYEX('School', 'IsAutoClose')

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-23

Designing and Creating a Database Use the sp_dboption system stored procedure to turn it on: sp_dboption 'School', 'AutoClose', 'TRUE'

Or the ALTER DATABASE Transact-SQL statement: ALTER DATABASE School SET AUTO_CLOSE OFF

Note that there’s quite a big difference in syntax between sp_dboption and ALTER DATABASE. SQL Server Books Online warns that sp_dboption may not be supported in future versions of SQL Server, so it probably makes sense to get in the habit of using ALTER DATABASE to set options not available in the Enterprise Manager.

Creating Tables When you create a new database, it will contain systems tables, but no user tables. SQL Server system tables contain metadata about the structure and security settings of your database. All system tables have the prefix “sys” on their names. Tables you create, called “user tables,” must follow these rules: •

Each table has a name that is unique in the database and follows the rules for identifiers. Tables can have the same name if they have different owners, and must then be used with the ownername.tablename syntax.

•

Each column describes a single characteristic of a set, and must have a unique name within that table.

To further conform to the rules for normalizing data outlined earlier in this chapter, tables must also have the following characteristics: •

Each row must be unique and describe one set of related information about the subject of the table. The primary key ensures the uniqueness of each row.

•

The order of the rows or columns should not be significant or meaningful in any way.

Each column you define has as its most important characteristic a data type. This limits the domain of possible values that can be inserted into the column.

3-24

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

SQL Server Data Types Specifying the data type of a table helps you avoid the “garbage in, garbage out” syndrome by strictly limiting the type of data that can be entered. For example, a column with a data type defined as datetime cannot accept the string data “tomorrow.” Data types also give you control over the amount of memory that is used to store each piece of data. SQL Server has built-in system data types, and also supports user-defined data types (which are comprised of system data types). These are the SQL Server system data types: binary[(n)]

The binary data type holds from 1 to 8,000 bytes of fixedlength binary data, where n is a value between 1 and 8,000. The storage size includes whatever is stored in the column plus 4 additional bytes. Binary is a fixed-length data type, so if you define a column as binary(5) it will always use 5 bytes of disk space plus the additional 4. Using a fixed-length data type is efficient when data in the column is close to the same size, since fixed-length storage allows SQL Server to retrieve data more efficiently.

varbinary[(n)]

The varbinary data type is the same as binary, except that it is a variable-length data type. Only the actual length of the data is stored, and the (n) parameter merely specifies the maximum length of the data. Use varbinary when the length of the binary data is unknown or varies in length.

text

The text data type consists of literal non-Unicode string data up to 2,147,483,647 characters. The text data type is managed internally as linked data pages that only appear to be stored in a table.

ntext

The ntext data type can hold data up to 1,073,741,823 Unicode characters, occupying twice the number of bytes as characters entered since Unicode characters each use 2 bytes of storage. Use ntext only if you need to support languages that require Unicode, since it has twice the overhead of the text data type.

image

The image data type is similar to the text data type in that it consists of linked data pages consisting of binary data. It can contain up to 2,147,483,647 bytes of binary data.

char[(n)]

The char data type is fixed-width, and can contain from 1 to 8,000 non-Unicode characters. The storage size is (n) regardless of the actual length of the data. Unused storage is padded with trailing spaces. Use the char data type when data entered in a column is the same length, such as a column that contains state names (“NY”, “CA”, etc.).

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-25

Designing and Creating a Database varchar[(n)]

varchar is similar to char, except that it is a variablelength. It can contain from 1 to 8,000 non-Unicode characters. The (n) parameter defines the maximum length of the string, and only the data actually contained in the column is stored. Use varchar when you have column lengths that vary in length.

nchar[(n)]

The nchar is a fixed-length data type that can contain from 1 to 4,000 Unicode characters. It is similar to char, but with twice the storage requirements. Use char when you need to store strings that require Unicode character sets.

nvarchar[(n)]

The nvarchar data type is similar to nchar, except it is a variable-length data type that can contain from 1 to 4,000 Unicode characters. Any data that is too long to fit will be truncated at the nth character.

datetime

The datetime data type consists of two 4-byte segments: 4 bytes for the number of days before or after the base date of 1/1/1900, and 4 bytes for the number of milliseconds after midnight, rounded to increments of .000, .003, or .007 seconds. The datetime data type encompasses dates between January 1, 1753, and December 31, 9999. Date and time data are entered by enclosing alphanumeric values with single quotes ('). The date and time parts are processed separately, so you can enter either the time or the date part first. However, when entering time values, you must enter the element’s hours, minutes, seconds, milliseconds, and AM/PM in that order with a colon separator or an am/pm signifier. The default value for the date part is 1/1/1900, and for the time part is 12:00:00:000AM.

smalldatetime

The smalldatetime data type consists of two 2-byte integers: 2 bytes for the number of days after 1/1/1900, and 2 bytes for the number of minutes past midnight. The range of values is much smaller than datetime, with valid dates between 1/1/1900 and 6/6/2079 and with time accuracy only to the minute. Use smalldatetime if you don’t need to store dates outside that range or time values with granularity of more than a minute.

decimal[(p[, s])] and numeric[(p[, s])]

3-26

The decimal and numeric data types are just two names for the same thing. The decimal/numeric data types are used for numbers with fixed precision and scale, indicated by the (p, s) parameters. When maximum precision is used, valid values are from - 10^38 -1 through 10^38 - 1. The scale (s) is the maximum number of digits that can be stored to the right of the decimal point, and it must be less Introduction to Microsoft SQL Server 2000

Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design than or equal to the precision. When specifying the precision, use a value of 1 through a maximum of 38. If you don’t specify a scale or precision, the default precision is 18, and the default scale is 0. The storage requirement depends on the precision that you specify. One thing to bear in mind is that SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types. float[(n)]

A double precision, or float, uses 8 bytes of storage and can hold either a positive or negative number from -1.79E + 308 through 1.79E + 308 with a binary precision up to 15 digits.

real

The real data type is a smaller version of float, which contains a single precision floating point number data with a value range from –3.40E + 38 through 3.40E + 38, with a storage size of 4 bytes.

int

The int data type uses 4 bytes of storage and can store whole numbers from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint

The smallint data type is a smaller version of int, consuming 2 bytes of storage and storing a range of whole numbers from -2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint

The tinyint data type consumes only 1 byte of storage and can store whole numbers from 0 through 255.

bigint

The bigint data type is new in SQL Server 2000. It can hold numbers in a range from –2^63 to 2^63-1, and uses 8 bytes of storage. Use bigint when you need a larger version of int.

money

The money data type can store decimal data scaled to four digits of precision. Values range from -2^63 (922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a tenthousandth of a monetary unit. Storage is 8 bytes. The money data type is useful when you don’t need more than four decimal places of precision since values are preserved exactly, with no rounding beyond the four digits.

smallmoney

The smallmoney data type is a smaller version of money, consuming 4 bytes of storage and with values ranging from - 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Use smallmoney when you don’t need to store a large range of money values.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-27

Designing and Creating a Database

3-28

bit

The bit data type can hold a value of either 1 or 0. Null values are not allowed. SQL Server stores bit fields efficiently by collecting multiple bit fields and storing up to eight in a single byte. Use the bit data type for true/false yes/no types of data where you don’t need to store a Null value.

identity

An identity column isn’t really a data type per se—it’s a SQL Server-specific column that provides an autoincrementing int value. Identity columns are often used as primary keys.

timestamp

A column defined as a timestamp generates a databasewide unique binary value that is created automatically when a row is inserted and updated every time the row is edited. A table is allowed only one timestamp column, and it consumes 8 bytes of storage. A timestamp column has nothing to do with the datetime data type or the actual time on your computer clock. SQL Server uses the timestamp column to indicate the sequence of activity on that row and to speed up certain operations. In TransactSQL you can use rowversion as a synonym for timestamp, and in SQL 2000 you can use timestamp fields in replicated tables.

sql_variant

The sql_variant data type is new in SQL Server 2000. The sql_variant data type can store int, binary, and char values, but not text, ntext, image, or timestamp values. The sql_variant is the least efficient data type and should be used only when no other option seems practical.

uniqueidentifier

A uniqueidentifier consists of a 16-byte value that is often represented as a 32-digit hexadecimal number, or GUID (globally unique identifier). New values can be generated automatically for uniqueidentifier by specifying the NEWID() function as a default value. A table can have multiple uniqueidentifier columns, and if it does not automatically generate unique values it may contain multiple occurrences of an individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY constraints are also specified. The uniqueidentifier data type has a substantial amount of overhead at 16 bytes of storage, which means that indexes built using uniqueidentifier keys will likely be slower than implementing the indexes using an int column, which occupies only 4 bytes.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Try It Out! To create a new Person table in the School (or School2) database using the Enterprise Manager, follow these steps: 1. Right-click on the Tables node in the School database in the Enterprise Manager and choose New Table from the menu. 2. Name the first column PersonID, and select a data type of int. Set the Identity property to Yes. Click the gold key on the toolbar to set this column as the primary key for the table. This will guarantee that each new record added has a unique value that is generated automatically. 3. Define additional columns with the following definitions: Name

Data Type

Length

Allow Nulls

LastName

varchar

30

No

FirstName

varchar

30

No

MI

char

1

Yes

Address

varchar

50

Yes

City

varchar

50

Yes

State

char

2

Yes

ZipCode

varchar

15

Yes

4. Click the Save button to save your changes. 5. To try out entering data in the new table, right-click anywhere in the table designer and choose Task|Open Table from the menu. Figure 10 shows entering some sample data in the new table. Note that the PersonID is generated automatically.

Figure 10. Entering data in the new table.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-29

Designing and Creating a Database

Computed Columns Computed columns, which were introduced in SQL Server 7.0, allow you to have a de-normalized column that is automatically updated when the values in the columns it is based on change. For example, in the Course table you could have a PricePerUnit and a Units column, along with a Total column, as shown in Figure 11. The Total column has the following Formula property: ([PricePerUnit] * [Units])

Figure 11. The Formula property allows you to configure a computed column.

If a value in either the PricePerUnit or the Units column changes, the Total column will be automatically recomputed.

3-30

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Creating Constraints You can restrict the data entered in columns by setting restrictions, or constraints, on the types of values allowed. Constraints can be placed on columns as part of creating tables, or they can be added later. There are six types of constraints that you can set on columns: •

Primary Key: Set on a column (or set of columns) to uniquely identify a row in a table. A table can have only one primary key.

•

Foreign Key: Set on a column (or set of columns) participating in a relationship between tables and referencing only the primary key or unique constraints in the related table. Tables can have multiple foreign keys, corresponding to relationships with up to 63 other tables in the same database. Foreign key constraints can also reference the primary key in the same table (a self join).

•

Not Null: Null (or unknown) values are not allowed. When you allow Null values, SQL Server keeps a special bitmap in every row to indicate which nullable columns actually contain Null values. SQL Server must then decode this bitmap for each row accessed, which adds additional overhead to data operations. The Not Null constraint is equivalent to making a column required.

•

Default: Default constraints provide default values and can contain constants, functions, ANSI-standard niladic functions (‘niladic’ is a five-dollar word for functions that don’t take parameters, like GETDATE()), or Nulls. Referring to other columns, tables, views, or stored procedures is not allowed. Defaults also cannot be created on identity or timestamp columns.

•

Check: Check constraints limit the range of values that can be entered in a column, based on a Boolean expression. If the expression returns a True value or a Null, then the entry is accepted. Mulitiple check constraints can be entered for a column, and they are evaluated in the order they were entered. You can also create Check constraints that refer to multiple columns.

•

Unique: This constraint requires that each entry in a column (or combination of columns) be unique. A table can have up to 249 unique constraints defined.

A primary key constraint can be added in the table designer by clicking the gold key button, as shown earlier in this chapter. Foreign key constraints are most easily added by using a database diagram, which will be covered later in this chapter. Null constraints are most easily added when defining the data type, as shown in the previous section. Unique constraints are added when creating indexes, as shown later in this chapter. That leaves default and check constraints, which are covered next.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-31

Designing and Creating a Database

Default Constraints A default constraint provides for a value to be automatically entered into a new row when no other data is explicitly entered in that column. You can define one default constraint per column. For example, Figure 12 shows creating a default of an empty string (‘’) for the MI (or middle initial) column in the Person table. If a person does not have a middle initial, then an empty string will be entered in the table automatically.

Figure 12. Creating a default constraint for the MI (middle initial) column.

CHECK Constraints A CHECK constraint checks the data before the record is saved. If the incoming data does not conform to the constraint, then the record won’t be saved. If the return value of the constraint expression is False, the constraint has been violated and the record will not be saved.

3-32

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Try It Out! Follow these steps to create a CHECK constraint limiting the state values for the Person table: 1. Right-click in the Table Designer and choose Check Constraints from the menu. Click the New button to enable the Constraint name and Constraint expression fields. 2. Type the definition of the constraint. You must type the expression before you change the name of the constraint. The following expression will limit the values entered into the State column to the abbreviation for Florida: State = 'FL'

If you have a list of more than one valid state, use the Transact-SQL IN clause: State IN ('FL', 'NY', 'CA', 'WA')

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-33

Designing and Creating a Database Figure 13 shows the completed constraint.

Figure 13. Creating a Check constraint.

The options at the bottom of the dialog box have the following meanings: •

Check existing data on creation. This option ensures that all data that exists in the table before the constraint was created is verified against the constraint.

•

Enforce constraint for replication. This option enforces the constraint when the table is replicated into a different database.

•

Enforce constraint for INSERTs and UPDATEs. This option causes the constraint to be enforced when data is inserted or updated.

3. Click Close when finished. The constraint will be saved when you save the table.

3-34

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design You can refer to multiple columns in a Check constraint (for example, making sure that the delivery date falls after the order date), but you can’t refer to columns in other tables. To validate data in one table against data in other table, you need to create a trigger. Be careful when creating Check constraints. If you really did want to limit the values for the State column to a particular set of states, it would probably be better to create a separate table of allowed states and to create a foreign key. That way, as the list of allowed states changed, you would only need to maintain the data in the related table, not the constraint. In general, such datadriven restrictions are easier to maintain than ones encoded in Check constraints.

Creating Rules If you have a constraint that you use over and over again, you can create a Rule instead. A Rule can be applied to multiple tables. To create a rule, right-click on the Rules node in the Enterprise Manager, and select New Rule from the menu. This loads the Rule Properties dialog box. Type a name for the rule, and an expression that defines the rule. The following expression will create a rule: @State = 'FL'

The variable, @State, represents the column name, which can vary from table to table.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-35

Designing and Creating a Database Once the rule is saved, right-click on it and select Properties from the menu. You can bind the rule to user-defined data types (UDTs) or to columns in a table. Figure 14 shows binding the StateFlorida rule (@State = 'FL') to the State column in the Person table.

Figure 14. Binding a rule to a column.

Using Rules with User-Defined Data Types You can create your own user-defined data types, which are composed of system data types. Each system data type has specific parameters that you can specify when designing your user-defined data type for a column. This allows you to fine tune, standardize, and document a column’s data type. You would create your own data type when you want something more specific than the built-in data types. For example, you might want to create a userdefined data type for a product code that always consisted of two alphabetical characters followed by six numeric characters. A user-defined data type that incorporated a rule limiting the number and kind of characters that are allowed in the column could limit entries to ones like this:

3-36

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

BE123456

To create the rule, right-click on the Rules node and select New Rule from the menu. Name the rule IDRule and enter the following expression: @ID LIKE '[a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]'

The dialog box should look like the one shown in Figure 15. Click OK when finished.

Figure 15. Creating a new rule.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-37

Designing and Creating a Database To create the user-defined data type, select the database and right-click on User-defined datatypes. Select New user-defined data type from the menu. This will load the User-Defined Data Type Properties dialog box, as shown in Figure 16. You can also specify a rule and a default value for the data type where appropriate.

Figure 16. Binding the user-defined data type to a rule.

3-38

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design When defining the data type for a column in a table, the custom data type will show up at the bottom of the data type drop-down list, after the system data types, as shown in Figure 17.

Figure 17. Assigning a user-defined data type to a column.

TIP:

User-defined data types and rules created in the model database will automatically be included in all subsequent databases that you create.

Triggers A trigger is a Transact-SQL procedure that executes only on INSERT, UPDATE, or DELETE statements—there’s no such thing as a trigger for a SELECT statement. A trigger becomes part of the statement or transaction that fires it, and a ROLLBACK TRANSACTION statement issued in a trigger cancels the whole transaction.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-39

Designing and Creating a Database

Uses for Triggers Use triggers to enforce business rules that can’t be expressed as a constraint, and to raise alerts. There are some business rules that are too complex to be implemented within the expressions available for constraints or rules. Triggers can reference values from other columns, other tables, or other databases, and they can contain more complex logic. You can perform almost any TransactSQL operation in a trigger, except creating or modifying the design of objects and performing system administration tasks. Triggers support conditional logic and can alter values in other tables. To understand triggers, you really need to understand Transact-SQL and transactions, which are both covered later in the course, as is a more detailed look at triggers.

Creating Indexes The Indexes/Keys tab on the table’s Properties dialog box allows you to create both indexes and unique constraints. Index creation is a very important part of defining the columns and tables in your database because of the impact that indexes have on the speed of data retrieval.

3-40

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design To create a new index, click the New button. Select the column name (or namesyou can create an index on more than one column) and specify the index type. Figure 18 shows creating a unique index/constraint on the LastName column.

Figure 18. Creating a unique index/constraint on the LastName column.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-41

Designing and Creating a Database The various options you can set for the index or constraint are explained in Table 3. Option

Meaning

Create UNIQUE

Unique indexes are extremely efficient and SQL Server will generally favor them over non-unique indexes given a choice. If you have a column that you know will always contain unique data, check off the Create UNIQUE checkbox when creating the index. When you create a UNIQUE constraint, a unique index is always created to support it. Nulls are treated as individual values in an index that’s defined as unique, so a single-column index can contain only one Null.

Ignore duplicate key

The Ignore duplicate key check box for unique indexes allows you to specify that if multiple rows are inserted into the table and some are duplicates, only the duplicates will be rejected. Otherwise, all the rows, including those with acceptably unique values, would be rejected if any were duplicates.

Fill factor

The Fill factor option determines how densely the index is packed when it is created. If the table is empty at the time you create the index, then you can ignore this setting since it is meaningful only if there is already data in the table. If the table is going to have many updates and inserts, then create an index with a low fill factor to leave room for the future keys to be inserted. However, if the table is read-only or will otherwise not change much, then a high fill factor will reduce the physical size of the index, lowering the number of disk reads needed. The fill factor only applies when you create the index—indexes stabilize at a certain density over time as keys are inserted and deleted.

Create as Only one index per table can be clustered, and generally it CLUSTERED should be on a column with unique values, although this is not required. If necessary, SQL Server will add a 4-byte value called a uniqueifier to each entry, ensuring that it points to one unique row of data. The data in the table is physically stored according to the order defined by the clustered index, just as the pages of a book are ordered by page number. Don’t automatically recompute statistics

SQL Server automatically recomputes statistics on a table when its optimization algorithms conclude that enough changes have occurred to justify the work. You can override this by checking the Don’t automatically recompute statistics check box, which is rarely a good idea unless you plan to create the statistics yourself as needed (see Create Statistics in Books Online).

Table 3. Options for creating indexes and unique constraints.

3-42

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Database Diagrams You can define relationships in the Table Designer by choosing the Properties dialog box and clicking on the Relationships tab. However, database diagrams offer a more complete environment for creating relationships and performing other design tasks on your tables. A single database can support multiple database diagrams, each diagramming a different set of tables.

Using Database Diagrams to Enforce Relationships The first time you create a database diagram, the wizard runs, stepping you through the process of selecting the tables you want added. Right-click on the Diagrams node in your database and choose New Database Diagram from the menu. This launches the Create Database Diagram Wizard. Follow all of the steps in the wizard to add the tables, and when the wizard finishes, they will all be arranged in the diagram. The Database Diagram Designer doesn’t have any menu commandseverything is done either from the right-click menu or from the toolbar. You can zoom the view in and out as needed. To create a relationship, select the primary key from the One side of the relationship (or the primary table), and drag it on to the foreign key value in the destination table. This launches the Create Relationship dialog box, as shown in Figure 19.

Figure 19. Creating a relationship between the Student and Enrollment tables.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-43

Designing and Creating a Database The meaning of the various options you can set on the foreign key constraint are listed in Table 4. Option

Meaning

Check existing data on creation

Data already existing in the table will be checked to make sure it matches the constraint.

Enforce relationship for replication

If you leave this option blank, the constraint will be enforced on users, but not on data changes that result from the replication process. In other words, the constraint won’t let users violate it, but if a row is inserted from another database through replication, the constraint will be ignored.

Enforce relationship for INSERTs and UPDATEs

The constraint will be enforced for data being inserted into the table, as well as for data being updated.

Cascade Update Related Fields

Checking this option will cause changed primary key values to be cascaded to the corresponding foreign key fields in related tables.

Cascade Delete Related Fields

Checking this option will cause records containing foreign keys to be deleted automatically when records containing the matching primary keys are deleted. Table 4. Relationship constraint options.

WARNING!

If you choose the Cascade Delete Related Fields option, you stand to lose your historical data should a record on the primary side of the relationship be deleted. For example, setting Cascade Delete on a customer/orders relationship will cause all orders for that customer to be lost if a customer is deleted from the database. If you do not choose the Cascade Delete option, then a customer cannot be deleted who has outstanding orders. The attempted deletion will fail, and neither the customer nor the orders will be deleted.

Modifying Tables from a Database Diagram Database diagrams are useful for modifying other table properties besides just setting relationships. In fact, all of the options available in the Table Designer are available from a database diagram.

Try It Out! Follow these steps to add a new column to the Enrollment table and make it part of the primary key:

3-44

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design 1. Select the Enrollment table in the database diagram, right-click and choose Table View|Standard from the menu. Figure 20 shows the table in the new view. 2. Set the column name to Semester, the data type to varchar (20), and clear the Allow Nulls checkbox. 3. Highlight StudentID, ClassID, and the new Semester column, and click the gold Primary Key button on the toolbar. 4. Click the Save button on the database diagram toolbar to save changes to the table.

Figure 20. Changing the view in the database diagram to edit a table.

TIP:

Not all column properties are displayed in the Standard view. You can choose to create a Custom view of the table, which allows you to specify the column properties you wish to modify. Only columns that are displayed in the database diagram view can be modified.

Taking time at the outset to properly design and specify your database will save you countless hours during the implementation phase. A well-designed database is amenable to change, and can be modified easily to conform to new requirements. The Enterprise Manager provides a robust and easy to use graphical user interface for creating your tables and defining columns. By selecting the proper data types for your columns and by adding constraints and triggers, you can protect the domain integrity of your data and enforce business

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-45

Designing and Creating a Database rules. Database diagrams allow you to build relationships between your tables and to work with table and column properties.

3-46

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Summary •

A solid database design is essential to the success of your SQL Server database application.

•

Database relationships can be one-to-many, one-to-one, or many-tomany.

•

SQL Server 2000 supports cascading updates and deletes.

•

Data is stored in SQL Server in files and filegroups.

•

The transaction log records all database activity before saving it to disk.

•

Database integrity is ensured through data types, constraints, and relationships.

•

Complex business rules can be implemented through triggers.

•

Unique constraints are created at the same time as an index.

•

Database diagrams allow you to set relationships as well as modify table properties.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-47

Designing and Creating a Database

(Review questions and answers on the following pages.)

3-48

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Questions 1. What do you need in addition to knowledge of relational design principles to build a successful database application? 2. What are the three types of relationships between tables? 3. What is the smallest unit of SQL Server storage? 4. Can a filegroup contain multiple databases? 5. What must the expression used in a CHECK constraint evaluate to? 6. Which properties of a table can be modified using a database diagram?

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-49

Designing and Creating a Database

Answers 1. What do you need in addition to knowledge of relational design principles to build a successful database application? Knowledge of the business

2. What are the three types of relationships between tables? One-to-many, one-to-one, and many-to-many

3. What is the smallest unit of SQL Server storage? A page is the smallest unit of SQL Server storage, at 8192 bytes (or 8 KB).

4. Can a filegroup contain multiple databases? No. Databases can be stored in multiple files, but a single file can contain data from only one database.

5. What must the expression used in a CHECK constraint evaluate to? The expression used in a CHECK constraint must return a True or Null value.

6. Which properties of a table can be modified using a database diagram? Only properties that are displayed in the view. Create a custom view to modify additional properties.

3-50

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Implementing the Design

Lab 3: Designing and Creating a Database

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-51

Lab 3: Designing and Creating a Database

Lab 3 Overview In this lab you’ll learn how to create a database. You’ll then create two tables, defining the columns and setting the primary key. You’ll learn how to create a CHECK constraint to limit the values in a table. You’ll then learn how to create a relationship between the tables using a database diagram. Each exercise will build on the objects created in the previous exercise. To complete this lab, you’ll need to work through three exercises: •

Create a SQL Server Database

•

Create SQL Server Tables

•

Create Relationships Using a Database Diagram

Each exercise includes an “Objective” section that describes the purpose of the exercise. You are encouraged to try to complete the exercise from the information given in the Objective section. If you require more information to complete the exercise, the Objective section is followed by detailed step-bystep instructions.

3-52

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Create a SQL Server Database

Create a SQL Server Database Objective In this exercise, you’ll create a new SQL Server database named “Orders” using the Enterprise Manager. Locate the new database in the default data directory on your SQL Server.

Things to Consider •

When creating a new database, make sure that there is sufficient room on the device chosen to store the database files.

•

Consider whether you want to locate the transaction log on the same device as the main data files.

Step-by-Step Instructions 1. Right-click on the Databases node in the Enterprise Manager and choose New Database. 2. Name the new database Orders and click the Data Files tab.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-53

Lab 3: Designing and Creating a Database 3. Verify that the file locations for the main data files are appropriate. Make sure that the File Properties Automatically grow file is turned on, as shown in Figure 21.

Figure 21. Creating a new database and setting the file to automatically grow.

4. Click the Transaction Log tab. If possible, locate the transaction log on another device. If you’re only working on a single machine, then simply leave it in the default folder. Click OK when finished and the database is created.

3-54

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Create SQL Server Tables

Create SQL Server Tables Objective In this exercise, you’ll create two tables in the Orders database you created in the first exercise: Customers and CustomerOrders. The Customers table will contain information about each customer. Columns you define will include a CustomerID identity column to serve as the primary key, LastName, FirstName, Address, City, State, and ZipCode. The CustomerOrders table will contain an OrderID identity column to serve as a primary key. It will also include an OrderDate and a DeliveryDate column in addition to a CustomerID column that will map back to the CustomerID in the Customers table. The default value for the OrderDate will be the current date. You will need to create a CHECK constraint to ensure that the DeliveryDate entered is greater than the OrderDate.

Things to Consider •

Table names must be unique in the database.

•

Identity columns must be defined as “Not Null.”

•

The data type for character columns should be varying for character data that will be different lengths.

•

The datetime data type encompasses a larger date range than smalldatetime. Which one is better suited for orders that start from today?

•

The expression used in a CHECK constraint must evaluate to either True or False.

Step-by-Step Instructions 1. Right-click on the Tables node in the Orders database and choose New Table from the menu. 2. This loads the Table Designer. For the first column, define the name as CustomerID, with a data type of int. Clear the Allow Nulls checkbox. 3. In the Columns section of the designer, set the Identity property to Yes. 4. To set CustomerID as the primary key, click the gold key button on the toolbar. Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-55

Lab 3: Designing and Creating a Database 5. Create the following additional columns: Name

Data Type

Length

Allow Nulls

LastName

varchar

30

No

FirstName

varchar

30

No

Address

varchar

50

Yes

City

varchar

20

Yes

State

char

2

Yes

ZipCode

varchar

11

Yes

6. Click the Save button and enter the name Customers. The table should look similar to that shown in Figure 22. Close the Table Designer when finished.

Figure 22. The newly-created Customers table.

3-56

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Create SQL Server Tables 7. Repeat step 1 to create another new table. Define the following columns: Name

Data Type

Length

Allow Nulls

OrderID

int

4

No

CustomerID

int

4

No

OrderDate

smalldatetime

4

No

DeliveryDate

smalldatetime

4

Yes

8. Select the OrderID column and set the Identity property to Yes. Click the gold key button on the toolbar to set OrderID as the primary key. 9. Select the OrderDate column. Set the Default Value property to the getdate() function. This causes the default value of the current date to be inserted. 10. Save the table and name it CustomerOrders. Right-click anywhere in the designer and choose Task|Open Table from the menu. Type in any number in the CustomerID column and move off of the row. Note that both the OrderID and OrderDate values are automatically filled in. Select the row and delete it. Close the Table view and return to the designer. 11. To create a constraint that limits the values entered in the DeliveryDate to be later than those entered in the OrderDate, right-click anywhere in the designer and choose Check Constraints from the menu. 12. Click the New button to enable the fields to enter the check constraint. Type the following in the Constraint expression window: DeliveryDate > OrderDate

13. Click Close when finished. To save the constraint, click the Save button on the Designer toolbar. 14. To test the constraint, choose Task|Open Table from the menu. Try entering a DeliveryDate that is earlier than the OrderDate. You should not be able to save the record.

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-57

Lab 3: Designing and Creating a Database

Create Relationships Using a Database Diagram Objective In this exercise, you’ll use a database diagram to create a relationship between the Customers and the CustomerOrders tables created in the previous exercise.

Things to Consider •

Any work done in the Database Diagram Designer is not saved in the underlying tables until you explicitly save the database diagram.

•

When creating a relationship between the Customers and the CustomerOrders table, should you set the option to cascade deletes? Or will it be necessary to prevent orders from being deleted when a customer is deleted in order to save a sales history?

Step-by-Step Instructions 1. To create a new database diagram in the Orders database, right-click on the Diagrams node and select New Database Diagram from the menu. 2. This launches the Create Database Diagram Wizard. Follow the steps to complete the wizard, adding the Customers and the CustomerOrders tables. Click Finish to complete the wizard.

3-58

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

Create Relationships Using a Database Diagram 3. This opens the database diagram with the two tables displayed. Because each customer can have many orders, start with the Customers table and select the CustomerID field. Holding down the left mouse button, drag and drop it on top of the CustomerID field in the CustomerOrders table and release the mouse. This loads the Create Relationship dialog box as shown in Figure 23.

Figure 23. Creating a relationship between the Customers and the CustomerOrders tables.

4. Check the option for Cascade Update Related Fields. This causes any updates to the CustomerID in the Customers table to cascade to any existing entries in the CustomerOrders table. Leave the Cascade Delete Related Records option blank. This way a customer who has outstanding orders cannot be deleted. Click OK when finished. 5. The relationship will show up as a join line between the Customers and the CustomerOrders tables. 6. Click the Save change script toolbar button. Click Yes to save the change script, and select the path to the lab directory. Save the file as DiagramChangeScript.sql (you don’t need to type the .sql file extensionit will be filled in automatically for you). 7. Click the Save button and save the diagram as OrdersDiagram. Click Yes to save changes to the tables. Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.

3-59

Lab 3: Designing and Creating a Database

3-60

Introduction to Microsoft SQL Server 2000 Copyright © by Application Developers Training Company and AppDev Products Company, LLC All rights reserved. Reproduction is strictly prohibited. Licensed to ITCourseware, LLC. Reprinted with permission.