RDBMS Concepts, MySQL &

PL/SQL — Study Notes

Covers: Chapter 5 (RDBMS Concepts and MySQL) in full,
and the introductory section of Chapter 6 (PL/SQL), based on
the pages provided.

Chapter 5: RDBMS Concepts and

MySQL
1. Data, Information & the DIKW Model
Term Meaning
Raw, unorganized facts and figures
Data (numbers, text, images) with no meaning by
themselves. From the Latin datum — "to give
something."
Data that has been processed and arranged
Information in a meaningful way, making it useful for
decision-making.
Example: 1, XYZ, 15 on their own are just data.
Arranged as Roll No = 1, Name = XYZ, Age = 15, they
become information.
 DIKW Hierarchy — shows how raw data is refined into
higher levels of understanding:
Data → Information → Knowledge → Wisdom
Data: 100
Information: 100 miles
Knowledge: 100 miles is quite a far distance
Wisdom: It's difficult to walk 100 miles, but vehicle
transport makes it manageable
2. Database & Its Elements
Database: An organized, inter-related collection of files/data,
structured so a user can combine information from different
files and retrieve exactly what's needed without knowing the
internal file structure.
Element Description
Table Composed of records and fields; holds
(Datasheet) data about one subject (e.g., a Student
table)
Field A single column of a table — one property
of a record (e.g., NAME , AGE )
A row of a table — all field values for one
Record entity (e.g., one student's complete
details)

3. DBMS (Database Management System)

DBMS = Database + Management System — software that
lets you store and retrieve data efficiently, securely and
reliably.
DBMS handles four broad tasks:
1. Data Definition – creating, modifying, and removing
definitions of data
2. Data Updation – inserting, modifying, deleting the actual
data
3. Data Retrieval – querying and extracting information
4. User Administration – registering/monitoring users,
enforcing security & integrity, handling concurrency and
recovery
Common DBMS Applications
Sector Typical Use
Telecom Call/network usage records, customer
details
Banking Transactions, account info, credit/debit
tracking
Industry Production, sales, purchase records
Sales Customer, product, invoice details
Airlines Reservations & flight schedule info
Education Student, staff, course, attendance, fee
records
Online Product, order & customer data (e.g.,
Shopping Amazon, Flipkart)
Components of a Database System
# Component Role
1 Data The actual data plus metadata
(data about the data)
2 Hardware Physical devices — hard disk, RAM,
ROM, I/O channels
The DBMS program itself — a
3 Software wrapper that provides an easy
interface to store/access/update
data
Database Lets users write commands to
4 Access create, insert, fetch, update and
Language delete data
5 Users DBAs, database designers,
application programmers, end users

4. RDBMS (Relational Database Management

System)
RDBMS is an advanced type of DBMS that stores data in
a structured, tabular format — rows and columns — and
lets related tables be linked together. It came into use in
the 1970s.
A Relational Database is "relational" because values
within a table (and across tables) are related to each
other, which makes it possible to run queries across
multiple tables at once.
Traditional File System — each application (e.g., Library,
Examinations, Registration) stores and manages its own
independent data files, unrelated to other applications' files.
Problems With Traditional File Processing
Problem Explanation
Same data duplicated across
Data Redundancy multiple files → wastes storage,
raises cost
Redundant copies get updated
Data Inconsistency inconsistently, so they no longer
match
Difficult Data Access No standard way to search; user
must know the exact file location
Unauthorized Access No proper access-control
mechanism
No Concurrent A file can be used by only one
Access user at a time
Data Isolation Data scattered across many
files/formats — hard to combine
Dependency on Changing a file's structure means
Application Programs updating every program that
reads it
Difficult to Maintain Hard to guarantee a transaction
Atomicity happens completely or not at all
 Problem Explanation
No Backup & No built-in protection if a file is
Recovery lost or corrupted
Weak security; no easy way to
Limited Data Sharing share data outside one
application
DBMS vs Traditional File System

Basis Traditional File DBMS

System
Cost Cheap Expensive
Scale Good for small Good for large systems
systems
Flexibility Lacks flexibility Flexible
Data Isolated Sharable
Sharing
Security Not secure Highly secure
Integrity Maintains integrity
Integrity problems (needs more design
effort)

5. Advantages & Disadvantages of DBMS

Advantages
 Minimal data redundancy — duplication is
reduced/controlled, saving storage
Data sharing — multiple users/applications can safely
share the same data
Easy searching — simple queries replace custom search
programs
Reduced inconsistency — centralized storage avoids
mismatched duplicate copies
Data integration — combines data from different
locations into one unified view
Easy & concurrent access — fast response, usable by
many people at once
Backup & recovery — automatic periodic backup;
database can be restored after a crash
Better decision-making — well-managed, accessible
data → better-quality information
Data security — access privileges restrict data to
authorized users only
Flexibility — more flexible than file-processing systems
Disadvantages
High initial investment in hardware, software, and trained
staff
Needs technical staff (a DBA) to manage the system,
raising cost
Needs extra disk storage space
Increased complexity can reduce performance/speed,
especially with many concurrent users
6. Three-Level Architecture of DBMS
A DBMS is described using three levels of data abstraction
that hide irrelevant details from users:
1. Internal / Physical Level — the lowest level; describes
how data is actually stored on storage devices (bytes,
blocks), and handles space allocation, compression, and
encryption.
2. Conceptual / Logical Level — describes what data is
stored and the relationships between it (schema,
constraints, security); doesn't concern itself with physical
storage. Maintained by the DBA.
3. External / View Level — the highest level; each user
gets a customised view of just the data relevant to them,
fetched via the conceptual and internal level mapping.
Many views (View 1 … View n) can sit on top of one
conceptual schema.
Database → Internal level → Conceptual level →
External level → User 1, User 2 … User n

7. Database Administrator (DBA) & Types of Users

The DBA makes the strategic/technical decisions for an
organisation's data and provides technical support for
implementing them — overall control of the database at a
technical level.
Roles of the DBA
1. Schema Definition – defines the overall logical schema of
the database
2. Storage Structure & Access Method Definition –
decides how data is physically stored and accessed
 3. Physical Organization Modification – adapts the
database's physical structure to changing needs or to
improve performance
4. Granting of User Access – authorises different users for
different parts of the database
5. Assisting Application Programmers – supports
programmers developing database applications
6. Approving Data Access – decides which users need
access to which parts of the database
7. Monitoring Performance – tracks system performance
and responds to issues
8. Backup & Recovery – ensures periodic backup so data
can be recovered after loss/damage
Types of Users
Category Type Description
Writes application programs
Direct Application (in COBOL, C, C++, Java, or
users Programmer a 4GL) that interact with the
database
Uses a ready-made
Direct application/query interface
users End User to get their task done (e.g., a
railway reservation counter
clerk)
Indirect DBA Overall technical controller
users of the database
 Category Type Description
Indirect System Determines end-user
users Analyst requirements and prepares
specifications for the system

8. Data Dictionary
A Data Dictionary stores metadata — data about the
database's objects — such as:
Table and column names, and their physical storage
details
Constraints (primary key, foreign key, etc.) and
security/access information
Views defined on the data, and when objects were
created/last accessed
Type Description
Active Data Automatically updated by the DBMS
Dictionary whenever the database structure
changes — always stays in sync
Passive Data Stored separately (e.g., as XML/Excel);
Dictionary must be updated manually, so it can fall
out of sync
Example entry: a data dictionary for a Student table might
record ROLL_NO → Number, length 6, Primary Key: Yes ;
NAME → Text, length 30, Primary Key: No .

9. Integrity Constraints
Integrity constraints are rules that maintain the accuracy
and consistency of data in a relational database.
Constraint Meaning
A value must fall within its attribute's
Domain defined domain (data type, range,
Constraint format — string, integer, date, currency,
etc.)
Entity Integrity The primary key of a table can never
Constraint be NULL, since it's used to uniquely
identify each row
Referential A foreign key value in one table must
Integrity match an existing primary key value in
Constraint the related table (or be NULL)
Keys must uniquely identify each
Key Constraint row/entity in a table — a primary key
must be unique and non-null

10. SQL Data Types

Numeric
int – whole numbers
decimal(m,d) – fixed-point number, where m = total
digits, d = digits after the decimal point
Date & Time
date – stores a date as YYYY-MM-DD

time – stores a time as HH:MM:SS

String
 char(n) – fixed-length string. If the value is shorter than
n , blanks are added — but the column always occupies
exactly n bytes.
varchar(n) – variable-length string. Storage equals the
actual length of the value (up to a maximum of n ); no
padding is added.
Choosing the right data type/size matters for storage
efficiency — don't define a field wider than it needs to be.
11. Introduction to SQL
SQL = Structured Query Language — the standard
database language used to interact with an RDBMS.
It was the first commercial language introduced for E. F.
Codd's relational model of the database.
Nearly all modern RDBMS (MySQL, Oracle, Informix,
Sybase, MS Access, SQL Server) use SQL as their
standard query language.
Characteristics of SQL
Uses English-like statements to query the database
Not case-sensitive (though keywords are conventionally
written in UPPERCASE)
A statement can span one line or several lines
Can perform almost all database operations — querying
and administrative tasks like security, backup, and user
management
Based on tuple relational calculus and relational algebra
12. Categories of SQL Commands
 Category Full Form Purpose Commands
Defines/modifies CREATE ,
Data database ALTER ,
DDL Definition structure DROP ,
Language (schema, tables, TRUNCATE ,
objects) RENAME

Data Manipulates the SELECT ,

DML Manipulation actual data inside INSERT ,
UPDATE ,
Language tables DELETE

Manages
DCL Data Control permissions/rights GRANT ,
Language on database REVOKE
objects
Manages COMMIT ,
Transaction transactions and ROLLBACK ,
TCL Control rollback of DML SAVEPOINT ,
Language changes SET
TRANSACTION

13. DDL Commands

Command Purpose
Creates database objects (databases, tables,
CREATE
etc.)
Alters/modifies the structure of an existing
ALTER
object
 Command Purpose
Deletes an entire object — table, index, view,
DROP
procedure, trigger — from the database
Removes all records from a table but keeps its
TRUNCATE
structure; frees the allocated space
RENAME Renames an existing database object
Adds comments/annotations to the data
COMMENT
dictionary
Create a database
CREATE DATABASE database_name;
USE database_name; -- selects the database
to work in

Create a table
CREATE TABLE table_name (
column1 data_type(size),
column2 data_type(size),
column3 data_type(size)
);

Example:
CREATE TABLE Student (
ROLL_NO INT(3),
NAME VARCHAR(30),
ADDRESS VARCHAR(30),
PHONE_NO INT(12),
 AGE INT(2)
);

DROP — deletes an object permanently, structure and data

both; cannot be undone
DROP TABLE table_name;
DROP DATABASE database_name;

TRUNCATE — empties a table's rows only, keeping the table

structure intact
TRUNCATE TABLE table_name;

ALTER TABLE — add, drop, or modify columns/constraints of

an existing table
ALTER TABLE table_name ADD (column_name
datatype);
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name MODIFY column_name
new_datatype;

14. DML Commands

INSERT INTO – adds a new row (only one row per statement;
text/date values need single quotes)
-- Method 1: values only (must match the table's
column order exactly)
INSERT INTO Student VALUES (1, 'RAM', 'DELHI',
'XXXXXXXXXX', 18);

-- Method 2: name the columns (order can differ;

 some columns can be skipped)
INSERT INTO Student (ROLL_NO, NAME, AGE) VALUES
(1, 'RAM', 18);

SELECT – retrieves data; the returned data is called a result

set
SELECT column1, column2 FROM table_name; --
specific columns
SELECT * FROM table_name; -- all
columns

WHERE clause – filters rows using a condition

SELECT column1, column2 FROM table_name WHERE
condition;

Operators usable with WHERE :

Operator Meaning
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
= Equal to
<> Not equal to
BETWEEN Within an inclusive range
 Operator Meaning
Search for a pattern ( % = any length/value of
LIKE
wildcard characters)
IN Match any value in a given list

SELECT * FROM Student WHERE AGE > 18;

SELECT * FROM Student WHERE ROLL_NO BETWEEN 1 AND
3; -- inclusive of 1 and 3
SELECT * FROM Student WHERE NAME LIKE 'S%';
-- names starting with S
SELECT * FROM Student WHERE NAME LIKE '%AM%';
-- names containing AM
SELECT * FROM Student WHERE AGE IN (18, 20);
-- age is 18 or 20

AND / OR / NOT
SELECT * FROM Student WHERE AGE = 18 AND NAME =
'RAM'; -- both conditions must be true
SELECT * FROM Student WHERE NAME = 'RAM' OR AGE =
20; -- either condition true
SELECT * FROM Student WHERE NOT NAME = 'RAM';
-- reverses the condition

ORDER BY – sorts the result set (default is ascending)

SELECT * FROM Student ORDER BY column_name ASC;
-- ascending
SELECT * FROM Student ORDER BY column_name DESC;
-- descending
SELECT * FROM Student ORDER BY col1 ASC, col2
DESC; -- multiple columns
DISTINCT – removes duplicate rows from the result set
SELECT DISTINCT column1, column2 FROM table_name;

UPDATE – modifies existing rows

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

⚠️ Omitting updates every row in the table.

WHERE

DELETE – removes rows

DELETE FROM table_name WHERE condition;

Arithmetic Operators
Operator Meaning Example
+ Addition a+b

- Subtraction a-b

* Multiplication a*b

/ Division a/b

% Modulus (remainder) a%b

Column / Table Alias – a temporary name for a column/table,

for the duration of one query
SELECT ROLL_NO, NAME AS S_NAME FROM Student;
15. Transactions & TCL Commands
A transaction is a group of tasks treated as one unit of work
— it either completes fully (success) or fails as a whole
(failure), never partially.
ACID Properties
Property Meaning
Atomicity All steps of the transaction happen, or none
do — "all or nothing"
Consistency The database moves from one valid state to
another
Isolation Concurrent transactions don't interfere with
each other
Durability Once committed, changes survive even a
system failure

SET TRANSACTION [READ WRITE | READ ONLY]; --

configures a transaction

COMMIT; -- permanently saves all changes

since the last COMMIT/ROLLBACK

ROLLBACK; -- undoes all changes since the last

COMMIT/ROLLBACK

SAVEPOINT savepoint_name; --
marks a point to return to later
ROLLBACK TO SAVEPOINT savepoint_name; --
undoes changes back to that point only
RELEASE SAVEPOINT savepoint_name; --
 removes a savepoint (can no longer roll back to
it)

16. DCL Commands & User Management

-- Create a user
CREATE USER user_name IDENTIFIED BY 'password';

-- GRANT: give access rights on an object to a

user
GRANT privilege_name ON object_name TO {user_name
| PUBLIC | role_name};
-- privilege_name examples: ALL, EXECUTE, SELECT

-- REVOKE: withdraw previously granted privileges

REVOKE privilege_name ON object_name FROM
{user_name | PUBLIC | role_name};

-- Delete a user
DROP USER user_name;

17. Constraints in SQL

Constraint Purpose
NOT NULL Column cannot store a NULL value
DEFAULT Provides a default value when none is
specified on INSERT
Ensures all values in a column are different (a
UNIQUE table can have several UNIQUE columns, and
each can hold one NULL)
 Constraint Purpose
PRIMARY Combines NOT NULL + UNIQUE —
KEY uniquely identifies each row; only one per
table
FOREIGN Links a column to the primary key of another
KEY table — enforces referential integrity
CHECK Ensures all values in a column satisfy a
specific condition
Table-creation example combining several constraints:
CREATE TABLE Student1 (
ROLL_NO INT(6) NOT NULL PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
ADDRESS CHAR(25),
AGE INT(6) NOT NULL CHECK (AGE >=
18),
FEE DECIMAL(18,2) DEFAULT 1000.00
);

Foreign key (linking two tables):

CREATE TABLE Subject (
Subject_id INT(4),
Subject_Name VARCHAR(20),
Roll_No INT(4),
FOREIGN KEY (Roll_No) REFERENCES Student1
(Roll_No)
);

Adding a constraint to an existing table:

 ALTER TABLE table_name ADD CONSTRAINT
constraint_name TYPE (column);

18. Keys in RDBMS

Keys are fields used to: create relationships between tables,
maintain uniqueness, keep data valid/consistent, and speed
up retrieval (via indexes).
Key Definition
Candidate Any attribute (or set of attributes) that could
Key uniquely identify a row; a table can have
several — one is chosen as the primary key
Primary The candidate key selected to uniquely
Key identify each row; cannot be NULL, cannot
repeat; only one per table
Alternate Candidate key(s) that were not chosen as
Key the primary key
Like a primary key (enforces uniqueness),
Unique Key but can hold one NULL, and a table can have
more than one
Composite Two or more columns combined to uniquely
Key identify a row, even if individually the
columns aren't unique
Any set of columns that can uniquely identify
Super Key a row — may include extra, unnecessary
columns. Every candidate key is a super key,
but not every super key is a candidate key
 Key Definition
Foreign A column (or columns) in one table that
Key refers to the primary key of another table,
linking the two
Primary Key vs Candidate Key
Primary Key Candidate Key
A minimal super key; only A relation can have more
one per relation than one candidate key
Cannot contain a NULL Not yet confirmed as
value primary — a candidate for it
Specifies the single most Any attribute that qualifies
important identifying to become the primary key
attribute
A confirmed candidate key Not confirmed to be the
primary key

19. SQL Functions

Aggregate Functions — operate on a set of values in a
column, return one value
Function Purpose
AVG(col) Average of the values
COUNT(col) / Number of rows / non-null values
COUNT(*) ( COUNT(DISTINCT col) for unique
 Function Purpose
values)
SUM(col) Total of the values
MIN(col) Smallest value
MAX(col) Largest value
FIRST(col) First value in the column
LAST(col) Last value in the column

SELECT AVG(MARKS) AS AvgMarks FROM Marks;

SELECT COUNT(*) AS NumStudents FROM Marks;
SELECT COUNT(DISTINCT AGE) AS NumStudents FROM
Marks;

Scalar Functions — operate on a single value per row, return

one value per row
Function Purpose
UCASE(col) Converts text to UPPERCASE
LCASE(col) Converts text to lowercase
Extracts characters from text
MID(col, start,
( length is optional; start is 1-
length)
indexed)
LEN(col) Length of the value
 Function Purpose
ROUND(col, Rounds a number to the given
decimals) number of decimal places
NOW() Current system date & time

SELECT UCASE(NAME) FROM Marks;

SELECT MID(NAME, 1, 3) FROM Marks; -- first 3
characters
SELECT ROUND(MARKS, 0) FROM Marks;
SELECT NOW() FROM Marks;

20. Key Definitions at a Glance

Data redundancy – duplication of data
Data abstraction – hiding irrelevant storage details from
the user
File – a collection of related records
Field – smallest unit of storage; one column value
Record – a row; collection of fields about one subject
Table – a collection of records (rows) and fields
(columns)
Database – a collection of inter-related tables
DBMS – a collection of programs to store and access
data
ACID – Atomicity, Consistency, Isolation, Durability
SQL – Structured Query Language
Constraints – rules limiting what data can go into a
column/table
Data dictionary – metadata about the database's objects
 Query – a request to retrieve data from a database
Result set – the table of data a query returns
View – a virtual/alternate way of presenting table data
21. Quick Revision: True / False
# Statement Answer
1 Data redundancy means repetition or True
duplication of data.
2 SQL statements are case sensitive. False
3 SELECT is a Data Definition Statement. False (it's
DML)
4 A collection of fields is called a record. True
5 A primary key does not allow null value True
as a zero or blank space.
6 COMMIT is a Transaction Control True
statement.
7 A null value is the same as a zero or False
blank space in the column.
8 The DROP TABLE command eliminates True
a table from the database.
The DISTINCT clause eliminates
9 duplicate values present in specific True
columns.
 # Statement Answer
10 The ORDER BY clause is used to sort
rows. True

22. Review Questions

1. What is the difference between Data and Information?
2. What is a Database?
3. What is RDBMS? What are its advantages and
disadvantages?
4. What is the difference between a Traditional File System
and RDBMS?
5. What is the difference between DBMS and RDBMS?
Also make sure you can explain, in your own words:
The three-level architecture of DBMS, and the role &
responsibilities of the DBA
SQL and its characteristics; the difference between char
and varchar
Domain, entity, and referential integrity constraints
The difference between DDL, DML, DCL and TCL
The purpose of the WHERE , DISTINCT , BETWEEN , and
ORDER BY clauses

How to create a table, insert rows, and create/delete

users in SQL
The use of AND , OR , and NOT operators

Chapter 6: PL/SQL (Introduction)

1. What is PL/SQL?
PL/SQL = Procedural Language extension to SQL, used
in Oracle databases. It is a superset of SQL.
Unlike plain SQL, PL/SQL lets a programmer write code in
a procedural format — adding decision-making, iteration
(looping), and other features typical of procedural
programming languages.
It combines SQL's data-manipulation power with
procedural extensions in a block-structured language: a
whole block of statements is passed to the Oracle engine
at once, which increases processing speed and
decreases network traffic.
2. Architecture of PL/SQL
PL/SQL architecture has three main components:
1. PL/SQL Block – the basic unit of PL/SQL code
2. PL/SQL Engine – compiles and executes the PL/SQL
block
3. Database Server – where the actual data resides and
SQL statements are processed
3. Features of PL/SQL
It's a procedural language — supports decision-making,
iteration, and other procedural programming features
Lets developers combine several SQL statements into
a single block, sent to the Oracle engine all at once
Can execute multiple queries in one block using a
single command, increasing speed and reducing traffic
Note: The uploaded pages cover Chapter 5 in full but only
the introductory portion of Chapter 6 — later PL/SQL
topics (blocks, variables, control structures, etc.) weren't
included in these pages.