Database Language and Model

Structured Query Language [SQL]

To provide various facilities to different types of users, a DBMS provides one or more specialized programming languages called database languages. SQL is used as an intermediary and a standard database in accessing many different types of database systems. It contains the statements that describe the data contained in the database. There are many types of SQL statements, but the two most commonly used are: one to specify the database schema and the other to express database queries and updates respectively known as:

1. DDL (Data Definition Langauge)

- It is a language that defines the structure of a database, such as a name, field length, and field type. It is a link between the logical and physical structures of the database.
Example:
A student table with fields, registration no, class, and a shift can be created using the DDL statement.
Create table student (registration_no - integer, name variable char (15), class - integer, shift variable char (20));

2. DML (Data Manipulation Language)

- It is a language that enables the users to access or manipulate data as organized by the appropriate data model. DML is used to update the database by adding new data, modify and delete the existing data. Its further of two types:

i) Procedural DMLs:
Procedural DMLs are considered to be low level languages, and they define what is needed and how to obtain the data. They are also called one-at-a-time DMLs as retrieves and processes each record separately.

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 form 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.

ii) Non-Procedural DMLs:
It requires a user to specify what are needed without specifying how to get those data. Generally, the end-users use the high level (non-procedural)DMLs for specifying their requirement.

Some tasks that come under DML:

  • 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.

Database Model

The database model describes the structures of a database. It is a collection of conceptual tools for describing data, relationships, etc. It defines how the data are stored and accessed. There are 3 types of Database models:

1. Hierarchical Database Model

In this model, records are logically organized into a hierarchy of relationships. This model is arranged logically in an inverted tree pattern. All records in the hierarchy are called nodes. Each node is related to the other in a parent-child relationship. Each parent record can have one or many child records, but a child can have only one parent.

Advantages of Hierarchical Database Model

  • Easiest and secure model.
  • Searching is easy and fast, if parent data is known.
  • Very efficient in handling ‘One-to-many’ relationship.
  • Build complex systems from simple components.

Disadvantages of Hierarchical Database Model

  • Outdated database model.
  • Modification and addition of child data element without consulting the parent is impossible or very hard.
  • If a parent node is deleted, all the children nodes also get erased automatically.
  • Increases redundancy as same data can be saved in different places.

2. Network Database Model

It is a modified version of a hierarchical database. In this model, each node may have several parents. This model has a higher level of flexibility than the hierarchical. It is easier to restructure the information stored in this structure, since the path of the relationship enables to store member records arbitrarily/randomly.

Advantages of Network Database Model

  • More flexible as it accepts ‘many-to-many’ relationships.
  • Searching is fast because of multi-directional pointers.
  • It reduces data redundancy.

Disadvantages of Network Database Model

  • It is a complex database model.
  • Less secure than hierarchical as it is open to all.
  • Needs long programs to handle the relationships.

3. Relational Database Model

In this model, the data elements are organized in the form of multiple tables with rows and columns. Each row represents data records (also known as a tuple), and each column represents a data field. Each data field must have an atomic (individual) data value. It is highly flexible to program and retrieve data.

Advantages of Relational Database Model

  • It includes very less redundancy.
  • Normalization is possible.
  • Quick data processing is possible.
  • Fast seraching is possible.

Disadvantages of Relational Database Model

  • More complex than other models.
  • Less secured model.
  • Too many rules make this database non-user-friendly.