Database Languages

TYPES OF DATABASE LANGUAGES

 Database Management System needs queries for accessing the data items including data storage, retrieval, deletion, update, etc.

There are various query languages that are traditionally supported by the databases as follows:

• DDL (Data Definition Language) – For definition of conceptual schemas or internal schemas, DDL is used by database designers. The data definition languages describe only the schema and not the data associated with database.
• DDL compiler is used for processing DDL queries and to store data description in the data catalogue. It is also useful for identification of database schema constructs.
• DDL is mainly used to describe conceptual schema and SDL (Storage Definition Language) is used for schema definition in internal layer (internal schema). When the databases are clearly separated into conceptual and internal schemas, this kind of usage is possible.
• There is another database language called VDL or View Definition Language which is used in three-schema architecture of databases. VDL is used in specification of view level- maps user views with the conceptual schema definition.
• In majority of cases, DDL alone is enough to specify both conceptual and external schema definitions.
• After compilation of database schemas, the database gets loaded with data and the users need languages- query languages for various data operations in the database. Manipulating data in database includes operates such as insertion, deletion, retrieval, update and data modification.
• The above operations are made possible only with the help of DML or Data Manipulation Language.
• In majority of the database management systems, DDL, VDL and DML are considered as integrated languages and not as separate languages. Only the data storage and its manipulation are maintained separately for better database performance.
• The favorite database query language is SQL or Structured Query Language which consists of VDL, DML and DDL. There are also specific integrity constraints in SQL.

 Data Definition Language (DDL)

 The data definition language is used for specifying database schema definitions.

1. Various operations associated with DDL are create, drop, truncate and alter.
2. The result of DDL compilation is stored in data directory which is otherwise called as data dictionary.
3. The DDL is used to create specific sets of tables which are the skeleton of the database.
4. Data dictionary is a file which contains data about the data called metadata.

      Example:

 create table employee ( eid   number(10), EmployeeNamechar(20), Salary integer, Branch varchar (15)) ;

Constraints in DBMS languages:

• Domain constraint is a kind of integrity constraint which is imposed on a specific column in the database.
• Referential integrity is the kind of foreign key constraint which specifies that the particular value in primary table is valid in other referential table.(e.g. Branch name in employee table must correspond to a valid Branch in the Branch table)

Storage Definition Language (SDL)

• The way of data storage and data access methods in database are specified using the storage definition language or SDL.                   

Data Manipulation Language (DML)

• The database language which is used for manipulating and accessing data in DBMS is known as Data Manipulation Language.
• This supports various data manipulation techniques including data retrieval, deletion, insertion and modification.
• Insertion is done through insert command, selection or retrieval is done through select command, deletion of a specific row is done through delete command and data update in database is done through update command.

       Example :

insert into table employee values ( ‘ Joy’, 2098, 25000, ‘Banglore’);

Types of Data Manipulation Languages (DMLs)

High-level or Non procedural

• In order to describe complex operations in database, high level or non-procedural languages are used.
• For interactive data entry from a terminal, DML queries are used in Database Management Systems.
• Using pre-compiler, DML statements are separately identified when it is used in embedded programming languages and then processing is done separately by the database management systems.
• DML queries are usually embedded with general purpose programming languages.
• DML is used to fetch record by record (row by row) and will process each of it separately. Each database object is also termed as a database object.
• For fetching of data at once, looping statements such as for loop is used along with DML.
• Using a single DML statement, only one record is retrieved at a time in case of low-level DML.
• Multiple records can be fetched at once using high-level DML statements.
• High-level DML is called set-oriented DML as it retrieves a set of records in the same time.
• High-level DML is used to describe what to retrieve but not the way of retrieving it (how) and thus it is called declarative.

Two groups of DML languages

Procedural DML Statements:

The procedural DML statements will specify what data is retrieved along with information about how the data is retrieved.

Declarative or non-procedural Statements:

The non-procedural query specifies only what kind of data is retrieved but not how the data   is retrieved.

 SQL (Structural Query Language)

• This is the most commonly used query language in all types of databases.
• Though many modern database query languages have emerged in recent times, all those are based on SQL and still SQL has got its demand.
• SQL statements can be embedded with other programming languages yet the SQL compiler can identify these SQL queries and process them separately.
• When DML is used along with other general purpose programming languages, DML is the query language used for data manipulations in a database whereas the other programming language is the host language.