SQL Server Database Design

Normalization

Normalization is basically for Data integrity offer database normalization provides four types of integrity. They are as follows.

1. Entity integrity
2. Domain integrity
3. Referential integrity
4. User defined integrity

Entity Integrity

Each entity represents a row in a table. Entity must be uniquely recognized in database. Primary key is used to uniquely identify an entity in a table. Violation of this rule will be against entity integrity.

What is composite primary key?

When more than one column are used in a table to create a primary key when it is called composite primary key.

What is natural primary key?

If there is data available in a table which can identify each no of the table uniquely then it will be called natural primary key but if primary key is artificial generated in a table it will be called surrogate primary key.

Domain Integrity

SQL Server provides a variety of tools for enforcing domain integrity for examples types user defined data types default constraint check constraints foreign key constraints all these are used to enforce domain integrity in a table.

Candidate key

Any number of columns in a table can be used as a primary key. All such combination of columns will be called candidate key. Out of this candidate keys we pick one of them as a primary key.

Surrogate key

Surrogate key is a unique identifier for rows in a table ordinary part of the table data.

First Normal Form in Database Design

Let us consider name property of a business entity called employee. We can first name middle name and last name together as name property. How we can separate them and create three properties for the employee. when we input a value in a column of a table state it must be atomic data otherwise it will violet the principle of First Normal Form for example an in employee table we can use Amit Kumar Gupta as value of name In name column but this is not atomic data. If you try to sort the table on the basis of surname then it will not be possible in this case because name field is going to hold multivalve data. The First Normal Form guides to use atomic data in a field or column of a table.

Remember that First Normal Form is a matter of judgment hard and fast rule to design a table.

Second normal formal: It means that each non primary key field of a table should depend only on primary key field of table and not on any non primary key field apart from table being in First Normal Form.

Identity column

Identity column is a column in a table for which SQL Server automatically provides values. When we define identity for a column then we set the initial value and stepping value for the Identity of the column. The initial value is known as seed value and stepping value is also called incremental value. Identity is a way to include surrogate key in a table.

Constraints on Table

Constraints are rules which are used to enforce Data integrity on a table. The Data integrity may be related to entity integrity, domain integrity or user defined integrity. Use different constraints such as unique, default, check, primary key, foreign key etc.

There are two ways to apply constraints.

1. Column constant is applied on a column of a table.
2. Table constant is applied at the table level.

What do you mean by cascading referential integrity in a database?

 continued...