Basic Database Concepts





The database system is an excellent computer-based record-keeping system. A collection of

data, commonly called a database, contains information about a particular enterprise. It maintains
any information that may be necessary for the decision-making process involved in the
management of that organization.
It can also be defined as a collection of interrelated data stored together to serve multiple
applications. The data is stored in a way that is independent of the programs that use the data. A
generic and controlled approach is used to add new data and modify and retrieve existing data
within the database. The data is structured to provide the basis for future application
development.
Purpose of Database
The intent of a database is that a collection of data should serve as many applications as
possible. Therefore, a database is often thought of as a repository of information needed to run
certain functions in a corporation or organization. It would permit not only the retrieval of data
but also the continuous modification of data needed for the control of operations. It may be
possible to search the database to obtain answers to questions or information for planning
purposes.
In a typical file-processing system, permanent records are stored in different files. Many
different application programs are written to extract the records and add the records to the
appropriate files. However, this scheme has several major limitations and disadvantages, such
as data redundancy (duplication of data), data inconsistency, maladaptive data, non-standard
data, insecure data, incorrect data, etc. A database management system is an answer to all these
problems as it provides centralized control of the data.
Database Abstraction
A major purpose of a database is to provide the user with only as much information as required
by them. This means that the system does not disclose all the details of the data rather it hides
some details of how the data is stored and maintained. The complexity of databases is hidden
from users, which is ordered through multiple levels of abstraction to facilitate their interaction
with the system. The different levels of the database are implemented through three layers:
1. Internal Level(Physical Level): It is the lowest level of abstraction closest to physical
storage. It describes how the data is stored concretely on the storage medium.
2. Conceptual Level: This level of abstraction describes what data is concretely stored in the
database. It also describes the relationships that exist between the data. At this level, databases
are described logically in terms of simple data structures. Users at this level are not concerned
with how these logical data structures will be implemented at the physical level.
3. External Level (View Level): This level is closest to users and is related to the way the data
is viewed by individual users.
Data Abstraction

Since a database can be viewed through three levels of abstraction, any change at one level
should not directly affect the other levels. As databases continue to grow, there may be frequent
changes to them sometimes. This should not lead to redesign and re-implementation of the
database. In such a context the concept of data independence proves beneficial.
Concept of Database
To store and manage data efficiently in the database let us understand some key terms:
1. Database Schema: It is a blueprint or structure of the database that defines the organization of
data, types of data that will be stored in rows and columns, constraints, and relationships between
tables.
2. Data Constraints: In a database, we can set rules to specify what kind of data can be stored in
each column of a table. These rules are called constraints. They help ensure the data is valid and
follows certain guidelines. Constraints are defined while we are creating or modifying a table.
3. Data dictionary or Metadata: Metadata is known as the data about the data. The database
schema along with different types of constraints on the data is stored by the DBMS in the
dictionary known as the data dictionary.
4. Database instance: In a database, a database instance is used to define the complete database
environment and its components. It consists of a set of memory structures and background
processes that are responsible for accessing and managing the database files.
5. Query: In a database, a query is used to access data from the database. Users have to write
SQL queries to retrieve or manipulate data from the database.
6. Data manipulation: In a database, we can easily manipulate data using the three main
operations that is Insertion, Deletion, and updation.
7. Data Engine: It is an underlying component that is used to create and manage various
database queries.
Advantages of Database
Let us consider some of the benefits provided by a database system and see how a database
system overcomes the above-mentioned problems:
1. Reduces database data redundancy to a great extent
2. The database can control data inconsistency to a great extent
3. The database facilitates sharing of data across applications and users.
4. Database enforce standards to ensure uniformity.
5. The database can ensure data security through user permissions and access control .
6. Integrity can be maintained through constraints and referential integrity.
Therefore, for systems with better performance and efficiency, database systems are preferred.
Disadvantages of Database
With the complex tasks to be performed by the database system, some things may come up
which can be termed as the disadvantages of using the database system. These are:-
1. Security may be compromised without good controls.
2. Integrity may be compromised without strong enforcement of constraints.
3. Extra hardware may be required for managing large databases.
4. Performance overhead may be significant, especially with complex queries or large
amounts of data
5. The system is likely to be complex which requires skilled professionals to manage and
maintain.
Database System:
A Database Management System (DBMS) is a software solution designed to
efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical
component in modern computing, enabling organizations to store, manipulate, and secure
their data effectively. From small applications to enterprise systems, DBMS plays a vital role
in supporting data-driven decision-making and operational efficiency.
What is a DBMS?
A DBMS is a system that allows users to create, modify, and query databases while
ensuring data integrity, security, and efficient data access. Unlike traditional file systems,
DBMS minimizes data redundancy, prevents inconsistencies, and simplifies data
management with features like concurrent access and backup mechanisms. It organizes data
into tables, views, schemas, and reports, providing a structured approach to data management.
Example:
A university database can store and manage student information, faculty records, and
administrative data, allowing seamless retrieval, insertion, and deletion of information as
required.
What is Data?
Data is a raw and unorganized fact that is required to be processed to make it meaningful. It
can be considered as facts and statistics collected together for reference or analysis.
Data are individual units of information. In analytical processes, data are represented by
variables. Data is always interpreted, by a human or machine, to derive meaning. So, data is
meaningless. Data contains numbers, statements, and characters in a raw form.

Types of Data
There are two types of Data:
1. Quantitative: Quantitative data refers to numerical information like weight, height, etc.
 2. Qualitative: Qualitative data refers to non-numeric information like opinions, perceptions,
etc.
What is Information?
Information is defined as structured, organized, and processed data, presented within a
context that makes it relevant and useful to the person who needs it. Data suggests that raw
facts and figures regarding individuals, places, or the other issue, that is expressed within the
type of numbers, letters or symbols.
Information is the knowledge that is remodeled and classified into an intelligible type, which
may be utilized in the method of deciding. In short, once knowledge ends up being purposeful
when conversing, it’s referred to as info. It’s one thing that informs, in essence, it provides a
solution to a specific question. It may be obtained from numerous sources like newspapers, the
internet, television, people, books, etc.
Difference between Information and Data
S.NO DATA INFORMATION

Data is defined as unstructured

Information refers to processed, organized,
information such as text,
and structured data. It gives context for the
observations, images, symbols,
facts and facilitates decision making. In
and descriptions. In other words,
other words, information is processed data
data provides no specific function
that makes sense to us.
Definition and has no meaning on its own.

Data are the variables that help to

Information is meaningful data.
Purpose develop ideas/conclusions.

Data are text and numerical

Information is refined form of actual data.
Nature values.

Dependence Data doesn't rely on Information. While Information relies on Data.

Bits and Bytes are the measuring Information is measured in meaningful units
Measurement unit of data. like time, quantity, etc.

As tabular data, graphs, and data Information can also be structured as

Structure trees can be easily structured. language, ideas, and thoughts.
 S.NO DATA INFORMATION

Data does not have any specific Information carries a meaning that has been
Purposefulness purpose assigned by interpreting data.

Knowledge Level It is low-level knowledge. It is the second level of knowledge.

Data does not directly help in Information directly helps in decision

Decision Making decision making. making.

Data is a collection of facts, which

Information puts those facts into context.
Meaning itself has no meaning.

Example of data is student test Example of information is average score of

Example scores. class that is derived from given data.

Difference between File System and DBMS





A file system and a DBMS are two kinds of data management systems that are used in different
capacities and possess different characteristics. A File System is a way of organizing files into
groups and folders and then storing them in a storage device. It provides the media that stores
data as well as enables users to perform procedures such as reading, writing, and even erasure.
On the other hand, DBMS is a more elaborate software application that is solely charged with the
responsibility of managing large amounts of structured data. It provides functionalities such as
query, index, transaction, as well as data integrity. Although the file system serves well for the
purpose of data storage for applications where data is to be stored simply and does not require
any great organization, DBMS is more appropriate for applications where data needs to be stored
and optimized for organizational and structural needs, security, etc.

File System
The file system is basically a way of arranging the files in a storage medium like a hard disk.
The file system organizes the files and helps in the retrieval of files when they are required. File
systems consist of different files which are grouped into directories. The directories further
contain other folders and files. The file system performs basic operations like management, file
naming, giving access rules, etc.
Example: NTFS(New Technology File System) , EXT(Extended File System).

File
System

DBMS ( Database Management System)

Database Management System is basically software that manages the collection of related data. It
is used for storing data and retrieving the data effectively when it is needed. It also provides
proper security measures for protecting the data from unauthorized access. In Database
Management System the data can be fetched by SQL queries and relational algebra. It also
provides mechanisms for data recovery and data backup.

Example:
Oracle, MySQL, MS SQL server.

Difference Between File System and DBMS

Basics File System DBMS

The file system is a way of

DBMS is software for managing the
arranging the files in a storage
database.
Structure medium within a computer.
Basics File System DBMS

Data Redundant data can be present in a

In DBMS there is no redundant data.
Redundancy file system.

It doesn't provide Inbuilt mechanism

It provides in house tools for backup
Backup and for backup and recovery of data if it
and recovery of data even if it is lost.
Recovery is lost.

Query There is no efficient query Efficient query processing is there in

processing processing in the file system. DBMS.

There is more data consistency

There is less data consistency in the
because of the process
file system.
Consistency of normalization .

It is less complex as compared to It has more complexity in handling

Complexity DBMS. as compared to the file system.

DBMS has more security

File systems provide less security in
Security mechanisms as compared to file
comparison to DBMS.
Constraints systems.

It has a comparatively higher cost

It is less expensive than DBMS.
Cost than a file system.

In DBMS data independence exists,

mainly of two types:
There is no data independence.
Data 1) Logical Data Independence .
Independence 2)Physical Data Independence.

Only one user can access data at a Multiple users can access data at a
User Access time. time.

The users are not required to write The user has to write procedures for
Meaning procedures. managing databases

Data is distributed in many files. So, Due to centralized nature data

Sharing it is not easy to share data. sharing is easy
 Basics File System DBMS

Data It give details of storage and It hides the internal details

Abstraction representation of data of Database

Integrity Integrity Constraints are difficult to Integrity constraints are easy to

Constraints implement implement

To access data in a file , user

requires attributes such as file name, No such attributes are required.
Attribute s file location.

Example Cobol , C++ Oracle , SQL Server

A file system manages storage, while a DBMS provides efficient data management. To learn
more, the GATE CS Self-Paced Course covers these differences thoroughly.
The main difference between a file system and a DBMS (Database Management System) is
the way they organize and manage data.
1. File systems are used to manage files and directories, and provide basic operations for
creating, deleting, renaming, and accessing files. They typically store data in a hierarchical
structure, where files are organized in directories and subdirectories. File systems are simple
and efficient, but they lack the ability to manage complex data relationships and ensure data
consistency.
2. On the other hand, DBMS is a software system designed to manage large amounts of
structured data, and provide advanced operations for storing, retrieving, and manipulating
data. DBMS provides a centralized and organized way of storing data, which can be accessed
and modified by multiple users or applications. DBMS offers advanced features like data
validation, indexing , transactions, concurrency control , and backup and recovery
mechanisms. DBMS ensures data consistency, accuracy, and integrity by enforcing data
constraints, such as primary keys, foreign keys, and data types.
In summary, file systems are suitable for managing small amounts of unstructured data , while
DBMS is designed for managing large amounts of structured data, and offers more advanced
features for ensuring data integrity, security, and performance.

UNIT -5
SQL Commands | DDL, DQL, DML, DCL and TCL Commands
Last Updated : 20 May, 2025




 SQL commands are crucial for managing databases effectively. These commands are divided
into categories such as Data Definition Language (DDL), Data Manipulation Language (DML),
Data Control Language (DCL), Data Query Language (DQL), and Transaction Control
Language (TCL).
In this article, we will explain the different types of SQL commands,
including DDL, DML, DCL, DQL, and TCL. These SQL sublanguages serve specific
purposes and are important for effective database management.
What are SQL Commands?
SQL commands are the fundamental building blocks for communicating with a database
management system (DBMS). It is used to interact with the database with some operations. It is
also used to perform specific tasks, functions, and queries of data. SQL can perform various
tasks like creating a table, adding data to tables, dropping the table, modifying the table, set
permission for users.
SQL Commands are mainly categorized into five categories:
 DDL – Data Definition Language
 DQL – Data Query Language
 DML – Data Manipulation Language
 DCL – Data Control Language
 TCL - Transaction Control Language

1. DDL - Data Definition Language

DDL or Data Definition Language actually consists of the SQL commands that can be used
for defining, altering, and deleting database structures such as tables, indexes, and schemas. It
simply deals with descriptions of the database schema and is used to create and modify the
structure of database objects in the database
Common DDL Commands
Command Description Syntax

CREATE Create database or its objects CREATE TABLE table_name

 Command Description Syntax

(table, index, function, views, (column1 data_type, column2

store procedure, and triggers) data_type, ...);

Delete objects from the

DROP DROP TABLE table_name;
database

Alter the structure of the ALTER TABLE table_name ADD

ALTER
database COLUMN column_name data_type;

Remove all records from a

table, including all spaces
TRUNCATE TRUNCATE TABLE table_name;
allocated for the records are
removed

Add comments to the data COMMENT 'comment_text' ON

COMMENT
dictionary TABLE table_name;

Rename an object existing in RENAME TABLE old_table_name

RENAME
the database TO new_table_name;
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
In this example, a new table called employees is created with columns for employee ID, first
name, last name, and hire date.
2. DQL - Data Query Language
DQL statements are used for performing queries on the data within schema objects. The
purpose of the DQL Command is to get some schema relation based on the query passed to
it. This command allows getting the data out of the database to perform operations with it. When
a SELECT is fired against a table or tables the result is compiled into a further temporary table,
which is displayed or perhaps received by the program.
DQL Command
Command Description Syntax

It is used to retrieve data from SELECT column1, column2, ...FROM

SELECT
the database table_name WHERE condition;
Example:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
This query retrieves employees' first and last names, along with their hire dates, from the
employees table, specifically for those in the 'Sales' department, sorted by hire date.
3. DML - Data Manipulation Language
The SQL commands that deal with the manipulation of data present in the database belong
to DML or Data Manipulation Language and this includes most of the SQL statements. It is the
component of the SQL statement that controls access to data and to the database. Basically, DCL
statements are grouped with DML statements.
Common DML Commands
Command Description Syntax

Insert data into a INSERT INTO table_name (column1, column2, ...)

INSERT
table VALUES (value1, value2, ...);

Update existing data UPDATE table_name SET column1 = value1,

UPDATE
within a table column2 = value2 WHERE condition;

Delete records from

DELETE DELETE FROM table_name WHERE condition;
a database table

Table control
LOCK LOCK TABLE table_name IN lock_mode;
concurrency

Call a PL/SQL or
CALL CALL procedure_name(arguments);
JAVA subprogram

EXPLAIN Describe the access EXPLAIN PLAN FOR SELECT * FROM

PLAN path to data table_name;
Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into the employees table with the first name 'Jane', last name
'Smith', and department 'HR'.
4. DCL - Data Control Language
DCL (Data Control Language) includes commands such as GRANT and REVOKE which
mainly deal with the rights, permissions, and other controls of the database system. These
commands are used to control access to data in the database by granting or revoking
permissions.
Common DCL Commands
 Command Description Syntax

Assigns new privileges to a

GRANT privilege_type [(column_list)]
user account, allowing access
GRANT ON [object_type] object_name TO user
to specific database objects,
[WITH GRANT OPTION];
actions, or functions.

Removes previously granted

REVOKE [GRANT OPTION FOR]
privileges from a user
privilege_type [(column_list)] ON
REVOKE account, taking away their
[object_type] object_name FROM user
access to certain database
[CASCADE];
objects or actions.
Example of DCL
GRANT SELECT, UPDATE ON employees TO user_name;
This command grants the user user_name the permissions to select and update records in the
employees table.
5. TCL - Transaction Control Language
Transactions group a set of tasks into a single execution unit. Each transaction begins with a
specific task and ends when all the tasks in the group are successfully completed. If any of
the tasks fail, the transaction fails. Therefore, a transaction has only two
results: success or failure. We can explore more about transactions here.
Common TCL Commands
Command Description Syntax

BEGIN BEGIN TRANSACTION

Starts a new transaction
TRANSACTION [transaction_name];

Saves all changes made during

COMMIT COMMIT;
the transaction

Undoes all changes made during

ROLLBACK ROLLBACK;
the transaction

Creates a savepoint within the

SAVEPOINT SAVEPOINT savepoint_name;
current transaction
Example:
BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
In this example, a transaction is started, changes are made, and a savepoint is set. If needed, the
transaction can be rolled back to the savepoint before being committed.

Most Important SQL Commands

There are also a few other SQL Commands we often rely on when writing powerful queries.
While they don’t fit neatly into the five main categories, they’re absolutely essential for working
with data effectively.
Command Description

SELECT Retrieves data from one or more tables.

INSERT Adds new rows (records) to a table.

UPDATE Modifies existing data in a table.

DELETE Removes specific rows from a table.

CREATE TABLE Creates a new table in the database.

ALTER TABLE Modifies the structure of an existing table (e.g., add or remove columns).

DROP TABLE Permanently deletes a table and its data.

TRUNCATE TABLE Removes all rows from a table but keeps its structure intact.

WHERE Filters records based on a condition.

ORDER BY Sorts the result set in ascending or descending order.

GROUP BY Groups rows that have the same values in specified columns.

HAVING Filters grouped data (used with GROUP BY).

JOIN Combines rows from two or more tables based on a related column.

DISTINCT Removes duplicate values from the result set.

 Command Description

IN / BETWEEN / LIKE Used for advanced filtering conditions.

UNION Combines the result of two or more SELECT queries.

GRANT Gives user privileges or permissions.

REVOKE Removes user privileges.

COMMIT Saves all changes made in the current transaction.

ROLLBACK Undoes changes if something goes wrong in a transaction.

SAVEPOINT Sets a point in a transaction to roll back to if needed.