Skip to content

Database Management System

CSE · Final · Spring 23

Paper 2 of 2By Md. Ferdous Foysal HridoySwitch

Paper 2 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: [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]