Database Management System
CSE · Final · Spring 23
Paper 2 of 2By Md. Ferdous Foysal HridoySwitch
Paper 2 of 2
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
Final Examination, Spring 2023
Course Code: CSE311 Course Title: Database Management System
Level: 3 Term: 1
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 schemas: [CO2]
Course (id, name, course_code)
Student (id, name, email, phone)
Enrolment (id, course_id, student_id, enrollment_date, enrollment_status)
a) Write a SQL query to list all of the students id, name and email in ascending order of ID who are enrolled in course with code "CSE311" [2]
b) Write a SQL query find the name and ID of the students who enrolled in "Database Management System" course after 15 March 2023. [1]
c) Write a SQL query to find the total number of students in each course with course name. [2.5]
d) Write a SQL query to find course names that have more than 15 student enrolment. [3]
2. Consider the following tables: [CO2]
[Figure: A table showing Ticket No, Travel Date, Travel Time, Seat No, Passenger ID, and Bus No]
[Figure: A table showing Passenger ID, Passenger Name, and Passenger Contact]
[Figure: A table showing Bus No, Bus Name, Staff, and Route Name]
a) Write a SQL query to find the seat numbers of Student bearing ID 183-15-2458 travelling on 03-06-2023 at 10:30 AM. [1]
b) Find the contact information of the passenger who bought Ticket Number T302. [2]
c) List the names of all students who have travelled to Uttara Between 03-06-2023 to 09-06-2023. [3]
d) Find the name of the staff who assisted the passenger with ticket no T302. [2]
3. Discover Normalization for the following table up to Third Normal Form. Be certain to indicate necessary constraints. [10] [CO4]
[Figure: A table containing Order Number, Order Date, Customer ID, Customer Name, Customer Address, Product ID, Product Name, Quantity, and Price]
4. How do triggers work in a database management system, and what are some practical use cases for implementing triggers? [4.5] [CO3]
5. Create appropriate examples to demonstrate the use of rollback and commit within a database transaction? [4.5] [CO3]
6. How do ACID properties guarantee the reliability and consistency of transactions in a database system? [4.5] [CO3]