Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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"
create table days ( slot unique )
as
select trunc( sysdate ) + level
from dual
connect by level <= 500 ;
500 rows affected

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 )
) ;
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 ;
end ;
/
1 rows affected

select * from hotels ;


ID
NAME_


1000
Tiny Hotel


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


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


-- tests for bookings - unique (roomid, guestid)
insert into bookings ( roomid, guestid, slot ) values ( 2000, 3000, date '2020-10-10' ) ;
1 rows affected

insert into bookings ( roomid, guestid, slot ) values ( 2000, 3000, date '2020-10-10' + 1 ) ;
1 rows affected

-- must fail (cannot boot 10 Oct twice)
insert into bookings ( roomid, guestid, slot ) values ( 2000, 3000, date '2020-10-10' ) ;
ORA-00001: unique constraint (FIDDLE_DELAEPXOIPSRGYEGLWJZ.BOOKINGS_PK) violated

-- must fail (guest 3001 cannot book room 2000 on the same day as guest 3000)
insert into bookings ( roomid, guestid, slot ) values ( 2000, 3001, date '2020-10-10' + 1 ) ;
ORA-00001: unique constraint (FIDDLE_DELAEPXOIPSRGYEGLWJZ.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 rows affected

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
;


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_2
guest_2
11-OCT-20


-- guest_04 books 3 weeks, room 1
begin
  for i in 0 .. 20
  loop
    insert into bookings ( roomid, guestid, slot )
      values ( 2000, 3003, date '2020-12-01' + i ) ;
   end loop;
end ;
/
1 rows affected

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
;


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_2
guest_2
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

Tiny Hotel
room_1
guest_4
04-DEC-20

Tiny Hotel
room_1
guest_4
05-DEC-20

Tiny Hotel
room_1
guest_4
06-DEC-20

Tiny Hotel
room_1
guest_4
07-DEC-20

Tiny Hotel
room_1
guest_4
08-DEC-20

Tiny Hotel
room_1
guest_4
09-DEC-20

Tiny Hotel
room_1
guest_4
10-DEC-20

Tiny Hotel
room_1
guest_4
11-DEC-20

Tiny Hotel
room_1
guest_4
12-DEC-20

Tiny Hotel
room_1
guest_4
13-DEC-20

Tiny Hotel
room_1
guest_4
14-DEC-20

Tiny Hotel
room_1
guest_4
15-DEC-20

Tiny Hotel
room_1
guest_4
16-DEC-20

Tiny Hotel
room_1
guest_4
17-DEC-20

Tiny Hotel
room_1
guest_4
18-DEC-20

Tiny Hotel
room_1
guest_4
19-DEC-20

Tiny Hotel
room_1
guest_4
20-DEC-20

Tiny Hotel
room_1
guest_4
21-DEC-20
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
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
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_2	guest_2	11-OCT-20