Database Design for SQL Server 2000

ER Modeling

Data modeling concepts are the basis for everything we do in the industry as DBAs. As seen in Chapter 2, an ER data model consists of three main components that are present in any model format:

• Entities. In data modeling, entities can be closely compared to the physical element of a table in SQL Server. An entity is a collection of related data elements such as customers or products.

• Attributes. Attributes are the characteristics given to an entity such as Salary and FirstName . Attributes of an entity represent a set of properties of a data element within an entity. They will become the columns or fields in the physical database.

• Relationships. Relationships show a logical link between two or more entities. A customer entity may be related to orders, a product entity may be related to sales, and so on.

Primary Keys

Primary keys consist of sets of attributes whose values uniquely identify the rows in a entity. Primary keys give an ID to a row and make the row unique throughout the entity. This means that rows can be located easily by this identifier. Additionally, primary keys can be made up of multiple columns known as a composite key.

Foreign Keys

Foreign keys help in the relational process between two entities. When a primary key is created on a parent entity, it can be connected to the foreign key of another entity. You can also link two entities by relating a foreign key to a unique column not defined as the primary key.

One-To-One Relationship

This type of relationship occurs when one row or data element of an entity is associated with only one row or element in the second entity. It is not surprising that one-to-one relationships are uncommon in the real world. They are used mostly when an entity has an extraordinarily large number of attributes ”then the entity is split in two to make it easier to manage. An extra entity also might be desired when developing the physical storage locations of the data. By separating seldom-used data from more frequently used information, faster data retrieval and updates can be accommodated.

One-To-Many Relationship

One-to-many relationships exist when a single instance of an entity (the parent entity) relates to many instances of another entity (the child entity). One-to-many relationships are the most common type of relationship. It is a natural occurrence in the real world ”for example, a customer will have many orders, and a manufactured product will have many components.

Many-to-Many Relationship

This type of relationship occurs when many rows or data elements in an entity are associated with many rows or data elements in another entity. For example, a many-to-many relationship occurs between the Trainee and Course entities. Many Trainees can enroll to a single course, and one trainee can be enrolled into numerous courses. This type of relationship is not uncommon in the real world. However, SQL Server doesn't actually directly implement many-to-many relationships. A many-to-many relationship is implemented using three entities. The two main entities are connected together using a third entity. The third entity contains keys and interrelationship information. Each entity is connected to the new entity as a one-to-many relationship. To discover the cardinality of a relationship, you look at the correlation between the entities.

Entity Integrity

Entity integrity defines a unique row attribute as an identifier for individual entities. Generally, the regulations of this type of data integrity are easy to follow. Simple ways of enforcing this type of integrity are using primary keys, UNIQUE constraints, and unique indexes when the entity design moves into the physical stage of design. Entity integrity specifies that primary keys on every instance of an entity must be present, unique, and have values other than null content.

Domain Integrity

Domain integrity is restricting the data entered to a domain or range of values, and thus preventing incorrect data from entering the system.

Referential Integrity

Referential integrity specifies that every foreign key value map to a primary key or other unique value in related tables. Referential integrity guarantees the smooth navigation of moving from entity to entity, so it is extremely important to ensure proper referential integrity definition. Referential integrity ensures that no orphaned records exist. Orphaned data is a term used when data in a child entity exists that points to a non-existent parent.

Advanced Referential Integrity Options

When defining relationships to be used in a SQL Server database system, two new options exist that allow more control over the actions that can occur affecting key values. Cascade Update Related Fields and Cascade Delete Related Records allows updates or deletions of key values to cascade through the tables defined to have foreign key relationships that can be traced back to the table on which the modification is made.

A cascading deletion will occur when you delete a row with a key referenced by foreign keys in existing rows in other tables; all rows containing those foreign keys are also deleted.

A cascading update occurs when you update a key value in a row where the key value is referenced by foreign keys in existing rows in other tables. All foreign key values are also updated to the new value specified for the key.

SQL Server Data Types

The following list (excerpted from Chapter 2) details SQL Server data types:

• bigint . Integer data from “2^63 ( “9223372036854775808) through 2^63 “1 (9223372036854775807).

• int . Integer data from “2^31 ( “2,147,483,648) through 2^31 “ 1 (2,147,483,647).

• smallint . Integer data from 2^15 ( “32,768) through 2^15 “ 1 (32,767).

• tinyint . Integer data from 0 through 255.

• bit . Integer data with either a 1 or 0 value.

• decimal . Fixed precision and scale numeric data from “10^38 +1 through 10^38 “1.

• numeric . Functionally equivalent to decimal.

• money . Monetary data from “2^63 ( “922,337,203,685,477.5808) through 2^63 “ 1 (+922,337,203,685,477.5807), accuracy to a ten-thousandth.

• smallmoney . Monetary data values from “214,748.3648 through +214,748.3647, accuracy to a ten-thousandth.

• float . Floating precision data from “1.79E + 308 through 1.79E + 308.

• real . Floating precision data from “3.40E + 38 through 3.40E + 38.

• datetime . Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds .

• smalldatetime . Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

• char . Fixed-length single-byte data with a maximum length of 8,000 characters .

• varchar . Variable-length single-byte data with a maximum of 8,000 characters.

• text . Variable-length single-byte data with a maximum length of 2^31 “ 1 (2,147,483,647) characters.

• nchar . Fixed-length Unicode, dual-byte data with a maximum length of 4,000 characters.

• nvarchar . Variable-length Unicode, dual-byte data with a maximum length of 4,000 characters.

• sysname . A system-supplied user -defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names .

• ntext . Variable-length Unicode, dual-byte data with a maximum length of 2^30 “ 1 (1,073,741,823) characters.

• binary . Fixed-length binary data with a maximum length of 8,000 bytes.

• varbinary . Variable-length binary data with a maximum length of 8,000 bytes.

• image . Variable-length binary data with a maximum length of 2^31 “ 1 (2,147,483,647) bytes.

• cursor . A reference to a cursor.

• sql_variant . A data type that stores values of various SQL Server-supported data types, except text , ntext , timestamp , and sql_variant .

• table . A special data type used to store a resultset for later processing.

• timestamp . A database-wide unique number that gets updated every time a row gets updated.

• uniqueidentifier . A globally unique identifier (GUID).