ER থেকে টেবিল — mapping-এর নিয়ম

১ · নকশা থেকে দালান — কেন নিয়ম মেনে অনুবাদ

পর্ব ৭-এ আপনি ER মডেলে entity, attribute আর relationship আঁকতে শিখেছেন; পর্ব ৮-এ cardinality, participation আর weak entity; পর্ব ৯-এ specialization আর generalization. সেগুলো ছিল কাগজের নকশা — কোন জিনিস আছে, কীভাবে জড়িত। কিন্তু DBMS ছবি বোঝে না, বোঝে টেবিল, কলাম, primary key আর foreign key. ER diagram থেকে এই টেবিলগুলো বানানোর কাজটিকে বলে ER-to-relational mapping. এটি conceptual নকশা থেকে logical নকশায় যাওয়ার সেতু (পর্ব ৪-এর conceptual → logical model).

পাঠ্যবইয়ে এই অনুবাদকে কয়েকটি ধাপে সাজানো হয়। সবচেয়ে প্রচলিত রূপটি Ramez Elmasri ও Shamkant Navathe-এর Fundamentals of Database Systems বইয়ের: ER-এর জন্য ৭টি ধাপ, EER-এর জন্য আরও ২টি (specialization আর union type বা category). এই পর্বে আমরা প্রথম ৮টি ধাপ সেভাবেই নেব, আর ধাপ ৯-এ বইয়ের category-র বদলে বাস্তবে বেশি দরকারি দুটি বিষয় রাখব — derived attribute আর recursive relationship (বইয়ে recursive relationship ধাপ ৩–৫-এর ভেতরেই পড়ে)। ধাপগুলো এক নজরে:

ক্রমটি এলোমেলো নয়। Weak entity-র টেবিল বানাতে মালিকের key লাগে, তাই strong entity আগে। Relationship-এর FK বসাতে দুই পাশের টেবিল আগে থেকে থাকা চাই। আর M:N-এর junction টেবিলের key আসে দুই পাশের key থেকে — যার মধ্যে weak entity-র composite key-ও থাকতে পারে।

এই পর্বের বেশিরভাগ এডিটর নিজের ছোট schema নিজে বানিয়ে নেয় (data-setup) — একটি লাইব্রেরির টেবিল: members (সদস্য), books (বই), authors (লেখক), copies (একটি বইয়ের প্রতিটি কপি), loans (ধার)। প্রতিটি এডিটরের নিচে “নমুনা ডেটাবেস” বাক্সে ঠিক কোন টেবিল আছে দেখতে পাবেন। কয়েকটি এডিটর ব্যবহার করে কোর্সের বিশ্ববিদ্যালয় ডেটাবেস db-uni: departments (dept_id, name, building), instructors (instructor_id, name, dept_id, salary, mentor_id — mentor আরেকজন শিক্ষক), students (student_id, name, dept_id, batch, cgpa), courses (course_id, title, dept_id, credits, instructor_id) আর enrollments (student_id, course_id, term, grade). অংশ ৭-এ দেখবেন এই টেবিলগুলো তার ER থেকে কীভাবে এসেছে।

২ · ধাপ ১–২ · Strong entity আর weak entity

ধাপ ১ — প্রতিটি strong entity একটি টেবিল

  ┌──────────────────────────────┐
  │            MEMBER            │      (member_id) = key, নিচে দাগ
  ├──────────────────────────────┤
  │  member_id                   │
  │  name ─┬─ first_name         │      composite: দুই অংশ
  │        └─ last_name          │
  │  address ─┬─ area            │      composite: দুই অংশ
  │           └─ city            │
  │  joined_on                   │
  └──────────────────────────────┘
          ⇩  ধাপ ১
  members(member_id, first_name, last_name, area, city, joined_on)

খেয়াল করুন, “name” আর “address” নামের কোনো কলাম নেই। Relational model-এর প্রতিটি ঘরে একটি atomic value থাকে (পর্ব ৫-এর first normal form). “Rahim Uddin, Mirpur, Dhaka” এক ঘরে রাখলে “ঢাকার সব সদস্য” খুঁজতে লেখার ভেতর থেকে শহর কেটে বের করতে হতো। আলাদা কলামে রাখলে সেটি একটি সরল WHERE. পুরো নাম দরকার হলে কোয়েরিতে জুড়ে নিলেই হয়:

-- ER: MEMBER(member_id, name(first, last), address(area, city), joined_on)
CREATE TABLE members (
  member_id  INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  area       TEXT,
  city       TEXT,
  joined_on  TEXT NOT NULL
);
INSERT INTO members VALUES (1, 'Rahim', 'Uddin', 'Mirpur', 'Dhaka', '2024-01-10'),
                           (2, 'Karima', 'Begum', 'Agrabad', 'Chattogram', '2024-03-05');
SELECT member_id, first_name || ' ' || last_name AS full_name, city
FROM members ORDER BY last_name;

Composite attribute ভাঙার সময় একটি বিচার লাগে: কতটা ভাঙবেন? বাংলাদেশের ঠিকানা “বাড়ি ১২, রোড ৫, মিরপুর ১০, ঢাকা ১২১৬” — প্রতিটি অংশ আলাদা কলাম করবেন, নাকি “address_line” আর “city”? নিয়মটি হলো: যে অংশ দিয়ে আপনি খুঁজবেন, সাজাবেন বা গুনবেন, সেটি আলাদা কলাম হওয়া উচিত। যে অংশ শুধু ছাপা হবে, তা একসাথে থাকলেও ক্ষতি নেই।

ধাপ ২ — weak entity: মালিকের key + partial key

পর্ব ৮-এ দেখেছেন, weak entity-র নিজের পূর্ণ key নেই; তাকে চেনা যায় শুধু তার মালিক (owner entity) আর নিজের partial key মিলিয়ে। লাইব্রেরির প্রতিটি বইয়ের কয়েকটি কপি থাকে: “Learning SQL-এর কপি ২”। “কপি ২” একা কিছু বোঝায় না — প্রতিটি বইয়েরই কপি ২ থাকতে পারে। মালিক BOOK আর COPY-কে জোড়ে একটি identifying relationship, আর COPY-র অংশগ্রহণ সেখানে total.

  ┌───────────┐      ◇◇───────◇◇      ╔═════════════╗
  │   BOOK    │──1──◇◇ HAS_COPY ◇◇══N══║    COPY     ║   ══ = total participation
  │ book_id   │      ◇◇───────◇◇      ║ copy_no ┄┄┄ ║   ┄┄ = partial key
  │ title     │                        ║ shelf       ║
  └───────────┘                        ╚═════════════╝
          ⇩  ধাপ ২
  copies(book_id, copy_no, shelf)    book_id → books ON DELETE CASCADE

এবার চালিয়ে দেখুন। এই এডিটরের schema: books(book_id, title) — দুটি বই, আর copies(book_id, copy_no, shelf) — Learning SQL-এর ৩টি আর Database Design-এর ১টি কপি:

-- দুটি বইয়েরই 'copy 1' আছে: partial key একা যথেষ্ট নয়
SELECT book_id, copy_no, shelf FROM copies WHERE copy_no = 1;

-- মালিক (book 1) মুছলে তার কপিগুলোও যায়
DELETE FROM books WHERE book_id = 1;
SELECT * FROM copies;

Weak entity-র মালিক নিজেও weak হতে পারে। ধরুন একটি বহুতল আবাসিক প্রকল্প: BUILDING → FLOOR (floor_no) → FLAT (flat_no). তখন flats-এর key হয় (building_id, floor_no, flat_no) — মালিকের পুরো composite key সাথে আসে। এজন্যই ধাপ ২-এ weak entity-গুলো মালিকের ক্রম অনুযায়ী বানাতে হয়।

৩ · ধাপ ৩–৫ · Binary relationship — 1:1, 1:N আর M:N

দুটি entity-র মধ্যে relationship-এর জন্য আলাদা টেবিল লাগবে কি না, আর লাগলে না হলে FK কোথায় বসবে — তা ঠিক করে cardinality ratio আর participation. আগে নিচের ডায়াগ্রামে তিন ধরনের সম্পর্কে FK বসানোর চেষ্টা ধাপে ধাপে দেখুন:

ধাপ ৩ — 1:1: তিনটি পথ

লাইব্রেরি-কার্ডের উদাহরণটি চালিয়ে দেখুন। Schema: members(member_id, name) — Rahim, Karima, Sabbir; library_cards(card_no, member_id, issued_on), যেখানে member_id-তে NOT NULL UNIQUE. শুরুতে শুধু Rahim-এর কার্ড LC-1001 আছে:

-- Karima-র প্রথম কার্ড: ঢুকবে
INSERT INTO library_cards VALUES ('LC-1002', 2, '2024-03-05');
SELECT m.name, c.card_no FROM members m
LEFT JOIN library_cards c ON c.member_id = m.member_id;

-- Rahim-এর দ্বিতীয় কার্ড: UNIQUE এটি আটকাবে (1:1)
INSERT INTO library_cards VALUES ('LC-1003', 1, '2025-02-01');

ধাপ ৪ — 1:N: FK বসে N দিকে

CREATE TABLE publishers (
  publisher_id INTEGER PRIMARY KEY,
  name         TEXT NOT NULL UNIQUE,
  city         TEXT
);
CREATE TABLE books (
  book_id      INTEGER PRIMARY KEY,
  isbn         TEXT NOT NULL UNIQUE,     -- natural key → alternate key
  title        TEXT NOT NULL,
  pub_year     INTEGER,
  publisher_id INTEGER REFERENCES publishers(publisher_id)   -- 1:N, N দিকে FK
);

publisher_id-তে NOT NULL নেই — অর্থাৎ ডিজাইনার ধরে নিয়েছেন প্রকাশক অজানা এমন পুরোনো বইও লাইব্রেরিতে থাকতে পারে (BOOK-এর অংশগ্রহণ partial). নিয়ম বদলালে (প্রতিটি বইয়ের প্রকাশক জানতেই হবে) শুধু NOT NULL যোগ হবে। এভাবেই participation-এর তথ্য NOT NULL-এ অনুবাদ হয় — ER diagram-এর ডাবল লাইন টেবিলে হয়ে যায় চার অক্ষরের একটি শব্দ।

ধাপ ৫ — M:N: junction টেবিল

এডিটরের schema: books (Learning SQL, Database Design, Data Structures), authors (Rafiq Ahmed, Nasreen Akter, Tanvir Alam) আর junction book_authors(book_id, author_id, author_order). Learning SQL আর Data Structures-এর দুজন করে লেখক:

-- কোন বইয়ের লেখক কারা, কোন ক্রমে?
SELECT b.title, ba.author_order, a.name
FROM book_authors ba
JOIN books   b ON b.book_id   = ba.book_id
JOIN authors a ON a.author_id = ba.author_id
ORDER BY b.title, ba.author_order;

-- উল্টো দিক: প্রত্যেক লেখকের কয়টি বই?
SELECT a.name, COUNT(*) AS books
FROM authors a JOIN book_authors ba ON ba.author_id = a.author_id
GROUP BY a.author_id ORDER BY a.name;

৪ · ধাপ ৬–৭ · Multi-valued attribute আর n-ary relationship

ধাপ ৬ — এক সদস্যের অনেক ফোন নম্বর

বাংলাদেশে একজন মানুষের দুটি-তিনটি মোবাইল নম্বর থাকা খুব সাধারণ — একটি নিজের, একটি অফিসের, একটি বাড়ির জন্য। ER-এ phone তাই একটি multi-valued attribute (ডাবল ডিম্বাকার, পর্ব ৭)। কিন্তু relational টেবিলের এক ঘরে একটির বেশি মান রাখা যায় না।

  ┌────────────┐
  │   MEMBER   │──── ((  phone  ))      (( )) = multi-valued
  │ member_id  │
  └────────────┘
          ⇩  ধাপ ৬
  member_phones(member_id, phone)    member_id → members ON DELETE CASCADE

সহজ মনে হতে পারে “phone1, phone2, phone3” কলাম রাখা। কিন্তু তাতে তিনটি সমস্যা: চতুর্থ নম্বর এলে schema বদলাতে হয়; বেশিরভাগ সারিতে phone2, phone3 NULL থাকে; আর “এই নম্বরটি কার?” খুঁজতে তিন কলামে OR লিখতে হয়। আলাদা টেবিলে সব প্রশ্ন এক কলামে। এডিটরের schema: members(member_id, name) — Rahim, Karima, Sabbir; member_phones(member_id, phone) — Rahim-এর দুটি নম্বর, Karima-র একটি, Sabbir-এর নেই:

-- প্রতিটি নম্বর আলাদা সারি; নম্বর নেই এমন সদস্যও দেখাতে LEFT JOIN
SELECT m.name, p.phone
FROM members m LEFT JOIN member_phones p ON p.member_id = m.member_id
ORDER BY m.member_id, p.phone;

-- 'কোন সদস্যের নম্বর 01911000011?' — এক কলামে খোঁজ
SELECT m.name FROM members m
JOIN member_phones p ON p.member_id = m.member_id
WHERE p.phone = '01911000011';

Multi-valued attribute যদি নিজেই composite হয় — যেমন প্রতিটি নম্বরের সাথে “ধরন” (personal/office) — তাহলে নতুন টেবিলে কলাম বাড়ে: member_phones(member_id, phone, phone_type). Key কী হবে, তা আবার নিয়ম দেখে: এক ধরনে একটিই নম্বর হলে (member_id, phone_type), নইলে (member_id, phone).

ধাপ ৭ — n-ary relationship: তিন বা তার বেশি অংশগ্রহণকারী

কখনো একটি ঘটনায় তিনটি entity একসাথে জড়িত, আর কোনো দুটি আলাদা করে সেটি বলতে পারে না। একটি গার্মেন্টস গ্রুপের কথা ভাবুন: কোন সরবরাহকারী (SUPPLIER) কোন কাপড় (FABRIC) কোন কারখানায় (FACTORY) কত গজ পাঠিয়েছে। এটি একটি ternary relationship SUPPLIES, যার attribute quantity_yards.

  SUPPLIER ──M──◇ SUPPLIES ◇──N── FABRIC
                     │  (quantity_yards)
                     P
                     │
                  FACTORY
          ⇩  ধাপ ৭
  supplies(supplier_id, fabric_id, factory_id, quantity_yards)

CREATE TABLE supplies (
  supplier_id    INTEGER REFERENCES suppliers(supplier_id),
  fabric_id      INTEGER REFERENCES fabrics(fabric_id),
  factory_id     INTEGER REFERENCES factories(factory_id),
  quantity_yards INTEGER NOT NULL CHECK (quantity_yards > 0),
  PRIMARY KEY (supplier_id, fabric_id, factory_id)
);

৫ · ধাপ ৮–৯ · Specialization, derived attribute আর recursive relationship

ধাপ ৮ — specialization: তিন কৌশল (পর্ব ৯-এর সংক্ষেপ)

পর্ব ৯-এ দেখেছেন, MEMBER-কে specialization করে STUDENT_MEMBER (প্রতিষ্ঠান, student ID) আর STAFF_MEMBER (পদবি) বানানো যায়। টেবিলে নেওয়ার প্রধান তিনটি কৌশল আছে (Elmasri–Navathe এগুলোকে ৮A, ৮B, ৮C বলেন; ৮D হলো একাধিক type-flag-ওয়ালা এক টেবিল)। নিচের ট্যাবে তিনটি পাশাপাশি দেখুন:

CREATE TABLE members (member_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL, joined_on TEXT NOT NULL);
CREATE TABLE student_members (
  member_id   INTEGER PRIMARY KEY REFERENCES members(member_id) ON DELETE CASCADE,
  institution TEXT NOT NULL, student_no TEXT NOT NULL);
CREATE TABLE staff_members (
  member_id   INTEGER PRIMARY KEY REFERENCES members(member_id) ON DELETE CASCADE,
  designation TEXT NOT NULL);
-- যেকোনো specialization-এ চলে; subclass-এর তথ্য পেতে JOIN লাগে

CREATE TABLE student_members (member_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL, joined_on TEXT NOT NULL,
  institution TEXT NOT NULL, student_no TEXT NOT NULL);
CREATE TABLE staff_members (member_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL, joined_on TEXT NOT NULL,
  designation TEXT NOT NULL);
-- শুধু total + disjoint হলে: প্রত্যেক সদস্য ঠিক একটি subclass-এ
-- 'সব সদস্য' পেতে UNION লাগে; member_id দুই টেবিলে আলাদা রাখার দায় আপনার

CREATE TABLE members (member_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL, joined_on TEXT NOT NULL,
  member_type TEXT NOT NULL CHECK (member_type IN ('student', 'staff', 'general')),
  institution TEXT, student_no TEXT,      -- শুধু student-এর
  designation TEXT,                       -- শুধু staff-এর
  CHECK (member_type = 'student' OR (institution IS NULL AND student_no IS NULL)),
  CHECK (member_type = 'staff'   OR designation IS NULL));
-- JOIN নেই, দ্রুত; কিন্তু অনেক NULL, আর subclass-এর NOT NULL লিখতে CHECK লাগে

কোনটি কখন? subclass কম আর তাদের নিজস্ব attribute অল্প হলে (গ) সরল ও দ্রুত। subclass-এর নিজস্ব attribute বেশি বা subclass-এর নিজের relationship থাকলে (ক) পরিষ্কার — যেমন শুধু STAFF_MEMBER বই অনুমোদন করতে পারে, তাহলে সেই FK staff_members-এর দিকে যাবে। (খ) কম ব্যবহৃত, কারণ total ও disjoint না হলে তথ্য হারায় বা দুই জায়গায় থাকে। এই তুলনা ORM-এর জগতেও আছে — ভাবনার প্রশ্ন ৩-এ দেখবেন।

ধাপ ৯ক — derived attribute: সাধারণত জমা নয়

ER-এ ড্যাশ-দেওয়া ডিম্বাকার derived attribute এমন মান, যা অন্য attribute থেকে হিসাব করা যায়: জন্মতারিখ থেকে বয়স, ধারের তারিখ থেকে ফেরতের শেষ দিন, কপি গুনে মোট কপি। এগুলো সাধারণত কলাম হিসেবে জমা রাখা হয় না, কারণ জমা রাখলে মূল মান বদলালে দুটি অমিল হতে পারে। তিনটি প্রচলিত পথ: কোয়েরিতে হিসাব, VIEW (পর্ব ২৪), আর generated column — যা DBMS নিজেই সূত্র থেকে হিসাব করে রাখে, হাতে লেখা যায় না:

CREATE TABLE loans (
  loan_id     INTEGER PRIMARY KEY,
  member      TEXT NOT NULL,
  loaned_on   TEXT NOT NULL,
  returned_on TEXT,
  due_on      TEXT GENERATED ALWAYS AS (date(loaned_on, '+14 days')) VIRTUAL
);
INSERT INTO loans (loan_id, member, loaned_on, returned_on) VALUES
  (1, 'Rahim', '2025-09-01', '2025-09-10'),
  (2, 'Karima', '2025-09-05', '2025-09-25');
CREATE VIEW loan_status AS
  SELECT loan_id, member, due_on,
    CAST(MAX(0, julianday(returned_on) - julianday(due_on)) AS INTEGER) AS days_late
  FROM loans;
SELECT * FROM loan_status;

একটি সীমা মনে রাখুন: generated column-এর সূত্র deterministic হতে হবে — একই ইনপুটে সবসময় একই ফল। তাই “বয়স = আজকের তারিখ − জন্মতারিখ” generated column হতে পারে না; SQLite-এ date('now') ব্যবহার করলে সারি লেখার সময় ত্রুটি আসে: non-deterministic use of date() in a generated column. বয়সের মতো সময়-নির্ভর মান VIEW-এ বা কোয়েরিতে হিসাব করুন। অন্য DBMS-এ বাক্যগঠন একটু আলাদা:

-- PostgreSQL 12+: STORED (সাম্প্রতিক 18 সংস্করণে VIRTUAL-ও এসেছে)
due_on date GENERATED ALWAYS AS (loaned_on + 14) STORED
-- MySQL 5.7+: VIRTUAL বা STORED
due_on DATE GENERATED ALWAYS AS (loaned_on + INTERVAL 14 DAY) VIRTUAL
-- SQL Server: computed column, PERSISTED = জমা রাখা
due_on AS DATEADD(day, 14, loaned_on) PERSISTED

ধাপ ৯খ — recursive relationship: একই টেবিলে FK

যে relationship-এ একই entity type দুটি ভিন্ন ভূমিকায় (role) অংশ নেয়, তাকে বলে recursive relationship. db-uni-তে MENTORS: একজন শিক্ষক আরেকজন শিক্ষকের mentor. Cardinality 1:N (একজন mentor-এর অনেক নবীন সহকর্মী, প্রত্যেকের সর্বোচ্চ একজন mentor), তাই নিয়ম ৪-ই খাটে — শুধু “N দিকের টেবিল” আর “1 দিকের টেবিল” একই টেবিল। FK কলামের নাম হয় ভূমিকার নামে: mentor_id → instructors(instructor_id). সম্পর্কটি M:N হলে (যেমন সদস্যরা একে অপরকে বই সুপারিশ করেন) নিয়ম ৫ খাটে: junction টেবিলের দুই FK-ই একই টেবিলের দিকে, যেমন recommendations(from_member_id, to_member_id).

-- MENTORS: instructors থেকে instructors-এ একটি 1:N সম্পর্ক
SELECT i.name AS instructor, m.name AS mentor
FROM instructors i
LEFT JOIN instructors m ON m.instructor_id = i.mentor_id
ORDER BY i.instructor_id;

৬ · পুরো উদাহরণ — একটি লাইব্রেরির ER থেকে কাজ-করা schema

এবার সব নিয়ম একসাথে। ধরুন একটি পাড়ার পাঠাগার তাদের খাতা-কলমের ক্যাটালগ ডেটাবেসে আনতে চায়। লাইব্রেরিয়ানের সাথে কথা বলে ব্যবসার নিয়মগুলো লেখা হলো:

• প্রতিটি বইয়ের একটি ISBN, শিরোনাম, প্রকাশের বছর আর (জানা থাকলে) একটি প্রকাশক।
• একটি বইয়ের একাধিক লেখক থাকতে পারে, আর লেখকের ক্রম (প্রথম, দ্বিতীয় লেখক) গুরুত্বপূর্ণ।
• একটি বইয়ের কয়েকটি কপি; প্রতিটি কপির একটি নম্বর (সেই বইয়ের ভেতরে ১, ২, ৩ …) আর একটি তাক।
• সদস্যের নাম (প্রথম ও শেষ অংশ), ঠিকানা (এলাকা, শহর), যোগদানের তারিখ আর এক বা একাধিক ফোন নম্বর।
• সদস্য একটি নির্দিষ্ট কপি ধার নেন; কবে নিলেন আর কবে ফেরত দিলেন লেখা থাকে। একই কপি একই সদস্য পরে আবার নিতে পারেন।
• ধারের ইতিহাস কখনো মোছা যাবে না — হিসাব আর হারানো বইয়ের খোঁজের জন্য দরকার।

LOAN-কে relationship হিসেবে আঁকা হয়েছে, কিন্তু একটি জিনিস খেয়াল করুন: একই (সদস্য, কপি) জোড়া একাধিকবার ঘটতে পারে। খাঁটি ER-এ relationship instance মানে একটি জোড়া — একই জোড়া দুবার হয় না। তাই ধার-এর মতো ঘটনাকে প্রায়ই associative entity (relationship-কে entity-তে উন্নীত করা) হিসেবে দেখা হয়, যার নিজের key থাকে। ম্যাপিংয়ে এর ফল নিচের দ্বিতীয় ডায়াগ্রামে দেখবেন। প্রথমে ধাপে ধাপে টেবিলগুলো:

এখন key বাছাই। বেশিরভাগ টেবিলে এটি সরল: strong entity-তে surrogate id (books-এ isbn থাকে UNIQUE alternate key হিসেবে, পর্ব ৬), weak entity-তে (মালিক, partial key), junction-এ দুই FK. কঠিন প্রশ্ন একটিই — loans. নমুনা ডেটা নিয়ে যাচাই করুন:

শেষ সিদ্ধান্ত: প্রতিটি foreign key-র ON DELETE. প্রতিটির জন্য একটিই প্রশ্ন — “parent না থাকলে child সারির কি আলাদা অর্থ বা মূল্য আছে?” (পর্ব ৬)। নিচে নিয়মগুলো বসানো আর একটি DELETE-এর পরিণতি দেখুন:

সব সিদ্ধান্ত মিলিয়ে সম্পূর্ণ DDL. নিচের দুটি এডিটর ঠিক এই schema আর নমুনা ডেটা নিয়ে চলে (“নমুনা ডেটাবেস” বাক্সে পুরোটা আছে):

CREATE TABLE publishers (publisher_id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, city TEXT);
CREATE TABLE books (book_id INTEGER PRIMARY KEY, isbn TEXT NOT NULL UNIQUE, title TEXT NOT NULL,
  pub_year INTEGER, publisher_id INTEGER REFERENCES publishers(publisher_id) ON DELETE RESTRICT);
CREATE TABLE authors (author_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book_authors (book_id INTEGER REFERENCES books(book_id) ON DELETE CASCADE,
  author_id INTEGER REFERENCES authors(author_id) ON DELETE RESTRICT,
  author_order INTEGER NOT NULL, PRIMARY KEY (book_id, author_id));
CREATE TABLE copies (book_id INTEGER REFERENCES books(book_id) ON DELETE CASCADE,
  copy_no INTEGER NOT NULL, shelf TEXT, PRIMARY KEY (book_id, copy_no));
CREATE TABLE members (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL,
  last_name TEXT NOT NULL, area TEXT, city TEXT, joined_on TEXT NOT NULL);
CREATE TABLE member_phones (member_id INTEGER REFERENCES members(member_id) ON DELETE CASCADE,
  phone TEXT NOT NULL, PRIMARY KEY (member_id, phone));
CREATE TABLE loans (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL, copy_no INTEGER NOT NULL,
  member_id INTEGER NOT NULL REFERENCES members(member_id) ON DELETE RESTRICT,
  loaned_on TEXT NOT NULL, returned_on TEXT,
  FOREIGN KEY (book_id, copy_no) REFERENCES copies(book_id, copy_no) ON DELETE RESTRICT);
CREATE UNIQUE INDEX one_open_loan ON loans(book_id, copy_no) WHERE returned_on IS NULL;

নমুনা ডেটা: ৩টি প্রকাশক, ৫টি বই (Learning SQL, Database Design, Golper Jhuli, Data Structures, Python for All), ৪ জন লেখক, ৮টি কপি (Learning SQL-এর ৩টি, Golper Jhuli-র ২টি, বাকিদের ১টি করে), ৫ জন সদস্য (Rahim, Karima, Sabbir, Nusrat, Tanvir), ৪টি ফোন নম্বর আর উপরের ডায়াগ্রামের ৬টি ধার। ডিজাইনটি কাজ করে কি না, দুটি বাস্তব প্রশ্ন দিয়ে যাচাই করি:

-- ১. এখন কোন কোন কপি কার কাছে?
SELECT l.loan_id, b.title, l.copy_no,
       m.first_name || ' ' || m.last_name AS member, l.loaned_on
FROM loans l
JOIN books   b ON b.book_id   = l.book_id
JOIN members m ON m.member_id = l.member_id
WHERE l.returned_on IS NULL
ORDER BY l.loan_id;

-- ২. প্রতিটি বইয়ের মোট কপি আর তাকে থাকা কপি
SELECT b.title, COUNT(*) AS total_copies,
       COUNT(*) - COUNT(l.loan_id) AS on_shelf
FROM books b
JOIN copies c ON c.book_id = b.book_id
LEFT JOIN loans l ON l.book_id = c.book_id AND l.copy_no = c.copy_no
                 AND l.returned_on IS NULL
GROUP BY b.book_id ORDER BY b.book_id;

এবার ON DELETE সিদ্ধান্তগুলো আসল ইঞ্জিনে পরীক্ষা করুন — একটি মোছা চলবে, একটি চলবে CASCADE-সহ, আর শেষটি উপরের ডায়াগ্রামের মতো আটকে যাবে:

-- Tanvir (5)-এর কোনো ধার নেই: সদস্যপদ মোছা যায়
DELETE FROM members WHERE member_id = 5;

-- 'Database Design' (2)-এর কপি কখনো ধার হয়নি: বই মুছলে কপিও যায় (CASCADE)
DELETE FROM books WHERE book_id = 2;
SELECT COUNT(*) AS copies_left FROM copies;

-- 'Golper Jhuli' (3)-এর কপি 1 ধার-ইতিহাসে আছে: পুরো DELETE বাতিল
DELETE FROM books WHERE book_id = 3;

৭ · db-uni কোথা থেকে এল — বিশ্ববিদ্যালয়ের ER-এর সাথে মিলিয়ে

এই কোর্সে যে বিশ্ববিদ্যালয় ডেটাবেস বারবার চালাচ্ছেন, সেটিও একটি ER থেকে এই নিয়মগুলো মেনেই তৈরি। পর্ব ৭-এ এর entity আর relationship-গুলো দেখেছেন (সেখানে relationship-এর নাম একটু ভিন্ন হতে পারে; অর্থ একই)। প্রতিটি ER উপাদান কোন ধাপে কোন টেবিল বা কলাম হয়েছে:

তিনটি জিনিস লক্ষ করার মতো। প্রথমত, enrollments-এর key-তে term আছে, যদিও ER-এ term relationship-এর একটি attribute. কারণ লাইব্রেরির loans-এর মতোই: একজন শিক্ষার্থী F পেয়ে একই কোর্স পরের term-এ আবার নিতে পারেন, তাই (student_id, course_id) জোড়া পুনরাবৃত্ত হয়। ডিজাইনার এখানে surrogate না নিয়ে term-কে key-তে নিয়েছেন — এটিও বৈধ, কারণ বিশ্ববিদ্যালয়ের নিয়মে একই term-এ একই কোর্স দুবার নেওয়া যায় না। দ্বিতীয়ত, “কোর্স কে পড়ান” এখানে 1:N ধরা হয়েছে (প্রতিটি কোর্সে একজন শিক্ষক)। বাস্তবে একটি কোর্সের একাধিক section আর একাধিক শিক্ষক থাকে; তখন এটি M:N বা SECTION নামে একটি weak entity হতো — পর্ব ১১-এর কেস স্টাডিতে এমন প্রশ্নই আসবে। তৃতীয়ত, কোনো 1:1 বা multi-valued attribute নেই, তাই সেই ধাপগুলো এখানে কিছু তৈরি করেনি। অ্যালগরিদমের প্রতিটি ধাপ প্রতিটি ER-এ কাজ পাবে, এমন নয়।

৮ · ডিজাইনের সিদ্ধান্ত — NULL, নামকরণ আর উল্টো পথ

NULL-ভরা এক টেবিল, নাকি ভাগ করা দুই টেবিল?

নিয়মগুলো মানলেও কিছু জায়গায় দুটি বৈধ নকশা থাকে, আর দুটির মধ্যে বেছে নিতে হয়। সবচেয়ে পরিচিত প্রশ্ন: একটি ঐচ্ছিক তথ্যগুচ্ছ (যেমন শুধু শিক্ষার্থী-সদস্যের প্রতিষ্ঠান আর student ID, বা শুধু কর্পোরেট গ্রাহকের ট্রেড লাইসেন্স, TIN, যোগাযোগ ব্যক্তি) মূল টেবিলে কলাম হবে, নাকি আলাদা 1:1 টেবিলে যাবে?

একটি কার্যকর নিয়ম: যদি একগুচ্ছ কলাম একসাথে থাকে বা একসাথে না থাকে (সব NULL বা সব পূর্ণ), আর সেটি অর্ধেকের কম সারিতে প্রযোজ্য, তবে সেটি প্রায় নিশ্চিতভাবে একটি লুকানো subclass বা 1:1 entity — আলাদা টেবিল ভাবুন। আর গোপনীয় তথ্য (বেতন, NID-র ছবি) আলাদা টেবিলে রাখলে নিরাপত্তার নিয়ম সহজ হয় (পর্ব ৫১)।

নামকরণের রীতি

নাম ER থেকে আসে, কিন্তু টেবিলে নামের রীতি দলের সবাইকে একরকম মানতে হয়। প্রচলিত ভালো অভ্যাস:

• snake_case, ছোট হাতের অক্ষর: member_phones, loaned_on. PostgreSQL উদ্ধৃতিহীন নাম ছোট হাতের অক্ষরে বদলে নেয় (Oracle বড় হাতের অক্ষরে), তাই MemberPhones লিখলে পরে "MemberPhones" উদ্ধৃতিসহ লিখতে হতে পারে।
• বহুবচন বা একবচন — একটি বেছে সবখানে: এই কোর্সে টেবিল বহুবচন (books), ER-এর entity একবচন (BOOK). অনেক দল একবচন (book) পছন্দ করে; দুটিই চলে, মিশ্রণ চলে না।
• Key-এর নাম <entity>_id: শুধু id না লিখে book_id; তাহলে FK কলামের নাম PK-র সাথে মেলে, JOIN পড়তে সহজ হয়, আর USING (book_id) লেখা যায়। ভূমিকা আলাদা হলে ভূমিকার নাম: mentor_id, from_member_id.
• Junction টেবিল দুই পাশের নামে: book_authors; সম্পর্কের নিজস্ব অর্থ থাকলে সেই নাম: enrollments, loans.
• সংরক্ষিত শব্দ এড়ান: order, group, user (PostgreSQL-এ সংরক্ষিত) টেবিলের নাম হলে প্রতিবার উদ্ধৃতি লাগে — orders, app_users লিখুন।
• তারিখের কলাম _on / _at: শুধু তারিখ হলে joined_on, সময়সহ হলে created_at — নাম দেখেই ধরন বোঝা যায়।

উল্টো পথ — schema দেখে ER উদ্ধার

বাস্তবে প্রায়ই নকশা আগে আসে না — আপনি হাতে পান দশ বছরের পুরোনো একটি ডেটাবেস, কোনো diagram ছাড়া। তখন mapping-এর নিয়মগুলো উল্টো দিকে চালিয়ে ER বের করা যায়; একে বলে reverse engineering. সূত্রগুলো:

এই সূত্র কাজে লাগাতে প্রথমে দরকার সব PK আর FK-র তালিকা, আর সেটি DBMS নিজেই দেয় — catalog থেকে (পর্ব ৩-এর system catalog). SQLite-এ pragma_foreign_key_list() আর pragma_table_info() টেবিলের মতো পড়া যায়। db-uni-র ওপর চালান:

-- schema থেকে ER পড়া: কোন টেবিল কোন টেবিলের দিকে আঙুল তোলে?
SELECT m.name AS child_table, f."from" AS fk_column,
       f."table" AS parent_table, f."to" AS parent_column
FROM sqlite_master m, pragma_foreign_key_list(m.name) f
WHERE m.type = 'table'
ORDER BY m.name, f."from";

-- composite primary key = সম্ভবত weak entity বা M:N
SELECT p.name AS table_name, COUNT(*) AS pk_columns
FROM sqlite_master p, pragma_table_info(p.name) t
WHERE p.type = 'table' AND t.pk > 0
GROUP BY p.name ORDER BY p.name;

PostgreSQL, MySQL বা SQL Server-এ একই তথ্য আসে standard information_schema থেকে:

SELECT kcu.table_name, kcu.column_name,
       ccu.table_name AS parent_table, ccu.column_name AS parent_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON kcu.constraint_name = tc.constraint_name AND kcu.table_schema = tc.table_schema
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY';
-- PostgreSQL-এ composite FK-র জন্য pg_constraint বেশি নির্ভরযোগ্য;
-- MySQL-এ constraint_column_usage নেই — key_column_usage-এর referenced_table_name দেখুন

৯ · AI-এর যুগে ER থেকে schema

একটি ER diagram-এর ছবি বা বর্ণনা দিয়ে AI-কে “CREATE TABLE লিখে দাও” বললে কয়েক সেকেন্ডে ফল আসে, আর বেশিরভাগ সময় তা চলেও। কিন্তু mapping-এর ভুল চলার সময় ধরা পড়ে না — ধরা পড়ে মাস কয়েক পরে, যখন দ্বিতীয় ধার লেখা যায় না বা একটি DELETE ইতিহাস মুছে দেয়। AI প্রায়ই যা করে:

AI-এর দেওয়া schema হাতে পেলে — যাচাইয়ের চেকলিস্ট

• প্রতিটি ER উপাদান (entity, relationship, multi-valued attribute) কোনো টেবিল বা কলামে পৌঁছেছে — কিছু হারায়নি
• কোনো কলামে কমা-দেওয়া তালিকা বা JSON-এ লুকানো সম্পর্ক নেই
• প্রতিটি 1:1-এ UNIQUE, প্রতিটি total participation-এ NOT NULL আছে
• weak entity-র key-তে মালিকের key আছে; ইতিহাসের সম্পর্কে একই জোড়া দুবার লেখা যায়
• প্রতিটি ON DELETE-এর পক্ষে এক লাইনের যুক্তি বলতে পারি, আর ইতিহাসের টেবিলে CASCADE নেই
• নমুনা ডেটা লোড করে ২–৩টি আসল প্রশ্নের কোয়েরি চালিয়েছি, হাতে গোনা উত্তরের সাথে সারির সংখ্যা মিলিয়েছি
• কোয়েরিতে AI যে কলামের নাম লিখেছে, সেগুলো সত্যিই schema-য় আছে (নেই এমন কলাম AI প্রায়ই বানিয়ে ফেলে)