The DBMS must support suitable languages and interfaces for each class of customers. In this topic, we will explain the types of languages and interfaces supported by using a DBMS.
The data definition language is the language used to define and change the conceptual schema of the database. DDL permits the DBA or customer to represent and name the entities, attributes, and relationships needed for the function, together with any related integrity and security constraints.
The DBMS will have a DDL compiler whose operation is to technique DDL statements to analyses the definition of the schema design and to save the schema definition in the DBMS directory.
There are following Data Definition Languages (DDL) Commands:
Create:
This command is used to generate or define the table.
Alter:
The structure of the table may be modified by way of using the Alter Table command. Alter Table permits converting the mechanism of a current table.
Drop:
This command is used to delete an object from the database.
Truncate:
This command is used to remove the records or information from the table, but its structure remains the same.
Rename:
This command is used to rename an object from the database.
Comment:
This command is used to comment on the data dictionary.
Storage Definition Language
The storage definition language is the language used to specify the internal schema. The storage mechanism and access techniques utilized by the database framework are decided through a set of statements. These statements represent the analysis of the implementation of database design, which is generally invisible from the customers.
Data Manipulation Language
The data manipulation language (DML) is the language used at the conceptual and view levels to retrieve, insert, delete, and modify information stored in the database.
- Select: It is used to retrieve data from a database.
- Insert: It is used to insert data into a table.
- Update: It is used to update existing data within a table.
- Delete: It is used to delete all records from a table.
- Merge: It performs UPSERT operation, i.e., insert or update operations.
- Call: It is used to call a structured query language or a Java subprogram.
- Explain Plan: It has the parameter of explaining data.
- Lock Table: It controls concurrency.
Query Language
Query Language is the part of the DML used for retrieving information. The term query language and data manipulation language are frequently used interchangeably.
Data Definition Language
DDL stands for Data Definition Language. It is used to define database structure or pattern.
It is used to create schema, tables, indexes, constraints, etc. in the database.
Using the DDL statements, you can create the skeleton of the database.
Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.
Here are some tasks that come under DDL:
- Create: It is used to create objects in the database.
- Alter: It is used to alter the structure of the database.
- Drop: It is used to delete objects from the database.
- Truncate: It is used to remove all records from a table.
- Rename: It is used to rename an object.
- Comment: It is used to comment on the data dictionary.
Data Control Language
- DCL stands for Data Control Language. It is used to retrieve the stored or saved data.
- The DCL execution is transactional. It also has rollback parameters.
(But in Oracle database, the execution of data control language does not have the feature of rolling back.)
Here are some tasks that come under DCL:
- Grant: It is used to give user access privileges to a database.
- Revoke: It is used to take back permissions from the user.
Transaction Control Language
TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction.
Here are some tasks that come under TCL:
- Commit: It is used to save the transaction on the database.
- Rollback: It is used to restore the database to original since the last Commit.
Interfaces:
Forms-Based Interfaces:
A forms-based interface displays a form to each user. Users can fill out all of the form entries to insert new data, or they can fill out only certain entries, in which case the DBMS will redeem same type of data for other remaining entries. These types of forms are usually designed or created and programmed for the users that have no expertise in operating system. Many DBMSs have forms specification languages which are special languages that help specify such forms.
Example: SQL* Forms is a form-based language that specifies queries using a form designed in conjunction with the relational database schema.
Menu-Based Interfaces for Web Clients or Browsing:
These interfaces present the user with lists of options (called menus) that lead the user through the formation of a request. Basic advantage of using menus is that they remove the tension of remembering specific commands and syntax of any query language. The query is basically composed step by step by collecting or picking options from a menu that is shown by the system. Pull-down menus are a very popular technique in Web based interfaces. They are also often used in browsing interface which allow a user to look through the contents of a database in an exploratory and unstructured manner.
Graphical User Interface:
A GUI typically displays a schema to the user in diagrammatic form. The user then can specify a query by manipulating the diagram. In many cases, GUIs utilize both menus and forms. Most GUIs use a pointing device such as mouse, to pick a certain part of the displayed schema diagram.
Natural language Interfaces:
These interfaces accept request written in English or some other language and attempt to understand them. A Natural language interface has its own schema, which is similar to the database conceptual schema as well as a dictionary of important words.
The natural language interface refers to the words in its schema as well as to the set of standard words in a dictionary to interpret the request.If the interpretation is successful, the interface generates a high-level query corresponding to the natural language and submits it to the DBMS for processing, otherwise a dialogue is started with the user to clarify any provided condition or request. The main disadvantage with this is that the capabilities of this type of interfaces are not that much advance.
Speech Input and Output:
There is limited use of speech be it for a query or an answer to a question or being a result of a request it is becoming commonplace. Applications with limited vocabularies such as inquiries for telephone directory, flight arrival/departure, and bank account information are allowed speech for input and output to enable ordinary folks to access this information.
The Speech input is detected using predefined words and used to set up the parameters that are supplied to the queries. For output, a similar conversion from text or numbers into speech takes place.
Interfaces for DBA:
Most database system contains privileged commands that can be used only by the DBA’s staff. These include commands for creating accounts, setting system parameters, granting account authorization, changing a schema, reorganizing the storage structures of a databases.