How to Apply Constraints on Columns in SQL Programming?

In SQL Programming, consider an example where a user entered a duplicate value in the EmployeeID column of the Employee table. This would mean that two employees have same employee ID. This would further results in erroneous results when anybody queries the table. As a database developer, you can prevent this by enforcing data integrity on the table by using constraints.

Constraints define rules that must be followed to maintain consistency and correctness of data. A constraint can either be created while creating a table or can be added later. When a constraint is added after the table is created, it checks the existing data. If there is any violation, then the constraint is rejected.

A constraint can be created by using by using either of the following statements:

CREATE TABLE statement
ALTER TABLE statement
A constraint can be defined on a column while creating a table. It can be created with the CREATE TABLE statement. The syntax of adding a constraint at the time of table creation is:
CREATE TABLE table_name
(
Column_name CONSTRAINT constraint_name constraint_type [, CONSTRAINT
)
Where

• Column_name is the name of the column on which the constraint is to be defined.
• Constraint_name is the name of the constraint to be created and must follow the rules for the identifier.
• Constraint_type is the type of constraint to be added.

Constraints can be divided into the following types:

• Primary key constraint
• Unique constraint
• Foreign key constraint
• Check constraint
• Default constrain

Create and Manage Tables

Types of Data Integrity

Difference between structure and class

Structure	Class
Structure use public modifier by default	Class use private modifier by default
In concept of inheritance, derived structure is public by default when access modifier doesn’t use in base class.	In case of class concept, derived class is private by default when access modifier doesn’t use in base class.
Use ‘struct’ keyword in language c, c# and ‘structure’ keyword use in VB for implementing structure block	Use ‘class’ keyword for implementing class block.
Structure is a collection of bits.	Class is a collection of members of society.
Structure is based on procedural approach	Class is based on object-oriented approach.
It doesn’t support Data hiding concepts. Means you can’t implement security in it.	You can implement security in it Using data hiding.
It is a value type	It is reference type
Size of empty structure is 0 byte	Size of empty class is 1 byte
In case of template, we can’t use it.	Always we use class in case of structure.
template<struct T> // ERROR, struct not allowed here	template<class T> // OK
Structure may represent real world object.	Class is highly suitable to represent real world objects rather than "Structures"
Structure value will be stored in stack memory	Class members are stored in heap memory.
Structure used for light weight object.	Class is used for complex data type

What are the types of Data Integrity in SQL Programming?

If checks are not applied while defining and creating tables, the data stored in the tables can become redundant. For example, if you do not store the data about all the employees with complete address details, then the data would not be useful.

Similarly, if a database used by the Human Resource department stores employee contact details in two separate tables, the details of the employees might not match. This would results inconsistency and confusion.
Therefore, it is important to ensure that the data stored in tables is complete and consistent. The concept of maintaining consistency and completeness of data is called data integrity. Data integrity is enforced to ensure that the data in a database is accurate, consistent, and reliable. It is broadly classified into the following categories:

• Entity integrity: ensures that each row can be uniquely identified by an attribute called the primary key column contains unique value in all the rows. In addition, this column cannot be NULL. Consider a situation where there might be two candidates for an interview with the same name ‘Jack’. By enforcing entity integrity, the two candidates can be identified by using the unique code assigned to them. For example, one candidate can have the code 001 and the other candidate can be 002.
• Domain integrity: Ensures that only a valid range of values is stored in a column. It can be enforced by restricting the type of data, the range of values, and the format of the data. For example, you have a table called Branchoffice with a column called offices are located in ‘Berkeley’, ‘Boston’, ‘Chicago’, ‘Dallas’, ‘Munchen’, ‘New Jersey’, ‘New York’, ‘Paris’, and ‘Washington’. By enforcing domain integrity, you can ensure that only valid values (as per the list specified) are entered in the City column of the BranchOffice TABLE.
• Referential integrity: Ensures that the values of the foreign key match the value of the corresponding primary key. For example, if a bicycle has been ordered and an entry is to be made in the OrderDetail table, then that bicycle code should exist in the product table. This ensures that an order is placed only for the bicycle that is available.
• User-defined integrity: Refers to a set of rules specified by a user, which do not belong to the entity, domain, and referential integrity categories.

When creating tables, the SQL Server allows you to maintain integrity by:

• Applying constraints
• Applying rules
• Using user-defined types

Create and Manage tables

Difference between procedure and function

Procedure	Function
A procedure is a block of code that can't returning a value. You can say it’s a types of function when that doesn’t return the value. Example. Module ABC     Sub Main()                SProcedure()             End Sub         Sub SProcedure()         Console.WriteLine("dotprogramming.blogspot.com")     End Sub     End Module	A function is a block of code and after running its code returns a value to the calling code. Example Module ABC     Dim a As Integer = 10     Dim b As Integer = 20         Dim result As Integer     Sub Main()         result = Addition(a, b)         Console.WriteLine(Addition(a, b))             End Sub         Function Addition(ByVal c As Integer, _                        ByVal d As Integer) As Integer         Return c+d     End Function     End Module
Procedure are not categorize or you can say it doesn’t divide in parts.	Functions are divide in two parts 1.       Built-in function 2.       User-defined function
Use “Sub” keyword for creating procedure	Use “function” keyword for creating function.
Procedure will execute when action is called	But in case of function you can say it’s a loop.

HTML server control classes in ASP.NET

ASP.NET uses the HTML server control classes to work with the HTML controls using a programming language. These classes include the HtmlTextArea class for the HTML <textarea> controls, the HtmlInputButton class for HTML buttons created with the <input type="button"> elements, the HtmlInputText class for HTML text fields created with the <input type="text"> elements, and the rest. while working with HTML server control, you are actually working with their supporting classes. You can even work with these classes directly in code.
Most of the attributes of HTML server controls can be specified through the properties window of IDE. These properties are written in lowercase. As soon as you change or specify the value of these properties, the corresponding changes are made to the HTML element in the .aspx page. For example, you can set the caption of an HTML server button at design-time using the Value property, which corresponds to the HTML <Input> element's value attribute. However, at runtime, this attribute is supported by the HtmlInputButton class's Value property. When we work with an object of the HtmlInputButton class on the server, the casing (upper or lower case) of property name in server code adheres to the coding language standard.
It is important to know that the HTML server control classes exactly correspond to the actual HTML controls that you see in the HTML tab in ToolBox. For example, the HtmlInputButton class is used not just for buttons but also for reset buttons. All these buttons are created using an HTML <input> element, and they differ only through the attribute named type; for example, a standard button uses <input type="button">, a reset button uses <input type="reset">, and a submit button uses <input type="submit">.

Note : As per HTML rules, the HTML controls must be in the HTML form(with .htm or .html extension) while they are sent back to the server. However, when you create a web form using the ASP.NET IDE, you need not require to create the HTML form explicitly; ASP.NET does that automatically as soon as you add a new web form to the website.

HTML classes are based on the HtmlControl class. Some server control class are listed in

HtmlAnchor : Creates an <a> element for navigation.

HtmlButton : Creates an HTML button using the <button> element.

HtmlForm : Allows access to the <form> HTML element which is a container for other controls on a web page.

HtmlgenericControl : Defines the methods, properties and events for all HTML server controls not represented by .NET Framework.

HtmlImage : Maps the <img> element for displaying image on a web page.

HtmlInputButton : Creates an Html button using the <input> element.

HtmlInputCheckBox : Creates an Html checkbox.

HtmlInputControl : Defines the methods, properties and events for HTML input controls.

HtmlInputFile : Creates an HTML file upload control.

HtmlInputHidden : Creates an HTML input hidden control.

HtmlInputImage : Creates an HTML button that display image.

HtmlInputRadioButton : Creates an HTML radio button.

HtmlInputText : Creates an HTML text field. You can also use this control to create password fields.

HtmlSelect : Maps <select> element and allow to create list control.

HtmlTable : Creates an HTML table.

HtmlTableCell : Creates an HTML cell in a Table.

Creating grouped elements and attributes in XML

The purpose of an XML schema is to define the building blocks of an XML document. An XML schema defines the following in an XML document:

• Elements
• Attributes
• Child elements 
• Order of the child elements
• Number of child elements
• State of the element, whether it is empty or includes text
• Data types for the elements and attributes 
• Default and fixed values for the elements and attributes

An XML schema enables you to combine related elements and attributes into groups. This Feature of creating grouped elements and attributes  enables you to perform the following tasks:

• Create a reusable group of elements and attributes:  A reusable group can be used to specify the content  model for a complex type of element or attribute. This eliminates the task of declaring the elements that have already been declared in some other context. For example, you can create a group of the FIRSTNAME and LASTNAME elements and reuse the group for customers, suppliers, and employees.
• Select a single element from a group: Assume that you want to use one element or Attribute, such as the phone number of an employee, from a group. To do so, you can create a group of such elements and allow one of them to be used in the XML document.
• Specify the sequence of elements: You can create a group of  elements and specify the sequence in which each element in the group should appear in the XML document.
• XSD provides the following  elements to group user-defined elements and attributes:

1. sequence
2. group
3. choice
4. all
5. attributeGroup

The sequence Element

The sequence element ensures that the elements declared within the opening and closing tags  of this element appear in a specific order. For example, to ensure that the employee first name, last name, designation, and department appear in a specific order in an XML
document, you can use the following statements in an XML schema:

<xsd:schema  xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
     <xsd:element name =”EMPLOYEE” type=”emptype”/>
     <xsd:complexType name =”empType name=”emptype”>
             <xsd:sequence>
                    <xsd:element name=”FIRSTNAME” type=”xsd:string”/>
                    <xsd:element name=”LASTNAME” type=”xsd:string”/>
                    <xsd:element name=”DESIGN”  type=”xsd:string”/>
                    <xsd:element name=”DEPARTMENT” type=”xsd:string”/>
             </xsd:sequence>
        </xsd:complexType>
</xsd:schema>

In preceding schema, the EMPLOYEE element is associated with the complex type emptype element. This complex type element contains the declarations for the FIRSTNAME, LASTNAME, DESIG, and DEPARTMENT elements. All these declarations are contained within the sequence element. This ensure that the elements appear in the same sequence in which they are declared.

Therefore, if you validate the following XML document against the preceding schema, it will result in an error because the DEPARTMENT element should come after the DESIG
element according to the schema.

<?xml version=”1.0”?>
<EMPLOYEE>
         <FIRSTNAME> James </FIRSTNAME>
         <LASTNAME > Williams <LASTNAME>
         <DEPARTMENT> MKTG </DEPARTMENT>
         <DESIG> MKTG EXEC </DESIG>
</EMPLOYEE>

The group Element

A set of elements can be grouped together by a common name in an XML schema, and incorporated into a complex data type. Grouping of elements is beneficial when you want a set of related elements to be referred using a common name.
The syntax for declaring a group element is as follows:

<group maxOccurs=”nonNegativeInteger | unbounded”
minOccurs=”nonNegativeInteger” name=”NCName” ref=”QName “> </group>

The following table describes the attributes of the group element.

Attribute	Description
maxOccurs	Used to specify the maximum of times a group can occur in the XML document. The value of the maxOccurs attribute must be an integer greater than or equal to zero. If you do not want to set a limit on the maximum number, you can specify the value of this attribute as unbounded.
minOccurs	Used to specify the minimum number of times a group can occur in the XMLdocument. The value of the minOccurs attribute must be an integer greater than or equal to zero to specify that the group is optional.
name	Used to assign a name for the group element. The name assigned to the group must not contain a colon.
ref	Used to refer to a group in a complex type element.

Consider the following example:

<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>

       <xsd:group name=”empname”>

              <xsd:sequence>

                     <xsd:element name=”FIRSTNAME” type=”xsd:string”/>

                     <xsd:element name=”LASTNAME” type=”xsd:string/>

                </xsd:sequence>

          </xsd:group>

<xsd:element name=”EMPLOYEE” type=”emptype”/>

<xsd:ComplexType name=”emptype”>

        <xsd:sequence>

               <xsd:group ref=”empname”/>

               <xsd:element name=”ADDRESS” type=”xsd:string”/>

         </xsd:sequence>

</xsd:complexType>

</xsd:schema>

In the preceding example, the FIRSTNAME and LASTNAME elements are grouped together by a common name called empname by using the group element. The order in which these elements must appear in the XML document is specified using the sequence element. You can refer to the empname group while creating a complex type by using the following Statement:

<xsd:group ref=”empname”/>

After declaring the elements are attributes in the preceding schema, you can create an XML document that conforms to the schema by using the following code snippet:

<?xml version=”1.0?>

<EMPLOYEE>

          <FIRSTNAME> Sam </FIRSTNAME>

<LASTNAME> Peterson </LASTNAME>

<ADDRESS> 10, LIONS STREET, BOSTON </ADDRESS>

</EMPLOYEE>

The choice Element      

XSD enables you to select a a single option from multiple options by using the choice element. The choice element allows only one of the elements contained in the group to be present within the parent element.

The syntax for declaring a choice element is as follows:

<choice id=”ID” maxOccurs=”nonNegativeInteger | unbounded”

minOccurs=”nonNegativeInteger”> </choice>

In the preceding syntax, id, maxOccurs, and minOccurs are attributes of the choice element. These attributes are similar to the attributes of the group element mentioned earlier.

Consider the following example. You may want to store either the office address or the residential  address of a customer in an XML document. You can implement this option by using the choice element, as shown in the following code:

<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>

        <xsd:element name=”CUSTOMER” type=”custtype”/>

        <xsd:complexType name=”custtype”>

               <xsd:sequence>

                      <xsd:group ref=”custname”/>

                      <xsd:element name=”ADDRESS” type=”addtype”/>

               </xsd:sequence>

                </xsd:complexType>

                <xsd:complexType name=”addtype”>

                       <xsd:choice>

                              <xsd:element name=”RESIDENCE” type=”xsd:string”/>

                              <xsd:element name=”OFFICE” type=”xsd:string”/>

                        </xsd:choice>

                  </xsd:complexType>

                  <xsd:group name=”custname”>

                         <xsd:sequence>

                                <xsd:element name=”FIRSTNAME” type=”xsd:string”/>

                                <xsd:element name=”LASTNAME” type=”xsd:string/>

                          </xsd:sequence>

                     </xsd:group>

               </xsd:schema> 

In the preceding code, the CUSTOMER element is declared with a reference to the complex

Type, custtype. This type further refers to the custname group, which contains the Declarations for the FIRSTNAME and LASTNAME elements. The custtype complex type also contains the declaration for the ADDRESS element, which refers to the complex type, addtype. This complex type contains the declerations for the RESIDENCE and OFFICE elements. These declerations appear within the choice element. Therefore, only one of these elements can be used at a time within the ADDRESS element.

The following XML doument conforms to the preceding schema:

<?xml version=”1.0”?>

<CUSTOMER>

        <FIRSTNAME> Sam </FIRSTNAME>

        <LASTNAME> Peterson </LASTNAME>

         <ADDRESS>

                  <RESIDENCE>10, LIONS STREET, BOSTON </RESIDENCE>

          </ADDRESS>

         </CUSTOMER>

If you include the OFFICE element within the preceding code, it will result in an error During document validation.

The all Element

In contrast to the sequence element, the all element enables you to use the child elements in any order.

The syntax for using the all element is as follows:

        <all maxOccurs=”posiveInteger” minOccurs=”0|1”> </all>

In the preceding syntax, the maxOccurs and minOccurs attributes have the same meaning as the maxOccurs and minOccurs element elements of the group element.

The following code contains the declaration for the EMPLOYEE element and its child elements. The code is the same as the one given for the group element. However, the sequence element has been replaced by the all element here.

<xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>

<xsd:element name=”EMPLOYEE” type=”emptype” />

       <xsd:complexType name=”emptype”>

              <xsd:all>

                      <xsd:element name=”FIRSTNAME” type=”xsd:string”/>

                      <xsd:element name=”LASTNAME” type=”xsd:string”/>

                      <xsd:element name=”DESIG” type=”xsd:string/>

                      <xsd:element name=”DEPARTMENT” type=”xsd:string/>

                </xsd:all>

          </xsd:complexType>

    </xsd:schema>

The attributeGroup Element

XSD enables you to group attributes that can be reused with different elements. You can group attributes by using the attributeGroup element:

The syntax of the attributeGroup element is as follows:

        <attributeGroup>

              attribute1

              attribute2

                  :

          </attributeGroup>

In the syntax, the attribute1 and attribute2 statements declare the various attributes that are to be grouped. The following example illustrates the use of the attributeGroup element. This code is used to declare the EMPLOYEE element, which contains the FIRSTNAME and LASTNAME elements. The EMPLOYEE element also contains the DEPARTMENT and DESIGNATION attributes.

        

        <xsd:schema xmlns:xsd=”http://www.w3.org/2001/XMLSchema”> 

               <xsd:element name=”EMPLOYEE” type=”emptype”/>

                <xsd:complexType name=”emptype”>

                       <xsd:group ref=”empname”/>

                       <xsd:attributeGroup ref=”depdesig/>

                </xsd:ComplexType>

                <xsd:group name=”empname”>

                       <xsd:sequence>

                              <xsd:element name=”FIRSTNAME” type=”xsd:string”/>

                              <xsd:element name=”LASTNAME” type=”xsd:string”/>

                       </xsd:sequence>

               </xsd:group>

               <xsd:attributeGroup name=”depdesig”>

                      <xsd:attribute name=”DEPARTMENT” type=”xsd:string”/>

                      <xsd:attribute name=”DESIGNATION” type=”xsd:string”/>

                 </xsd:attributeGroup>

        </xsd:schema>

In the preceding example, the emptype complex type has a reference to the depdesig attribute group, which contains the declarations for the DEPARTMENT and DESIGNATION attributes.

You can use the elements and attributes declared in the preceding schema as follows:

        <?xml version=”1.0”?>

         <EMPLOYEE DEPARTMENT=”Mktg” DESIGNATION=”Mgr”>

                  <FIRSTNAME> James </FIRSTNAME>

                   <LASTNAME> Wallace </LASTNAME>

         </EMPLOYEE>

An attributeGroup element is typically used in cases where the same set of attributes is Used in multiple elements within the schema.

How to Create or Manage Tables using Query in SQL Programming

As a database developer, you need to create tables to store data. While creating tables in a relational database. You need to specify certain rules and constraints for columns that specify the kind of data to be stored. In addition, you need to specify the relationships between various tables.

If the table that you are creating needs to store a large volume of data, you can create partitioned table. This helps in improving the performance of the queries.

In addition to creating tables, you are responsible for managing tables. The management of tables involves modifying tables to add columns or to change the rules imposed on the table. It also involves deleting tables, when not required.

Creating a Table

In SQL Server, programmer can create a table by using the CREATE TABLE statement. The syntax of the CREATE TABLE statement is:

CREATE TABLE
 [database_name . [ scheme_name ] .] table_name
  ({ <column_definition> | <computed_column_definition> }
  [ <table_constraint> ] [ ,…n])
 [ ON { partition_scheme_name (partition_column_name ) |
Filegroup
 | “default” } ]
 [ {TEXTIMAGE_ON { filegroup | “default” } ]
[ ; ]
Where

• database_name specifies the name of the database where the table is created if you do not specify a database name, the table is created in the current database.
• Schema_name specifies the schema name where the new table belongs. Schema is a logical group of database objects in a database. Schemas help in improving manageability of objects in a database.
• table_name specifies the new table name. The table name can be a maximum of 128 characters.
• Column_name specifies the name of the column and must be unique in the table. It can be a maximum of 128 characters.
• Computed_column_definition specifies the expression, which produces the value of the computed column. A computed column does not exist physically in the memory but it is used to generate a computed value. For example, if you have the order quantity stored in one column and the unit price in another column, you can use compute_column_definition to find the total price of the products. The following SQL query displays the use of computed_column_definition: totalPrice AS OrderQty * UnitPrice
• table_constraint is an optional keyword that specifies the PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, or CHECK constraint.
• Partition_scheme_name specifies the partition scheme name that defines the file groups on which the partition of a table is mapped. Ensure that the partition scheme exist within the database.
• partition_column_name specifies the column name on which a partitioned table will be partitioned.
• TEXTIME_ON { filegroup | “default”} are keywords that specify that the text, ntext, image, xml, varchar (max), nvarcar (max), varbinary (max), and CLR user-defined type columns are stored on the specified filegroup. If there are no large value columns in the table, TEXTIMAGE_ON is not allowed.

Consider the following example. The management of Adventure Works, Inc. needs to maintain the leave details of the employees. For this, you need to create a table, EmployeeLeave, in the HumanResources schema, with the following details.

EmployeeID int  NOT NULL
LeaveStartDate date  NOT NULL
LeaveEndDate date  NOT NULL
LeaveReason Varchar(100) NOT NULL
LeaveType char(2)  NOT NULL

You can use the following statement to create the table:
(
CREATE TABLE HumanResources.EmployeeLeave
LeaveStartDate datetime NOT NULL,
LeaveEndDate datetime NOT NULL,
LeaveReson varchar (100),
LeaveType char(2) NOT NULL
)

Guidelines to Create Tables

When creating tables, programmer need to consider some guidelines like column names within a table must be unique, but the same column name can be used in different tables within a database. The table name can be of maximum 128 characters.

Apply constraints on columns