Database Languages and Interface

Database Languages

Database languages are used to read, store and update the data in the database. Specific languages are used to perform various operations of the database.

Types of Database Languages

DDL(Data Definition Language)

Data Definition Language(DDL) is used for describing structures or patterns and its relationship in a database. It is also used to define the database schema, tables, index, Constraints, etc. It can also be used to store information like the number of tables, names, columns, indexes, etc. The commands only affect the database structure and not the data.

The commands used in DDL are:

Create: It is used to create a database or table.

Alter: It is used to make a change in the structure of a database.

Drop: It is used to completely delete a table from the database

Rename: It is used to rename a table.

Truncate: It is used to delete the entities inside the table while holding the structure of the table.

Comment: It is used to comment on the data dictionary.

DML(Data Manipulation Language)

DML is used to manipulate the data present in the table or database. We can easily perform operations such as store, modify, update, and delete on the database.

The commands used in DML are:

Select: It shows the record of the specific table. Also, it can be used with a WHERE clause to get the particular record.

Insert: It allows users to insert data into the database or tables.

Update: It is used to update or modify the existing data in database tables.

Delete: It is used to delete records from the database tables. Also, it can be used with a WHERE clause to delete a particular row from the table.

Merge: It allows the insert and update(UPSERT) operations.

DCL(Data Control Language)

DCL works to deal with SQL commands that are used to permit a user to access, modify and work on a database. it is used to access stored data. It gives access, revokes access, and changes the permission to the owner of the database as per the requirement.

The commands used in DCL are:

Grant: It is used to give access to security privileges to a specific database user.

Revoke: It is used to revoke the access from the user that is being granted by the grant command.

TCL(Transaction Control Language)

It can be grouped into a logical transaction and is used to run the changes made by the DML command in the database.

Commit: Transaction on the database is saved using Commit.

Rollback: The database gets restored to the original since the last commit.

Interface

An interface is a program that allows users to input queries into a database without writing the code in the query language. An interface can be used to manipulate the database for adding, deleting, updating, or viewing the data.

Types of Interface are

Form−based Interface

A form is displayed to each user by the form−based interface. The user fills in the details and submits the form to make a new entry into the database. It can also be done when the user only fills in some details and the system will help by retrieving the rest of the details from the database. The form−based interface is built for the naive user(inexperienced user) which deals with a limited number of operations. Many DBMS have specification language which helps the programmer define such forms.

Example

Student entering his roll. no, branch in the form to get the grade card.

Menu−based User Interface

In this interface, the user was provided with a list of options (called a menu) through which the user forms a request. The user doesn’t need to memorize the command and syntax and the query is composed step by step by picking options from a menu. Pull−down menu interfaces are mostly used in web−based user interfaces and are often used in browsing interfaces by which the database content can be looked through.

Example

In a shopping website, categories are selected from the menu, brands are selected from the menu of brands, and budget ranges are applied from the menu of budget range.

GUI(Graphical User Interface)

Users are provided a schema of diagrammatic form by which query can be specified through manipulating the diagram. GUI utilizes both menu and form in several cases. Schema Diagram's specific parts are selected using devices used by GUI.

Example

You liked a video on Instagram by tapping with your finger, and the color changes to red. The visual graphic gets changed due to user action.

Natural Language Interface

A natural language interface contains its unique schema more like the high−level conceptual schema. It also has a directory of important words. It generates a query based on the interpretation of important words in the input by the user and if the interpretation is successful, then it displays the result to the user.

Example

A user googled the fastest car in India, and now the natural language interface will look for the important words i. e. fastest, car, India, and show the result accordingly.

Speech Input and Output

The users query the interface with speech and get the answer in speech. The input is detected using predefined words and conversions are done into speech to provide the output. Nowadays, it has become the most common type of interface.

Example

OK Google, Siri on Apple, and Alexa is used in the form of speech.

Interface for DBA

DBA staff are provided commands that can only be used by them only to create an account, grant account authorization, and change a schema, and storage structure reorganization.

Conclusion

This article consists of two parts containing database languages and interface. Database languages are used to perform operations on the database and have 4 types i. e. DDL, DCL, DML, and TCL. DDL is used to describe patterns in the database. DCL work to deal with SQL command for providing access. DML is used for the manipulation of data. TCL refers to logistic transactions. The second part comprises of interface which refers to a program that allows the user to do the task without using codes. Various types of interfaces are Form based interfaces, Menu based user Interfaces, Graphical user interfaces, Natural language interfaces, speech input and output, and interfaces for DBA.