SQL Server 2005

System Versus User Objects

System databases include Master, Model, MSDB, Resource, TempDB, and Distribution. SQL Server creates these databases during the installation process.

In addition to system databases, there are also system tables, stored procedures, functions, and other system objects.

Whereas system objects are part of the SQL Server system, you create user objects. User objects include the databases, stored procedures, functions, and other database objects that you build.

Each column or set of columns in a table that contains unique values is considered a candidate key. One candidate key becomes the primary key. The remaining candidate keys become alternate keys. A primary key made up of one column is considered a simple key. A primary key comprising multiple columns is considered a composite key.

A domain is a pool of values from which columns are drawn. A simple example of a domain is the specific data range of employee hire dates. In the case of the Order table, the domain of the CustomerID column is the range of values for the CustomerID in the Customers table.

Normalization

Normalization is the process of applying a series of rules to ensure that your database achieves optimal structure. Normal forms are a progression of these rules. Each successive normal form achieves a better database design than the previous form did.

To achieve first normal form, all columns in a table must be atomic. This means, for example, that you cannot store the first name and last name in the same field. The reason for this rule is that data becomes very difficult to manipulate and retrieve if multiple values are stored in a single field.

Second Normal Form

To achieve second normal form, all nonkey columns must be fully dependent on the primary key. In other words, each table must store data about only one subject.

Third Normal Form

To attain third normal form, a table must meet all the requirements for first and second normal form, and all nonkey columns must be mutually independent. This means that you must eliminate any calculations, and you must break out data into lookup tables.

Although the developer’s goal is normalization, many times it makes sense to deviate from normal forms. We refer to this process as denormalization. The primary reason for applying denormalization is to enhance performance.

If you decide to denormalize, document your decision. Make sure that you make the necessary application adjustments to ensure that the system properly maintains denormalized fields. Finally, test to ensure that performance is actually improved by the denormalization process.

Integrity Rules

Although integrity rules are not part of normal forms, they are definitely part of the database design process. Integrity rules are broken into two categories. They include overall integrity rules and database-specific integrity rules.

Overall Rules

The two types of overall integrity rules are referential integrity rules and entity integrity rules. Referential integrity rules dictate that a database does not contain any orphan foreign key values. This means that

• Child rows cannot be added for parent rows that do not exist. In other words, an order cannot be added for a nonexistent customer.
• A primary key value cannot be modified if the value is used as a foreign key in a child table. This means that a CustomerID cannot be changed if the orders table contains rows with that CustomerID
• A parent row cannot be deleted if child rows are found with that foreign key value. For example, a customer cannot be deleted if the customer has orders in the orders table

SQL Server has two wonderful features. One is called Cascade Update, and the other is called Cascade Delete. These features make it easier for you to work with data, while ensuring that referential integrity is maintained. With the Cascade Update feature, SQL Server Express automatically updates the foreign key field on the child rows when the primary key of the parent is modified. This allows the system to modify a primary key while maintaining referential integrity. Likewise, the Cascade Delete feature deletes the associated child rows when the parent rows are deleted, once again maintaining referential integrity.

Entity integrity dictates that the primary key value cannot be null. This rule applies not only to single-column primary keys, but also to multicolumn primary keys. In fact, in a multicolumn primary key, no field in the primary key can be null. This makes sense because if any part of the primary key can be null, the primary key can no longer act as a unique identifier for the row. Fortunately, SQL Server does not allow a field in a primary key to be null.

Database-Specific Rules

The other set of rules applied to a database are not applicable to all databases, but, instead, are dictated by business rules that apply to a specific application. Database-specific rules are as important as overall integrity rules. They ensure that the user enters only valid data into a database. An example of a database-specific integrity rule is that the delivery date for an order must fall after the order date.

A view is a virtual table. Its contents are based on a query. Like a table, a view is composed of rows and columns. Except in the case of a special type of view called an indexed view, views exist only in memory.

A stored procedure is a piece of programming code that can accept input parameters and can return one or more output parameters to the calling procedure or batch (see Figure 1.9). Stored procedures generally perform operations on the database, including the process of calling other stored procedures. They can also return status information to the calling procedure to indicate whether they succeeded or failed.