Database Management Systems
Course Code CSE3001
LTP 4
By
Dr. Lokesh Malviya
Relational Database
A relational database is a type of database that stores and
provides access to data points that are related to one another.
Relational databases are based on the relational model, an
intuitive, straightforward way of representing data in tables.
In a relational database, each row in the table is a record with a
unique ID called the key.
The columns of the table hold attributes of the data, and each
record usually has a value for each attribute, making it easy to
establish the relationships among data points.
Relation Name Attributes
STUDENT
Name Ssn Age
Ajay 1091 21
Tuples Deepika 1092 24
Suman 1093 23
Aditya 1094 22
For Example: UNIVERSITY Database STUDENT
COURSE Name Student_id Class Major
Course_name Course_id Credit Department Amit 17 1 CS
C Prog. CS1310 4 CS Neha 8 2 CS
Data Structures CS3320 3 CS
DMS MATH2410 3 MATH Grade_Report
Database CS3380 3 CS Student_id Section_id Grade
17 112 B
SECTION 17 119 C
Section_id Course_id Semester Year Instructor 8 85 A
85 MATH2410 Fall 07 Manish 8 92 A
92 CS1310 Fall 07 Vivek
112 MATH2410 Spring 08 Deepika PREREQUISITE
Course_id Pre_id
119 CS3380 Spring 08 Jitendra
CS3380 CS3320
CS3320 CS1310
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 Languages
Data Definition Language (DDL)
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.
Data Manipulation Language (DML)
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 one and multiple records
from a table.
Data Manipulation Language (DML)
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.
Data Control Language (DCL)
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 Oracle database, the execution of
data control language does not have the feature of
rolling back.)
DCL:
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 which have the
authorization of Revoke:
CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and
SELECT.
Transaction Control Language (TCL)
TCL is used to run the changes made by the DML
statement. TCL can be grouped into a 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 original
since the last Commit.
