Skip to content

Database Management System

ITM · Final · Summer 25

Section NJ(A)

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

Read the questions as text

Read off the PDF by AI, so it may have mistakes. The PDF is the original.

Daffodil International University Department of Information Technology & Management Faculty of Science & Information Technology Final Examination, Summer 2025 Course Code: ITM 221; Course Title: Database Management 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. Consider the following Schema and Solve with SQL for the following questions: Project (P_ID, Project_Name, Manager, Budget) Employee (E_ID, Employee_Name, Address, Department_No) Department (Department_No, Department_Name, Location) Emp_Timehour (P_ID, E_ID, Hour_Rate) 1. Find the names of employees who worked on the project with the highest budget. 2. List all projects managed by the same manager who manages the project named 'AI System'. 3. Find the total hours worked (sum of Hour_Rate) by employees on each project. 4. Find the average budget of all projects. 5. Find all projects with a budget between 50,000 and 100,000. [Marks: 10, CLO-3, Level-3] 2. Imagine you want to withdraw money from an ATM. You provide all the required details, and the system asks for the withdrawal amount. You input the amount and confirm the process. However, during this process, an error occurs, and the transaction does not complete. Consider the above situation. Explain the states of the transaction by drawing a state diagram. Also, explain how the system maintains consistency in this case. [Marks: 5, CLO-5, Level-2] 3. a. Apply the concepts of insert, update, and delete anomalies to justify the need for normalization with relevant examples. [Marks: 6, CLO-4, Level-4] CustomerID: L3J | CustomerName: Jackson CustomerStreet: 123 - 4th Avenue | CustomerCity: Fonthill CustomerPhone: 284-6783 Purchased Artworks ArtID | Art Title | ArtistNo | Artist Name | Artist Phone | Purchase Date | Sales Price A001 | Laugh with Teeth | 03 | Carol Channing | 245-3562 | 09/17/2000 | 7000 A003 | South toward Sea | 15 | Dennis Frings | 245-3250 | 05/11/2000 | 1800 A004 | At the Movies | 03 | Carol Channing | 245-3562 | 02/14/2002 | 5550 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, CLO-4, Level-4] 4. Outline ERD from the result of Normalization that you solve in Q.3.c. [Marks: 4, CLO-2, Level-4] Book Book_id | Title | Author_name | Genre | Price | Published year B101 | The Great Gatsby | F. Scott Fitzgerald | Classic | $120 | 1965 B103 | Brave New World | Aldous Huxley | Mystery | $60 | 1932 B104 | Pride and Prejudice | Jane Austen | Romance | $50 | 2022 Customer Purchase Customer_id | Customer_name | Book_id | Purchase_amount C101 | Emily Brown | B101 | 120 C101 | Emily Brown | B103 | 60 C103 | Alice Smith | B105 | 50 5. Show the results of inner join, full outer join for the schema Book and Custmer_Purchase. [Marks: 4, CLO-4, Level-4]