Jahangirnagar University — Department of CSE — Admission: 2015 (PMSCS Program)প্রশ্ন ও সমাধান

A database schema is the fixed logical design/blueprint of a database (tables, columns, data types, keys, constraints, relationships), while a database instance is the actual data stored at one particular moment — a snapshot that changes constantly.

Analogy: the schema is like a house's architectural blueprint (rarely changes); the instance is like the furniture and people inside the house right now (changes every minute).

Aspect	Schema	Instance
What it represents	Structure / design of the database	Actual data present at a given time
Also called	Intension of the database	Extension (state) of the database
Frequency of change	Rare — only via DDL (ALTER, CREATE)	Continuous — every INSERT/UPDATE/DELETE
Analogy	Type definition / class	Object / row values
Example	STUDENT(roll INT, name VARCHAR(30), dept VARCHAR(20))	(101, "Rahim", "CSE"), (102, "Karim", "EEE")

erDiagram
    STUDENT {
        int roll PK
        string name
        string dept
    }

The diagram above is the schema. The instance is the set of rows it currently holds, e.g. {(101, Rahim, CSE), (102, Karim, EEE)} — if a new student is admitted the instance changes immediately, but the schema (the table's structure) stays the same.

Exam takeaway: Schema = structure (invariant), Instance = data (variant) — a schema is defined once at design time; an instance is the live state of the database and changes with every transaction.

SQL (Structured Query Language) is the standard declarative language used to define, manipulate, control access to, and query data stored in a relational database. Almost every RDBMS (MySQL, Oracle, PostgreSQL, SQL Server, etc.) implements SQL as its core interface language.

SQL statements are classified into three broad categories:

Category	Full Form	Purpose	Key Commands
DDL	Data Definition Language	Defines/modifies the database's structure	CREATE, ALTER, DROP, TRUNCATE
DML	Data Manipulation Language	Manipulates the data stored inside tables	SELECT, INSERT, UPDATE, DELETE
DCL	Data Control Language	Controls access rights/permissions	GRANT, REVOKE

Examples:

-- DDL: define structure
CREATE TABLE Student(roll INT PRIMARY KEY, name VARCHAR(30));
ALTER TABLE Student ADD dept VARCHAR(20);

-- DML: work with data
INSERT INTO Student VALUES (101, 'Rahim', 'CSE');
SELECT * FROM Student WHERE dept = 'CSE';
UPDATE Student SET dept = 'EEE' WHERE roll = 101;
DELETE FROM Student WHERE roll = 101;

-- DCL: manage privileges
GRANT SELECT ON Student TO 'analyst';
REVOKE SELECT ON Student FROM 'analyst';

Note (bonus, beyond the three asked): many textbooks also identify a fourth category, TCL (Transaction Control Language) — COMMIT, ROLLBACK, SAVEPOINT — used to manage the atomicity of a group of DML statements.

Exam takeaway: DDL shapes the schema, DML works the data, DCL manages permissions — remembering "Define → Manipulate → Control" fixes the order.