DBMS-এর গঠন — three-schema architecture আর data independence

১ · Schema আর instance — নকশা বনাম এই মুহূর্তের অবস্থা

আগের দুই পর্বে দেখেছেন, ডেটাবেস কেন দরকার আর ফাইলে ডেটা রাখার সমস্যাগুলো কী। এবার প্রশ্ন হলো — একটি DBMS ভেতরে কীভাবে সাজানো, যাতে সেই সমস্যাগুলো আর না থাকে? উত্তরের প্রথম ধাপ একটি সরল কিন্তু গভীর ভাগ: নকশা আর ডেটা আলাদা।

পাড়ার মুদি দোকানের বাকির খাতাটি কল্পনা করুন। খাতার প্রথম পাতায় দোকানদার কলাম টেনে লিখে রেখেছেন — “তারিখ, ক্রেতার নাম, জিনিস, দাম, পরিশোধ।” এই কলামের কাঠামো বছরের পর বছর একই থাকে। কিন্তু প্রতিদিন নতুন নতুন লাইন যোগ হয়, পুরোনো বাকি কেটে দেওয়া হয়। কলামের কাঠামো হলো schema; আজকের তারিখে খাতায় যত লাইন আছে, সেটি instance.

এই কোর্সের বেশিরভাগ উদাহরণ চলবে একটি ছোট অনলাইন দোকানের ডেটাবেসে, যার নাম আমরা দিয়েছি db-shop. এতে পাঁচটি টেবিল: customers (গ্রাহক — customer_id, name, city, phone, joined_on), categories (পণ্যের ধরন), products (পণ্য — product_id, name, category_id, price টাকায়, stock মজুত), orders (অর্ডার — কে, কবে, status) আর order_items (কোন অর্ডারে কোন পণ্য কয়টি, কত দামে)। নিচের editor-এর “নমুনা ডেটা” বাক্স খুলে পুরো ডেটা দেখতে পারেন।

নিচের কোডটি পাতাতেই, আসল SQLite ইঞ্জিনে চলে। প্রতিবার চালালে একদম নতুন একটি ডেটাবেস তৈরি হয়, নমুনা ডেটা ঢোকে, তারপর আপনার লেখা স্টেটমেন্টগুলো একে একে চলে। কোড বদলে দেখতে পারেন; “আগের কোড” বোতাম আসল কোড ফিরিয়ে আনে।

-- instance: কয়টি সারি? schema: কয়টি column?
SELECT COUNT(*) AS row_count FROM products;
SELECT COUNT(*) AS column_count FROM pragma_table_info('products');

INSERT INTO products VALUES (111, 'Tea 400g', 4, 380, 50);

SELECT COUNT(*) AS row_count FROM products;
SELECT COUNT(*) AS column_count FROM pragma_table_info('products');

এই ভাগটা কেন এত জরুরি? কারণ অ্যাপ্লিকেশন প্রোগ্রাম লেখা হয় schema দেখে, instance দেখে নয়। “products টেবিলে price নামে একটি column আছে” — এই চুক্তির ওপর ভরসা করে হাজার লাইন কোড লেখা হয়। Instance যতই বদলাক, চুক্তি ঠিক থাকলে কোড চলবে। আর schema বদলানোর দরকার পড়লে কী হবে — এই প্রশ্নের উত্তর দিতেই তৈরি হয়েছে তিন-স্তরের নকশা।

২ · তিন স্তরের নকশা — ANSI/SPARC three-schema architecture

একটি বিশ্ববিদ্যালয়ের কথা ভাবুন। একই শিক্ষার্থীর তথ্য তিন রকম মানুষ তিন রকম চোখে দেখেন। হিসাব শাখা (accounts office) জানতে চায় — কার কত টাকা বকেয়া। রেজিস্ট্রার অফিস জানতে চায় — কে কোন কোর্সে কী গ্রেড পেয়েছে। আর আইটি বিভাগের ডেটাবেস প্রশাসক (DBA) ভাবেন — এই লাখো সারি ডিস্কে কোথায়, কোন index-এ, কোন সার্ভারে।

একটিমাত্র নকশা দিয়ে তিনজনকে খুশি রাখা কঠিন। আবার তিনটি আলাদা ফাইল রাখলে ফিরে আসবে পর্ব ০২-এর সেই পুরোনো সমস্যা — একই তথ্য তিন জায়গায়, তিন রকম। সমাধান হিসেবে ১৯৭০-এর দশকে আমেরিকার মান-নির্ধারণী সংস্থা ANSI-র একটি study group প্রস্তাব করে three-schema architecture: ডেটা থাকবে একবারই, কিন্তু তার বর্ণনা থাকবে তিন স্তরে।

নিচের ডায়াগ্রামে একই বিশ্ববিদ্যালয়ের ডেটাবেস ওপর থেকে নিচে দেখুন। “পরে” চাপুন — প্রতিটি স্তরে বর্ণনাটি কেমন, আর কে সেটি দেখেন, খেয়াল করুন।

student_id │ name           │ term        │ due
2101       │ Arif Hossain   │ 2025-Fall   │ 12000
2203       │ Fahim Karim    │ 2025-Fall   │ 30500
-- নেই: CGPA, গ্রেড, কোর্সের তালিকা

student_id │ name           │ course_id │ grade
2101       │ Arif Hossain   │ CSE201    │ A
2101       │ Arif Hossain   │ CSE301    │ A-
-- নেই: ফি, বকেয়া, ব্যাংকের তথ্য

students    (student_id, name, dept_id, batch, cgpa)
courses     (course_id, title, credits)
enrollments (student_id, course_id, term, grade)
fees        (student_id, term, amount, paid)

fee_due_view    = students ⋈ fees,  due = amount − paid
transcript_view = students ⋈ enrollments

university.db   page size 4096 bytes
students     → B+ tree, key = student_id
enrollments  → B+ tree, key = (student_id, course_id, term)
idx_students_dept  → index on students(dept_id)
fees         → আলাদা disk-এ (SSD), রাতে backup

Mapping — স্তরগুলোর মাঝের অনুবাদক

তিনটি স্তর থাকলেই হবে না; একটি স্তরের অনুরোধ পাশের স্তরের ভাষায় অনুবাদ করতে হবে। এই অনুবাদের নিয়মকে বলে mapping. দুটি mapping আছে:

• External–conceptual mapping: “fee_due_view-এর due মানে fees.amount − fees.paid, আর name আসে students থেকে।” SQL-এ এই mapping লেখা হয় view-এর সংজ্ঞায় — পরের অংশে নিজে লিখবেন।
• Conceptual–internal mapping: “students টেবিলের সারিগুলো university.db-এর এই page-গুলোতে, student_id ক্রমে।” এটি DBMS নিজে রাখে, আপনি সাধারণত শুধু CREATE INDEX বা storage-এর কিছু option দিয়ে প্রভাব ফেলেন।

Mapping-এর একটি দাম আছে: প্রতিটি কোয়েরিকে স্তর পার হয়ে অনুবাদ হতে হয়, তাতে সামান্য সময় যায়। কিন্তু এই দামের বিনিময়ে যা পাওয়া যায় — স্বাধীনভাবে প্রতিটি স্তর বদলানোর ক্ষমতা — সেটি অনেক বেশি মূল্যবান। অংশ ৪-এ সেটাই দেখব।

৩ · External view হাতে-কলমে — CREATE VIEW

SQL-এ external schema বানানোর প্রধান হাতিয়ার হলো CREATE VIEW. View হলো নাম দেওয়া একটি SELECT কোয়েরি, যাকে পরে টেবিলের মতো ব্যবহার করা যায়। সাধারণ view নিজে কোনো ডেটা রাখে না — প্রতিবার ব্যবহারের সময় মূল টেবিল থেকে হিসাব করে। (পর্ব ২৪-এ view আর materialized view বিস্তারিত দেখবেন।)

ধরুন দোকানের গুদাম (warehouse) বিভাগ শুধু জানতে চায় কোন পণ্য কয়টি মজুত আছে। দাম জানার দরকার তাঁদের নেই, আর দাম দেখালে বরং ঝুঁকি। তাঁদের জন্য একটি external view:

-- গুদামের external view: দাম লুকানো
CREATE VIEW stock_view AS
  SELECT product_id, name, stock
  FROM products;

-- গুদামের লোক শুধু view জানেন, মূল টেবিল নয়
SELECT * FROM stock_view WHERE stock < 10;

এবার হিসাব শাখার view. তাঁরা প্রতিটি অর্ডারের মোট টাকা চান, বাতিল অর্ডার বাদে। “মোট টাকা” কোনো টেবিলে রাখা নেই — order_items-এর quantity × unit_price যোগ করে বের করতে হয়। এখানে JOIN আর GROUP BY ব্যবহার হয়েছে, যেগুলো পর্ব ১৮ ও ১৯-এ ধাপে ধাপে শিখবেন; আপাতত শুধু দেখুন view কীভাবে জটিলতা লুকিয়ে রাখে।

-- হিসাব শাখার external view: প্রতি অর্ডারের মোট টাকা
CREATE VIEW order_totals AS
  SELECT o.order_id, o.order_date,
         SUM(i.quantity * i.unit_price) AS total
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.order_id;

SELECT * FROM order_totals WHERE total > 5000;

৪ · Data independence — নিচের স্তর বদলালে ওপরের স্তর কি ভাঙে?

তিন স্তর আর mapping-এর আসল উদ্দেশ্য একটাই: data independence. এর অর্থ — এক স্তরের schema বদলালেও তার ওপরের স্তর আর অ্যাপ্লিকেশন প্রোগ্রাম যেন বদলাতে না হয়। শুধু মাঝের mapping বদলালেই চলবে। দুই রকম data independence আছে:

কোন পরিবর্তন কী ভাঙে, নিচের ডায়াগ্রামে একটি একটি করে দেখুন। প্রতিটি ধাপে দোকানের ডেটাবেসে একটি পরিবর্তন করা হচ্ছে; গুদামের stock_view আর অ্যাপের কোয়েরির কী হয়, খেয়াল করুন।

Physical independence নিজে চালিয়ে দেখুন — index যোগ, কোয়েরি একই

SQLite-এ EXPLAIN QUERY PLAN লিখে যেকোনো কোয়েরির আগে বসালে DBMS কোয়েরিটি চালায় না, বরং বলে দেয় কীভাবে চালাত। নিচে একই কোয়েরি দুবার পরীক্ষা করা হচ্ছে — index বানানোর আগে আর পরে। কোয়েরির লেখা এক অক্ষরও বদলানো হয়নি।

EXPLAIN QUERY PLAN
  SELECT name, price FROM products WHERE price = 550;

-- internal schema বদলালাম: নতুন index
CREATE INDEX idx_products_price ON products(price);

EXPLAIN QUERY PLAN
  SELECT name, price FROM products WHERE price = 550;

SELECT name, price FROM products WHERE price = 550;

Logical independence — যোগ করা নিরাপদ, মুছে ফেলা নয়

এবার conceptual schema বদলাই। প্রথমে products-এ নতুন একটি column যোগ করি, তারপর গুদামের পুরোনো view আগের মতো চলে কি না দেখি।

CREATE VIEW stock_view AS
  SELECT product_id, name, stock FROM products;

-- conceptual schema বদলাল: নতুন column
ALTER TABLE products ADD COLUMN weight_kg REAL;

-- গুদামের পুরোনো কোয়েরি, এক অক্ষরও না বদলে
SELECT * FROM stock_view WHERE stock < 10;

এবার উল্টোটা: যে column-এর ওপর view নির্ভর করে, সেটিই মুছে ফেলার চেষ্টা। এই কোডে ইচ্ছা করেই একটি error আছে।

CREATE VIEW stock_view AS
  SELECT product_id, name, stock FROM products;

-- view যে column ব্যবহার করে, সেটিই মুছছি
ALTER TABLE products DROP COLUMN stock;

৫ · DBMS-এর ভাষা — DDL, DML, DCL আর TCL

তিন স্তরের সাথে মিল রেখে DBMS-এর সাথে কথা বলার ভাষাকেও কাজ অনুযায়ী ভাগ করা হয়। SQL একটিমাত্র ভাষা, কিন্তু তার স্টেটমেন্টগুলো চারটি দলে পড়ে:

কিছু বইয়ে SELECT-কে আলাদা করে DQL (Data Query Language) বলা হয়, আবার ANSI/SPARC-এর মূল ভাষায় internal স্তরের জন্য আলাদা SDL (storage definition language)-এর কথাও আছে। আধুনিক DBMS-এ SDL আলাদা ভাষা নয় — CREATE INDEX, tablespace বা storage option-এর মতো কিছু DDL স্টেটমেন্টই সেই কাজ করে। পরীক্ষায় এই চার দলের নাম আর উদাহরণ প্রায়ই চাওয়া হয়।

Procedural বনাম declarative DML

DML দুই রকম হতে পারে। Procedural (navigational) DML-এ প্রোগ্রামারকে বলতে হয় কীভাবে ডেটা খুঁজতে হবে — “প্রথম record-এ যাও, পরেরটায় যাও, এই link ধরে অন্য record-এ যাও।” ১৯৬০–৭০-এর দশকের CODASYL network ডেটাবেসে এভাবেই লেখা হতো। Declarative DML-এ বলতে হয় শুধু কী চাই — “৫৫০ টাকার পণ্যগুলোর নাম চাই।” কীভাবে খুঁজবে, সেটি DBMS-এর optimizer ঠিক করে।

-- procedural (navigational) ধারা: কীভাবে খুঁজব, ধাপে ধাপে
open products file
record = FIND FIRST product
while record exists:
    if record.price = 550: print record.name
    record = FIND NEXT product

-- declarative ধারা (SQL): শুধু কী চাই
SELECT name FROM products WHERE price = 550;

দেখলেন তো, অংশ ৪-এ index যোগ করার পর SQL কোয়েরি না বদলেই দ্রুত হয়ে গেল? Procedural কোড হলে সেটা হতো না — প্রোগ্রামার নিজে “ফাইলের শুরু থেকে শেষ পর্যন্ত পড়ো” লিখে রেখেছেন, index এলেও কোড সেটা ব্যবহার করবে না। Declarative ভাষাই physical data independence-কে বাস্তবে সম্ভব করে। এটি পর্ব ০৪-এ data model-এর ইতিহাসে আবার আসবে।

TCL হাতে-কলমে — ভুল করে সব দাম দ্বিগুণ!

একটি transaction হলো কয়েকটি কাজকে একসাথে বাঁধা একক — হয় সবগুলো স্থায়ী হবে, নয়তো কোনোটিই না। নিচে ভুল করে সব পণ্যের দাম দ্বিগুণ করা হলো, তারপর ROLLBACK দিয়ে ফিরিয়ে আনা হলো।

BEGIN;                                  -- TCL
UPDATE products SET price = price * 2;  -- DML (ভুল!)
SELECT name, price FROM products WHERE product_id = 101;
ROLLBACK;                               -- TCL: সব বাতিল
SELECT name, price FROM products WHERE product_id = 101;

DCL — কার কী অনুমতি

SQLite একটি ফাইলের ভেতরে চলে, তাই এতে user বা GRANT নেই — ফাইল খুলতে পারলেই সব পারা যায় (অনুমতি নিয়ন্ত্রণ করে অপারেটিং সিস্টেম)। Client-server DBMS-এ DCL খুব গুরুত্বপূর্ণ। নিচের কোডটি PostgreSQL-এর, এই পাতায় চালানো যাবে না:

-- গুদামের জন্য একটি role, শুধু stock_view পড়ার অনুমতি
CREATE ROLE warehouse LOGIN PASSWORD 'change-me';
GRANT SELECT ON stock_view TO warehouse;
REVOKE ALL ON products FROM warehouse;   -- মূল টেবিলে হাত নেই

এভাবেই view (external schema) আর DCL মিলে আসল নিরাপত্তা দেয়: গুদামের লোক দাম দেখতে পান না, কারণ মূল টেবিলে তাঁর অনুমতিই নেই। MySQL-এ একই কাজের লেখা একটু আলাদা (CREATE USER, তারপর GRANT SELECT ON shop.stock_view TO …)।

৬ · DBMS-এর ভেতরের যন্ত্রপাতি — query processor আর storage manager

এবার DBMS প্রোগ্রামটির ভেতরে ঢুকি। বড় যেকোনো DBMS-কে মোটামুটি দুটি বড় যন্ত্রে ভাগ করা যায়, আর দুজনের মাঝে একটি “খাতা”:

Storage manager-এর অংশগুলো এক নজরে:

• Buffer manager — ডিস্কের page-এর কপি মেমরিতে (buffer pool) রাখে। মেমরি ডিস্কের চেয়ে বহু গুণ দ্রুত, তাই একই page বারবার ডিস্ক থেকে পড়া এড়ানোই এর কাজ। জায়গা ফুরোলে কোন page সরাবে তাও ঠিক করে (পর্ব ৩৯)।
• File manager — ডিস্কে ডেটাবেস ফাইলের জায়গা বরাদ্দ করে আর নির্দিষ্ট page পড়ে-লেখে।
• Transaction manager — প্রতিটি transaction-এর শুরু, COMMIT আর ROLLBACK-এর হিসাব রাখে।
• Lock manager — একসাথে অনেকে একই ডেটায় কাজ করলে কে কখন হাত দিতে পারবে, lock দিয়ে ঠিক করে (পর্ব ৪৬)।
• Recovery manager — log রাখে, যাতে বিদ্যুৎ চলে গেলেও COMMIT হওয়া কাজ না হারায় আর অর্ধেক কাজ মুছে যায় (পর্ব ৪৮)।

এবার একটি কোয়েরিকে এই যন্ত্রপাতির ভেতর দিয়ে ধাপে ধাপে পাঠাই। ধরুন idx_products_price index আগেই বানানো আছে, আর অ্যাপ জানতে চাইছে ৫৫০ টাকার পণ্য কোনগুলো।

এই যাত্রার প্রতিটি ধাপ পরের মডিউলগুলোর এক-একটি বিষয়: parser আর optimizer — পর্ব ৪২–৪৩; buffer আর page — পর্ব ৩৯; index — পর্ব ৪০–৪১; transaction, lock আর recovery — মডিউল ৭. এখন শুধু মানচিত্রটি মাথায় রাখুন।

৭ · System catalog — ডেটাবেস নিজের কথা নিজেই জানে

একটি লাইব্রেরির কথা ভাবুন। বইগুলো তাকে থাকে, কিন্তু কোন বই কোন তাকে, কোন লেখকের, কয় কপি — এই তথ্য থাকে আলাদা ক্যাটালগে। মজার ব্যাপার, ক্যাটালগটিও নিজে একটি বই বা কার্ডের বাক্স, লাইব্রেরিরই অংশ। Relational DBMS-ও ঠিক এভাবে চলে: schema-র সব তথ্য রাখা হয় টেবিলের আকারে, যাকে সাধারণ SQL দিয়েই পড়া যায়।

SQLite-এ এই catalog-এর নাম sqlite_master (নতুন version-এ sqlite_schema নামেও ডাকা যায়)। এর প্রতিটি সারি একটি টেবিল, index, view বা trigger.

-- ডেটাবেসে কী কী আছে? catalog-কে জিজ্ঞেস করি
SELECT name, type FROM sqlite_master;

একটি নির্দিষ্ট টেবিলের column-গুলোর বিস্তারিত দেখতে SQLite-এ আছে PRAGMA table_info:

PRAGMA table_info(products);

Catalog-এর নাম আর গঠন প্রতিটি DBMS-এ আলাদা, কিন্তু SQL মান (SQL-92 থেকে) একটি সাধারণ রূপ দিয়েছে: INFORMATION_SCHEMA. PostgreSQL, MySQL আর SQL Server-এ এটি আছে; SQLite আর Oracle-এ নেই (Oracle-এর নিজস্ব data dictionary view আছে, যেমন USER_TABLES, ALL_TAB_COLUMNS). নিচের কোড PostgreSQL-এর, এই পাতায় চলবে না:

-- মান অনুযায়ী: information_schema (MySQL, SQL Server-এও আছে)
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;

-- PostgreSQL-এর নিজস্ব catalog: pg_catalog
SELECT relname, relkind, reltuples
FROM pg_catalog.pg_class
WHERE relname LIKE 'products%';

প্রথম কোয়েরি PRAGMA table_info-এর মতোই column-এর তালিকা দেয়, কিন্তু যেকোনো মান-মেনে চলা DBMS-এ চলে। দ্বিতীয়টি PostgreSQL-এর ভেতরের pg_class টেবিল পড়ে — relkind-এ r মানে সাধারণ টেবিল, i মানে index, v মানে view; আর reltuples হলো optimizer-এর জন্য রাখা আনুমানিক সারির সংখ্যা (ANALYZE বা autovacuum চালালে হালনাগাদ হয়)। অর্থাৎ optimizer যে statistics দেখে সিদ্ধান্ত নেয় (অংশ ৬-এর ধাপ ৩), সেটিও catalog-এই থাকে।

Catalog কেন টেবিলে রাখা হয়? কারণ তাহলে আলাদা কোনো ভাষা বা টুল লাগে না — একই SQL, একই query processor, একই নিরাপত্তা ব্যবস্থা। ডেটাবেস ব্যবস্থাপনার টুল (DBeaver, pgAdmin), ORM আর migration টুল (পর্ব ৫০) সবাই এভাবেই catalog পড়ে জানে ডেটাবেসে কী আছে। Codd ১৯৮৫ সালে relational DBMS-এর যে ১২টি নিয়ম প্রকাশ করেন, তার চতুর্থটিই হলো — catalog-ও relational হতে হবে, সাধারণ ব্যবহারকারী একই ভাষায় সেটি পড়তে পারবেন।

৮ · DBMS কোথায় চলে — centralized, client-server, embedded আর distributed

এতক্ষণ দেখলাম DBMS-এর ভেতরের গঠন। এবার প্রশ্ন — DBMS, অ্যাপ আর ব্যবহারকারী কোন কম্পিউটারে বসে, আর কীভাবে একে অপরের সাথে কথা বলে? এই বিন্যাসকেও architecture বলা হয়।

৯ · AI-এর যুগে — schema আর architecture বুঝে AI ব্যবহার

AI এখন মুহূর্তেই SQL কোয়েরি, view আর এমনকি পুরো ডেটাবেস ডিজাইন লিখে দেয়। কিন্তু AI আপনার ডেটাবেসের catalog দেখতে পায় না — সে অনুমান করে। তাই AI-এর লেখা কোয়েরি প্রায়ই চলে, কিন্তু ভুল উত্তর দেয়: NULL ভুলভাবে সামলায়, JOIN-এ সারি দ্বিগুণ করে ফেলে, এমন column-এর নাম বানায় যা নেই, index বা transaction-এর কথা ভাবে না। এই পর্বের ধারণাগুলোই সেই ফাঁক ধরার হাতিয়ার।

AI সাধারণত যেসব ভুল করে

AI-এর লেখা SQL বা ডিজাইন হাতে পেলে — যাচাইয়ের চেকলিস্ট

• প্রতিটি টেবিল ও column-এর নাম catalog-এর (sqlite_master, PRAGMA table_info, information_schema) সাথে মিলিয়েছি
• নমুনা ডেটায় চালিয়ে অন্তত একটি সারির ফল হাতে হিসাব করে মিলিয়েছি
• NULL, শূন্য, বাতিল বা ফাঁকা — এমন প্রান্তিক সারিতে আলাদা করে পরীক্ষা করেছি
• JOIN-এর আগে-পরে সারির সংখ্যা (COUNT(*)) তুলনা করেছি
• বড় টেবিলে চলবে এমন কোয়েরির plan (EXPLAIN) দেখেছি; দরকার হলে index-এর কথা ভেবেছি
• একাধিক পরিবর্তন একসাথে হলে BEGIN … COMMIT আছে কি না দেখেছি, আর অ্যাপের কোডে SELECT * নেই