Skip to content

Database Management System

CSE · Midterm · Summer 26

Batch 67

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 Department of Computer Science & Engineering Midterm Examination, Summer 2026 Course Code: CSE 311, Course Title: Database Management System Level: 3 Term: 1 Batch: 67 Time: 01:30 Hrs Marks: 25 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.] A university plans to develop a Campus Event Equipment Management System to manage students, clubs, events, equipment, and equipment bookings. Each Student has a unique StudentID, a Name consisting of First Name and Last Name, Date of Birth, Email, and one or more Phone Numbers. The system also stores the student's Address, which consists of House No, Street, City, and Postal Code. The student's Age is calculated from the Date of Birth. Each Club has a unique ClubID, Club Name, Establishment Date, Faculty Advisor Name, and Contact Email. A club can have multiple student members, and a student may join multiple clubs. For each membership, the system records the Joining Date and Member Role, such as President, Secretary, or General Member. Each Club can arrange multiple Events. Every Event has a unique EventID, Event Title, Event Date, Venue, Budget, and Event Type. An event is organized by one club, while one club can organize many events. The university maintains different types of Equipment, such as projectors, microphones, speakers, cameras, banners, and laptops. Each Equipment has a unique EquipmentID, Equipment Name, Category, Purchase Date, Condition Status, and Available Quantity. An event may require multiple equipment items, and the same equipment item may be used in multiple events at different times. For each equipment item booked for an event, the system stores the Requested Quantity, Approved Quantity, Booking Date, Return Date, and Return Condition. These details are recorded in an Equipment Booking entity. An Equipment Booking cannot exist without both an Event and an Equipment item and is uniquely identified by the combination of EventID and EquipmentID. 1. a) Based on the given scenario, construct an Entity Relationship Diagram (ERD) by applying appropriate database modeling techniques. Clearly show all types of entities, attributes, primary keys, all possible types of relationships, and cardinalities (1:1, 1:M, M: N). | 05 | CO1 1. b) Apply the rules of ER-to-Relational Mapping to transform the ERD from Question 1(a) into a relational database schema. Clearly show all table names, attributes, primary keys (PK), and foreign keys (FK). | 02 | CO1 1. c) Explain the purpose of DDL, DML, DCL, and TCL in a Database Management System (DBMS). Provide one appropriate SQL command example for each category. | 03 | Use the following schema to answer Questions 2 and 3: - Customer (customer_id, customer_name, email, phone, city) - Movie (movie_id, title, genre, language, duration_minutes, release_date, rating) - Cinema_Hall (hall_id, hall_name, location, seating_capacity) - Show_Schedule (show_id, movie_id, hall_id, show_date, show_time, ticket_price) - Booking (booking_id, customer_id, show_id, booking_date, ticket_quantity, total_amount, payment_status) Write SQL queries for the following statements: 2. i) Construct an SQL statement to create the Booking table with appropriate data types, primary key, and foreign key constraints. 2. ii) Construct an SQL statement to insert a new customer record with the following information: Customer ID = 101, Customer Name = 'Rahim Uddin', Email = 'rahim@email.com', Phone = '01711111111', and City = 'Dhaka'. 2. iii) Apply an SQL command to update the phone number of the customer whose Customer ID is 101 to '01812345678'. 2. iv) Write an SQL command to delete the customer record whose Customer ID is 101. 2. v) Construct an SQL query to display the movie title, genre, language, and rating of all movies in descending order of rating. | 05 | CO2 Write Subqueries for the following statements: 3. i) Display the title, genre, and rating of all movies whose rating is higher than the average rating of all movies. 3. ii) Display the customer ID, customer name, and city of the customers who have made at least one booking with a total amount greater than the average total amount of all bookings. 3. iii) Display the title and rating of the movie or movies that have the highest rating among all movies for which at least one show has been scheduled. | 10 | CO2