Skip to content

Database Management System

ITM · Final · Spring 25

Section NJ

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 ITM Faculty of Science & Information Technology Final Examination, Spring 2025 Course Code: ITM 221; Course Title: Database Management Systems Sections & Teachers: NJ 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. Employee Employee_id | Employee_name | Department_id | Department 101 | Steven | 200 | HR 202 | John Doe | 201 | Finance 301 | Lex | 114 | Marketing 402 | Alexander | 203 | Accounting 501 | Bruce | 121 | IT Salary EMPLOYEE_ID | Salary | PHONE_NUMBER | HIRE_DATE | Department_ID 101 | 20000 | 5151234567 | 2003-06-17 | 200 202 | 3598 | 5241234567 | 2005-09-21 | 201 301 | 3000 | 5151258567 | 2001-01-13 | 114 404 | 79000 | 5151248567 | 2006-01-03 | 203 505 | 348799 | 515489567 | 2005-06-25 | 121 102 | 12390 | 578489567 | 2006-02-05 | 123 Develop SQL queries for the given question: a. Retrieve the average salary for each department. b. Count the number of employees in each department. c. Get the details of employees who work in the "Marketing Department". d. Get the employee information whose name starts with "A". e. Find the phone numbers of those employees who were hired between 17th June 2003 and 3rd January 2006. 2. Consider the following tables: Topic: DIU library Management DIU library management data where student's information is recorded, when a student borrowed a book, when he returned etc. StudentID: 0242200051888888 | DepartmentName: ITM StudentName: Sumaiya | DepartmentNo: 51 StudentEmail: sumu23@gmail.com | BorrowedDate: 20-04-24 StudentPhoneNo: 01789955454 | ReturnDate: 04-05-24 BorrowID: 04 BookID | BookName | AuthorName | Publisher 04 | To Kill a Mockingbird | Harper Lee | HarperCollins 05 | 1984 | George Orwell | Penguin Books 06 | The Great Gatsby | F. Scott Fitzgerald | Scribner a) Normalize the table from 1NF up to BCNF; examining every step of the process from those tables given in question 2 with proper explanation. [Marks-8] [CLO-3 Level-3] b) Distinguish between full functional dependency and partial dependency from the above tables. [Marks-2] c) Analyze two anomalies that may exist in the above tables. [Marks-4] d) Student_Id | Student_name | Student_Address | Course_no | Course_title Explain the current normalization level of the given table. [Marks-2] 3. a) Establish an Entity relationship diagram based on the tables given in question 2 with appropriate attribute type, cardinality, and relationship. [Marks-5] [CLO-2 Level-3] b) Consider the following 2 tables find the output of Inner join and Left outer join [Marks-4] flightNo | from | to | distance | departs | aircraftId 101 | New York | Los Angeles | 4500 | 10:00 AM | A1 102 | Chicago | Miami | 2000 | 2:00 PM | A2 103 | Dallas | Seattle | 3000 | 6:00 PM | A2 104 | Boston | Houston | 2600 | 8:30 AM | A6 aircraftId | airName | range A1 | Boeing 737 | 5000 A2 | Airbus A320 | 4500 A3 | Embraer E175 | 3500 A4 | Bombardier CRJ | 4000 4. a) Alice initiates a transfer of 5,000 to Bob. The system debits Alice's account, but due to a network failure, the credit to Bob's account fails. Based on this case, which ACID properties are affected, explain? Explain the states of the transaction for that transaction [Marks-5] [CLO-5 Level-2]