Database Management System

Daffodil International University Department of Information Technology & Management Faculty of Science & Information Technology Final Examination, Fall 2025 Course Code: ITM 221; Course Title: Database Managemnt Systems Sections & Teachers: NJ(A) Time: 2:00 Hrs Marks: 40 Answer ALL Questions [The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.] 1. Imagine you want to make a payment using your credit card at a point-of-sale (POS) terminal or an online payment gateway. You provide all the required details, such as card number, expiry date, CVV, and OTP (if required). The system then asks for payment confirmation. You confirm the transaction, but during processing, an error occurs (for example, network failure or insufficient credit limit), and the transaction does not complete. [Marks-5] CLO-5 Level-4 Identify the different states of the credit card transaction by drawing a state diagram. Explain how the system maintains transaction consistency and prevents incorrect charges or data corruption in this failure scenario. 2 a. Apply the concepts of insert, update, and delete anomalies to justify the need for normalization for the Project Billing Receipt. [Marks-6] CLO-4 Level-4 Project Billing Receipt Project No: 1001 Bill No: 405 Project Name: Class Monitoring System Bill Date: 12/01/22 Dept No: 35 Dept Name: Marketing Dept Location: Ashulia, Dhaka EmployeeNo | Name | Responsibility | Phone | Salary | ManagerId | Manager name | Manageremail 800 | Arif Rahman | Analyst | 017888 | 2222 | M101 | Lira | l@gmail 801 | Rani | Developer | 1234445 | 4000 | M101 | Lira | l@gmail 805 | Afjal | Developer | 56777 | 9000 | M103 | Naima | Nm@gmail Consider the given schema and answer the following questions b. List the normalization rules sequentially [Marks-3] CLO-4 Level-4 c. Normalize the table from 1NF up to 3NF; examining every step of the process with proper explanation. [Marks-8] 3. Draw ERD from the result of Normalization that you solve in Q.2.c. [Marks-4] CLO-3 Level-3 Table: Student studentID | studentName | dept | batch 101 | Arefin | CSE | 46 102 | Maria | ITM | 45 103 | Tamim | CSE | 47 104 | Sabila | ITM | 46 Table: BookBorrow borrowID | studentID | bookName | borrowDate | Borrow_charge B001 | 101 | Database Design | 12/10/2022 | 100 B002 | 101 | AI for Beginners | 15/10/2022 | 100 B003 | 103 | Cloud Computing | 20/10/2022 | 500 B004 | 103 | Web Technologies | 22/10/2022 | 700 4 Show the results of inner join, full outer join for the schema Student and BookBorrow. [Marks-4] 5 Solve those problem for the schema Student and BookBorrow [Marks-10] CLO-3 Level-3 a) List all students from ITM department. b) Delete the borrowing record of "Web Technologies". c) Find student name whose name starts with T. d) Find the number of student in each department. e) Find those student name whose borrowed charge greater than Arefin's borrowed charge. f) Find the average charged amount of CSE department students. g) Find the student name who borrowed at 22/10/2022. h) Find student list of batch 46.