Relational Databases

Characteristics of RDBMS

Table : 

Data is stored in a table and consists of rows and columns. Each row represents the single instance of an entity. Columns defines the properties of an entity. Each column is defined by a datatype. All the rows have same number of columns.

Some columns are used to establish relationship between tables.

Primary key uniquely identifies each row. Only one primary key is allowed to use in a table. The primary key does not accept the any duplicate and NULL values. 

Foreign key is used reference or link to the primary key of another table.





Normalization :
Splitting an entity into more than one table refers to normalization which helps in reducing storage, avoids data duplication, improves data quality.


Use cases of RDBMS :
  1.  Well suited for OLTP applications.
  2. Examples of OLTP applications - Banking, online retail applications, flight reservation systems, most online purchasing applications
  3. It is not suitable to store media, imgaes, audio files etc for which blob storage can be used, Social networking sites - Graph database can be used.
Benefits of RDBMS :

Follows ACID properties -

Atomicity
Atomicity refers to the fact that a transaction succeeds or it fails. It is an all-or-nothing operation. Despite being composed of multiple steps, those steps are treated as a single operation or a unit. 
For example, in an application that transfers funds from one account to another, the atomicity property ensures that, if a debit is made successfully from one account, the corresponding credit is made to the other account.
       

Consistency

Consistency refers to the characteristic that requires data updated via transactions to respect the other constraints or rules within the database systems to keep data in a consistent state. For example, you set in place SQL triggers or integrity constraints that check personal balances and prevent an account from withdrawing more money than they have - your app offers no credit. So if I started with 50 dollars, I will not be allowed to send 100 dollars to someone else.

Isolation

The intermediate state of a transaction is invisible to other transactions. As a result, transactions that run concurrently appear to be serialized. Two parallel transactions are in reality isolated and seem to be performed sequentially.
For example, in an application that transfers funds from one account to another, the isolation property ensures that another transaction sees the transferred funds in one account or the other, but not in both, nor in neither.

Durability

After a transaction successfully completes, changes to data persist and are not undone, even in the event of a system failure. Transactions and database modifications are not kept in volatile memory but are saved to permanent storage, such as disks. This prevents data loss during system failure, such as a power outage.
For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.


Index

An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer.  Index can be created, dropped, renamed, altered.

Syntax:
 CREATE INDEX index
 ON TABLE column;

where the index is the name given to that index and TABLE is the name of the table on which that index is created and column is the name of that column for which it is applied. 

For multiple columns:

Syntax:

 CREATE INDEX index
 ON TABLE (column1, column2,.....);
Unique Indexes:
Unique indexes are used for the maintenance of the integrity of the data present in the table as well as for the fast performance, it does not allow multiple values to enter into the table. 
Syntax:
CREATE UNIQUE INDEX index
 ON TABLE column;

When should indexes be created:

  • A column contains a wide range of values.
  • A column does not contain a large number of null values.
  • One or more columns are frequently used together in a where clause or a join condition.

When should indexes be avoided:

  • The table is small
  • The columns are not often used as a condition in the query
  • The column is updated frequently

A table or view can contain the following types of indexes:

  • Clustered

  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. 
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.                                                                                   

  • Nonclustered

  • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.








Indexes are automatically created when PRIMARY KEY and UNIQUE constraints are defined on table columns. For example, when you create a table with a UNIQUE constraint, Database Engine automatically creates a nonclustered index. If you configure a PRIMARY KEY, Database Engine automatically creates a clustered index, unless a clustered index already exists.





View





Comments

Post a Comment