0
1.0kviews
Explain different languages that are supported to manage the data in DBMS.
1 Answer
0
37views

Database languages

  • A DBMS must provide appropriate languages and interfaces for each category of users to express database queries and updates.

  • Database Languages :

    • communicates with the database
    • used to create and maintain database on computer.
  • Mainly two types of Database Languages :

    • Data Definition Language (DDL) : to specify database schema
    • Data Manipulation Language (DML) : to express database queries and updates.
  • In practical, DDL and DML are not separate languages, instead they are the parts of a single database language such as widely used SQI (Structured Query Language)

    Data Definition Language (DDL)

  • DDL (stands for Data Definition Language) is used for specifying the database schema.

  • Used by the DBA and database designers to specify the conceptual schema of a database
  • DDL is used for creating tables, schema, indexes, constraints etc. in database.

Eg : create table account (

account-number char(10), balance (integer)

  • DDL is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.
    • DDL compiler generates a set of table templates stored in a data dictionary.
    • Data dictionary contains Metadata (i.e. data about data)
      • Database Schema
        • Table name
        • Column names & types
      • Integrity Constraints
        • Primary Key
      • Authorization
        • Who can access what

DDL Commands in SQL :

  • CREATE : used to create the database instance .
  • ALTER : used to alter the structure of the database ยท
  • DROP : used to delete the database instance .
  • TRUNCATE : Used to remove all records from a table
  • RENAME : used to rename database instances .
  • Example :

    create table department

    (dept_name char(20),

    building char(15),

    budget numeric(12,2));

    Note : These commands either defines or update the database schema that's why they come under Data Definition language.

Data Manipulation Language (DML)

  • DML (stands for Data Manipulation Language) is used for accessing and manipulating data in a database.

  • It allows users to insert, update, delete and retrieve data from the database.

  • DML is also known as Query Language

    • A query is a statement requesting the retrieval of information
  • Two classes of DML languages :

    • Procedural DMLS/High-level DMLS :
    • User specifies what data is required and how to get those data
    • Procedural DML is embedded into a high-level programming language like java, etc.
    • Eg : PL/SQL
  • Declarative (Non-Procedural)DMLs/Low-level DMLS :
    • User specifies what data is required only; without specifying how to get those data
    • Declarative DMLS are usually easier to learn and use than procedural DMLs. However, since a user does not have to specify how to get the data, the database system has to figure out an efficient means of accessing data.
    • Eg : SQL

DML Commands in SQL :

  • SELECT : used to retrieve data from a database.
  • INSERT : used to insert data into a table.
  • UPDATE : used to update existing data within a table.
  • DELETE : used to delete all records from a table.
  • Example :

    select student_name, Student_age Classes

    from student;

Other Database Language

  • Data Control Language (DCL)
  • Transaction Control Language (TCL)

Data Control language (DCL)

  • Data Control Language (DCL) is used to control privilege in Databases i.e. DCL is used for granting and revoking user access on a database (Authorization)

  • To perform any operation in the database, such as for creating tables, sequences, or views, we need privileges.

  • In particular, it is a component of Structured Query Language (SQL)

  • DCL Commands :

    • GRANT - To give user access privileges to a database
    • REVOKE - To take back permissions from the user
  • The operations for which privileges may be granted to or revoked from a user or role apply to both the Data definition language (DDL) and the Data manipulation language (DML),

Transaction Control Language (TCL)

  • TCL (Transaction Control Language) commands are used to manage transactions in the database.

  • TCL is used to run the changes made by the DML statement.

  • The changes in the database that we made using DML commands are either performed or rollbacked using TCL.

  • TCL Commands :

  • COMMIT : to save the transaction on the database

  • ROLLBACK : to restore the database to original since the last Commit.
  • SAVEPOINT : Savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.
Please log in to add an answer.