How to check if hotel room is booked using Oracle SQL constraints

I am an absolute beginner when it comes to SQL, and I’m trying to figure out how to do a simple integrity check. I am modelling a hotel registration system with four tables: Hotel, Room, Booking and Guest. What I’m interested in is in the booking table, which has attributes hotelNo, guestNo, dateFrom, dateTo, roomNo, where the first three are a composite primary key. Now then, the issue at hand is that under the current system, two people might have the same room booked at the same time, which would obviously be a problem in real life. I imagine that a solution might start to look something like

CREATE TABLE Booking(
-- All the attribute definitions go here...
    CONSTRAINT OneGuestAtATime
        CHECK (NOT EXISTS(SELECT(dateFrom FROM Booking ...))) -- I become unsure of what to do around here
);

Bear in mind that while I am a computer engineering student, I’ve never done SQL before, so a hand-holdy walk-through would be appreciated 🙂

Edit: I think my problem would be solved by a constraint to the effect of

CASE 1 (Overlap): “If the dateFrom or dateTo attributes of the record I am trying to insert falls between the dateFrom and dateTo attributes of a given record previously in the table, reject this insertion since there is some overlap between the two bookings.”

CASE 2 (Superset): “If I am attempting to insert record X and there is a record already in the table named Y such that its Y.dateFrom > X.dateFrom and Y.dateTo < X.dateTo, then X is a superset of Y and should be rejected.”

I’m not sure of how to translate that to SQL, though.

Edit 2: The tables

CREATE TABLE Hotel (
    hotelNo NUMBER NOT NULL,
    hotelName VARCHAR2(1024) NOT NULL,
    city VARCHAR2(1024) NOT NULL,
    --
    PRIMARY KEY (hotelNo)
);

CREATE TABLE Room (
    roomNo NUMBER(4,0) NOT NULL,
    hotelNo NUMBER(5,0) NOT NULL,
    type VARCHAR2(1024),
    price NUMBER(6,2) NOT NULL,
    --
    PRIMARY KEY (roomNo, hotelNo),
    FOREIGN KEY (hotelNo) REFERENCES Hotel
);

CREATE TABLE Guest(
    guestNo NUMBER(8,0) NOT NULL,
    guestName VARCHAR(1024) NOT NULL,
    guestAddress VARCHAR(1024) NOT NULL,
    --
    PRIMARY KEY (guestNo)
);

CREATE TABLE Booking(
    hotelNo NUMBER(8,0) NOT NULL,
    guestNo NUMBER(8,0) NOT NULL,
    dateFrom DATE NOT NULL,
    dateTo DATE NOT NULL,
    roomNo NUMBER(4,0) NOT NULL,
    --
    PRIMARY KEY (hotelNo, guestNo, dateFrom),
    FOREIGN KEY (hotelNo) REFERENCES Hotel,
    FOREIGN KEY (guestNo) REFERENCES Guest,
    FOREIGN KEY (hotelNo, roomNo) REFERENCES Room(hotelNo, roomNo),
    --
    CONSTRAINT DateIntegrity
        CHECK (dateFrom < dateTo)

Answer

In your model, the HOTEL and GUEST tables are okay (they may need some more columns later, but that’s not a problem). For ROOM, you have decided to use a composite PK. However, a single column as ID would suffice. In BOOKING, the foreign key referencing HOTEL is redundant. Guests are booking rooms (which have a unique ID, and are tied to a HOTEL already) at certain days.

It may help (your learning) to auto-generate the IDs, and define different “start” values for them – when querying the tables at a later stage, you will recognise the IDs immediately eg HOTELs could have 1000+, rooms could have 2000+ etc (see DDL code below).

When following the link provided by @Abra, you have seen that triggers etc can be used for solving the problem. The solution below is inspired by this answer (also mentioned as “option 4” here), and uses the idea of breaking down bookings into days (“slots”), which can then be used for unique (or PK) constraints. Please read the comments, as they contain more explanations.

DDL code

create table hotels (
  id number generated always as identity start with 1000 primary key
, name_ varchar2( 100 )
) ;

create table rooms (
  id number generated always as identity start with 2000 primary key
, name_ varchar2( 100 )
, hotelid number references hotels( id )
) ;

create table guests (
  id number generated always as identity start with 3000 primary key
, last_name varchar2( 100 )
) ;

-- additional table, populated 500 days "into the future"
-- (no bookings _before_ the sysdate) due to FK constraint in bookings
create table days ( slot primary key )
as
select trunc( sysdate ) + level
from dual
connect by level <= 500 ;  -- Oracle only!

create table bookings (
  roomid number references rooms( id )
, slot date references days( slot ) not null
, guestid number references guests( id ) not null
, constraint bookings_pk primary key( roomid, slot )
) ;

Model

Populate HOTELS, ROOMS, GUESTS

-- For populating HOTELS, ROOMS, and GUESTS, we are just using a little PL/SQL script.
-- You can also use single INSERTs.
begin
-- insert one hotel  
  insert into hotels ( name_ ) values ( 'Tiny Hotel' ) ;
-- insert 8 rooms  
  for r in 1 .. 8
  loop
    insert into rooms( name_, hotelid ) values ( 'room_' || to_char( r ), 1000 ) ;
  end loop ;
-- insert 9 guests
  for g in 1 .. 9
  loop
    insert into guests( last_name ) values ( 'guest_' || to_char( g ) ) ;
  end loop ;
  commit ;
end ;
/

Data in HOTELS, ROOMS, GUESTS

SQL> select * from hotels ;
    ID NAME_        
  1000 Tiny Hotel   

SQL> select * from rooms ;
    ID NAME_      HOTELID 
  2000 room_1        1000 
  2001 room_2        1000 
  2002 room_3        1000 
  2003 room_4        1000 
  2004 room_5        1000 
  2005 room_6        1000 
  2006 room_7        1000 
  2007 room_8        1000 

SQL> select * from guests ;
    ID LAST_NAME   
  3000 guest_1     
  3001 guest_2     
  3002 guest_3     
  3003 guest_4     
  3004 guest_5     
  3005 guest_6     
  3006 guest_7     
  3007 guest_8     
  3008 guest_9 

Testing

-- tests for bookings - unique (roomid, slot)
-- guest 3000 books room 2000, 2 days
-- these 2 inserts must succeed
insert into bookings ( roomid, guestid, slot ) 
  values ( 2000, 3000, date '2020-10-10' ) ;
insert into bookings ( roomid, guestid, slot ) 
  values ( 2000, 3000, date '2020-10-10' + 1 ) ;  -- + 1 here could be + i in a loop ...

-- INSERT must fail - guest 3000 cannot book room 2000 twice (on the same day)
insert into bookings ( roomid, guestid, slot ) 
  values ( 2000, 3000, date '2020-10-10' ) ;
--ERROR at line 1:
--ORA-00001: unique constraint (...BOOKINGS_PK) violated


-- this INSERT must fail
-- guest 3001 cannot have room 2000 on the same day as guest 3000
insert into bookings ( roomid, guestid, slot ) 
  values ( 2000, 3001, date '2020-10-10' + 1 ) ;
--ERROR at line 1:
--ORA-00001: unique constraint (...BOOKINGS_PK) violated


-- guest 3001 can have a different room at the same date, though
-- this insert must succeed
insert into bookings ( roomid, guestid, slot ) 
  values ( 2001, 3001, date '2020-10-10' + 1 ) ;
-- 1 row created.

You can insert more dates for testing by using PL/SQL and coding a loop: see dbfiddle.

Simple query

-- all current bookings
select
  H.name_
, R.name_
, G.last_name
, B.slot
from hotels H
  join rooms R on H.id = R.hotelid
  join bookings B on R.id = B.roomid
  join guests G on G.id = B.guestid
;

-- result
NAME_        NAME_    LAST_NAME   SLOT        
Tiny Hotel   room_1   guest_1     10-OCT-20   
Tiny Hotel   room_1   guest_1     11-OCT-20   
Tiny Hotel   room_1   guest_4     01-DEC-20   
Tiny Hotel   room_1   guest_4     02-DEC-20   
Tiny Hotel   room_1   guest_4     03-DEC-20  
...

Query with more test data (INSERTs: see dbfiddle)

-- query that returns
-- all current bookings with nights_booked etc
-- CAUTION: for recurring bookings (same ROOM and GUEST but different slots)
--   this query will give us misleading results
select
  H.name_
, R.name_
, G.last_name
, count( B.slot)     nights_booked
, min( B.slot )      arrival_date
, max( B.slot ) + 1  departure_date
from hotels H
  join rooms R on H.id = R.hotelid
  join bookings B on R.id = B.roomid
  join guests G on G.id = B.guestid
group by H.name_, R.name_, G.last_name
;

-- result
NAME_        NAME_    LAST_NAME     NIGHTS_BOOKED ARRIVAL_DATE   DEPARTURE_DATE   
Tiny Hotel   room_1   guest_1                   2 10-OCT-20      12-OCT-20        
Tiny Hotel   room_1   guest_4                  21 01-DEC-20      22-DEC-20        
Tiny Hotel   room_2   guest_2                   1 11-OCT-20      12-OCT-20 

As your question was more about modelling and constraints, the queries will probably need more work.