DBMS
DBMS FUNCTIONS:
DBMS performs several important functions that guarantee the integrity and
consistency of the data in the database.
Those functions transparent to end users and can be accessed only through the
use of DBMS.
They include: 1.Data Dictionary Management
2. Data Storage Management
3.Data transformation and Presentation
4.Security Management
5.Multiple Access Control
6.Backup and Recovery Management
7. Data Integrity Management
8. Database Access Languages
9.Databases Communication Interfaces
SQL Commands | DDL, DQL, DML, DCL and TCL Commands
DDL (Data Definition Language) commands in SQL are used to define, modify,
and delete the structure of database objects like tables, views, indexes, and
schemas. These commands are auto-committed, meaning changes are
permanently saved and cannot be rolled back. DDL (Data Definition
Language) actually consists of 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.
Command Description Syntax
Create database or its
CREATE TABLE table_name
objects (table, index,
CREATE (column1 data_type,
function, views, store
column2 data_type, ...);
procedure and triggers)
DROP Delete objects from the DROP TABLE table_name;
Command Description Syntax
database
ALTER TABLE table_name
Alter the structure of
ALTER ADD COLUMN
the database
column_name data_type;
Remove all records from
a table, including all TRUNCATE TABLE
TRUNCATE
spaces allocated for the table_name;
records are removed
COMMENT ON TABLE
Add comments to the
COMMENT table_name IS
data dictionary
'comment_text';
RENAME TABLE
Rename an object
RENAME old_table_name TO
existing in the database
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.
The primary DDL commands are:
CREATE: Used to create new database objects.
Code
CREATE DATABASE database_name;
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
CREATE INDEX index_name ON table_name (column_name);
ALTER: Used to modify the structure of an existing database object.
Code
ALTER TABLE table_name
ADD column_name datatype constraints;
ALTER TABLE table_name
DROP COLUMN column_name;
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;
DROP: Used to delete existing database objects.
Code
DROP DATABASE database_name;
DROP TABLE table_name;
DROP INDEX index_name;
TRUNCATE: Used to remove all records from a table, including the
memory allocated for the data, but keeping the table structure intact.
Code
TRUNCATE TABLE table_name;
RENAME: Used to change the name of a database object (e.g., a table or
column).
Code
ALTER TABLE old_table_name RENAME TO new_table_name;
ALTER TABLE table_name RENAME COLUMN old_column_name TO
new_column_name;
COMMENT: Used to add comments to the data dictionary about tables,
columns, or other database objects.
Code
COMMENT ON TABLE table_name IS 'This is a comment for the table.';
COMMENT ON COLUMN table_name.column_name IS 'This is a comment for
the colu
2. DQL - Data Query Language
DQL is used to fetch data from the database. The main command is SELECT,
which retrieves records based on the query. The output is returned as a result
set (a temporary table) that can be viewed or used in applications.
DQL Command
Command Description Syntax
SELECT column1,
It is used to retrieve data
SELECT column2, ...FROM table_name
from the database
WHERE condition;
Indicates the table(s) from SELECT column1
FROM
which to retrieve data. FROM table_name;
SELECT column1
Filters rows before any
WHERE FROM table_name
grouping or aggregation
WHERE condition;
SELECT column1,
Groups rows that have the
GROUP AVG_FUNCTION(column2)
same values in specified
BY FROM table_name
columns.
GROUP BY column1;
Command Description Syntax
SELECT column1,
AVG_FUNCTION(column2)
Filters the results of
HAVING FROM table_name
GROUP BY
GROUP BY column1
HAVING condition;
SELECT DISTINCT column1,
Removes duplicate
DISTINCT column2, ...
rows from the result set
FROM table_name;
SELECT column1
ORDER Sorts the result set by one
FROM table_name
BY or more columns
ORDER BY column1 [ASC | DESC];
By default, it sorts
SELECT * FROM table_name LIMIT
LIMIT in ascending order unless
number;
specified as DESC
Note: DQL has only one command, SELECT. Other terms like FROM, WHERE,
GROUP BY, HAVING, ORDER BY, DISTINCT and LIMIT are clauses of SELECT, not
separate commands.
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
DML commands are used to manipulate the data stored in database tables.
With DML, you can insert new records, update existing ones, delete unwanted
data or retrieve information.
Common DML Commands
Command Description Syntax
INSERT INTO table_name (column1,
Insert data into a
INSERT column2, ...) VALUES (value1,
table
value2, ...);
UPDATE table_name SET column1 =
Update existing data
UPDATE value1, column2 = value2 WHERE
within a table
condition;
Delete records from a DELETE FROM table_name WHERE
DELETE
database table condition;
Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into employees table with 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
GRANT Assigns new privileges to GRANT privilege_type
Command Description Syntax
a user account, allowing [(column_list)] ON
access to specific [object_type]
database objects, object_name TO user
actions or functions. [WITH GRANT OPTION];
Removes previously REVOKE [GRANT OPTION
granted privileges from a FOR] privilege_type
user account, taking [(column_list)] ON
REVOKE
away their access to [object_type]
certain database objects object_name FROM user
or actions. [CASCADE];
Example:
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, transaction fails. Therefore, a
transaction has only two results: success or failure.
Common TCL Commands
Command Description Syntax
BEGIN BEGIN TRANSACTION
Starts a new transaction
TRANSACTION [transaction_name];
Saves all changes made
COMMIT COMMIT;
during the transaction
Command Description Syntax
Undoes all changes made
ROLLBACK ROLLBACK;
during the transaction
Creates a savepoint
SAVEPOINT
SAVEPOINT within the current
savepoint_name;
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;
Generalization, Specialization and Aggregation in ER Model:
Using the ER model for bigger data creates a lot of complexity while
designing a database model, So in order to minimize the complexity
Generalization, Specialization and Aggregation were introduced in the
ER model. These were used for data abstraction. In which an
abstraction mechanism is used to hide details of a set of objects.
Generalization
Generalization is the process of extracting common properties from a
set of entities and creating a generalized entity from it. It is a bottom-
up approach in which two or more entities can be generalized to a
higher-level entity if they have some attributes in common.
Example: STUDENT and FACULTY can be generalized to a higher-
level entity called PERSON as shown in diagram below. In this case,
common attributes like P_NAME and P_ADD become part of a
higher entity (PERSON) and specialized attributes like S_FEE
become part of a specialized entity (STUDENT).
Specialization
In specialization, an entity is divided into sub-entities based on its
characteristics. It is a top-down approach where the higher-level entity
is specialized into two or more lower-level entities.
Example: an EMPLOYEE entity in an Employee management
system can be specialized into DEVELOPER, TESTER, etc. as shown
in figure below. In this case, common attributes like E_NAME,
E_SAL, etc. become part of a higher entity (EMPLOYEE) and
specialized attributes like TES_TYPE become part of a specialized
entity (TESTER).
Specialization is also called as "Top-Down approach".
Inheritance
It is an important feature of generalization and specialization. In
specialization, a higher-level entity is divided into lower-level sub-
entities that inherit its attributes. In generalization, similar lower-level
entities are combined into a higher-level entity that holds common
attributes. In both cases, inheritance allows sub-entities to reuse the
properties of the parent entity.
1. Attribute inheritance: It allows lower level entities to
inherit the attributes of higher level entities and vice versa. In
diagram Car entity is an inheritance of Vehicle entity ,So Car
can acquire attributes of Vehicle. Example: Car can acquire
Model attribute of Vehicle.
2. Relationship Inheritance: Sub-entities also inherit
relationships of the parent entity.
3. Overriding Inheritance: Sub-entities can override or add
their own attributes or behaviors different from the parent.
4. Participation inheritance: Participation inheritance in ER
modeling refers to the inheritance of participation constraints
from a higher-level entity (superclass) to a lower-level entity
(subclass). It ensures that subclasses adhere to the same
participation rules in relationships, although attributes and
relationships themselves are inherited differently.
5. Aggregation:
Aggregation is an abstraction
through which we can represent
relationships as higher-level entity
sets.
An ER diagram is not capable of
representing the relationship between
an entity and a relationship which
may be required in some scenarios. In
those cases, a relationship with its
corresponding entities is aggregated
into a higher-level entity.
Example: an Employee working on a project may require some
machinery. So, REQUIRE relationship is needed between the
relationship WORKS_FOR and entity MACHINERY. Using
aggregation, WORKS_FOR relationship with its entities EMPLOYEE
and PROJECT is aggregated into a single entity and relationship
REQUIRE is created between the aggregated entity and
MACHINERY.
What is Schema?
A database schema is the skeleton structure that represents the logical
view of the entire database. (or) The logical structure of the database
is called as Database Schema. (or) The overall design of the database
is the database schema. It defines how the data is organized and how
the relations among them are associated. It formulates all the
constraints that are to be applied on the data.
EG: STUDENT SID SNAME PHNO
What is Instance?
The actual content of the database at a particular point in time. (Or)
The data stored in the database at any given time is an instance of the
database
Student
S_id Name phno
1201 Venkat 9014901442
1202 teja 9014774422
In the above table 1201, 1202, Venkat etc are said to be instance of
student table
