Database Management System
ITM · Final · Spring 25
Section NJ
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]