Organization of Databases as well as Tables (Relations) Part – 4

Applying Information Reliability

If constraints are not used at the time of describing as well as forming tables or relations, the information kept in the tables or relations can turn out to be repeated information. For an instance, if an individual do not stock the information regarding every Customer with comprehensive personal details, then the information is not beneficial. In the same way, if the database castoff by the Bank stocks the customer personal details and the account details in two (2) isolated tables or relations, then the particulars of the customer may not get matched. This would consequence in contradiction as well as misunderstanding.

For that reason, it is significant to guarantee that the information kept in tables or relations is comprehensive as well as reliable. The notion of keeping reliability as well as wholeness of information is known as information reliability. Information reliability is applied to guarantee that the information in a database is precise, reliable as well as trustworthy. It is approximately categorized into the subsequent groups:

· Objects Reliability – It confirms that every single row or tuple can be exclusively recognized through a column or attribute known as Primary Key. The Primary Key column or attribute has exclusive data in every row or tuple. Moreover, this column or attribute cannot be NULL.

Think through an instance, where there may be two (2) customers with a same name ‘ My Name ’. Through applying object reliability, the two (2) customers can be recognized by means of the exclusive ID (Code Number) allocated to them. For a specimen, one customer can have the CustomerID as ‘ C#25001 ’ while the other customer can have the CustomerID as ‘ C#25001 ’.

· Area Reliability – It confirms that merely a legal choice of data is kept in a column or attribute. It can be limiting the sort of information, the variety of information, as well as the setup of the information.

For an instance, the Bank has a table or relation called LoanDetails with a column or attribute titled as LoanType which stocks the type of loans that the bank provide. The type of loans that the bank provide at the current time are ‘ Education Loan ’ , ‘ House Loan ’ , ‘ Car Loan ’, ‘ Personal Loan ’ only . By means of applying area reliability, an individual can guarantee that simply legal data as per the list stated above are put in the LoanType column or attribute of the LoanDetails table or relation.

· Referential Reliability – It confirms that the data of the Foreign Key is matched with the data of the equivalent Primary Key.

For an instance, if a customer applies for a loan and the loan gets sanctioned by the loan manager, then the details of the loan in the LoanDetails table or relation along with the LoadID must be present in the LoanDetails table or relation. This guarantees that a loan has been applied for which the LoanDetails are present and accessible.

· End – User – Demarcated Reliability – It mentions a group of guidelines stated through an end – user, which does not fit in to the object, area plus referential reliability groups.

At the time of forming any tables or relations, the SQL Server permits an individual to uphold the reliability by means of the following:

· Applying Constrictions

· Applying Guidelines

· Using End – User – Demarcated Types

Applying Constrictions

Think through an instance where an end – user put an identical data in the CustomerID column or attribute of the Customer . Details table or relation. As a consequence this means that both the customer is having the similar CustomerID. As result of this it will produce a flawed outcome as soon as somebody enquiries the table or relation. Begin a database designer n individual can stop this by means of applying information reliability on the table or relation through constrictions.

Constrictions describe the guidelines which should be maintained to keep the constancy as well as exactness of the information. A constriction can be formed at the time of forming a table or relation otherwise it can be implemented in future also. The minute a constriction is implemented later the table or relation is formed, it scrutinize the present information. Uncertainly, if there is slightly any defilement is found then the constriction gets overruled.

A constriction can be formed by means of the subsequent commands::

· CREATE TABLE command

· ALTER TABLE command

A constriction can be demarcated on a column or attribute at the time of forming a table or relation. It can be formed by means of the CREATE TABLE command. The code of implementing a constriction while forming the table or relation follows:

CREATE TABLE My_Table_Name

( My_Column_Name CONSTRAINT My_Constraint_Name My_Constraint_Type

[ , CONSTRAINT My_Constraint_Name My_Constraint_Type ] )

here,

· My_Table_Name – It stipulates the fresh table or relation title that an individual wants to provide. The table or relation title should be under 128 characters.

· My_Column_Name – It is the label of the column or attribute on which the constriction is to be demarcated.

· My_Constraint_Name – It is the label of the constriction that an individual is forming as well as this constriction should follow the guidelines intended for the identifier.

· My_Constraint_Type – It is the category of the constriction that is to implemented.

The constriction can be distributed among the subsequent sorts:

· Primary Key constriction

· Unique constriction

· Foreign Key constriction

· Check constriction

· Default constriction

In the upcoming part we will be discussing about what a Primary Key constriction is, what is unique constriction and what is the Foreign Key constriction.