Scribd
Upload
1 views32 pages

System Design & Development Final

The document outlines the design and implementation of a hotel management database using MySQL, detailing the creation of tables for bookings, customers, rooms, and pricing. It includes a Python script for managing user login, check-in, check-out processes, and room management functionalities. The system is structured to ensure data integrity and ease of use for administrative tasks within the hotel management context.

Uploaded by

a22300455
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
1 views32 pages

System Design & Development Final

The document outlines the design and implementation of a hotel management database using MySQL, detailing the creation of tables for bookings, customers, rooms, and pricing. It includes a Python script for managing user login, check-in, check-out processes, and room management functionalities. The system is structured to ensure data integrity and ease of use for administrative tasks within the hotel management context.

Uploaded by

a22300455
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Database Design:

An essential part of system design is creating an efficient data


storage structure. The process begins with developing a logical
model of the data. A database acts as a container that stores tables,
queries, reports, and rules that enforce data integrity and validation.
In a logical data model, information is arranged into separate tables
to remove anomalies and reduce redundancy. The quality of a
database largely depends on the structure of its tables and the
relationships among them.
This software project uses a database named hotel_management,
which consists of the following tables.
Database:
Tables:-
In my software of Library management. I have created many tables
to make the software user friendly and to make it different from
others.

Tables are shown below:

1. bookings

2. customers

3. login

4. room_prices
5. rooms

6. sno
import [Link] as myc
import datetime
print("\n* HOTEL MANAGEMENT SYSTEM *\n")

# ------------------------- CONNECT TO MYSQL ------------------------- #

mydb = [Link](
host="localhost",
user="root",
password="Somay@101")
cur = [Link]()

# ------------------------- CREATE DATABASE ------------------------- #

[Link]("CREATE DATABASE IF NOT EXISTS hotel_management")


[Link]("USE hotel_management")

# ------------------------- CREATE TABLES ---------------------------- #

[Link]("""
CREATE TABLE IF NOT EXISTS login(
username VARCHAR(30),
password VARCHAR(30)
)
""")
[Link]("""
CREATE TABLE IF NOT EXISTS sno(
customer_id INT,
booking_id INT
)
""")
[Link]("""
CREATE TABLE IF NOT EXISTS rooms(
room_no INT PRIMARY KEY,
room_type VARCHAR(30),
price INT,
status VARCHAR(30)
)
""")
[Link]("""
CREATE TABLE IF NOT EXISTS customers(
customer_id INT PRIMARY KEY,
name VARCHAR(30),
age INT,
gender CHAR(1),
phone VARCHAR(10)
)
""")
[Link]("""
CREATE TABLE IF NOT EXISTS bookings(
booking_id INT PRIMARY KEY,
customer_id INT,
room_no INT,
check_in DATE,
check_out DATE,
total_bill INT
)
""")
[Link]("""
CREATE TABLE IF NOT EXISTS room_prices(
standard INT,
deluxe INT,
suite INT
)
""")
[Link]()
print("TABLES CREATED SUCCESSFULLY")

# ------------------ DEFAULT VALUES ------------------ #

# login default
[Link]("SELECT * FROM login")
rows = [Link]()
if len(rows) == 0:
[Link]("INSERT INTO login VALUES(%s,%s)", ("admin",
"1234"))
[Link]()
# room prices default
[Link]("SELECT * FROM room_prices")
rows = [Link]()
if len(rows) == 0:
[Link]("INSERT INTO room_prices
VALUES(2000,5000,10000)")
[Link]()

# rooms default
[Link]("SELECT * FROM rooms")
rows = [Link]()
if len(rows) == 0:
[Link]("INSERT INTO rooms
VALUES(101,'Standard',2000,'Available')")
[Link]("INSERT INTO rooms
VALUES(201,'Deluxe',5000,'Available')")
[Link]("INSERT INTO rooms
VALUES(301,'Suite',10000,'Available')")
[Link]()

# initialize sno table


[Link]("SELECT * FROM sno")
rows = [Link]()
if len(rows) == 0:
[Link]("INSERT INTO sno VALUES(0,0)")
[Link]()
print("DEFAULT DATA INSERTED SUCCESSFULLY\n")
#------------------------- MAIN PROGRAM ---------------------------#
while True:
print("""
1. Login
2. Exit
""")
ch = input("Enter your choice: ")

# ------------------------- LOGIN ---------------------------- #


if ch == "1":
pas = input("Enter Password: ")
[Link]("SELECT * FROM login")
data = [Link]()
username, real_pass = data[0]
if pas == real_pass:
print("\nLOGIN SUCCESSFUL\n")
while True:
print("""
------------------- ADMIN MENU -------------------
1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------
""")
adm = input("Enter your choice: ")

# ---------------------- CHECK-IN ---------------------- #

if adm == "1":
print("\n-- AVAILABLE ROOMS --")
[Link]("SELECT * FROM rooms WHERE
status='Available'")
rooms = [Link]()
if len(rooms) == 0:
print("No rooms available!\n")
continue
for r in rooms:
print(f"Room {r[0]} | {r[1]} | Price: {r[2]}")
room_no = int(input("Enter Room Number: "))
[Link]("SELECT * FROM rooms WHERE room_no=%s
AND status='Available'", (room_no,))
room = [Link]()
if len(room) == 0:
print("Invalid or unavailable room!\n")
continue
# Customer details
name = input("Name: ")
age = int(input("Age: "))
gender = input("Gender (M/F): ").upper()
phone = input("Phone: ")
# Fetch and update SNO
[Link]("SELECT * FROM sno")
sno_data = [Link]()
cust, bok = sno_data[0]
cust += 1
bok += 1
check_in = [Link]()
# Insert into customers
[Link]("INSERT INTO customers
VALUES(%s,%s,%s,%s,%s)",
(cust, name, age, gender, phone))
# Insert booking
[Link]("""
INSERT INTO bookings
VALUES(%s,%s,%s,%s,NULL,NULL)
""", (bok, cust, room_no, check_in))
[Link]("UPDATE rooms SET status='Booked' WHERE
room_no=%s", (room_no,))
[Link]("UPDATE sno SET customer_id=%s,
booking_id=%s", (cust, bok))
[Link]()
print(f"\nCheck-In Successful! Customer ID: {cust} |
Booking ID: {bok}\n")
# ---------------------- ADD ROOM ---------------------- #
elif adm == "2":
room_no = int(input("Room Number: "))
room_type = input("Room Type (Standard/Deluxe/Suite):
").capitalize()
[Link]("SELECT * FROM room_prices")
std, dlx, ste = [Link]()[0]
prices = {"Standard": std, "Deluxe": dlx, "Suite": ste}
if room_type not in prices:
print("Invalid room type!\n")
continue
[Link]("INSERT INTO rooms VALUES(%s,%s,%s,%s)",
(room_no, room_type, prices[room_type],
"Available"))
[Link]()

print("Room added successfully.\n")

# ---------------------- CHECK-OUT ---------------------- #


elif adm == "3":
booking_id = int(input("Enter Booking ID: "))
[Link]("SELECT * FROM bookings WHERE
booking_id=%s AND check_out IS NULL", (booking_id,))
booking = [Link]()
if len(booking) == 0:
print("Invalid booking!\n")
continue
booking = booking[0]
room_no = booking[2]
check_in = booking[3]
check_out = [Link]()
days = (check_out - check_in).days
if days == 0:
days = 1
[Link]("SELECT price FROM rooms WHERE
room_no=%s", (room_no,))
price = [Link]()[0][0]
total = days * price
print(f"""
-------- BILL --------
Room: {room_no}
Days Stayed: {days}
Price/Day: {price}
Total Bill: {total}
----------------------
""")
[Link]("""
UPDATE bookings
SET check_out=%s, total_bill=%s
WHERE booking_id=%s
""", (check_out, total, booking_id))
[Link]("UPDATE rooms SET status='Available'
WHERE room_no=%s", (room_no,))
[Link]()
print("CHECK-OUT COMPLETE.\n")
# ---------------------- VIEW DATA ---------------------- #
elif adm == "4":
print("""
1. All Customers
2. All Rooms
3. Current Bookings
4. Booking History
""")
vv = input("Enter choice: ")
if vv == "1":
[Link]("SELECT * FROM customers")
customer_v=[Link]()
if len(customer_v) ==0:
print("NO CUSTOMER ENTERED")
else:
print("------------------------------------------------------------")
print("ID","|","NAME","|","AGE","|","GENDER","|","
PHONE")
print("------------------------------------------------------------")
for r in customer_v:
print(r[0],"|",r[1],"|",r[2],"|",r[3],"|",r[4])
print("---------------------------------------------------------")
elif vv == "2":
[Link]("SELECT * FROM rooms")
room_v=[Link]()
if len(room_v)==0:
print("NO ROOM ENTERED")
else:
print("------------------------------------------------------------")
print("ROOM NO","|","ROOM
TYPE","|","PRICE","|","STATUS")
print("------------------------------------------------------------")
for r in room_v:
print(r[0],"|",r[1],"|",r[2],"|",r[3])
print("---------------------------------------------------------")
elif vv == "3":
[Link]("SELECT * FROM bookings WHERE
check_out IS NULL")
bookingv=[Link]()
if len(bookingv)==0:
print("NO CURRENT BOOKING")
else:
print("------------------------------------------------------------")
print("BID","|","CID","|","ROOM NO","|","CHECK-
IN","|","CHECK-OUT","|","TOTAL BILL")
print("------------------------------------------------------------")
for r in bookingv:
print(r[0],"|",r[1],"|",r[2],"|",r[3],"|",r[4],"|",r[5])
print("---------------------------------------------------------")
elif vv == "4":
[Link]("SELECT * FROM bookings")
booking_v=[Link]()
if len(booking_v) ==0:
print("NO HISTORY OF BOOKING")
else:
print("------------------------------------------------------------")
print("BID","|","CID","|","ROOM NO","|","CHECK-
IN","|","CHECK-OUT","|","TOTAL BILL")
print("------------------------------------------------------------")
for r in booking_v:
print(r[0],"|",r[1],"|",r[2],"|",r[3],"|",r[4],"|",r[5])
print("---------------------------------------------------------")

# ---------------------- MODIFY ---------------------- #


elif adm == "5":
print("""
1. Modify Room Prices
2. Modify Specific Room
3. Back
""")
mm = input("Enter choice: ")
if mm == "1":
std = int(input("New Standard Price: "))
dlx = int(input("New Deluxe Price: "))
ste = int(input("New Suite Price: "))
[Link]("UPDATE room_prices SET standard=%s,
deluxe=%s, suite=%s",
(std, dlx, ste))
[Link]("UPDATE rooms SET price=%s WHERE
room_type='Standard'", (std,))
[Link]("UPDATE rooms SET price=%s WHERE
room_type='Deluxe'", (dlx,))
[Link]("UPDATE rooms SET price=%s WHERE
room_type='Suite'", (ste,))
[Link]()
print("Prices updated.\n")
elif mm == "2":
room_no = int(input("Room No: "))
[Link]("SELECT * FROM rooms WHERE
room_no=%s", (room_no,))
rr = [Link]()
if len(rr) == 0:
print("Room not found.\n")
continue
print("""
1. Change Price
2. Change Status
""")
cc = input("Enter: ")
if cc == "1":
new = int(input("New Price: "))
[Link]("UPDATE rooms SET price=%s WHERE
room_no=%s", (new, room_no))
elif cc == "2":
new = input("New Status (Available/Booked): ")
[Link]("UPDATE rooms SET status=%s WHERE
room_no=%s", (new, room_no))
[Link]()
print("Room updated.\n")
# ---------------------- CHANGE PASSWORD ---------------------- #
elif adm == "6":
old = input("Current Password: ")
[Link]("SELECT password FROM login")
real = [Link]()[0][0]
if old == real:
new = input("New Password: ")
[Link]("UPDATE login SET password=%s", (new,))
[Link]()
print("Password updated.\n")
else:
print("Incorrect password.\n")
elif adm == "7":
print("Logging out...\n")
break
else:
print("WRONG PASSWORD\n")

# EXIT PROGRAM
elif ch == "2":
print("Exiting System...")
break
else:
print("Invalid choice.\n")
* HOTEL MANAGEMENT SYSTEM *

TABLES CREATED SUCCESSFULLY


DEFAULT DATA INSERTED SUCCESSFULLY

1. Login
2. Exit

Enter your choice: 1


Enter Password: 1234

LOGIN SUCCESSFUL

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 1

-- AVAILABLE ROOMS --
Room 101 | Standard | Price: 2000
Room 201 | Deluxe | Price: 5000
Room 301 | Suite | Price: 10000
Enter Room Number: 101
Name: Gautam
Age: 17
Gender (M/F): M
Phone: 8537849190

Check-In Successful! Customer ID: 1 | Booking ID: 1

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 2


Room Number: 302
Room Type (Standard/Deluxe/Suite): Suite
Room added successfully.

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 1

-- AVAILABLE ROOMS --
Room 201 | Deluxe | Price: 5000
Room 301 | Suite | Price: 10000
Room 302 | Suite | Price: 10000
Enter Room Number: 301
Name: Somay
Age: 17
Gender (M/F): M
Phone: 6392638680

Check-In Successful! Customer ID: 2 | Booking ID: 2

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 1

-- AVAILABLE ROOMS --
Room 201 | Deluxe | Price: 5000
Room 302 | Suite | Price: 10000
Enter Room Number: 302
Name: Abhimanyu
Age: 17
Gender (M/F): M
Phone: 7462782430
Check-In Successful! Customer ID: 3 | Booking ID: 3

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 3


Enter Booking ID: 1

-------- BILL --------


Room: 101
Days Stayed: 1
Price/Day: 2000
Total Bill: 2000
----------------------

CHECK-OUT COMPLETE.

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 4

1. All Customers
2. All Rooms
3. Current Bookings
4. Booking History

Enter choice: 1
---------------------------------------------------------------------------------
ID | NAME | AGE | GENDER | PHONE
---------------------------------------------------------------------------------
1 | Gautam | 17 | M | 8537849190
---------------------------------------------------------------------------------
2 | Somay | 17 | M | 6392638680
---------------------------------------------------------------------------------
3 | Abhimanyu | 17 | M | 7462782430
---------------------------------------------------------------------------------
------------------- ADMIN MENU -------------------
1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 4

1. All Customers
2. All Rooms
3. Current Bookings
4. Booking History

Enter choice: 2
---------------------------------------------------------------------------------
ROOM NO | ROOM TYPE | PRICE | STATUS
---------------------------------------------------------------------------------
101 | Standard | 2000 | Available
---------------------------------------------------------------------------------
201 | Deluxe | 5000 | Available
---------------------------------------------------------------------------------
301 | Suite | 10000 | Booked
---------------------------------------------------------------------------------
302 | Suite | 10000 | Booked
---------------------------------------------------------------------------------

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 4

1. All Customers
2. All Rooms
3. Current Bookings
4. Booking History

Enter choice: 3
---------------------------------------------------------------------------------
BID | CID | ROOM NO | CHECK-IN | CHECK-OUT | TOTAL BILL
---------------------------------------------------------------------------------
2 | 2 | 301 | 2025-11-28 | None | None
---------------------------------------------------------------------------------
3 | 3 | 302 | 2025-11-28 | None | None
---------------------------------------------------------------------------------

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 4

1. All Customers
2. All Rooms
3. Current Bookings
4. Booking History

Enter choice: 4
---------------------------------------------------------------------------------
BID | CID | ROOM NO | CHECK-IN | CHECK-OUT | TOTAL BILL
---------------------------------------------------------------------------------
1 | 1 | 101 | 2025-11-28 | 2025-11-28 | 2000
---------------------------------------------------------------------------------
2 | 2 | 301 | 2025-11-28 | None | None
---------------------------------------------------------------------------------
3 | 3 | 302 | 2025-11-28 | None | None
---------------------------------------------------------------------------------

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 5

1. Modify Room Prices


2. Modify Specific Room
3. Back

Enter choice: 1
New Standard Price: 2000
New Deluxe Price: 8000
New Suite Price: 15000
Prices updated.

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 5

1. Modify Room Prices


2. Modify Specific Room
3. Back

Enter choice: 2
Room No: 201

1. Change Price
2. Change Status

Enter: 1
New Price: 4000
Room updated.

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 5

1. Modify Room Prices


2. Modify Specific Room
3. Back

Enter choice: 2
Room No: 201

1. Change Price
2. Change Status

Enter: 2
New Status (Available/Booked): Booked
Room updated.

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 4

1. All Customers
2. All Rooms
3. Current Bookings
4. Booking History

Enter choice: 2
---------------------------------------------------------------------------------
ROOM NO | ROOM TYPE | PRICE | STATUS
---------------------------------------------------------------------------------
101 | Standard | 2000 | Available
---------------------------------------------------------------------------------
201 | Deluxe | 4000 | Booked
---------------------------------------------------------------------------------
301 | Suite | 15000 | Booked
---------------------------------------------------------------------------------
302 | Suite | 15000 | Booked
---------------------------------------------------------------------------------

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------

Enter your choice: 6


Current Password: 1234
New Password: 123
Password updated.

------------------- ADMIN MENU -------------------


1. Check-In (Add Customers)
2. Add New Room
3. Check-Out (Generate Bill)
4. View Customer & Billing Details
5. Modify Prices / Room Details
6. Change Password
7. Logout
---------------------------------------------------
Enter your choice: 7
Logging out...

1. Login
2. Exit

Enter your choice: 2


Exiting System...

You might also like