Assignment¶
Design, populate, and query a database for a hotel reservation system with the following business rules:
- The hotel has a number of rooms of two types: Deluxe and Suite
- For every night, some rooms are made available for reservation for a specific price.
- A guest can make a reservation for an avavilable room for one night. The reservation must include credit card payment info. At most one reservation can be made per night per room.
- A guest can check into a room that has been reserved. An attempt to check in without a reservation will generate an error.
- A guest can check out only after checking in. An attempt to check out multiple times or check out without checking in will generate an error.
Your Python code should provide the following:
A section to create the tables. The design must be in 3rd normal form following the conventions discussed in class and enforcing the business rules above.
Provide code to populate rooms and room availability with prices.
The function
reserve_room(room, date, guest_name, credit_card)
to make a reservation. A script that populates at least 300 reservations (e.g. usefaker
)The functions
checkin(room, date)
andcheckout(room, date)
to check guests in and out. Write a script that invokescheckin
andcheckout
for a buncha guests. Demonstrate that that the functions enforces the rules of the business.Write a query to list all guests who have stayed in a given room in 2021.
Write a query to list all dates on which a specific guest stayed at the hotel.
Define the database¶
import datajoint as dj
schema = dj.Schema('hotel')
[2024-09-09 19:34:13,205][INFO]: Connecting root@localhost:3306
[2024-09-09 19:34:13,223][INFO]: Connected root@localhost:3306
@schema
class Room(dj.Manual):
definition = """
room : int
---
room_type : enum('Deluxe', 'Suite')
"""
@schema
class RoomAvailable(dj.Manual):
definition = """
-> Room
date : date
---
price : decimal(6, 2)
"""
@schema
class Guest(dj.Manual):
definition = """
guest_id : int unsigned
---
guest_name : varchar(60)
index(guest_name)
"""
@schema
class Reservation(dj.Manual):
definition = """
-> RoomAvailable
---
-> Guest
credit_card : varchar(80)
"""
@schema
class CheckIn(dj.Manual):
definition = """
-> Reservation
"""
@schema
class CheckOut(dj.Manual):
definition = """
-> CheckIn
"""
dj.Diagram(schema)
Populate Room Availability¶
import faker
import random
import datetime
import tqdm
fake = faker.Faker()
# populate rooms
Room.insert((i, "Deluxe" if i%2 else "Suite") for i in range(80))
# Populate Room availability: 45 days starting on start_date
start_date = datetime.date(2023, 11, 1)
days = 45
for day in tqdm.tqdm(range(days)):
price = random.randint(40, 350)
RoomAvailable.insert(
dict(key,
date=start_date + datetime.timedelta(days=day),
price=price) for key in Room.fetch('KEY'))
100%|██████████| 45/45 [00:00<00:00, 136.84it/s]
Functions¶
class HotelException(Exception): pass
class RoomUnavailable(HotelException): pass
class RoomAlreadyReserved(HotelException): pass
class AlreadyChecked(HotelException): pass
class NoReservation(HotelException): pass
class NotCheckedIn(HotelException): pass
def reserve_room(room, date, guest_name, credit_card):
# lookup guest by name
keys = (Guest & {'guest_name': guest_name}).fetch('KEY')
if keys:
# if multiple found, use the first, for example
key = keys[0]
else:
# if not registered before, create a new guest id
key = dict(guest_id=random.randint(0, 2**32-1))
Guest.insert1(dict(key, guest_name=guest_name))
try:
Reservation.insert1(
dict(key, room=room, date=date, credit_card=credit_card))
except dj.errors.DuplicateError:
raise RoomAlreadyReserved(room, date.isoformat()) from None
except dj.errors.IntegrityError:
raise RoomUnavailable(room, date.isoformat()) from None
def check_in(room, date):
try:
CheckIn.insert1(dict(room=room, date=date))
except dj.errors.DuplicateError:
raise AlreadyChecked(room, date.isoformat()) from None
except dj.errors.IntegrityError:
raise NoReservation(room, date.isoformat()) from None
def check_out(room, date):
try:
CheckOut.insert1(dict(room=room, date=date))
except dj.errors.DuplicateError:
raise AlreadyChecked(room, date.isoformat()) from None
except dj.errors.IntegrityError:
raise NotCheckedIn(room, date.isoformat()) from None
Operations¶
# make a bunch of random reservations
number_of_guests = 100
max_nights = 20
for i in tqdm.tqdm(range(number_of_guests)):
guest = fake.name()
credit_card=' '.join((fake.credit_card_number(),
fake.credit_card_expire(),
fake.credit_card_security_code()))
for j in range(random.randint(1, max_nights)):
date = fake.date_between_dates(start_date, start_date+datetime.timedelta(days=45))
room = random.randint(0, 80)
try:
reserve_room(room, date, guest, credit_card)
except HotelException as e:
print(repr(e))
# show successful reservations
Reservation()
Guest()
# Try check in
check_in(2, datetime.date(2023, 11, 2))
---------------------------------------------------------------------------
NoReservation Traceback (most recent call last)
Cell In[18], line 2
1 # Try check in
----> 2 check_in(2, datetime.date(2023, 11, 2))
Cell In[14], line 7, in check_in(room, date)
5 raise AlreadyChecked(room, date.isoformat()) from None
6 except dj.errors.IntegrityError:
----> 7 raise NoReservation(room, date.isoformat()) from None
NoReservation: (2, '2023-11-02')
# checkin a bunch of people
checkins = random.sample(Reservation().fetch('KEY'), k=int(0.9*len(Reservation())))
for r in tqdm.tqdm(checkins):
try:
check_in(**r)
except AlreadyChecked as e:
print(repr(e))
0%| | 0/828 [00:00<?, ?it/s]
100%|██████████| 828/828 [00:02<00:00, 330.90it/s]
CheckIn()
# Try duplicate checkin -- should fail
check_in(**checkins[0])
---------------------------------------------------------------------------
AlreadyChecked Traceback (most recent call last)
Cell In[22], line 2
1 # Try duplicate checkin
----> 2 check_in(**checkins[0])
Cell In[15], line 5, in check_in(room, date)
3 CheckIn.insert1(dict(room=room, date=date))
4 except dj.errors.DuplicateError:
----> 5 raise AlreadyChecked(room, date.isoformat()) from None
6 except dj.errors.IntegrityError:
7 raise NoReservation(room, date.isoformat()) from None
AlreadyChecked: (46, '2023-11-08')
# Try checkout
check_out(2, datetime.date(2023, 10, 2))
---------------------------------------------------------------------------
NotCheckedIn Traceback (most recent call last)
Cell In[23], line 3
1 # Try checkout
----> 3 check_out(2, datetime.date(2023, 10, 2))
Cell In[16], line 7, in check_out(room, date)
5 raise AlreadyChecked(room, date.isoformat()) from None
6 except dj.errors.IntegrityError:
----> 7 raise NotCheckedIn(room, date.isoformat()) from None
NotCheckedIn: (2, '2023-10-02')
# checkout a bunch of people
checkouts = random.sample(CheckIn().fetch('KEY'), k=int(0.9*len(CheckIn())))
for r in tqdm.tqdm(checkouts):
try:
check_out(**r)
except AlreadyChecked as e:
print(repr(e))
4%|▍ | 32/745 [00:00<00:02, 317.79it/s]
100%|██████████| 745/745 [00:02<00:00, 356.68it/s]
# try duplicate checkout -- should fail
check_out(**checkouts[0])
---------------------------------------------------------------------------
AlreadyChecked Traceback (most recent call last)
Cell In[25], line 3
1 # try duplicate checkout
----> 3 check_out(**checkouts[0])
Cell In[16], line 5, in check_out(room, date)
3 CheckOut.insert1(dict(room=room, date=date))
4 except dj.errors.DuplicateError:
----> 5 raise AlreadyChecked(room, date.isoformat()) from None
6 except dj.errors.IntegrityError:
7 raise NotCheckedIn(room, date.isoformat()) from None
AlreadyChecked: (19, '2023-11-03')