Database Management System
CSE · Final · Spring 26
Batch 66
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, Spring 2026
Course Code: CSE 311, Course Title: Database Management System
Level: 3 Term: 1 Batch: 66
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) Apply the principles of database normalization and transform the following relation to 3NF systematically showing the functional dependencies in each subsequent conversion step.
Pet_ID | Type | Age | Owner | Visit Date | Treatment
246 | DOG | 12 | Sharif | JAN 13/2025 | 01 - RABIES VACCINATION
246 | DOG | 12 | Sharif | MAR 27/2025 | 10 - EXAMINE AND TREAT
246 | DOG | 12 | Sharif | APR 02/2025 | 05 - HEART WORM TEST
298 | DOG | 2 | Ahona | JAN 21/2025 | 08 - TETANUS VACCINATION
298 | DOG | 2 | Ahona | MAR 10/2025 | 05 - HEART WORM TEST
341 | CAT | 4 | Raima | JAN 23/2001 | 01 - RABIES VACCINATION
341 | CAT | 4 | Raima | JAN 13/2025 | 01 - RABIES VACCINATION
519 | BIRD | 2 | Zohan | APR 30/2025 | 20 - ANNUAL CHECK UP
519 | BIRD | 2 | Zohan | APR 30/2025 | 12 - EYE WASH
Table 1: Pet Hospital Data
[8] CO1
1. b) A hospital has branches in different cities and wants doctors in each branch to access patient records quickly, even if one server becomes unavailable. Explain why a distributed database would be more suitable than a centralized database in this case. In your answer, distinguish how fragmentation and replication would help the hospital, and mention one possible challenge of using such a system.
[4] CO1
2. You are designing a payroll system with the following schemas:
- Employees(emp_id, name, dept_id, salary),
- Departments(dept_id, dept_name, budget),
- SalaryAudit(log_id, emp_id, old_salary, new_salary, updated_at).
Based on this schema construct the following Stored Procedure and Trigger.
CO2
2. a) A stored procedure UpdateDepartmentsSalaries(dept_id, percentage) that increases the salary of all employees in a given department by the specified percentage.
[5]
2. b) An appropriate trigger on Employees such that, when the procedure is executed, the old and new salary values are logged in the SalaryAudit table along with other entries as mentioned in the schema.
[5]
3. Consider the following schema to construct the queries below. Primary keys are boldfaced and foreign keys are italicised.
- Users(user_id (PK), user_name, email)
- Genres(genre_id (PK), genre_name)
- Movies(movie_id (PK), movie_title, release_year, genre_id (FK -> Genres.genre_id))
- Subscriptions(subscription_id (PK), plan_name, start_date, end_date, user_id (FK -> Users.user_id))
- WatchHistory(user_id (PK, FK -> Users.user_id), movie_id (PK, FK -> Movies.movie_id), watch_date, watch_time)
- Ratings(user_id (PK, FK -> Users.user_id), movie_id (PK, FK -> Movies.movie_id), rating)
CO2
3. a) Write an SQL query to display movie titles along with their genre names.
[2]
3. b) Write an SQL query to display user names along with their subscription plan names, including users who do not have any subscription.
[2]
3. c) Write an SQL query to display user names, movie titles, and ratings given by users, including users who have not rated any movie.
[2]
3. d) Write an SQL query to find users who have watched ALL movies of the 'Action' genre.
[2]
3. e) Write an SQL query to find the movie(s) with the highest average rating, considering only movies that have at least 5 ratings.
[2]
4. Consider a University database with five tables:
- Students (student id, name, email, phone, address, dept id),
- Instructors (instructor id, name, dept id),
- Courses (course id, course name, instructor id, dept id, credits),
- Enrollments (enrollment id, student id, course id, semester), and
- Grades (grade id, enrollment id, letter grade, numeric grade).
CO3
4. a) Analyze how database views and access control mechanisms can be used to support different user roles (student, instructor, and department head) while protecting sensitive information.
[4]
4. b) Examine how improper privilege assignment on base tables could lead to security risks, and suggest how GRANT/REVOKE or role-based access control can mitigate these issues.
[3]
4. c) Analyze a transaction scenario where a student is enrolled in two courses with corresponding grade entries. Discuss how the use of transactions and savepoints can handle partial failures while maintaining data consistency, and explain the implications for both reliability and security.
[3]