Skip to content

Database Management System

CSE · Final · Spring 23

Paper 1 of 2By Mohammad Al FaiedSwitch

Paper 1 of 2

This browser can’t show the PDF here

Open it, and your phone’s PDF viewer takes it, or download it.

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]