Database Design:Part3

SQL Server Database Storage

·         SQL Server maps a database over a set of operating-system files

·         Database files 

SQL Server DataTypes

Objects that contain data have an associated data type that defines the kind of data; for example, character, integer, or binary, the object can contain. The following objects have data types:

·         Columns in tables and views.

·         Parameters in stored procedures.

·         Variables.

·         Transact-SQL functions that return one or more data values of a specific data type.

·         Stored procedures that have a return code, which always has an integer data type.

Assigning a data type to an object defines four attributes of the object:

·         The kind of data contained by the object.

·         The length or size of the stored value.

·         The precision of the number (numeric data types only).

·         The scale of the number (numeric data types only).

For more information about data type precision, scale and length, see Precision, Scale, and Length (Transact-SQL).

Transact-SQL has these system data types. 

bigint	binary	bit	char	CLR
cursor	date	datetime	datetime2	datetimeoffset
decimal	float	hierarchyid	image	int
money	nchar	ntext	numeric	nvarchar
real	rowversion	smalldatetime	smallint	smallmoney
sql_variant	table	text	time	timestamp
int, bigint, smallint, and tinyint (Transact-SQL)	varbinary	varchar	uniqueidentifier	xml (Transact-SQL)

All data stored in Microsoft SQL Server must be compatible with one of these base data types. The cursor data type is the only system data type that cannot be assigned to a table column. It can be used only with variables and stored procedure parameters.

Several base data types have synonyms (for example, rowversion is a synonym for timestamp, and national character varying is a synonym for nvarchar). For more information about the behavior of synonyms, see Data Type Synonyms (Transact-SQL).

Two kinds of user-defined data types can also be created:

·         Alias data types are created from base data types. They provide a mechanism for applying a name to a data type that is more descriptive of the types of values to be held in the object. This can make it easier for a programmer or database administrator to understand the intended use of any object defined with the data type. For example:

·         -- Create a birthday datetype that allows nulls.

CREATE TYPE birthday

FROM datetime NULL

GO

-- Create a table using the new data type.

CREATE TABLE employee (emp_id char(5), emp_first_name char(30), emp_last_name char(40), emp_birthday birthday)

For more information, see Working with Alias Data Types.

·         CLR user-defined data types are based on data types created in managed code and uploaded in a SQL Server 

 

SQL View:

Index:

DataBase Design: Part2

Normalization is the process of removing redundancies from the data. For example, when you convert from an indexed sequence access method (ISAM) style application, normalization often involves breaking data in a single file into two or more logical tables in a relational database. Transact-SQL queries then recombine the table data by using relational join operations. By avoiding the need to update the same data in multiple places, normalization improves the efficiency of an application and reduces the opportunities for introducing errors due to inconsistent data.

However, there are tradeoffs to normalization. A database that is used primarily for decision support (as opposed to update-intensive transaction processing) may not have redundant updates and may be more understandable and efficient for queries if the design is not fully normalized. Nevertheless, data that is not normalized is a more common design problem in database applications than over-normalized data. Starting with a normalized design, and then selectively denormalizing tables for specific reasons, is a good strategy.

                                                                                                                            

Whatever the database design, you should take advantage of these features in SQL Server to automatically maintain the integrity of your data:

• CHECK constraints ensure that column values are valid.
• DEFAULT and NOT NULL constraints avoid the complexities (and opportunities for hidden application bugs) caused by missing column values.
• PRIMARY KEY and UNIQUE constraints enforce the uniqueness of rows (and implicitly create an index to do so).
• FOREIGN KEY constraints ensure that rows in dependent tables always have a matching master record.
• IDENTITY columns efficiently generate unique row identifiers.
• timestamp columns ensure efficient concurrency checking between multiple-user updates.
• User-defined data types ensure consistency of column definitions across the database.

By taking advantage of these features, you can make the data rules visible to all users of the database, rather than hiding them in application logic. These server-enforced rules help avoid errors in the data that can arise from incomplete enforcement of integrity rules by the application itself. Using these facilities also ensures that data integrity is enforced as efficiently as possible.

2.2.1. First Normal Form (1NF)

The general concept of normalization is broken up into several "normal forms." An entity is said to be in the first normal form when all attributes are single-valued. To apply the first normal form to an entity, we have to verify that each attribute in the entity has a single value for each instance of the entity. If any attribute has repeating values, it is not in 1NF.

A quick look back at our database reveals that we have repeating values in the Songs attribute, so the CD is clearly not in 1NF. To remedy this problem, an entity with repeating values indicates that we have missed at least one other entity. One way to discover other entities is to look at each attribute and ask the question "What thing does this describe?"

What does Song describe? It lists the songs on the CD. So Song is another "thing" that we capture data about and is probably an entity. We will add it to our diagram and give it a Song Name attribute. To complete the Song entity, we need to ask if there is more about a Song that we would like to capture. We identified earlier song length as something we might want to capture. Figure 2-1 shows the new data model.

Figure 2-1. A data model with CD and Song entities

Now that the Song Name and Song Length are attributes in a Song entity, we have a data model with two entities in 1NF. None of their attributes contain multiple values. Unfortunately, we have not shown any way of relating a CD to a Song.

2.2.2. The Unique Identifier

Before discussing relationships, we need to impose one more rule on entities. Each entity must have a unique identifier -- we'll call it the ID. An ID is an attribute of an entity that meets the following rules:

·         It is unique across all instances of the entity.

·         It has a non-NULL value for each instance of the entity, for the entire lifetime of the instance.

·         It has a value that never changes for the entire lifetime of the instance.

The ID is very important because it gives us a way to know which instance of an entity we are dealing with. Identifier selection is critical because it is also used to model relationships. If, after you've selected an ID for an entity, you find that it doesn't meet one of the above rules, this could affect your entire data model.

Novice data modelers often make the mistake of choosing attributes that should not be identifiers and making them identifiers. If, for example, you have a Personentity, it might be tempting to use the Name attribute as the identifier because all people have a name and that name never changes. But what if a person marries? What if the person decides to legally change his name? What if you misspelled the name when you first entered it? If any of these events causes a name change, the third rule of identifiers is violated. Worse, is a name really ever unique? Unless you can guarantee with 100% certainty that the Name is unique, you will be violating the first rule. Finally, you do know that all Person instances have non-NULL names. But are you certain that you will always know the name of a Person when you first enter information about them in the database? Depending on your application processes, you may not know the name of a Person when a record is first created. The lesson to be learned is that there are many problems with taking a nonidentifying attribute and making it one.

The solution to the identifier problem is to invent an identifying attribute that has no other meaning except to serve as an identifying attribute. Because this attribute is invented and completely unrelated to the entity, we have full control over it and guarantee that it meets the rules of unique identifiers. Figure 2-1 adds invented ID attributes to each of our entities. A unique identifier is diagrammed as an underlined attribute.

Figure 2-1. The CD and Song entities with their unique identifiers

2.2.3. Relationships

The identifiers in our entities enable us to model their relationships. A relationship describes a binary association between two entities. A relationship may also exist between an entity and itself. Such a relationship is called a recursive relationship. Each entity within a relationship describes and is described by the other. Each side of the relationship has two components: a name and a degree.

Each side of the relationship has a name that describes the relationship. Take two hypothetical entities, an Employee and a Department. One possible relationship between the two is that an Employee is "assigned to" a Department. That Department is "responsible for" an Employee. The Employee side of the relationship is thus named "assigned to" and the Department side "responsible for."

Degree, also referred to as cardinality, states how many instances of the describing entity must describe one instance of the described entity. Degree is expressed using two different values: "one and only one" (1) and "one or many" (M). An employee is assigned to one department at a time, so Employee has a one and only one relationship with Department. In the other direction, a department is responsible for many employees. We therefore say Department has a "one or many" relationship with Employee. As a result a Department could have exactly one Employee.

It is sometimes helpful to express a relationship verbally. One way of doing this is to plug the various components of a direction of the relationship into this formula:

entity1 has [one and only one | one or many] entity2

Using this formula, Employee and Department would be expressed like so:

Each Employee must be assigned to one and only one Department.

Each Department may be responsible for one or many Employees.

We can use this formula to describe the entities in our data model. A CD contains one or many Songs and a Song is contained on one and only one CD. In our data model, this relationship can be shown by drawing a line between the two entities. Degree is expressed with a straight line for "one and only one" relationships or "crows feet" for "one or many" relationships. Figure 2-1 illustrates these conventions.

Figure 2-1. Anatomy of a relationship

How does this apply to the relationship between Song and CD? In reality, a Song can be contained on many CDs, but we ignore this for the purposes of this example.Figure 2-2 shows the data model with the relationships in place.

Figure 2-2. CD-Song relationship

With these relationships firmly in place, we can go back to the normalization process and improve upon the design. So far, we have normalized repeating song values into a new entity and modeled the relationship between it and the CD entity.

2.2.4. Second Normal Form (2NF)

An entity is said to be in the second normal form if it is already in 1NF and all nonidentifying attributes are dependent on the entity's entire unique identifier. If any attribute is not dependent entirely on the entity's unique identifier, that attribute has been misplaced and must be removed. Normalize these attributes either by finding the entity where it belongs or by creating an additional entity where the attribute should be placed.

In our example, "Herbie Hancock" is the Band Name for two different CDs. This fact illustrates that Band Name is not entirely dependent on CD ID. This duplication is a problem because if, for example, we had misspelled "Herbie Hancock," we would have to update the value in multiple places. We thus have a sign that Band Nameshould be part of a new entity with some relationship to CD. As before, we resolve this problem by asking the question: "What does a band name describe"? It describes a band, or more generally, an artist. Artist is yet another thing we are capturing data about and is therefore probably an entity. We will add it to our diagram with Band Name as an attribute. Since all artists may not be bands, we will rename the attribute Artist Name. Figure 2-1 shows the new state of the model.

Figure 2-1. The data model with the new Artist entity

Of course, the relationships for the new Artist table are missing. We know that each Artist has one or many CDs. Each CD could have one or many Artists. We model this in Figure 2-2.

Figure 2-2. The Artist relationships in the data model

We originally had the Band Name attribute in the CD entity. It thus seemed natural to make Artist directly related to CD. But is this really correct? On closer inspection, it would seem that there should be a direct relationship between an Artist and a Song. Each Artist has one or more Songs. Each Song is performed by one and only one Artist. The true relationship appears in Figure 2-3.

Figure 2-3. The real relationship between Artist and the rest of our data model

Not only does this make more sense than a relationship between Artist and CD, but it also addresses the issue of compilation CDs.

2.2.5. Kinds of Relationships

When modeling the relationship between entities, it is important to determine both directions of the relationship. After both sides of the relationship have been determined, we end up with three main kinds of relationships. If both sides of the relationship have a degree of one and only one, the relationship is called a "one-to-one" or "1-to-1" relationship. As we will find out later, one-to-one relationships are rare. We do not have one in our data model.

If one of the sides as a degree of "one or many" and the other side has a degree of "one and only one," the relationship is a "one-to-many" or "1-to-M" relationship. All of the relationships in our current data model are one-to-many relationships. This is to be expected since one-to-many relationships are the most common.

The final kind of relationships is where both sides of the relationship are "one or many" relationships. These kind of relationships are called "many-to-many" or "M-to-M" relationships. In an earlier version of our data model, the Artist-CD relationship was a many-to-many relationship.

2.2.6. Refining Relationships

As we noted earlier, one-to-one relationships are quite rare. In fact, if you encounter one during your data modeling, you should take a closer look at your design. A one-to-one relationship may imply that two entities are really the same entity. If they do turn out to be the same entity, they should be folded into a single entity.

Many-to-many relationships are more common than one-to-one relationships. In these relationships, there is often some data we want to capture about the relationship. For example, take a look at the earlier version of our data model in Figure 2-2 that had the many-to-many relationship between Artist and CD. What data might we want to capture about that relationship? An Artist has a relationship with a CD because an Artist has one or more Songs on that CD. The data model inFigure 2-3 is actually another representation of this many-to-many relationship.

All many-to-many relationships should be resolved using the following technique:

1.       Create a new entity (sometimes referred to as a junction entity). Name it appropriately. If you cannot think of an appropriate name for the junction entity, name it by combining the names of the two related entities (e.g., ArtistCD). In our data model, Song is a junction entity for the Artist-CD relationship.

2.       Relate the new entity to the two original entities. Each of the original entities should have a one-to-many relationship with the junction entity.

3.       If the new entity does not have an obvious unique identifier, inherit the identifying attributes from the original entities into the junction entity and make them together the unique identifier for the new entity.

In almost all cases, you will find additional attributes that belong in the new junction entity. If not, the many-to-many relationship still needs to be resolved, otherwise you will have a problem translating your data model into a physical schema.

2.2.7. More 2NF

Our data model is still not in 2NF. The value of the Record Label attribute has only one value for each CD, but we see the same Record Label in multiple CDs. This situation is similar to the one we saw with Band Name. As with Band Name, this duplication indicates that Record Label should be part of its own entity. Each Record Label releases one or many CDs. Each CD is released by one and only one Record Label. Figure 2-1 models this relationship.

Figure 2-1. Our data model in the second normal form

2.2.8. Third Normal Form (3NF)

An entity is said to be in the third normal form if it is already in 2NF and no non-identifying attributes are dependent on any other nonidentifying attributes. Attributes that are dependent on other nonidentifying attributes are normalized by moving both the dependent attribute and the attribute on which it is dependent into a new entity.

If we wanted to track Record Label address information, we would have a problem for 3NF. The Record Label entity with address data would have State Name andState Abbreviation attributes. Though we really do not need this information to track CD data, we will add it to our data model for the sake of our example. Figure 2-1 shows address data in the Record Label entity.

Figure 2-1. Record Label address information in our CD database

The values of State Name and State Abbreviation would conform to 1NF because they have only one value per record in the Record Label entity. The problem here is that State Name and State Abbreviation are dependent on each other. In other words, if we change the State Abbreviation for a particular Record Label -- from MN to CA -- we also have to change the State Name -- from Minnesota to California. We would normalize this by creating a State entity with State Name and State Abbreviation attributes. Figure 2-2 shows how to relate this new entity to the Record Label entity.

Figure 2-2. Our data model in the third normal form

Now that we are in 3NF, we can say that our data model is normalized. 

We can further normalize it till 5/6 normal forms but we will discuss it later.