Showing posts with label DBMS. Show all posts
Showing posts with label DBMS. Show all posts

Friday, 30 August 2013

STRUCTURED QUERY LANGUAGE | DBMS | DQL,DDL,DML,DCL

SQL, which is an abbreviation for Structured Query Language, is a language used to request data from a database, to add, update, or remove data within a database, or to manipulate the metadata of the database.
SQL is generally pronounced as the three letters in the name, e.g. ess-cue-ell, or in some people's usage, as the word sequel.
SQL is a declarative language in which the expected result or operation is given without the specific details about how to accomplish the task. The steps required to execute SQL statements are handled transparently by the SQL database. Sometimes SQL is characterized as non-procedural because procedural languages generally require the details of the operations to be specified, such as opening and closing tables, loading and searching indexes, or flushing buffers and writing data to file systems. Therefore, SQL is considered to be designed at a higher conceptual level of operation than procedural languages because the lower level logical and physical operations aren't specified and are determined by the SQL engine or server process that executes it.
Instructions are given in the form of statements, consisting of a specific SQL statement and additional parameters and operands that apply to that statement. SQL statements and their modifiers are based upon official SQL standards and certain extensions to that each database provider implements. Commonly used statements are grouped into the following categories:

Data Definition Language (DDL)
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
CREATE :
Used to create a new table, a view of a table, or other object in database.
Example : create table test (id int null, name varchar(20));

Constraints:
Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.
Domain integrity constraint :Domain integrity Constraints are the rules enforced on a particular row of a table.
Not Null Constraint : Ensures that a column cannot have NULL value.
Default Constraint : Provides a default value for a column when none is specified.
Check Constraint : The CHECK constraint ensures that all values in a column satisfy certain conditions.
Entity integrity constraint :Constraints are the rules enforced on data columns on table

  • Unique Constraint : Ensures that all values in a column are different. 
  • Primary Key : Uniquely identified each rows/records in a database table 
Referential integrity constraint : It relates two or more tables
Foreign Key: Uniquely identified a rows/records in any another database table.

ALTER :

ALTER - Used to modify an existing database object, such as a table.
Example : Alter table test add d_id int;

DROP :

DROP - Used to delete an entire table, a view of a table or other object in the database.the structure remains 
Example : Drop table test;

TRUNCATE :

TRUNCATE - Used to delete an entire table, a view of a table or other object in the database with structure 
Example : truncate table test;

Data Manipulation Language (DML)

  • INSERT
  • UPDATE
  • DELETE
INSERT :
INSERT - Used to create a record. 
Example :
  • To insert values for every columns 
Insert into test values(1,’cse’,1)
  • To insert values for selected columns 
Insert into test(id,name) values(2,’it’); 

UPDATE :
UPDATE - Used to change certain records.
Example :
  • To Update with particular condition 
update test set id = 3 where id =2;
  • To update every rows of the table without condition 
update test set d_id = d_id * 1 

DELETE :
DELETE - Used to delete certain records
Example :
  • To delete a particular row 
Delete from test where d_id =1;
  • To delete every data of a table 
Delete from test

Data Control Language (DCL)


GRANT - Used to give a privilege to someone. 

REVOKE - Used to take back privileges granted to someone.


Data Query Language (DQL)

SELECT - Used to retrieve certain records from one or more tables.

Monday, 5 August 2013

Client/Server database

The client-server model consists of three parts,they are as follows,
  1. Client
  2. Server
  3. Network
  1. Client : The client is the machine running the front-end applications. The client has no direct data access responsibilities. It simply requests processes from the server and displays data managed by the server.
  2. Server : The server is the machine that runs the DBMS software and handles the functions required for concurrent, shared data access. The server receives and processes SQL and other query statements originating from client applications.
  3. Network : The network enables remote data access through client-server and server-to-server communication.
Advantages:
  • It enables wider access to existing databases
  • Increased performance
  • Hardware costs may be reduces
  • Communication costs are reduces
  • Increased consistency

Monday, 1 July 2013

DEFINE TRIGGERS | WHAT IS MEANT BY TRIGGER IN DBMS

Triggers:

A Trigger defines an action that the database should take when some database related event occurs.The execution of triggers is transparent to the users.A trigger may cause another trigger to be invoked.A mutating trigger is a trigger that triggers itself.

Types Of Triggers:

The triggers types are defined by the type of triggering transactions and by the level at which the trigger is executed.
  • Row-level triggers
  • Statement-level triggers
  • Before and after triggers
Row-level triggers:
Row-level triggers,trigger once for each row in a transaction.This trigger can be created by using the for each row clause trigger comment.
Statement-level triggers:
Statement-level triggers execute once in a transaction.It can be created by using create trigger comment.It is one of the default type of triggers.
Before and after triggers:
Since the events that executes triggers are database transactions,triggers can be executed immediatly before or after INSERT,UPDATE and DELETE comments.

Purpose of Triggers:
  • To generate data automatically
  • To enforce complex integrity constrains
  • To customize complex security authorizations
  • To maintain replicate tables
  • To audit data modifications