Database Management System
CSE · Final · Spring 23
Paper 1 of 2By Mohammad Al FaiedSwitch
Paper 1 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:
Course (id, name, course_code)
Student (id, name, email, phone)
Enrolment (id, course_id, student_id, enrollment_date, enrollment_status)
a) Write an 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 an SQL query to find the name and ID of the students who enrolled in "Database Management System" course after 15 March 2023. [1]
c) Write an SQL query to find the total number of students in each course with course name. [2.5]
d) Write an SQL query to find course names that have more than 15 student enrolment. [3]
2. Consider the following tables:
Ticket No. | Travel Date | Travel Time | Seat No. | Passenger ID | Bus No.
T301 | 03-06-2023 | 10:30 AM | D3 | 163-15-54215 | S_11
T301 | 03-06-2023 | 10:30 AM | D4 | 163-15-54215 | S_11
T302 | 03-06-2023 | 10:30 AM | A1 | 183-15-2458 | S_02
T302 | 03-06-2023 | 10:30 AM | A2 | 183-15-2458 | S_02
T302 | 03-06-2023 | 10:30 AM | AJ | 183-15-2458 | S_02
T304 | 05-06-2023 | 7.00 AM | D2 | 123-15-8546 | S_12
T305 | 07-06-2023 | 7.00 AM | C2 | 131-15-3167 | S_12
Passenger ID | Passenger Name | Passenger Contact
163-15-54215 | Maruf Ahmed | 0175224668
183-15-2458 | Jahangir Khan | 0185245687
123-15-8546 | Ronia Shetty | 0154687453
131-15-3167 | Bristy Zahan | 0154687453
Bus No. | Bus Name | Staff | Route Name
S_11 | Surjomukhi 11 | Jehir | Dhanmondi
S_12 | Surjomukhi 12 | Alanin | Uttara
S_02 | Surjomukhi 2 | Alamin | Mirpur
a) Write an 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]
Order Number | Order Date | Customer ID | Customer Name | Customer Address | Product ID | Product Name | Quantity | Price
001 | 2022-01-01 | C001 | John Smith | 123 Main St | P001, P002 | MacBook, Laser Printer | 2, 1 | 1000, 500
002 | 2022-01-05 | C002 | Jane Doe | 456 Oak Ave | P004 | Asus Zenbook | 1 | 1200
003 | 2022-01-10 | C003 | Bob Johnson | 789 Maple St | P003, P005 | Samsung Z-Fold, Realme Bud 3 | 3, 1 | 3000, 300
4. How do triggers work in a database management system, and what are some practical use cases for implementing triggers? [4.5]
5. Create appropriate examples to demonstrate the use of rollback and commit within a database transaction? [4.5]
6. How do ACID properties guarantee the reliability and consistency of transactions in a database system? [4.5]