Introduction to Structured Query Language (SQL) – Part – 7

Foreign Keys Constrictions

A Foreign Key (FK) constriction stipulates that the data in a column or attribute otherwise in a group of columns or attributes should match the data appearing in a number of row or tuples of alternative table or relation. It can be said that this preserves the referential reliability among two (2) associated tables or relation. For instances think that an individual have the saving account table or relation which we already used numerous time already:

CREATE TABLE SavingAc ( Ac_ID INTEGER PRIMARY KEY , Name CHAR , Balance INT ) ;

Let’s assume that an individual have a table or relation for keeping transaction IDs of these savings accounts. An individual wants to confirm that the transaction table or relation only has transactions of savings account which really exist. Therefore, an individual can describe a Foreign Key (FK) constriction in the transaction table or relation which references the SavingAc table or relation:

CREATE TABLE SavingTran ( Tran_ID INTEGER PRIMARY KEY , Ac_ID INTEGER REFERENCES SavingAc ( Ac_ID ) , [ Date ] DATETIME ) ;

At present it is unmanageable to generate transactions with account number which are not present in the SavingAc table or relation. It can be said in this type of circumstances that the SavingTran table or relation is the referencing table or relation as well as the SavingAc table or relation is the referenced table or relation. In the same way, there are referencing as well as referenced columns or attributes.

Indexes in Structured Query Language

The relational databases like Structured Query Language (SQL) Server make usage of indexes to discover the information speedily as soon as a query is executed. Crafting as well as eliminating indexes from a database schema will hardly affect the outcomes of an application’s code; indexes are executed behind the scenes in assistance of the database engine. On the other hand, forming the appropriate index can extremely boost up the performance of an application. The Structured Query Language (SQL) Server engine usages an index in much the similar method as a reader use a book index. For an instance, one (1) method to find every reference to INSERT command in a Structured Query Language (SQL) book can be starting with first page and scanning every single page of the book thereafter. An individual can mark every single time he or she finds the word INSERT until he or she reach the end of the book. This method is pretty time taking as well as difficult. In place of this, an individual can make use of the index at the back of the book to get the page numbers for every single existence of the INSERT command. This method creates the similar results as above, however with incredible speed resulting in saving a lot of time.

At the time when Structured Query Language (SQL) Server has no index to make use of for searching, the outcome is identical to the reader who gazes through every single page of a book to find a particular word: the Structured Query Language (SQL) engine wants to visit every single row or tuple in a table or relation. In database language an individual can term this activity as a table or relation scanning, or just a scan. The table or relation scan is not always a difficult, and is every now and then mandatory. However, when a table or relation starts to have thousands of rows or tuples as well as then billions of rows or tuples in addition grows beyond, scans turns out to be compatibly sluggish as well as more luxurious. Think through the subsequent query on the SavingAc table or relation of the XYZBank database. This query tries to recover account ID in a precise price range.

SELECT Ac_ID , Name , Balance FROM SavingAc WHERE ( Balance < 5000 ) ;

There is presently no index on the SavingAc table or relation to help the above mentioned query, hence the database engine do a scan as well as inspects every single data to find if Balance is less than 5000.

At the present think if an individual forms an index, same like a book index, on the information in the Balance column or attributes. Every single index entry will have a replica of the Balance data for a row or tuple, as well as a reference, just like a page number to the row or tuple where the data is originated. Structured Query Language (SQL) will categorize these index entries into ascending format. The index will permit the database to rapidly narrow down the searching process of the rows or tuples to fulfill the query, as well as avoid scanning of every single row or tuple in the table or relation.

An individual can form the similar indexes by means of the subsequent Structured Query Language (SQL). The statement stipulates the title of the index which is INX_Balance in this case, the table or relation name which is SavingAc in this case, as well as the column or attribute which need to be indexed which is Balance in this case.

CREATE INDEX [ INX_Balance ] ON SavingAc ( Balance )

In What Manner It Works

The database takes the columns or attributes stated in a CREATE INDEX statement as well as categorizes the data into a superior data structure which is well-known as a B-tree. The B-tree arrangement supports quick search with a least amount of disk reads, permitting the database engine to rapidly find the initial as well as ending points for the query which has been executed. Theoretically, an individual might think of an index as an index which we find at the back of any book, which helps us to find key words swiftly, thus it make the database capable of to find the records quickly by narrow downing the number of searches. An individual can dodged a table or relation scan to get the query results but at that moment it will be time consuming and will put stress on the amount of disk read the query need to make to get the result.

This is the last part of the article series “Introduction to Structured Query Language (SQL)”, hope readers have got some valuable information from this series.

Thanks.