Database Management System (DBMS) (ডেটাবেস ম্যানেজমেন্ট সিস্টেম)

উত্তর:

রিলেশনাল অ্যালজেব্রা সমাধান:

(ক) কুয়েরি ১: Department = 'Computer Science' এবং Marks >= 80 শর্তে সিলেকশন করে Name কলাম প্রজেক্ট করা হলো:
\Pi_{Name} ( \sigma_{Department = 'Computer Science' \land Marks \ge 80} (STUDENT) )

(খ) কুয়েরি ২: STUDENT ও ENROLL টেবিলের ন্যাচারাল জয়েন করে Course_ID = 'CS101' এবং Marks >= 90 শর্ত প্রয়োগ করে Roll কলাম প্রজেক্ট করা হলো:
\Pi_{Roll} ( \sigma_{Course\_ID = 'CS101' \land Marks \ge 90} (STUDENT \bowtie ENROLL) )

ধাপে ধাপে ক্লোজার নির্ণয়:

1. A-এর ক্লোজার:
   \(A^+ = \{A\}\)
   \(A ightarrow BC\) প্রয়োগে: \(A^+ = \{A, B, C\}\)
   \(B ightarrow D\) প্রয়োগে: \(A^+ = \{A, B, C, D\}\)
   \(CD ightarrow E\) প্রয়োগে: \(A^+ = \{A, B, C, D, E\}\)।
   যেহেতু \(A^+\) সম্পূর্ণ রিলেশনের সকল অ্যাট্রিবিউট নির্ণয় করতে পারে, তাই A একটি Candidate Key।
2. E-এর ক্লোজার:
   \(E^+ = \{E\}\)
   \(E ightarrow A\) প্রয়োগে: \(E^+ = \{E, A\}\)। যেহেতু \(A\) একটি ক্যান্ডিডেট কি, তাই \(E^+ = \{A, B, C, D, E\}\)।
   অতএব, E একটি Candidate Key।
3. CD-এর ক্লোজার:
   \(CD^+ = \{C, D\}\)
   \(CD ightarrow E \implies CD^+ = \{C, D, E\}\)
   \(E ightarrow A \implies CD^+ = \{A, C, D, E\}\)
   \(A ightarrow BC \implies CD^+ = \{A, B, C, D, E\}\)।
   \(C\) বা \(D\) একা কি হতে পারে না, তাই CD একটি Candidate Key।
4. BC-এর ক্লোজার:
   \(B ightarrow D \implies BC ightarrow CD \implies BC^+ = \{A, B, C, D, E\}\)।
   অতএব, BC-ও একটি Candidate Key।

চূড়ান্ত উত্তর: রিলেশন \(R\)-এর ক্যান্ডিডেট কি-সমূহ হলো {A, E, CD, BC}।

বিশ্লেষণ ও ধাপে ধাপে নরমালাইজেশন:

1. বর্তমান নরমাল ফর্ম: টেবিলটি 1NF-এ আছে, কিন্তু 2NF-এ নেই।
   কারণ: ক্যান্ডিডেট কি হলো যৌথ (Student_ID, Course_ID)। এখানে আংশিক নির্ভরতা বিদ্যমান:
   • Student_ID → Student_Name (আংশিক নির্ভরতা)।
   • Course_ID → Course_Name (আংশিক নির্ভরতা)।
2. 2NF-এ বিভাজন (আংশিক নির্ভরতা দূরীকরণ):
   • STUDENT(Student_ID, Student_Name)
   • COURSE(Course_ID, Course_Name, Instructor_Name, Instructor_Office)
   • ENROLLMENT(Student_ID, Course_ID) [উভয় টেবিলের ফরেন কি]।
3. 3NF-এ বিভাজন (ট্রানজিটিভ নির্ভরতা দূরীকরণ):
   COURSE টেবিলে Course_ID → Instructor_Name এবং Instructor_Name → Instructor_Office বিদ্যমান।
   এখানে নন-কি অ্যাট্রিবিউট Instructor_Office অপর নন-কি অ্যাট্রিবিউটের ওপর নির্ভরশীল (ট্রানজিটিভ নির্ভরতা)।
   একে ভেঙে দুটি টেবিলে বিভক্ত করা হলো:
   • INSTRUCTOR(Instructor_Name, Instructor_Office)
   • COURSE(Course_ID, Course_Name, Instructor_Name) [Instructor_Name হলো Foreign Key]।

চূড়ান্ত 3NF রিলেশনাল স্কিমা:
১. STUDENT(Student_ID, Student_Name)
২. INSTRUCTOR(Instructor_Name, Instructor_Office)
৩. COURSE(Course_ID, Course_Name, Instructor_Name)
৪. ENROLLMENT(Student_ID, Course_ID)

SQL সমাধান:

(ক) GROUP BY ও HAVING সহ কুয়েরি:

SELECT d.Dept_Name, AVG(e.Salary) AS Avg_Salary
FROM EMPLOYEE e
INNER JOIN DEPARTMENT d ON e.Dept_ID = d.Dept_ID
GROUP BY d.Dept_ID, d.Dept_Name
HAVING COUNT(e.Emp_ID) > 3
ORDER BY Avg_Salary DESC;

(খ) কোরিলেটেড সাবকুয়েরি (Correlated Subquery):

SELECT e1.Emp_ID, e1.Emp_Name, e1.Dept_ID, e1.Salary
FROM EMPLOYEE e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM EMPLOYEE e2
    WHERE e2.Dept_ID = e1.Dept_ID
);

ACID বৈশিষ্ট্যের বিশদ বিশ্লেষণ:

ট্রানজ্যাকশন \(T\)-এর কার্যপ্রণালী নিম্নরূপ:

T: 1. Read(A);  2. A = A - 10000;  3. Write(A);  4. Read(B);  5. B = B + 10000;  6. Write(B);  7. Commit;

1. Atomicity (পরমাণুতা):
   • ধাপ ৩-এর পর (\(A = 40,000\) টাকা) সিস্টেম ক্র্যাশ করলে অ্যাকাউন্ট B-তে টাকা জমা হয়নি।
   • পুনরায় চালু হলে রিকভারি ম্যানেজার Write-Ahead Log (WAL) পরীক্ষা করে দেখে যে <T, Commit> রেকর্ড নেই। ফলে সিস্টেম সঙ্গে সঙ্গে UNDO অপারেশন চালায় এবং অ্যাকাউন্ট A-এর টাকা ফিরিয়ে দিয়ে \(A = 50,000\) করে। অর্থাৎ ট্রানজ্যাকশন পুরোপুরি বাতিল হয়, কোনো টাকা নষ্ট হয় না।
2. Consistency (সঙ্গতি):
   • নিয়ম: মোট ব্যাংকিং ব্যালেন্স \(A + B = ৫০,০০০ + ২০,০০০ = ৭০,০০০\) টাকা।
   • লেনদেনের পূর্বে মোট ব্যালেন্স ৭০,০০০ টাকা। সফল লেনদেনের পরও \(৪০,০০০ + ৩০,০০০ = ৭০,০০০\) টাকা অপরিবর্তিত থাকে।
3. Isolation (বিচ্ছিন্নতা):
   • ধাপ ৩ ও ধাপ ৬-এর মধ্যবর্তী সময়ে অন্য কোনো অডিট ট্রানজ্যাকশন যদি ব্যালেন্স পড়ে, তবে সে \(A = ৪০,০০০\) ও \(B = ২০,০০০\) দেখতে পেত (মোট ৬০,০০০ টাকা — যা অসঙ্গত)।
   • এক্সক্লুসিভ লক (X-lock) প্রয়োগের মাধ্যমে DBMS অন্য কাউকে অন্তর্বর্তী মান দেখতে দেয় না যতক্ষণ না COMMIT সম্পন্ন হয়।
4. Durability (স্থায়িত্ব):
   • ধাপ ৭-এ Commit হওয়ার সাথে সাথে তথ্য হার্ডডিস্কে স্থায়ী হয়। এর ১ সেকেন্ড পর বিদ্যুৎ চলে গেলেও ডেটাবেস থেকে কোনো পরিবর্তন মুছে যাবে না; রিকভারি ম্যানেজার REDO লগের মাধ্যমে তা অক্ষুণ্ণ রাখে।