Database Management System
CSE · Midterm · Spring 26
Paper 1 of 3Batch 66 · Md. Ferdous Foysal HridoySwitch
Batch 66
Paper 1 of 3
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, Spring 2026
Course Code: CSE311, Course Title: Database Management System
Level: 3 Term: 1 Batch: 66
Time: 01:30 Hrs
Marks: 25
Answer ALL Question
[The figures in the right margin indicate the full marks and corresponding course outcomes. All portions of each question must be answered sequentially.]
1. In an online ticket booking system, two users attempt to reserve the last available seat at the same time and both receive successful booking confirmations. Later, after a sudden power failure, the system restarts and some of the confirmed bookings are no longer found in the database. | CO1
a) Describe the disadvantages of the conventional flat file system? | [2]
b) Analyze the given scenario and explain which ACID properties are violated, providing clear justification for each. | [3]
2. A national Environmental Protection Authority operates an integrated information system to monitor and manage environmental pollution across the country. The authority oversees multiple Regions and each Region contains several Monitoring Zones. Within each Monitoring Zone, multiple Sensor Stations are installed to collect environmental data such as air quality, water quality, noise levels, and temperature at regular time intervals. Each Sensor Station is equipped with multiple Sensors, where each Sensor measures a specific environmental parameter. Sensor readings are time-stamped and must be stored historically for trend analysis. Environmental Inspection Teams are assigned to Monitoring Zones and may conduct field inspections that result in inspection reports. Industries located within Monitoring Zones are required to comply with environmental regulations, and violations may be recorded against an industry based on sensor data or inspection findings. Regulatory actions such as warnings, fines, or shutdown orders may be issued, and the system must maintain a complete history of violations, inspections, and enforcement actions for legal and policy analysis. | CO1
Draw a complete ER diagram for the system. Include:
- entities, attributes (mark key attributes),
- relationship types,
- cardinality and participation constraints,
- any weak entity (if applicable).
b) [Figure: An ER diagram showing entities customer, loan, account, payment, savings-account, checking-account, interest-rate, and overdraft-limit with various attributes and relationships] | [4]
Convert the above ER diagram into a relational database schema. Be certain to indicate primary keys and foreign keys.
3. Match_schedule | Teams | Tournaments | CO2
match_id (PK) | Team_id (PK) | Tournament_id (PK)
Tournament_id (FK) | Team_name | Tournament_name
team1_id (FK) | Net_worth | prize_money
team2_id (FK) | tournament_id (FK) | starting_date
Winning_team_id (FK) | Players | ending_date
mvp_id (FK) | player_id (PK) | winning_team_id (FK)
- | player_name | -
- | salary | -
- | team_id (FK) | -
- | nationality | -
Construct SQL queries for the following questions using the given Schema.
a) Show the Tournament_id and Tournament_name of those tournaments that have prize money of more than 20 million USD. | [1]
b) Show the details of players from Brazil or Argentina. | [1]
c) Show details of those players who are getting paid more than 10 million dollars and less than 20 million dollars | [1]
d) Show the average salary of the players from every country. | [2]
e) Using a subquery, show the details of the highest-paid player in Real Madrid. | [2]
f) Using a subquery, find the name of the team that got the highest number of wins in the "UEFA Champions League." | [3]