Hotel - The DataJoint Book

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. use faker)
The functions checkin(room, date) and checkout(room, date) to check guests in and out. Write a script that invokes checkin and checkout 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¶

%xmode minimal
import datajoint as dj
schema = dj.Schema('hotel')

Exception reporting mode: Minimal

[2025-10-11 03:07:53,440][INFO]: DataJoint 0.14.6 connected to dev@db: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, 410.39it/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))

# 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))

 40%|███▉      | 327/819 [00:00<00:00, 1104.34it/s]

AlreadyChecked(2, '2023-11-02')

100%|██████████| 819/819 [00:00<00:00, 1203.70it/s]

CheckIn()

# Try duplicate checkin -- should fail 
check_in(**checkins[0])

AlreadyChecked: (7, '2023-12-12')

# Try checkout

check_out(2, datetime.date(2023, 10, 2))

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))

100%|██████████| 737/737 [00:00<00:00, 1450.70it/s]

# try duplicate checkout -- should fail

check_out(**checkouts[0])

AlreadyChecked: (28, '2023-11-29')