RDBMS Concepts, MySQL &

PL/SQL — Complete Notes

(Class XII Computer Science)

CHAPTER 5: RDBMS CONCEPTS AND

MYSQL
1. Data vs Information
Term Meaning
Raw, unorganized facts, figures, and
Data numbers (text, word, number). Not yet
meaningful.
Data that has been processed, organized,
Information and arranged in a meaningful way so it
becomes useful for decision-making.
Information = Processed Data. Raw data is collected
and entered for processing; once processed and
arranged in a significant manner, it becomes information.
Example: Roll No 1, Name XYZ, Age 15 (three separate
data items) → combined and organized → becomes
meaningful information about a student.
DIKW Model (Data → Information → Knowledge →
Wisdom)
A model showing the structural/functional relationship
between the four:
Data – raw facts (analogy: 100 units)
Information – processed data (100 miles)
Knowledge – interrelated information
Wisdom – applied knowledge (hardest to "walk" — i.e.,
hardest to attain/use)

2. Database Basics
Database: A collection of inter-related files that are linked
in such a way that information from one file may be
combined/cross-referenced with information from other
files — so that a user gets exact information needed
without duplication.
Table (Datasheet): A database is composed of tables.
Each table holds data about a different but related
subject.
Record: A row in a table — represents all data about one
particular person/item (composed of fields).
Field: A column in a table — represents a single piece of
data (e.g., NAME field) for every record.
Example — Student table fields: ROLL_NO, NAME, ADDRESS,
PHONE, AGE

3. DBMS (Database Management System)

 DBMS = Database + Management System
A DBMS is system software that allows users to store,
retrieve, update, and manage data in a database efficiently
and reliably, without needing to know low-level details of how
data is physically stored.
Tasks DBMS allows (Data Definition Language use):
1. Creation of a database
2. Updating a database
3. Retrieval of information from the database
4. Data Updation — insertion, modification, deletion of
actual data in the database
Applications of DBMS:
| Sector | Use |
|---|---|
| Telecom | Storing call, network usage, customer detail info |
| Banking | Customer info, transactions, credit/debit
statements |
| Industry | Tracking inventory/product info at distribution
centres |
| Airlines | Reservation and flight schedule information |
| Sales | Customer, product, and invoice information |
| Education | Student, staff, course, attendance, fees data |
| Online Shopping | Product details, addresses, preferences
(e.g., Amazon, Flipkart) |

4. Components of a Database System

1. Data — the actual data + metadata (data about data —
name, size, related data etc.)
 2. Hardware — physical devices: I/O channels, hard disks,
RAM, ROM etc.
3. Software — the DBMS software itself (a wrapper around
the physical database, provides an easy interface to
store/access/update data)
4. Database Access Language — used to write commands
to access, insert, update and delete data (submitted to
DBMS for execution)
5. Users — coordinate to form an effective database
system
Types of DBMS Users
Database Administrators (DBA)
End Users
Application Programmers
Database Designers
System Analyst

5. Traditional File Processing System

A method where data is stored in different, independent files
for each application (each managed by a separate program).
Characteristics:
Files carry independent data on each other
COBOL, C, C++ commonly used
Each file has its own set of data — leads to duplication
Less flexible, many limitations
Problems in Traditional File Systems
 Problem Explanation
Same data is duplicated across
Data Redundancy multiple files → wastes storage,
raises cost
Data Inconsistency Duplicated data not updated
everywhere → mismatched values
Unauthorized No proper access control — file
Access accessible by anyone
Difficult Data Locating specific info requires
Access manual/tedious search
Hard to ensure a transaction
Difficult Atomicity completes fully or not at all (rollback
of partial updates is difficult)
Data Isolation Data scattered in various formats
across different files/programs
Dependency on Changing file structure requires
Application rewriting all dependent application
Programs programs
No Backup & No built-in mechanism to
Recovery secure/recover lost data
Limited Data Data is private to each application;
Sharing hard to share securely
No Concurrent Only one user can access a file at a
Access time
DBMS vs Traditional File System
 Aspect DBMS Traditional File
System
Cost Expensive Cheap
Suitable for Large systems Small systems
with few items
Data Sharing Sharable, Isolated, poor
controlled sharing
Searching Fast & easy Slow, tedious
Redundancy Minimal High
(centralized)
Data Integration Unified view at No integration
different locations
Security Highly secure Not secure
Design Effort High design effort Little/no design
effort
Backup/Recovery Automatic No built-in
mechanism
Concurrent Multiple users at One user at a
Access once time
Flexibility Flexible Lacks flexibility
Advantages of DBMS: Data integration, easy access to data,
concurrent access, better backup & recovery, improved
decision making, data security, flexibility.
Disadvantages of DBMS: High initial cost
(hardware/software/trained staff), requires technical staff,
needs extra disk space, complex, and can be slower due to
added complexity.

6. RDBMS (Relational Database Management

System)
RDBMS = a DBMS designed specifically for relational
databases (data stored in structured tables of rows and
columns).
Came into existence in the 1970s (advanced version of
DBMS).
Allows data to be related across multiple tables — makes
it easy to locate and access specific values because
tables may be related to each other.
A relational DBMS lets you create, update, and
administer a relational database; the row-based table
structure connects related data and maintains security,
accuracy, integrity, and consistency.
Examples: MySQL, Oracle, SQL Server

7. Three-Level Architecture of DBMS

Purpose: to hide irrelevant/complex internal storage details
from users (data abstraction), while still providing users an
efficient interface.
 Level Also Description
Called
Lowest level — describes how
data is actually stored on
Internal Physical physical devices (bytes,
Level Level gigabytes, compression,
encryption techniques).
Responsible for allocating
storage space.
Whole design of the database
— schema, relationships
Conceptual Logical among data, constraints, and
Level Level security are described here.
Does not care how data is
physically stored. Maintained
by the DBA.
Highest level, closest to the
user. Shows only the relevant
External View part of the database to each
Level Level user via "views." Different
users see different views; hides
unrelated details.

8. DBA — Database Administrator

The DBA is the person who makes strategic and policy
decisions regarding the enterprise's data and provides
technical support for implementing them. DBA is the primary
resource with overall control of both the system and the data.
Role of the DBA (functions):
1. Schema Definition
2. Storage Structure and Access Method Definition
3. Physical Organization Modification
4. Assisting Application Programmers
5. Approving Data Access
6. Monitoring Performance
7. Liaising with Users
8. Backup and Recovery
Types of DBMS Users
Direct Users:
a) Direct — end users who interact directly via GUI-based
systems
b) Indirect — application programmers who write DB
programs in COBOL, C, C++, Java, PL/1, or 4GL/SQL
Indirect Users: e.g., System Analyst — determines
requirements of end users, prepares specs for
transactions that meet those requirements.

9. Data Dictionary
A data dictionary contains metadata i.e. data about the data
stored in the database — table names, column info,
constraints, storage, security, views, etc. It is like a
"bookkeeping" system for the database — the DBMS itself
doesn't interact directly with actual data but uses the data
dictionary to manage it.
Why we need a Data Dictionary:
 To easily identify/understand info about a database object
Contains physical info (storage, constraints)
Contains info about table views
Table constraints (primary key, foreign key attributes)
Types of Data Dictionary
| Type | Description |
|---|---|
| Active Data Dictionary | Automatically updated by the
DBMS whenever the database structure changes — always in
sync. |
| Passive Data Dictionary | Stored separately (e.g., as XML
or Excel file); must be manually updated — can go out of sync
with the actual database. |

10. Integrity Constraints

Rules that ensure accuracy and consistency of data in a
relational database; used to maintain quality of information.
Integrity Constraint
├── Domain Constraint
├── Entity Integrity Constraint
├── Referential Integrity Constraint
└── Key Constraint

Constraint Meaning
Domain The value stored for an attribute must
Constraint come from its defined domain (data
type, range, format).
 Constraint Meaning
Entity Integrity Primary key value cannot be NULL
Constraint (needed to uniquely identify each row).
Referential If a foreign key exists in Table 2
Integrity referencing Table 1's primary key, every
Constraint value of that foreign key must exist in
Table 1 (or be NULL).
Key Constraint Keys must be unique and non-null —
used to uniquely identify entities.

11. SQL — Structured Query Language

SQL is a standardized language for interacting with
RDBMS (created by E. F. Codd as part of the relational
model).
All major RDBMS (MySQL, Oracle, Sybase, Informix, MS
Access, SQL Server) use SQL as their standard database
language.
SQL is not case-sensitive.
Four Categories of SQL Commands
 Category Full Form Purpose Commands
CREATE ,
ALTER ,
Data Define/modify DROP ,
DDL Definition database TRUNCATE ,
Language structure/schema RENAME ,
COMMENT

Data Manipulate data SELECT ,

INSERT ,
DML Manipulation present in the UPDATE ,
Language database DELETE

Data Control Manage access ,

DCL Language rights/permissions GRANT

to DB objects REVOKE

COMMIT ,
Transaction Manage changes ROLLBACK ,
TCL Control made by DML SAVEPOINT ,
Language statements SET
TRANSACTION

12. SQL Data Types

A. Numeric Data Types
| Type | Use |
|---|---|
| int | For numbers without decimal |
| Decimal(m,d) | For floating/real numbers (m = total digits, d
= digits after decimal) |
B. Date & Time
| Type | Format |
|---|---|
| date | YYYY-MM-DD |
| time | HH:MM:SS |
C. String Data Types
| Type | Use |
|---|---|
| char(n) | Fixed-length string, max n characters |
| varchar(n) | Variable-length string, max n characters |
CHAR vs VARCHAR
| CHAR | VARCHAR |
|---|---|
| Fixed length | Variable length |
| If value stored is shorter than the defined length, blanks are
added — total size stays n bytes | If value is shorter than max
length, no blanks are padded — size stored is only as much as
the value needs |

13. Key SQL Statements & Syntax

Database & Table
CREATE DATABASE database_name;
USE database_name;

CREATE TABLE table_name (

column1 data_type(size),
column2 data_type(size)
);
 DROP TABLE table_name; -- deletes table +
structure
DROP DATABASE database_name;

TRUNCATE TABLE table_name; -- deletes all

rows, keeps structure

Insert
INSERT INTO table_name VALUES (value1, value2,
...);
INSERT INTO table_name (col1, col2) VALUES
(value1, value2);

Select & Where

SELECT column1, column2 FROM table_name;
SELECT * FROM table_name;
SELECT column1 FROM table_name WHERE column2
operator value;

Operators usable with WHERE

| Operator | Meaning |
|---|---|
| > | Greater than |
| >= | Greater than or equal to |
| < | Less than |
| <= | Less than or equal to |
| = | Equal to |
| <> | Not equal to |
| BETWEEN | Inclusive range |
| LIKE | Pattern search |
| IN | Multiple possible values |
 SELECT * FROM Student WHERE ROLL_NO BETWEEN 1 AND
3;
SELECT * FROM Student WHERE NAME LIKE 'S%';
-- starts with S
SELECT NAME FROM Student WHERE Age IN (18, 20);

AND / OR / NOT
SELECT * FROM Student WHERE Age = 18 AND NAME =
'RAM';
SELECT * FROM Student WHERE NAME = 'RAM' OR AGE =
20;
SELECT * FROM Student WHERE NOT NAME = 'RAM';

AND → all conditions must be True

OR → at least one condition must be True
NOT → returns False if the condition is True
ORDER BY
SELECT * FROM Student ORDER BY ROLL_NO; -
- ASC by default
SELECT * FROM Student ORDER BY ROLL_NO DESC;
SELECT * FROM Student ORDER BY column1 ASC,
column2 DESC;

ALTER 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;
UPDATE
UPDATE table_name SET column1 = value1 WHERE
condition;
UPDATE Student SET NAME = 'DEEPAK' WHERE Age =
20;

⚠ Without a WHERE clause, all rows get updated.

DISTINCT
SELECT DISTINCT column_name FROM table_name;

Removes duplicate values from the result.

Arithmetic Operators
| Operator | Meaning |
|---|---|
| + | Addition |
| - | Subtraction |
| * | Multiplication |
| / | Division |
| % | Modulus (remainder) |
Alias (temporary name)
SELECT ROLL_NO, NAME AS S_NAME FROM Student;

Used to make table/column names more readable; doesn't

change the actual name in the database.

14. Transactions & TCL

A transaction groups a set of tasks into a single execution
unit. It succeeds only if all the tasks succeed; if any task fails,
the transaction fails (all steps must complete — atomicity).
SET TRANSACTION [READ WRITE | READ ONLY];
COMMIT; -- saves changes
permanently
ROLLBACK; -- reverts
changes since last COMMIT/ROLLBACK
SAVEPOINT savepoint_name; -- marks a point
to roll back to
ROLLBACK TO savepoint_name;
RELEASE SAVEPOINT savepoint_name;-- removes a
savepoint

ACID Properties of a Transaction

| Property | Meaning |
|---|---|
| Atomicity | Transaction is all-or-nothing |
| Consistency | DB moves from one valid state to another |
| Isolation | Concurrent transactions don't interfere with each
other |
| Durability | Once committed, changes are permanent even
after a crash |

15. User Accounts & Access Control (DCL)

CREATE USER user_name IDENTIFIED BY 'password';

GRANT privilege_name ON object_name TO {user_name

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

GRANT — gives access rights to a user

REVOKE — takes away previously granted rights

Privileges: ALL , EXECUTE , SELECT etc.

Objects: TABLE , VIEW , STORED PROC , SEQUENCE

16. Table-Level & Column-Level Constraints

Constraint Purpose
NOT NULL Column cannot store NULL
DEFAULT Provides a default value when none is
specified
UNIQUE Prevents two rows from having identical
values in a column
PRIMARY Uniquely identifies each row (combination
KEY of NOT NULL + UNIQUE)
FOREIGN Links two tables; value must match a
KEY Primary Key value in another table
CHECK Ensures a value satisfies a given condition

CREATE TABLE Student1 (

Roll_No INT(6) NOT NULL PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Age INT(4) NOT NULL CHECK (AGE >= 18),
 Fee DECIMAL(18,2) DEFAULT 1000.00
);

-- Foreign Key (linking Subject table to

Student1)
CREATE TABLE Subject (
Subject_id INT(6) NOT NULL,
Name VARCHAR(20) NOT NULL,
Roll_No INT(4),
PRIMARY KEY (Subject_id),
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);

17. Types of Keys

Key Definition
Candidate Any column(s) that can qualify to uniquely
Key identify each record; a table can have
multiple candidate keys.
Primary The candidate key selected by the table
Key designer to uniquely identify rows; cannot
contain NULL; only one per table.
Alternate A candidate key not chosen as the primary
Key key.
 Key Definition
Similar to primary key but can allow one
Unique Key NULL value; can have multiple unique keys
per table.
A key made of two or more columns
Composite combined to uniquely identify a row
Key (individual columns may not be able to do so
alone).
A set of columns (may include extra, non-
Super Key essential columns) that can uniquely identify
a row — every candidate key is a super key,
but not vice versa.
A column in one table that refers to the
Foreign Key Primary Key of another table — enforces
referential integrity.
Primary Key vs Candidate Key
| Primary Key | Candidate Key |
|---|---|
| Only one per relation | A relation can have multiple
candidate keys |
| Cannot contain NULL | Can contain NULL (if not selected as
primary) |
| Confirmed to be the table's identifying attribute | Only a
specification/possibility for being the primary key |

18. SQL Functions

A. Aggregate Functions (operate on a set of values → return
a single value)
Function Purpose Example
Average of
AVG() a numeric SELECT AVG(MARKS) AS

column AvgMarks FROM Marks;

SELECT COUNT(*) AS

Number of NumStudents FROM Marks;

<br> SELECT
COUNT()
rows COUNT(DISTINCT AGE) FROM
Marks;

Total of SELECT SUM(AGE) AS

SUM()
values TotalAge FROM Marks;

Minimum SELECT MIN(MARKS) AS

MIN()
value MinMarks FROM Marks;

Maximum SELECT MAX(MARKS) AS

MAX()
value MaxMarks FROM Marks;

First value in SELECT FIRST(MARKS) AS

FIRST()
the column MarksFirst FROM Marks;

Last value in SELECT LAST(MARKS) AS

LAST()
the column MarksLast FROM Marks;

B. Scalar Functions (operate on single user-input values →

return a single value)
 Function Purpose Example
Converts to SELECT
UCASE()
uppercase UCASE(NAME) FROM
Marks;

Converts to SELECT
LCASE()
lowercase LCASE(NAME) FROM
Marks;

Extract text
MID(col,
from a field SELECT
start,
(start position MID(NAME,1,3)
length)
is 1-based) FROM Marks;

Length of a SELECT LEN(NAME)

LEN()
field's value FROM Marks;

ROUND(col, Rounds a SELECT

decimals) numeric field ROUND(MARKS,0)

FROM Marks;

Current system SELECT NOW()

NOW()
date & time FROM table_name;

Note: ROUND() follows IEEE 754 standard — values like

5.5 and 6.5 both round to the nearest even integer (i.e.,
both round to 6).

19. Quick Definitions (Summary)

Data: raw, unorganized fact.
Information: processed data that is meaningful.
Database: collection of inter-related files.
DBMS: collection of programs to store & access data
efficiently.
RDBMS: DBMS that stores data as related tables.
Data abstraction: process of hiding irrelevant details
from the user.
Data schema: overall design of the database.
Constraints: rules enforced on data columns of a table.
Data dictionary: metadata about the stored data.
Data redundancy: duplication of data.
File: collection of records hiding irrelevant details.
Field: smallest unit of storage — a column.
Column: collection of values for a particular attribute in a
table.
Record (tuple): a row — collection of column values.
Table: collection of records (rows) and fields (columns).
Query: a request/command to retrieve data from a
database.
Result set: the data returned from a query.
View: an alternate/logical way to see a table (built from a
query).
Transaction: a collection of DB operations treated as one
unit.
COMMIT: marks successful end of a transaction.
ROLLBACK: marks unsuccessful end/undo of a
transaction.
Integrity Constraints: rules enforced on data to maintain
accuracy & consistency.
CHAPTER 6: PL/SQL (Introduction)
What is PL/SQL?
PL/SQL = Procedural Language extension of SQL, used
in Oracle.
Unlike plain SQL, PL/SQL allows the programmer to write
code using procedural elements — decision-making
( IF ), iteration (loops), etc.
Full form: "Procedural Language extensions to SQL"
PL/SQL is a superset of SQL — combines SQL's data
manipulation power with the processing power of
procedural languages, to create super-powerful SQL
queries.
Combines the block-structure of a procedural language
with the power of SQL — increases processing speed and
decreases network traffic (since a whole block of
statements is sent to the Oracle engine at once, instead of
one query at a time).
Architecture of PL/SQL
PL/SQL architecture mainly consists of three components:
1. PL/SQL Block
2. PL/SQL Engine
3. Database Server
Features of PL/SQL
PL/SQL is basically a procedural language, providing
functionality of decision-making, iteration, and many more
 features of procedural programming languages.
PL/SQL can execute a number of queries in one block
using a single command.

Textbook Exercise Answers (from PDF)

True/False
1. Data redundancy means repetition or duplication of data.
— True
2. SQL statements are case sensitive. — False
3. SELECT is a Data Definition Statement. — False
4. DISTINCT clause eliminates the duplicate values present
in any specific columns. — True
5. Primary key does not allow null value in the column. —
True
6. Null value is same as a zero or blank space. — False
7. COMMIT is a Transaction control statement. — True
8. DROP TABLE command is used to delete a table from
database. — True
9. Collections of fields is called a Record. — True
10. The ORDER BY clause is used to sort the rows. — True