Database Management System
CIS · Final · Summer 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, Summer 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 each question must be answered sequentially.]
1. (a) List three significant differences between a file-processing system and a DBMS. [3] CO1 L-1,2
1. (b) What is a candidate key in a relational database, and how does it differ from a primary key? Give an example. [3]
1. (c) Consider the following relation schema. If each student can enroll in multiple courses and each course can have multiple students, which key(s) would you define as the primary key?
Relation Schema: StudentCourses(StudentID, CourseID) [2]
2. (a) Unnormalized Table (Student Management Systems)
Enroll_ID | Student_ID | Student_Name | Dept | Course_Code | Course_Title | Instructor_Name | Instructor_Contact
301 | S01 | Arif | CSE | CSE101 | Database Systems | Dr. Karim | 1811111111
302 | S02 | Nabila | CSE | CSE101 | Database Systems | Dr. Karim | 1811111111
303 | S01 | Arif | CSE | CSE202 | Data Structures | Dr. Rahman | 1822222222
304 | S03 | Tanvir | EEE | EEE105 | Circuit Analysis | Dr. Alam | 1833333333
305 | S02 | Nabila | CSE | CSE202 | Data Structures | Dr. Rahman | 1822222222
306 | S04 | Meghna | BBA | BBA110 | Principles of Management | Dr. Hasan | 1844444444
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
2. (b) Differentiate between Natural Join and Inner Join in SQL with proper examples. Highlight how they handle join conditions, output, and control over attributes. [4]
3. (a) In Summer 2025, 50 new patients registered at a community clinic. The clinic kept basic records; the first six are shown below.
P_ID | P_Name | Gender | Age | District | Phone
25-001 | ARMAN | M | 20 | Dhaka | 01709890986
25-002 | NADIA | F | 19 | Chittagong | 01824543257
25-003 | SUMON | M | 21 | Sylhet | 01609856789
25-004 | RIMA | F | 27 | Rajshahi | 01767556498
25-005 | TAREQ | M | 22 | Khulna | 01876509873
25-006 | MUNNA | F | 18 | Dhaka | 01387653980
Table 02: Patient_Information
Write the SQL DDL & DML for three Patient records, enforcing appropriate integrity constraints for all attributes. [4] CO3 L-3,5
3. (b) Write the SQL queries for the following tasks based on Table 02.
I. Find the patient(s) with the highest age.
II. Increase Age by 3 for patients under 20.
III. Find the patient(s) phone number who uses the "Robi Sim Operator".
IV. Find patients whose age is between 19 and 23. [8]
4. (a) Explain what a derived attribute is in a database, and illustrate your answer with a real-world example. [3] CO2 L-2,3,4
4. (b) An online bookstore wants to create a database to manage its inventory, customer information, and sales. This system will track books, customers, and orders. The business has identified the following requirements:
- Customers can place orders, and a customer can place many orders.
- An order can contain one or more products (books).
- A book can have one or more authors, and an author can write multiple books.
Now, visualize an ER diagram that will be designed by you. Make sure to capture the constraints on the relationships involved, and designate appropriate primary keys for the entities. [5]