Skip to content

Database Management System

CSE · Midterm · Summer 25

Batch 65

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 2025 Course Code: CSE311, Course Title: Database Management System Level: 2 Term: 3 Batch: 65 Time: 1: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.) 1. You are tasked with maintaining a database. The tasks include: - Dropping obsolete columns from a table, - Deleting outdated records based on a date condition, and - Assigning new user roles with specific permissions. Classify each of these operations as DDL, DML, or DCL commands. Describe how executing each affects database structure, data integrity, and access control. | [5] | CO 1 2. a) During the COVID-19 pandemic, the Bangladesh government established a system to track vaccinations and testing across the country. Each citizen is registered with a unique National ID number and has a full name divided into first, middle, and last names, multiple contact phone numbers, and an address containing village, post office, and district. Citizens can register at multiple vaccination centers, each with a unique center code, name, and location. Each vaccination center offers different vaccine types, identified by a vaccine code and name. Citizens receive multiple vaccine doses; each dose record includes the date administered and the batch number. Some citizens also undergo COVID-19 tests at various testing centers, which have unique IDs, names, and locations. Each test has a test ID, date conducted, and test result. Citizens may report multiple COVID-related symptoms during follow-up calls. Each symptom record includes the symptom name and severity level. For emergency contact, each citizen provides contacts with full names, multiple phone numbers, and relation to the citizen. Healthcare workers assigned to vaccination and testing centers are identified by employee IDs, names, and roles. Some healthcare workers supervise centers, and each center must have exactly one supervisor. The government also tracks quarantine facilities, each with a unique ID, location, and capacity. Citizens may be assigned to quarantine for specific periods. Using the scenario, construct an ER diagram and then a schema diagram showing entities, attributes (including composite and multivalued), keys, all relationship types with cardinalities, weak entities, and participation constraints. | [7] | CO 1 b) Construct Relational Algebra to retrieve the required information from the given schema. Students(student_id, name, age) Enrollments(student_id, course_id, grade) I. Find the names of students who are older than 20. II. Find the course IDs of all courses where students have received a grade of 'A'. III. List all student names along with the course IDs they are enrolled. IV. Identify the students who have enrolled in every course offered in the system. V. Find all students and their grades specifically for the course with course_id = 'CSE101'. | [5] | CO 1 3. a) Construct SQL queries to retrieve the required information from the given schema. Employees (emp_id, name, department_id, salary, age) Departments (department_id, department_name, location) Projects (project_id, project_name, department_id, budget) I. Find the names of employees who work in departments located in Dhaka. II. List the departments where the total budget of all projects exceeds 250,000. III. Retrieve the names of employees whose salary is higher than the average salary of their respective departments. IV. Find the names of employees who work in departments that have at least one project with a budget less than 130,000. V. List employees who work in departments where every project has a budget greater than 100,000 | [8] | CO 2