Data Integrity basics

The first step in specifying the domains of a table is to determine column data types. A domain is the set of all allowed values in a column. A domain includes not only the concept of enforcing data types, but also the values allowed in the column. For example, the domain for the Color column of the Production.Product table includes the data type nvarchar, and a size limit of 15 characters.

Null Values

Columns can either accept or reject null values. NULL is a special value in databases that represents the concept of an unknown value. NULL is different from a blank character or 0. Blank is actually a valid character, and 0 is a valid number. NULL just represents the idea that we do not know what this value is. NULL is also different from a zero-length string. If a column definition contains the NOT NULL clause, you cannot insert rows that have the value NULL for that row. If the column definition has only the NULL keyword, it accepts null values.

Allowing null values in a column can increase the complexity of any logical comparisons that use the column. The ISO standard states that any comparison against NULL does not evaluate to TRUE or FALSE, it evaluates to UNKNOWN. This introduces three-value logic to comparison operators, which can be difficult to manage correctly.

Constraints, Rules, Defaults, and Triggers

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

Table columns have properties besides data type and size. These other properties are an important part of guaranteeing the integrity of the data and the referential integrity of the tables in a database:

• Data integrity refers to each occurrence of a column having a correct data value. The data values must be of the right data type and in the correct domain.
  
  
• Referential integrity indicates that the relationships between tables have been correctly maintained. Data in one table should only point to existing rows in another table and not point to rows that do not exist.
  

The following objects are used to maintain both types of integrity:

• Constraints
  
  
• Rules
  
  
• Defaults
  
  
• DML Triggers

</ 2003 authoring ddue.schemas.microsoft.com 5:content>

Data Integrity

Enforcing data integrity guarantees the quality of the data in the database. For example, if an employee is entered with an employee ID value of 123, the database should not permit another employee to have an ID with the same value.
Entity Integrity

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier columns or the primary key of a table, through UNIQUE indexes, UNIQUE constraints or PRIMARY KEY constraints.

Domain Integrity

Domain integrity is the validity of entries for a specific column. You can enforce domain integrity to restrict the type by using data types, restrict the format by using CHECK constraints and rules, or restrict the range of possible values by using FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules.

Referential Integrity

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

Referential integrity preserves the defined relationships between tables when rows are entered or deleted. In SQL Server, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys, through FOREIGN KEY and CHECK constraints. Referential integrity makes sure that key values are consistent across tables. This kind of consistency requires that there are no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database.

When you enforce referential integrity, SQL Server prevents users from doing the following:

• Adding or changing rows to a related table if there is no associated row in the primary table.
  
  
• Changing values in a primary table that causes orphaned rows in a related table.
  
  
• Deleting rows from a primary table if there are matching related rows.

For example, with the Sales.SalesOrderDetail and Production.Product tables in the AdventureWorks database, referential integrity is based on the relationship between the foreign key (ProductID) in the Sales.SalesOrderDetail table and the primary key (ProductID) in the Production.Product table. This relationship makes sure that a sales order can never reference a product that does not exist in the Production.Product table.

User-Defined Integrity

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

User-defined integrity lets you define specific business rules that do not fall into one of the other integrity categories. All the integrity categories support user-defined integrity. This includes all column-level and table-level constraints in CREATE TABLE, stored procedures, and triggers.

</ 2003 authoring ddue.schemas.microsoft.com 5:content>

</ 2003 authoring ddue.schemas.microsoft.com 5:content>

Online Transaction Processing Database Design

Online transaction processing database applications are optimal for managing changing data. These applications typically have many users who are performing transactions at the same time that change real-time data. Although individual requests by users for data usually reference few records, many of these requests are being made at the same time. Common examples of these types of databases are airline ticketing systems and banking transaction systems. The primary concerns in this kind of application are concurrency and atomicity.

Concurrency controls in a database system guarantee that two users cannot change the same data, or that one user cannot change a piece of data before another user is finished with it. For example, if you are talking to an airline ticket agent to reserve the last available seat on a flight and the agent starts the process of reserving the seat in your name, another agent should not be able to tell another passenger that the seat is available.

Atomicity guarantees that all the steps in a transaction are completed successfully as a group. If any step fails, no other steps should be completed. For example, a banking transaction may involve two steps: taking funds out of your checking account and putting them into your savings account. If the step that removes the funds from your checking account succeeds, you want to make sure that the funds are put into your savings account or put back into your checking account.

Online Transaction Processing Design Considerations

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

Transaction processing system databases should be designed to promote the following:

• Good data placement
  
  I/O bottlenecks are a big concern for OLTP systems, because of the number of users modifying data all over the database. When you design a database, determine the likely access patterns of the data and combine frequently accessed data together. Use filegroups and RAID (redundant array of independent disks) systems to help in this.
  
  
• Short transactions to minimize long-term locks and improve concurrency
  
  Avoid user interaction during transactions. Whenever possible, run a single stored procedure to process the whole transaction. The order in which you reference tables within your transactions can affect concurrency. Put references to frequently accessed tables at the end of the transaction to minimize the duration that locks are held.
  
  
• Online backup
  
  OLTP systems are frequently characterized by continuous operations in which down time is kept to an absolute minimum. That is, they operate 24 hours a day, 7 days a week. Although the SQL Server Database Engine can back up a database while it is being used, schedule the backup process to occur during times of low activity to minimize the effects on users.
  
  
• High normalization of the database
  
  Reduce redundant information to increase the speed of updates and improve concurrency. Reducing data also improves the speed of backups, because less data has to be backed up.
  
  
• Minimal or no historical or aggregated data
  
  Data that is rarely referenced can be archived into separate databases, or moved out of the heavily updated tables into tables that contain only historical data. This keeps tables as small as possible, and improves backup times and query performance.
  
  
• Careful use of indexes
  
  Indexes must be updated every time a row is added or modified. To avoid over-indexing heavily updated tables, keep indexes narrow. Use the Database Engine Tuning Advisor to design your indexes.
  
  
• Optimum hardware configuration to handle the large numbers of concurrent users and quick response times required by an OLTP system

</ 2003 authoring ddue.schemas.microsoft.com 5:content>

Decision Support Database design -- Report Database

Decision-support database applications are optimal for data queries that do not change data. For example, a company can periodically summarize its sales data by date, sales region, or product, and store this information in a separate database to be used for analysis by senior management. To make business decisions, users must be able to determine trends in sales quickly by querying the data based on various criteria. However, they do not have to change this data. The tables in a decision-support database are heavily indexed, and the raw data is frequently preprocessed and organized to support the various types of queries to be used. Because the users are not changing data, concurrency and atomicity issues are not a concern; the data is changed only by periodic, bulk updates made during off-hour, low-traffic times in the database.

Decision Support Design Considerations

</ 2003 authoring ddue.schemas.microsoft.com xmlns="http://ddue.schemas.microsoft.com/authoring/2003/5" 5:content>

Decision-support system databases should be designed to promote the following:

• Heavy indexing
  
  Decision-support systems have low update requirements, but large volumes of data. Use many indexes to improve query performance.
  
  
• Denormalization of the database
  
  Introduce pre-aggregated or summarized data to satisfy common query requirements and improve query response times.
  
  
• Use of a star or snowflake schema to organize the data within the database.

</ 2003 authoring ddue.schemas.microsoft.com 5:content>

Delegates

Delegates in C# (and in other programming languages such as Java) allow you to do things that

other languages do through leveraging function pointers. In C++ there is a feature called a callback

function that uses pointers to functions to pass them as parameters to other functions. The

main difference between delegates and function pointers is that delegates are both object-oriented

and type-safe, and the delegate encapsulates both the object instance and a method (this encapsulation

protects data from corruption by other functions because of errors in programming).

A delegate can hold references to one or more functions and invoke them as needed.

Delegates differ in other ways from function pointers:

❑

Delegates are dynamic and are declared at runtime. In C++ you had to know the function

name before you were able to use the function pointer.

❑ Delegates don’t just point to one function. Instead, they point to an ordered set of functions.

What You Can Do with the Bulk API

The REST Bulk API lets you query, insert, update, upsert, or delete a large number of records asynchronously. The records can include binary attachments, such as Attachment objects or Salesforce CRM Content. You first send a number of batches to the server using an HTTP POST call and then the server processes the batches in the background. While batches are being processed, you can track progress by checking the status of the job using an HTTP GET call. All operations use HTTP GET or POST methods to send and receive XML or CSV data.

Important

Currently base64 fields are not supported in queries with the Bulk API

 

 

 

 

 In most circumstances, the Bulk API is significantly faster than the SOAP-based API for loading large numbers of records. However, performance depends on the type of data that you're loading as well as any workflow rules and triggers associated with the objects in your batches. It's useful to understand the factors that determine optimal loading time.

 

Cross-Page Posting in ASP.NET Web Pages

ASP.NET by default, submits the form to the same page. Cross page posting is submitting the form to a different page. This is usually required when you are creating a multi page form to collect information from the user on each page. When moving from the source to the target page, the values of controls in the source page can be accessed in the target page.

To use cross-page posting, you have to use the PostBackURL attribute to specify the page you want to post to.

Follow these steps :

Step 1: Create a new ASP.NET website called CrossPagePosting. By default, the website is created with a single webpage, Default.aspx. Right click the project in the Solution Explorer > Add New Item >Web Form. Keep the original name Default2.aspx and click ‘Add’. The website will now contain two pages, Default.aspx and Default2.aspx.

Step 2: On the source page, Default.aspx, drop a button on the form. Set the text of the button as ‘TargetButton’. Set the ‘PostBackUrl’ property of a Button to the URL of the target page, Default2.aspx.

<asp:Button ID="Button1" runat="server" PostBackUrl="~/Default2.aspx" Text="TargetButton" /></div>

Step 3: In the target page Default2.aspx, drop a label on the page from the toolbox.

Step 4: In the Page_Load() of Default2.aspx, you can then access the ‘PreviousPage’ property to check if the page is being accessed as Cross Page postback.

protected void Page_Load(object sender, EventArgs e)

{

       if (Page.PreviousPage != null)

       {

 }

}

Step 5: To retrieve values from the source page, you must access controls using the ‘FindControl()’ method of the ‘PreviousPage’. We will be accessing the Text property of the Button control placed in Default.aspx.

protected void Page_Load(object sender, EventArgs e)

{

        if (Page.PreviousPage != null)

        {

            Button btn = (Button)(Page.PreviousPage.FindControl("button1"));

            Label1.Text = btn.Text;

        }

}

Step 6: In the Solution Explorer, right click Default.aspx > ‘Set as Start Page’. Run the application and click on the button. As you can observe, the page is posted to Default2.aspx and the value containing the name of the button control gets displayed in the label.

Difference between Server.Transfer and Cross Page Posting

One could argue that even the ‘Server.Transfer’ method can be used to move between pages. However there are a few differences between ‘Server.Transfer’ and ‘Cross Page’ Posting.

In ‘Server.Transfer’, the URL doesn't change whereas in cross page posting, the form is submitted to a different page, thereby changing the url.

The Server.Transfer method is a server-based operation whereas cross-page postback is a client-based transfer.

Note: To determine whether the page was invoked from a cross-page posting or a Server.Transfer operation, the Page class exposes a property named IsCrossPagePostBack.