Database Management System
CSE · Final · Summer 26
Paper 4 of 4Batch 67 · ALI JAHAN RIASHADSwitch
Batch 67
Paper 4 of 4
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 Computer Science & Engineering
Final Examination, Summer 2026
Course Code: CSE 311, Course Title: Database Management System
Level: 3 Term: 1 Batch: 67
Time: 02: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. a) A bank stores customer data across several branches and keeps copies of important account information at multiple locations. During a network failure, some branches remain available but may show outdated data. Identify the suitable DBMS type, fragmentation and replication techniques, and explain the CAP theorem trade-off in this scenario. | 5 | CO3
1. b) A university plans to move its student database from local servers to the cloud for better accessibility, scalability, and cost management. Explain the suitable cloud service model and deployment model, describe two cloud characteristics, and mention one security concern the university should consider. | 5 |
2. | Student ID | Student Name | Dept ID | Dept Name | Course ID | Course Name | Instructor ID | Instructor Name | Semester | Grade |
| S101 | Rahim Ahmed | D01 | CSE | C201 | Database Systems | I10 | Dr. Hasan | Spring 2026 | A |
| S101 | Rahim Ahmed | D01 | CSE | C202 | Data Structures | I11 | Ms. Nusrat | Spring 2026 | B+ |
| S102 | Farzana Islam | D01 | CSE | C201 | Database Systems | I10 | Dr. Hasan | Spring 2026 | A- |
Normalize the relation up to Third Normal Form (3NF). Show the resulting relations and clearly identify the primary keys and foreign keys. | 5 | CO1
3. Employee (Emp_ID, Emp_Name, Job_Title, Salary, Hire_Date, Dept_ID, Manager_ID)
Department (Dept_ID, Dept_Name, Location_ID)
Location (Location_ID, City, Country)
Project (Project_ID, Project_Name, Budget, Dept_ID)
Employee_Project (Emp_ID, Project_ID, Assigned_Date, Working_Hours)
Write SQL queries for the following:
i. Display the employee name, job title, department name, city, and salary of employees earning more than 50,000. Sort the result by salary in descending order. | (1) | CO2
ii. Define a view employee_details(Emp_ID, Emp_Name, Job_Title, Dept_Name). | (1) |
iii. Display each employee's name, manager's name, department name, and city. Employees without an assigned manager must also appear. | (1) |
iv. Display the employee name, project name, department name, and working hours for employees assigned to projects with a budget greater than 500,000. | (2) |
v. Display the project name, department name, and budget of projects whose budget is greater than the average project budget. | (2) |
vi. Display the employee name, department name, project name, and working hours of employees whose working hours are greater than the average working hours of employees assigned to the same project. | (3) | 10 |
4. a) Write stored procedures for the following:
i. Create a stored procedure named CreateRoleWithPrivilege that accepts the role name, table name, and privilege type as IN parameters. The procedure should create the role and grant the specified privilege on the given table. | (3) | 7 | CO3
ii. Create a stored procedure named CreateDatabaseUser that accepts a username and password as IN parameters and creates a new database user using the provided credentials. | (2) |
iii. Create a stored procedure named AssignRoleToUser that accepts a role name and username as IN parameters and grants the specified role to the user. | (2) |
4. b) Buses (bus_id, bus_name, total_seats, booked_seats)
Bookings (booking_id, passenger_id, bus_id, booking_date, booking_status)
i. Write a trigger on the Bookings table that checks seat availability when a passenger attempts to book a ticket. The booking must be prevented if all seats on the selected bus have already been booked. Display a suitable error message when the booking cannot be completed. | (4) | 8 |
ii. Write a trigger that decreases the number of booked seats by 1 when a booking status changes from Confirmed to Cancelled. | (4) |