Database Management System:


WHAT IS DBMS?

A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

A DBMS makes it possible for end users to create, read, update and delete data in a database. The DBMS essentially serves as an interface between the database and end users or application programs, ensuring that data is consistently organized and remains easily accessible.

The DBMS manages three important things: the data, the database engine that allows data to be accessed, locked and modified — and the database schema, which defines the database’s logical structure. These three foundational elements help provide concurrency, security,data integrity and uniform administration procedures. Typical database administration tasks supported by the DBMS include change management, performance monitoring/tuning and backup and recovery. Many database management systems are also responsible for automated rollbacks, restarts and recovery as well as the logging and auditing of activity.

Popular types of DBMSes

Popular database models and their management systems include:

Relational database management system (RDMS)  – adaptable to most use cases, but RDBMS Tier-1 products can be quite expensive.

NoSQL DBMS – well-suited for loosely defined data structures that may evolve over time.

In-memory database management system (IMDBMS) – provides faster response times and better performance.

Columnar database management system (CDBMS) – well-suited for data warehousesthat have a large number of similar data items.

Cloud-based data management system – the cloud service provider is responsible for providing and maintaining the DBMS.

Advantages of a DBMS

Using a DBMS to store and manage data comes with advantages, but also overhead. One of the biggest advantages of using a DBMS is that it lets end users and application programmers access and use the same data while managing data integrity. Data is better protected and maintained when it can be shared using a DBMS instead of creating new iterations of the same data stored in new files for every new application. The DBMS provides a central store of data that can be accessed by multiple users in a controlled manner.

WHAT IS DATABASE?

a database is basically a collection of information organized in such a way that a computer program can quickly select desired pieces of data. You can think of a database as an electronic filing system.

Traditional databases are organized by fields, records, and files. A field is a single piece of information; a record is one complete set of fields; and a file is a collection of records. For example, a telephone book is analogous to a file. It contains a list of records, each of which consists of three fields: name, address, and telephone numbe

 

DATA VS INFORMATION

Data and information are interrelated. Data usually refers to raw data, or unprocessed data. It is the basic form of data, data that hasn’t been analyzed or processed in any manner. Once the data is analyzed, it is considered as information. Information is “knowledge communicated or received concerning a particular fact or circumstance.” Information is a sequence of symbols that can be interpreted as a message. It provides knowledge or insight about a certain matter.

Data and information are interrelated. In fact, they are often mistakenly used interchangeably. Data is considered to be raw data. It represents ‘values of qualitative or quantitative variables, belonging to a set of items.’ It may be in the form of numbers, letters, or a set of characters. It is often collected via measurements. In data computing or data processing, data is represented by in a structure, such as tabular data, data tree, a data graph, etc.

Data usually refers to raw data, or unprocessed data. It is the basic form of data, data that hasn’t been analyzed or processed in any manner. Once the data is analyzed, it is considered as information.

Information is “knowledge communicated or received concerning a particular fact or circumstance.” Information is a sequence of symbols that can be interpreted as a message. It provides knowledge or insight about a certain matter. Information can be recorded as signs, or transmitted as signals.

 

Functions of DBMS:

i) Data Storage Management: It provides a mechanism for management of permanent storage of the data. The internal schema defines how the data should be stored by the storage management mechanism and the storage manager interfaces with the operating system to access the physical storage.

(ii) Data Manipulation Management: A DBMS furnishes users with the ability to retrieve, update and delete existing data in the database.

(iii) Data Definition Services: The DBMS accepts the data definitions such as external schema, the conceptual schema, the internal schema, and all the associated mappings in source form.

(iv) Data Dictionary/System Catalog Management: The DBMS provides a data dictionary or system catalog function in which descriptions of data items are stored and which is accessible to users.

(v) Database Communication Interfaces: The end-user’s requests for database access are transmitted to DBMS in the form of communication messages.

(vi) Authorization / Security Management: The DBMS protects the database against unauthorized access, either international or accidental. It furnishes mechanism to ensure that only authorized users an access the database.

{vii) Backup and Recovery Management: The DBMS provides mechanisms for backing up data periodically and recovering from different types of failures. This prevents the loss of data,

(viii) Concurrency Control Service: Since DBMSs support sharing of data among multiple users, they must provide a mechanism for managing concurrent access to the database. DBMSs ensure that the database kept in consistent state and that integrity of the data is preserved.

(ix) Transaction Management: A transaction is a series of database operations, carried out by a single user or application program, which accesses or changes the contents of the database. Therefore, a DBMS must provide a mechanism to ensure either that all the updates corresponding to a given transaction are made or that none of them is made.

(x) Database Access and Application Programming Interfaces: All DBMS provide interface to enable applications to use DBMS services. They provide data access via Structured Query Language (SQL). The DBMS query language contains two components: (a) a Data Definition Language (DDL) and (b) a Data Manipulation Language (DML).

 

COMMANDS IN DBMS :

DML DCL DDL TCL SQL

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – delete objects from the database
  • TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • RENAME – rename an object

create command

create is a DDL command used to create a table or a database.


Creating a Database

To create a database in RDBMS, create command is uses. Following is the Syntax,

create database database-name;

Example for Creating Database

create database Test;

The above command will create a database named Test.


Creating a Table

create command is also used to create a table. We can specify names and datatypes of various columns along.Following is the Syntax,

create table table-name
{
 column-name1 datatype1,
 column-name2 datatype2,
 column-name3 datatype3,
 column-name4 datatype4
};

create table command will tell the database system to create a new table with given table name and column information.


Example for creating Table

create table Student(id int, name varchar, age int);

The above command will create a new table Student in database system with 3 columns, namely id, name and age.

 

alter command

alter command is used for alteration of table structures. There are various uses of alter command, such as,

  • to add a column to existing table
  • to rename any existing column
  • to change datatype of any column or to modify its size.
  • alter is also used to drop a column.

To Add Column to existing Table

Using alter command we can add a column to an existing table. Following is the Syntax,

alter table table-name add(column-name datatype);

Here is an Example for this,

alter table Student add(address char); 

The above command will add a new column address to the Student table


To Add Multiple Column to existing Table

Using alter command we can even add multiple columns to an existing table. Following is the Syntax,

alter table table-name add(column-name1 datatype1, column-name2 datatype2, column-name3 datatype3);

Here is an Example for this,

alter table Student add(father-name varchar(60), mother-name varchar(60), dob date); 

The above command will add three new columns to the Student table


To Add column with Default Value

alter command can add a new column to an existing table with default values. Following is the Syntax,

alter table table-name add(column-name1 datatype1 default data);

Here is an Example for this,

alter table Student add(dob date default '1-Jan-99'); 

The above command will add a new column with default value to the Student table


To Modify an existing Column

alter command is used to modify data type of an existing column . Following is the Syntax,

alter table table-name modify(column-name datatype);

Here is an Example for this,

alter table Student modify(address varchar(30)); 

The above command will modify address column of the Student table


To Rename a column

Using alter command you can rename an existing column. Following is the Syntax,

alter table table-name rename old-column-name to column-name;

Here is an Example for this,

alter table Student rename address to Location; 

The above command will rename address column to Location.


To Drop a Column

alter command is also used to drop columns also. Following is the Syntax,

alter table table-name drop(column-name);

Here is an Example for this,

 

 

alter table Student drop(address); 

The above command will drop address column from the Student table

 

truncate command

truncate command removes all records from a table. But this command will not destroy the table’s structure. When we apply truncate command on a table its Primary key is initialized. Following is its Syntax,

truncate table table-name

Here is an Example explaining it.

truncate table Student;

The above query will delete all the records of Student table.

truncate command is different from delete command. delete command will delete all the rows from a table whereas truncate command re-initializes a table(like a newly created table).

For eg. If you have a table with 10 rows and an auto_increment primary key, if you use delete command to delete all the rows, it will delete all the rows, but will not initialize the primary key, hence if you will insert any row after using delete command, the auto_increment primary key will start from 11. But in case of truncatecommand, primary key is re-initialized.


drop command

drop query completely removes a table from database. This command will also destroy the table structure. Following is its Syntax,

drop table table-name

Here is an Example explaining it.

drop table Student;

The above query will delete the Student table completely. It can also be used on Databases. For Example, to drop a database,

 drop database Test;

The above query will drop a database named Test from the system.


rename query

rename command is used to rename a table. Following is its Syntax,

rename table old-table-name to new-table-name

Here is an Example explaining it.

rename table Student to Student-record;

The above query will rename Student table to Student-record.

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

  • SELECT – retrieve data from the a database
  • INSERT – insert data into a table
  • UPDATE – updates existing data within a table
  • DELETE – deletes all records from a table, the space for the records remain
  • MERGE – UPSERT operation (insert or update)
  • CALL – call a PL/SQL or Java subprogram
  • EXPLAIN PLAN – explain access path to data
  • LOCK TABLE – control concurrency

1) INSERT command

Insert command is used to insert data into a table. Following is its general syntax,

INSERT into table-name values(data1,data2,..)

Lets see an example,

Consider a table Student with following fields.

S_id S_Name age
INSERT into Student values(101,'NIKHIL',15);

The above command will insert a record into Student table.

S_id S_Name age
101 NIKHIL 15

Example to Insert NULL value to a column

Both the statements below will insert NULL value into age column of the Student table.

INSERT into Student(id,name) values(102,'AMAN');

Or,

INSERT into Student values(102,'Alex',null);

The above command will insert only two column value other column is set to null.

S_id S_Name age
101 Adam 15
102 AMAN

Example to Insert Default value to a column

INSERT into Student values(103,'NIK',default)
S_id S_Name age
101 Adam 15
102 Alex
103 NIK 14

Suppose the age column of student table has default value of 14.

Also, if you run the below query, it will insert default value into the age column, whatever the default value may be.

INSERT into Student values(103,'Chris')

2) UPDATE command

Update command is used to update a row of a table. Following is its general syntax,

UPDATE table-name set column-name = value where condition;

Lets see an example,

update Student set age=18 where s_id=102;
S_id S_Name age
101 Adam 15
102 Alex 18
103 chris 14

Example to Update multiple columns

UPDATE Student set s_name='Abhi',age=17 where s_id=103; 

The above command will update two columns of a record.

S_id S_Name age
101 Adam 15
102 Alex 18
103 Abhi 17

3) Delete command

Delete command is used to delete data from a table. Delete command can also be used with condition to delete a particular row. Following is its general syntax,

DELETE from table-name;

Example to Delete all Records from a Table

DELETE from Student;

The above command will delete all the records from Student table.


Example to Delete a particular Record from a Table

Consider the following Student table

S_id S_Name age
101 Adam 15
102 Alex 18
103 Abhi 17
DELETE from Student where s_id=103;

The above command will delete the record where s_id is 103 from Student table.

S_id S_Name age
101 Adam 15
102 Alex 18

DCL

Data Control Language (DCL) statements. Some examples:

  • GRANT – gives user’s access privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

TCL

Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

  • COMMIT – save work done
  • SAVEPOINT – identify a point in a transaction to which you can later roll back
  • ROLLBACK – restore database to original since the last COMMIT
  • SET TRANSACTION – Change transaction options like isolation level and what rollback segment to use

Commit command

Commit command is used to permanently save any transaaction into database.

Following is Commit command’s syntax,

commit;


Rollback command

This command restores the database to last commited state. It is also use with savepoint command to jump to a savepoint in a transaction.

Following is Rollback command’s syntax,

rollback to savepoint-name;

Savepoint command

savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.

Following is savepoint command’s syntax,

savepoint savepoint-name;

Example of Savepoint and Rollback

Following is the class table,

ID NAME
1 abhi
2 adam
4 alex

Lets use some SQL queries on the above table and see the results.

INSERT into class values(5,'Rahul');
commit;
UPDATE class set name='abhijit' where id='5';
savepoint A;
INSERT into class values(6,'Chris');
savepoint B;
INSERT into class values(7,'Bravo');
savepoint C;
SELECT * from class;

The resultant table will look like,

ID NAME
1 abhi
2 adam
4 alex
5 abhijit
6 chris
7 bravo

Now rollback to savepoint B

rollback to B;
SELECT * from class;

The resultant table will look like

ID NAME
1 abhi
2 adam
4 alex
5 abhijit
6 chris

Now rollback to savepoint A

rollback to A;
SELECT * from class;

The result table will look like

ID NAME
1 abhi
2 adam
4 alex
5 abhijit

 

What is SQL?

SQL (pronounced “ess-que-el”) stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database. This tutorial will provide you with the instruction on the basics of each of these commands as well as allow you to put them to practice using the SQL Interpreter.