Database Language

• A DBMS has appropriate languages and interfaces to express database queries and updates.
• Database languages can be used to read, store and update the data in the database.

Types of Database Language

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

These commands are used to update the database schema that's why they come under Data definition language.

2. Data Manipulation Language

DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests.

Here are 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.

3. 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 the 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.

There are the following operations that have the authorization of Revoke:

CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

4. Transaction Control Language

TCL is used to run the changes made by the DML statement. TCL can be grouped into 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 transactions original since the last Commit.

Read More

 

Data Independence

• Data independence can be explained using the three-schema architecture.
• Data independence refers characteristic of being ability to modify the schema at one level of the database system without altering the schema at the next higher level.

There are two types of data independence:

1. Logical Data Independence

• Logical data independence refers characteristic of being able to change the conceptual schema without having to change the external schema.
• Logical data independence is used to separate the external level from the conceptual view.
• If we do any changes in the conceptual view of the data, then the user view of the data would not be affected.
• Logical data independence occurs at the user interface level.

2. Physical Data Independence

• Physical data independence can be defined as the capacity to change the internal schema without having to change the conceptual schema.
• If we do any changes in the storage size of the database system server, then the Conceptual structure of the database will not be affected.
• Physical data independence is used to separate conceptual levels from the internal levels.
• Physical data independence occurs at the logical interface level.

Fig: Data Independence

Read More

 

Data model Schema and Instance

• The data which is stored in the database at a particular moment of time is called an instance of the database.
• The overall design of a database is called schema.
• A database schema is the skeleton structure of the database. It represents the logical view of the entire database.
• A schema contains schema objects like table, foreign key, primary key, views, columns, data types, stored procedure, etc.
• A database schema can be represented by using the visuala diagram. That diagram shows the database objects and their relationship with each other.
• A database schema is designed by the database designers to help programmers whose software will interact with the database. The process of database creation is called data modeling.

A schema diagram can display only some aspects of a schema like the name of record type, data type, and constraints. Other aspects can't be specified through the schema diagram. For example, the given figure neither shows the data type of each data item nor the relationship among various files.

In the database, actual data changes quite frequently. For example, in the given figure, the database changes whenever we add a new grade or add a student. The data at a particular moment is called the instance of the database.

Read More

 

Data Models

Data Model is the modeling of the data description, data semantics, and consistency constraints of the data. It provides the conceptual tools for describing the design of a database at each level of data abstraction. Therefore, there are the following four data models used for understanding the structure of the database:

1) Relational Data Model: This type of model designs the data in the form of rows and columns within a table. Thus, a relational model uses tables for representing data and in-between relationships. Tables are also called relations. This model was initially described by Edgar F. Codd, in 1969. The relational data model is the widely used model which is primarily used by commercial data processing applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of data as objects and relationships among them. These objects are known as entities, and a relationship is an association among these entities. This model was designed by Peter Chen and published in 1976 papers. It was widely used in database design. A set of attributes describe the entities. For example, student_name, and student_id describe the 'student' entity. A set of the same type of entities is known as an 'Entity set', and the set of the same type of relationships is known as a 'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of functions, encapsulation, and object identity, as well. This model supports a rich type system that includes structured and collection types. Thus, in the 1980s, various database systems following the object-oriented approach were developed. Here, the objects are nothing but data-carrying properties.

4) Semistructured Data Model: This type of data model is different from the other three data models (explained above). The semistructured data model allows the data specifications at places where the individual data items of the same type may have different attributes sets. The Extensible Markup Language, also known as XML, is widely used for representing semistructured data. Although XML was initially designed for including the markup information in the text document, it gains importance because of its application in the exchange of data.

Read More

 

Three schema Architecture

• The three schema architecture is also called ANSI/SPARC architecture or three-level architecture.
• This framework is used to describe the structure of a specific database system.
• The three schema architecture is also used to separate the user applications and physical database.
• The three schema architecture contains three-levels. It breaks the database down into three different categories.

The three-schema architecture is as follows:

In the above diagram:

• It shows the DBMS architecture.
• Mapping is used to transform the request and response between various database levels of architecture.
• Mapping is not good for small DBMS because it takes more time.
• In External / Conceptual mapping, it is necessary to transform the request from the external level to the conceptual schema.
• In Conceptual / Internal mapping, DBMS transforms the request from the conceptual to the internal level.

Objectives of Three schema Architecture

The main objective of the three-level architecture is to enable multiple users to access the same data with a personalized view while storing the underlying data only once. Thus it separates the user's view from the physical structure of the database. This separation is desirable for the following reasons:

• Different users need different views of the same data.
• The approach in which a particular user needs to see the data may change over time.
• The users of the database should not worry about the physical implementation and internal workings of the database such as data compression and encryption techniques, hashing, optimization of the internal structures, etc.
• All users should be able to access the same data according to their requirements.
• DBA should be able to change the conceptual structure of the database without affecting the user's
• The internal structure of the database should be unaffected by changes to physical aspects of the storage.

1. Internal Level

• The internal level has an internal schema that describes the physical storage structure of the database.
• The internal schema is also known as a physical schema.
• It uses the physical data model. It is used to define how the data will be stored in a block.
• The physical level is used to describe complex low-level data structures in detail.

The internal level is generally is concerned with the following activities:

• Storage space allocations.three levels
  For Example B-Trees, Hashing, etc.
• Access paths.
  For Example Specification of primary and secondary keys, indexes, pointers, and sequencing.
• Data compression and encryption techniques.
• Optimization of internal structures.
• Representation of stored fields.

2. Conceptual Level

• The conceptual schema describes the design of a database at the conceptual level. The conceptual level is also known as the logical level.
• The conceptual schema describes the structure of the whole database.
• The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data.
• At the conceptual level, internal details such as an implementation of the data structure are hidden.
• Programmers and database administrators work at this level.

3. External Level

• At the external level, a database contains several schemas that are sometimes called subschema. The subschema is used to describe the different views of the database.
• An external schema is also known as a view schema.
• Each view schema describes the database part that which a particular user group is interested and hides the remaining database from that user group.
• The view schema describes the end-user interaction with database systems.

Mapping between Views

The three levels of DBMS architecture don't exist independently of each other. There must be a correspondence between the three levels i.e. how they actually correspond with each other. DBMS is responsible for correspondence between the three types of schema. This correspondence is called Mapping.

There are basically two types of mapping in the database architecture:

• Conceptual/ Internal Mapping
• External / Conceptual Mapping

Conceptual/ Internal Mapping

The Conceptual/ Internal Mapping lies between the conceptual level and the internal level. Its role is to define the correspondence between the records and fields of the conceptual level and files and data structures of the internal level.

External/ Conceptual Mapping

The external/Conceptual Mapping lies between the external level and the Conceptual level. Its role is to define the correspondence between a particular external and conceptual view.

Read More

 

DBMS Architecture

• The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers, and other components that are connected with networks.
• The client/server architecture consists of many PCs and a workstation that are connected via the network.
• DBMS architecture depends upon how users are connected to the database to get their requests done.

Types of DBMS Architecture

Database architecture can be seen as a single-tier or multi-tier. But logically, database architecture is of two types: 2-tier architecture and 3-tier architecture.

1-Tier Architecture

• In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.
• Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end-users.
• The 1-Tier architecture is used for the development of the local application, where programmers can directly communicate with the database for a quick response.

2-Tier Architecture

• The 2-Tier architecture is the same as the basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database on the server-side. For this interaction, APIs like ODBC, and JDBC are used.
• The user interfaces and application programs are run on the client-side.
• The server side is responsible to provide the functionalities like query processing and transaction management.
• To communicate with the DBMS, the client-side application establishes a connection with the server-side.

Fig: 2-tier Architecture

3-Tier Architecture

• The 3-Tier architecture contains another layer between the client and server. In this architecture, the client can't directly communicate with the server.
• The application on the client-end interacts with an application server which further communicates with the database system.
• The end user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
• The 3-Tier architecture is used in the case of the large web applications.

Fig: 3-tier Architecture

Read More

 

DBMS vs. File System

File System Approach

File-based systems were an early attempt to computerize the manual system. It is also called a traditional-based approach in which a decentralized approach was taken where each department stored and controlled its own data with the help of a data processing specialist. The main role of a data processing specialist was to create the necessary computer file structures, and also manage the data within structures and design some application programs that create reports based on file data.

In the above figure:

Consider an example of a student's file system. The student file will contain information regarding the student (i.e. roll no, student name, course, etc.). Similarly, we have a subject file that contains information about the subject and the result file which contains the information regarding the result.

Some fields are duplicated in more than one file, which leads to data redundancy. So to overcome this problem, we need to create a centralized system, i.e. DBMS approach.

DBMS:

A database approach is a well-organized collection of data that are related in a meaningful way that can be accessed by different users but stored only once in a system. The various operations performed by the DBMS system are Insertion, deletion, selection, sorting, etc.

In the above figure,

In the above figure, duplication of data is reduced due to the centralization of data.

There are the following differences between DBMS and File systems:

Basis	DBMS Approach	File System Approach
Meaning	DBMS is a collection of data. In DBMS, the user is not required to write the procedures.	The file system is a collection of data. In this system, the user has to write the procedures for managing the database.
Sharing of data due	Due to the centralized approach, data sharing is easy.	Data is distributed in many files, and it may be of different formats, so it isn't easy to share data.
Data Abstraction	DBMS gives an abstract view of data that hides the details.	The file system provides the detail of the data representation and storage of data.
Security and Protection	DBMS provides a good protection mechanism.	It isn't easy to protect a file under the file system.
Recovery Mechanism	DBMS provides a crash recovery mechanism, i.e., DBMS protects the user from system failure.	The file system doesn't have a crash mechanism, i.e., if the system crashes while entering some data, then the content of the file will be lost.
Manipulation Techniques	DBMS contains a wide variety of sophisticated techniques to store and retrieve the data.	The file system can't efficiently store and retrieve the data.
Concurrency Problems	DBMS takes care of Concurrent access of data using some form of locking.	In the File system, concurrent access has many problems like redirecting the file while deleting some information or updating some information.
Where to use	Database approach used in large systems which interrelate many files.	File system approach used in large systems which interrelate many files.
Cost	The database system is expensive to design.	The file system approach is cheaper to design.
Data Redundancy and Inconsistency	Due to the centralization of the database, the problems of data redundancy and inconsistency are controlled.	In this, the files and application programs are created by different programmers so that there exists a lot of duplication of data which may lead to inconsistency.
Structure	The database structure is complex to design.	The file system approach has a simple structure.
Data Independence	In this system, Data Independence exists, and it can be of two types. Logical Data Independence Physical Data Independence	In the File system approach, there exists no Data Independence.
Integrity Constraints	Integrity Constraints are easy to apply.	Integrity Constraints are difficult to implement in file system.
Data Models	In the database approach, 3 types of data models exist: Hierarchal data models Network data models Relational data models	In the file system approach, there is no concept of data models exists.
Flexibility	Changes are often a necessity to the content of the data stored in any system, and these changes are more easily with a database approach.	The flexibility of the system is less as compared to the DBMS approach.
Examples	Oracle, SQL Server, Sybase etc.	Cobol, C++ etc.

Read More

 

Difference between DBMS and RDBMS

Although DBMS and RDBMS both are used to store information in the physical database there are some remarkable differences between them.

The main differences between DBMS and RDBMS are given below:

No.	DBMS	RDBMS
1)	DBMS applications store data as files.	RDBMS applications store data in a tabular form.
2)	In DBMS, data is generally stored in either a hierarchical form or a navigational form.	In RDBMS, the tables have an identifier called the primary key and the data values are stored in the form of tables.
3)	Normalization is not present in DBMS.	Normalization is present in RDBMS.
4)	DBMS does not apply any security with regards to data manipulation.	RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.
5)	DBMS uses file system to store data, so there will be no relation between the tables.	in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
6)	DBMS has to provide some uniform methods to access the stored information.	RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
7)	DBMS does not support distributed database.	RDBMS supports distributed database.
8)	DBMS is meant to be for small organization and deal with small data. it supports single user.	RDBMS is designed to handle large amount of data. it supports multiple users.
9)	Examples of DBMS are file systems, xml etc.	Example of RDBMS are mysql, postgre, sql server, oracle etc.

After observing the differences between DBMS and RDBMS, you can say that RDBMS is an extension of DBMS. There are many software products in the market today who are compatible for both DBMS and RDBMS. Means today a RDBMS application is DBMS application and vice-versa.

Read More

 

What is RDBMS

RDBMS stands for Relational Database Management Systems.

All modern database management systems like SQL, MS SQL Server, IBM DB2, ORACLE, My-SQL, and Microsoft Access are based on RDBMS.

It is called Relational DataBase Management System (RDBMS) because it is based on the relational model introduced by E.F. Codd.

How it works

Data is represented in terms of tuples (rows) in RDBMS.

A relational database is the most commonly used database. It contains several tables and each table has its own primary key.

Due to a collection of an organized set of tables, data can be accessed easily in RDBMS.

Brief History of RDBMS

From 1970 to 1972, E.F. Codd published a paper proposing the use of a relational database model.

RDBMS is originally based on E.F. Codd's relational model invention.

What is table

The RDBMS database uses tables to store data. A table is a collection of related data entries and contains rows and columns to store data.

A table is the simplest example of data stored in RDBMS.

Let's see the example of the student table.

ID	Name	AGE	COURSE
1	Ajeet	24	B.Tech
2	aryan	20	C.A
3	Mahesh	21	BCA
4	Ratan	22	MCA
5	Vimal	26	BSC

What is field

The field is a smaller entity of the table which contains specific information about every record in the table. In the above example, the field in the student table consists of id, name, age, and course.

---

What is row or record?

A row of a table is also called a record. It contains the specific information of each individual entry in the table. It is a horizontal entity in the table. For example, The above table contains 5 records.

Let's see one record/row in the table.

1

Ajeet

24

B.Tech

What is column

A column is a vertical entity in the table which contains all information associated with a specific field in a table. For example "name" is a column in the above table which contains all information about the student's name.

Ajeet

Aryan

Mahesh

Ratan

Vimal

NULL Values

The NULL value of the table specifies that the field has been left blank during record creation. It is totally different from the value filled with zero or a field that contains space.

Data Integrity

There are the following categories of data integrity exist with each RDBMS:

Entity integrity: It specifies that there should be no duplicate rows in a table.

Domain integrity: It enforces valid entries for a given column by restricting the type, the format, or the range of values.

Referential integrity: It specifies that rows cannot be deleted, which are used by other records.

User-defined integrity: It enforces some specific business rules that are defined by users. These rules are different from the entity, domain, or referential integrity.

Read More