Database Management System
CIS · Final · Spring 25
Read the questions as text
Read off the PDF by AI, so it may have mistakes. The PDF is the original.
Daffodil International University
Faculty of Science & Information Technology
Department of Computing and Information System
Final Examination, Spring-2025
Course Code: CIS222, Course Title: Database Management System
Level: 2 Term: 2
Exam Duration: 2 Hours
Marks: 40
Answer ALL Questions
[The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of the exam must be answered sequentially.]
1. (a) How is a DBMS different from a File Processing System? Briefly explain with proper example. [3] | CO1, L-1,2
(b) What is a foreign key in a relational database, and how does it help maintain data integrity between tables? [3] | CO1, L-1,2
(c) Given the following relation schema, which key would you define as the primary key if each book can have multiple authors and each author can write multiple books?
Relation Schema: BookAuthors(BookID, AuthorID) [2] | CO1, L-1,2
2. (a) The students from the CIS department who donated blood to assist patients in different hospitals are listed in Table 01.
Donor ID | Donor_Name | Blood_Group | Contact_No | Donation_Date | Hospital_Details
101 | ZUNAYED | A+ | 1234567890 | 3/1/2024 | City Hospital, Dhaka
102 | SABBIR | B+ | 987654321 | 3/5/2024 | Green Health Clinic, Dhaka
103 | Masud | O+ | 1122334455 | 3/2/2024 | City Hospital, Dhaka
104 | JYOTI | A- | 2233445566 | 3/8/2024 | City Hospital, Dhaka
105 | TARIF | AB+ | 3344556677 | 3/9/2024 | Green Health Clinic, Dhaka
106 | AMINA | B+ | 4455667788 | 3/4/2024 | Dhaka Medical College, Dhaka
Table 01: Blood_Donation_System
Now, display the result of each step in the normalization process (up to 3NF), and provide the data dictionary for each resulting table. [8] | CO3, L-1,2,4
(b) Explain the purpose of the ON clause in a JOIN operation. When would it be appropriate to use a FULL OUTER JOIN instead of a LEFT JOIN or RIGHT JOIN? [5] | CO3, L-1,2,4
3. (a) In the Spring 2025 semester, 75 students were admitted to the CIS department. The admissions office has recorded the required information for all students, with the details of the first six presented in the following table.
S_ID | S_Name | SSC | HSC | District | Age
232-16-001 | NAZIUR | 4.80 | 4.50 | Dhaka | 20
232-16-002 | SANJANA | 5.00 | 5.00 | Chittagong | 19
232-16-004 | DEV | 4.25 | 4.10 | Dhaka | 21
232-16-006 | Sabbir | 4.90 | 5.00 | Sylhet | 27
232-16-007 | SHEKHA | 3.80 | 4.00 | Rajshahi | 22
232-16-009 | AKHI | 5.00 | 4.90 | Khulna | 18
Table 02: Student_Information
Write the SQL DDL based on the above-mentioned table. [Ensure appropriate integrity constraints for all attributes] [3] | CO3, L-3,5
(b) Write the SQL queries for the following tasks based on Table 02:
I. Find the student(s) with the highest age.
II. Increase Age by 3 for students under 20.
III. Find students whose SSC GPA is greater than the average SSC GPA.
IV. Find students whose age is between 19 and 23. [8] | CO3, L-3,5
4. (a) Please carefully read the following scenario:
Suppose Mr. Rahim wants to transfer 5000 Taka to Mr. Karim through the bKash app during Eid-ul-Fitr 2025 as Eid Salami. Mr. Rahim notices that the amount has been deducted from his account, but Mr. Karim did not receive the Eid Salami due to a system crash.
Now, visualize the different states of this transaction. Which property of a transaction is violated in this scenario? Briefly explain with your opinion. [4] | CO2, L-2,3,4
(b) Mr. Rakib, a student of the CIS department, wants to develop a database for the DIU Library Management System. Before implementing the database, he needs to design an ER diagram based on the following scenario.
The library system should store information about books, authors, and borrowers. A book can have multiple authors, and each author can write multiple books, A borrower can borrow multiple books, and a book can be borrowed by multiple borrowers over time. [Prepare at least three attributes for each entity.]
Now, visualize an ER diagram that is designed by Mr. Rakib. Make sure to capture the constraints on the relationships involved, and designate appropriate primary keys for the entities. [4] | CO2, L-2,3,4