Technical discussions about .NET and J2SE Concepts

DBMS Concepts General Introduction

Hi Friends!!!Greetings to Everyone!!!!

I am posting this general introduction about the ADO.NET concepts in short description, based on .NET point of view.

IDE Used:
–Microsoft SQL Server 2005/2008

Introduction:
–File System Concepts — Drawback is Redundancy Problem
–DBMS Concept is evolved.

General Phenomenon:

  • Relation Schema — refers to the tables
  • Record — value of particular field
  • RecordSet — Entire row values of particular tuple
  • Attribute — refers to the column
  • Tuple — refers to the row values
  • Instance  — refers to the database object or instance

Main Properties of DBMS:
A – Atomicity   — Either all transactions or none of the transactions executed
C – Consistency —  Updates has to be done parallel
I – Isolation  — All the transactions executed as an isolated manner [Separate]
D – Durability — Once the changes has been made, it cant be undone

CRUD Operations:
C- CREATE
R – RETRIEVE [SELECT]
U – UPDATE
D – DELETE

Statements in ADO.NET:
1. DDL [Data Definition Language]
2. DML [Data Manipulation Language]
3. DCL [Data Control Language]
4. TCL [Transaction Control Language]
5. DQL [Data Query Language]

DDL Statements:

  • Statements which are affecting the physical structure of database objects.

Example:

CREATE
DROP
TRUNCATE
ALTER

DML Statements:

  • Statements which are performing manipulations of records on the database objects.

Example:

INSERT
UPDATE
DELETE

DQL Statements:

  • Statements which are retrieving the recordset values from database objects.

Example:

SELECT

DCL Statements:

  • Statements that are used to set and reset the access privileges.

Example:

GRANT

REVOKE

TCL Statements:

  • Statements that are used to perform operations based on the transactions.

Example:

COMMIT

ROLLBACK

SAVEPOINT

Constraints:

1. Key Constraint

Super Key — Combination of fields which are having ability to become primary key
Candidate Key — Minimal subset of super key is called as candidate key
Primary Key — One from candidate key is called as primary key
–which is used to uniquely identify the recordset
Alternate or Secondary Key — Remaining in the candidate keys [Candidate Key – (Minus) Primary Key]
Composite Key — [Super Key-Candidate Key]
Foreign Key — Primary key of another table

2. Not Null Constraint — will not allow null values, but will allow the duplicate values.

3. Unique Constraint — will not allow duplicate values, but will allow the null value once.

4. Check Constraint — will check the values to be in the specified range. Example: For Gender, only the possible values are Male/Female. Check Constraint will not allow any other values.

5. Default Constraint  — If the value is not supplied while inserting a column values, default value will be inserted.

Advertisements

4 responses

  1. If you people want the example query statements, please drop me the request. I will post asap. Thank You.

    May 30, 2013 at 12:02 pm

  2. vignesh

    Very good explanation and thank you so much.

    May 30, 2013 at 12:23 pm

  3. Nice Explanation Sir

    June 11, 2013 at 11:14 am

  4. Nice Post dude

    June 12, 2013 at 11:44 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s