Tag Archives: DDL

INTRODUCTION TO DBMS & RDBMS

INTRODUCTION TO DBMS & RDBMS

A DBMS (Database Management System) is a software program used to manage a database. These programs enable users to access and modify database.It basically control the storage management and fetching of data from database.
 
A DBMS includes four main components, which are:
-Modeling Language,
-Data Structures,
-DB Query Language and Report Writer,
-Transaction Mechanism.
 
RDBMS
RDBMS is relational database management system. Basically, we can say that RDBMS is an extension of DBMS systems.

Difference between DBMS and RDBMS

1. RDBMS (Relational database management system) applications store data in a tabular form while DBMS applications store data as files.

2. Normalization is added to RDBMS whereas not present in DBMS system.

3. RDBMS maintained relationship between tables ( Primary Key or Foreign Key contraints) but in DBMS is not maintained.

4. RDBMS Support ACID property of Transaction (Atomocity, Consistency, Isolation and Durability) but DBMS not.

5. RDBMS supports distributed database but DBMS not.

6. Example of RDBMS are mysql, postgre, sql server, oracle etc and DBMS are file systems, xml,Dbase, Microsoft Access, LibreOffice Base, FoxPro etc,

 
TYPES OF LANGUAGE:
 
DDL – DATA DEFINITION LANGUAGE
The SQL sentences that are used to create these objects are called DDL’s or Data Definition Language. The SQL provides various commands for defining relation schemas, deleting relations, creating indexes and modify relation schemas. DDL is part of SQL which helps a user in defining the data structures into the database. Following are the various DDL commands are
• Alter table & Create table & drop table
• Create index & drop index
• Create view & drop view
 
DML – DATA MANIPULATION LANGUAGE
The SQL sentences used to manipulate data within these objects are called DML’s or Data Manipulation Language. It is language that enables users to access or manipulate data as organized by appropriate data model. By data manipulation we have:
• Retrieval of information stored in database.
• Insertion of new information into database.
• Deletion of information from database.
• Modification of data stored in database.
 
FOLLOWING ARE DML COMMANDS ARE:
• Select
• Update
• Delete
• Insert
 
DCL – DATA CONTROL LANGUAGE
The SQL sentences, which are used to control the behavior of these objects, are called DCL’s or Data Control Language. It is language used to control data and access to the database. Following are some DCL commands are
• Commit
• Rollback
• Save point
• Set transaction
 
A Data Control Language (DCL) is a computer language and a subset of SQL, used to control access to data in a database.
Examples of DCL commands include:
• GRANT to allow specified users to perform specified tasks.
• REVOKE to cancel previously granted or denied permissions.
 
The following privileges can be GRANTED TO or REVOKED FROM a user or role:
• CONNECT
• SELECT
• INSERT
• UPDATE
• DELETE
• EXECUTE
• USAGE
 

Advertisements

How to Create, Alter AND Drop a table

How to Create, Alter AND Drop a table

CREATE TABLE: A table is basic unit of storage. It is composed of rows and columns. To create a table we will name the table and the columns of the table. We follow the rules to name tables and columns:
• It must begin with a letter and can be up to 30 characters long.
• It must not be duplicate and not any reserved word.

SYNTAX to create a table is:

CREATE TABLE tablename (column_name1 datatype (size), column_name2 datatype (size) …);

Example:
CREATE TABLE studen(rollno number (4), name varchar2 (15));

ALTER TABLE: After creating a table one may have need to change the table either by add new columns or by modify existing columns. One can do so by using alter table command.

SYNTAX to add a column is

ALTER TABLE tablename ADD(col1 varchar(10),col2 number(10));

SYNTAX to modify a column is:

ALTER TABLE tablename MODIFY(col1 datatype,col2 datatype);

DROP TABLE: To remove the definition of oracle table, the drop table statement is used.
Drop command is used to remove the structure of table so no rollback possible in this case.

SYNTAX to drop table is

DROP TABLE tablename