Database Management System
ITM · Final · Summer 25
Section NJ(A)
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]