IGNOU BCSL-34
- DBMS Lab,
Latest Solved Assignment
(July 2023 - January 2024)

Q1. A XYZ-star hotel requires a computerized system to automize its front office operations that support the following functionalities:

• Easy input facility for new data
• Keep details about rooms, tariff, details of occupied rooms, details of unoccupied rooms, check-in/ check-out timings, staff etc..
• Update necessary details about the room-billing, extra facilities used like gym, laundry, STD/ISD calls made etc.
• Query support
• Report generation

Perform the following tasks:

(i) Draw the ER diagram by identifying the entities, relationships and cardinality by using any of the drawing tools like smartdraw, dia, visio, conceptdraw etc.. Follow proper conventions.
(ii) Create suitable database to support/accommodate all the functionalities referred above.
(iii) Using MS-Access, design various forms to support the hotels’ front office operations such as enquiry form, tariff, categories of rooms along with their category, list of vacant rooms, list of occupied rooms, list of unoccupied rooms, room-billing (which includes extra facilities also), restaurant billing, staff details, list of phone numbers, reservations etc..
(iv) Report generation like daily reports of list of vacant rooms, list of occupied rooms, onduty staff list, list of room reservations made etc.

Q1. The objective of this project is to create a “Bus Ticket Booking Management System” for ABC Travels company that should automate and provide answers of different queries and generate reports related to bus ticket booking that were previously tedious and error prone.
They ply buses to various destinations from Delhi. They have various categories of buses like luxury, semi-luxury, deluxe, super deluxe, Volvo and sleeper. This system is built on the concept of booking bus tickets in advance. The passenger can book tickets only one month before their travel. The user can check the bus schedule, book tickets, cancel reservations, and check the bus status board using this system. When purchasing tickets, the user must first enter the bus number, after which the system will display the entire number of bus seats along with the passengers’ names, and the user must then enter the number of tickets, seat number, and person’s name.
To develop the system, you are required to create the database in any RDBMS, which must provide the following functionalities:
• Query support
• Report generation
• Easy input facility for new data
• Keep details about costs, ticket sales etc
• Maintain necessary details about customer, staff, bus schedules, seats etc

After creating the database you must perform the following tasks:

i) Find out how many customers booked the tickets but did not board the bus on a particular day.
ii) To display how many seats are filled and how many seats are vacant for a particular trip of a specific category of bus.
iii) Find the name of driver who was on duty on a particular day.
v) To calculate the no. of passengers travelled on a particular day and particular month.
v) To calculate total income earned for that day.
vi) To calculate total income earned for a particular month.
vii) To calculate no. of male, female and kids travelled on a particular day.
viii) Design a report to display different age-group of passengers who prefer the luxury, semi-luxury, deluxe, super deluxe, Volvo and sleeper bus travel.
ix) To display the report consisting of the complete status of each bus on a particular day.

Note: You must perform the above said activities and also take prints of screenshots of the layouts, sample input and output along with the necessary documentation for this practical question. Assumptions can be made wherever necessary.